Mysql transaction question

Question

My code:

mysql_query("SET AUTOCOMMIT=0");
mysql_query("START TRANSACTION");

insert_query, update_query1, update_query2

mysql_query("COMMIT");
update_query3

Why does update_query3 query doesn't work if I put it after COMMIT? It works if I put it before the COMMIT. Why is that? It's really strange.

Thank you

Answer 1

Because COMMIT (or ROLLBACK for that matter) marks the end of the transaction.

You'd have to use:

mysql_query("COMMIT AND CHAIN");

..to create a new transaction to begin as soon as the current one ends, and the new transaction has the same isolation level as the just-terminated transaction.

But it still means you need to have:

mysql_query("COMMIT");

...after the update_query3 to commit the changes.

Answer 2

First you disable auto-commit. Then you begin transaction, make changes and commit them. Then you execute another query that implicitly starts another transaction. In order for changes to get committed, you have to call "COMMIT" explicitly because auto-commit is turned off.

Answer 3

update_query3 isn't part of any existing transaction. So it starts a new (implicit) transaction.

You never commit the transaction started by update_query3, so it'll get rolled back when you close the connection.