Hi there guys,

My doubt is related with MySQL and Transactions (InnoDB) so here it
goes:

I have a query like this:

START TRANSACTION;
UPDATE sections SET position=position-%d WHERE position > %d;
DELETE FROM sections WHERE id IN %s;
COMMIT;

Since I'm using PHP and MySQL, I must use mysql_query function that
only allows one query at a time, so I'll have 4 queries.

I've been advised to execute the four queries like this:

$query1 = 'START TRANSACTION';
$result1 = $db->query($query1);
if (!$result1) die("transaction failed at step1");

$query2 = 'UPDATE sections SET position=position-%d WHERE position >
%d';
$result2 = $db->query(sprintf($query2, count($id), $db-
>result($result)));

if (!$result2) die("transaction failed at step2");

$query3 = 'DELETE FROM sections WHERE id IN %s';
$result3 = $db->query(sprintf($query3, $ids));
if (!$result3) die("transaction failed at step3");

$query4 = 'COMMIT';
$result4 = $db->query($query4);
if (!$result4) die("transaction failed at step4");
echo 'Done...';

PS: (Die it's just used has an example, wont use it in my final
script).

1. In this situation, should I assume that if query number 4 isn't
executed with success, the informations (update + delete) wont be
saved, since the COMMIT wasn't done with success? If not how should I
handle this?

2. If one of the first three steps fail, do I need to use ROLLBACK? Or
should I assume that (like the one above) COMMIT wasn't done, so
informations wont be saved?

3. What is the best way and the safest to do this kind of query
without avoiding the risk of loose any data.


Thanks in advance.

On Aug 4, 3:38 pm, "João Morais" <jcsmor...@gmail.com> wrote:
> Hi there guys,
>
> My doubt is related with MySQL and Transactions (InnoDB) so here it
> goes:
>
> I have a query like this:
>
> START TRANSACTION;
> UPDATE sections SET position=position-%d WHERE position > %d;
> DELETE FROM sections WHERE id IN %s;
> COMMIT;
>
> Since I'm using PHP and MySQL, I must use mysql_query function that
> only allows one query at a time, so I'll have 4 queries.
>
> I've been advised to execute the four queries like this:
>
> $query1 = 'START TRANSACTION';
> $result1 = $db->query($query1);
> if (!$result1) die("transaction failed at step1");
>
> $query2 = 'UPDATE sections SET position=position-%d WHERE position >
> %d';
> $result2 = $db->query(sprintf($query2, count($id), $db-
>
> >result($result)));
>
> if (!$result2) die("transaction failed at step2");
>
> $query3 = 'DELETE FROM sections WHERE id IN %s';
> $result3 = $db->query(sprintf($query3, $ids));
> if (!$result3) die("transaction failed at step3");
>
> $query4 = 'COMMIT';
> $result4 = $db->query($query4);
> if (!$result4) die("transaction failed at step4");
> echo 'Done...';
>
> PS: (Die it's just used has an example, wont use it in my final
> script).
>
> 1. In this situation, should I assume that if query number 4 isn't
> executed with success, the informations (update + delete) wont be
> saved, since the COMMIT wasn't done with success? If not how should I
> handle this?

It won't be saved, but it won't be rolled back, either. The
transaction will be left open and you might run into other problems
(like other connections that are waiting for the transaction to
finish). I'm not sure if open transactions are automatically rolled
back when the connection is closed -- but I wouldn't rely on that
behavior anyway.

>
> 2. If one of the first three steps fail, do I need to use ROLLBACK? Or
> should I assume that (like the one above) COMMIT wasn't done, so
> informations wont be saved?

As I said before, I'm not sure if the transaction is automatically
rolled back -- so it's always best to explicitly roll back the
transaction upon failure.

>
> 3. What is the best way and the safest to do this kind of query
> without avoiding the risk of loose any data.

Begin your transaction. After each step, check for success or
failure. If it fails, roll back the transaction and don't do anything
else. If it succeeds, continue on to the next step. When every step
has completed with success, commit the transaction.

ZeldorBlat wrote:
> On Aug 4, 3:38 pm, "João Morais" <jcsmor...@gmail.com> wrote:
>
>>Hi there guys,
>>
>>My doubt is related with MySQL and Transactions (InnoDB) so here it
>>goes:
>>
>>I have a query like this:
>>
>> START TRANSACTION;
>> UPDATE sections SET position=position-%d WHERE position > %d;
>> DELETE FROM sections WHERE id IN %s;
>> COMMIT;
>>
>>Since I'm using PHP and MySQL, I must use mysql_query function that
>>only allows one query at a time, so I'll have 4 queries.
>>
>>I've been advised to execute the four queries like this:
>>
>> $query1 = 'START TRANSACTION';
>> $result1 = $db->query($query1);
>> if (!$result1) die("transaction failed at step1");
>>
>> $query2 = 'UPDATE sections SET position=position-%d WHERE position >
>>%d';
>> $result2 = $db->query(sprintf($query2, count($id), $db-
>>
>>
>>>result($result)));
>>
>> if (!$result2) die("transaction failed at step2");
>>
>> $query3 = 'DELETE FROM sections WHERE id IN %s';
>> $result3 = $db->query(sprintf($query3, $ids));
>> if (!$result3) die("transaction failed at step3");
>>
>> $query4 = 'COMMIT';
>> $result4 = $db->query($query4);
>> if (!$result4) die("transaction failed at step4");
>> echo 'Done...';
>>
>>PS: (Die it's just used has an example, wont use it in my final
>>script).
>>
>>1. In this situation, should I assume that if query number 4 isn't
>>executed with success, the informations (update + delete) wont be
>>saved, since the COMMIT wasn't done with success? If not how should I
>>handle this?
>
>
> It won't be saved, but it won't be rolled back, either. The
> transaction will be left open and you might run into other problems
> (like other connections that are waiting for the transaction to
> finish). I'm not sure if open transactions are automatically rolled
> back when the connection is closed -- but I wouldn't rely on that
> behavior anyway.
>
>
>>2. If one of the first three steps fail, do I need to use ROLLBACK? Or
>>should I assume that (like the one above) COMMIT wasn't done, so
>>informations wont be saved?
>
>
> As I said before, I'm not sure if the transaction is automatically
> rolled back -- so it's always best to explicitly roll back the
> transaction upon failure.

If the application dies without committing the transaction,
the transaction had better be rolled back. That's the whole
point of rollback.

John Nagle

John Nagle wrote:
> ZeldorBlat wrote:
>> On Aug 4, 3:38 pm, "João Morais" <jcsmor...@gmail.com> wrote:
>>
>>> Hi there guys,
>>>
>>> My doubt is related with MySQL and Transactions (InnoDB) so here it
>>> goes:
>>>
>>> I have a query like this:
>>>
>>> START TRANSACTION;
>>> UPDATE sections SET position=position-%d WHERE position > %d;
>>> DELETE FROM sections WHERE id IN %s;
>>> COMMIT;
>>>
>>> Since I'm using PHP and MySQL, I must use mysql_query function that
>>> only allows one query at a time, so I'll have 4 queries.
>>>
>>> I've been advised to execute the four queries like this:
>>>
>>> $query1 = 'START TRANSACTION';
>>> $result1 = $db->query($query1);
>>> if (!$result1) die("transaction failed at step1");
>>>
>>> $query2 = 'UPDATE sections SET position=position-%d WHERE
>>> position >
>>> %d';
>>> $result2 = $db->query(sprintf($query2, count($id), $db-
>>>
>>>
>>>> result($result)));
>>>
>>> if (!$result2) die("transaction failed at step2");
>>>
>>> $query3 = 'DELETE FROM sections WHERE id IN %s';
>>> $result3 = $db->query(sprintf($query3, $ids));
>>> if (!$result3) die("transaction failed at step3");
>>>
>>> $query4 = 'COMMIT';
>>> $result4 = $db->query($query4);
>>> if (!$result4) die("transaction failed at step4");
>>> echo 'Done...';
>>>
>>> PS: (Die it's just used has an example, wont use it in my final
>>> script).
>>>
>>> 1. In this situation, should I assume that if query number 4 isn't
>>> executed with success, the informations (update + delete) wont be
>>> saved, since the COMMIT wasn't done with success? If not how should I
>>> handle this?
>>
>>
>> It won't be saved, but it won't be rolled back, either. The
>> transaction will be left open and you might run into other problems
>> (like other connections that are waiting for the transaction to
>> finish). I'm not sure if open transactions are automatically rolled
>> back when the connection is closed -- but I wouldn't rely on that
>> behavior anyway.
>>
>>
>>> 2. If one of the first three steps fail, do I need to use ROLLBACK? Or
>>> should I assume that (like the one above) COMMIT wasn't done, so
>>> informations wont be saved?
>>
>>
>> As I said before, I'm not sure if the transaction is automatically
>> rolled back -- so it's always best to explicitly roll back the
>> transaction upon failure.
>
> If the application dies without committing the transaction,
> the transaction had better be rolled back. That's the whole
> point of rollback.
>
> John Nagle

It depends on how the program exits.

For instance, if a PHP program just exits normally without closing the
connection, should any open transaction be rolled back? Or what if the
program closes the connection without committing or rolling back?
That's basically what happens when the programmer calls die() (which I
do not recommend).

I don't know for sure about PHP - but I know other databases have
different behavior. For instance, with DB2, it is documented that if a
C program exits with a RC of 0, the transaction will be committed. IF
it's a non-zero return code, the transaction will be rolled back.

However, I haven't been able to find anything similar in the MySQL doc,
and nothing pertaining to PHP in the MySQL doc.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================