CODEWITHSUNDEEP
mysqltransactions
good_evening
good_evening

Reputation: 21759

Mysql transaction 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

Upvotes: 1

Views: 271

Answers (3)

OMG Ponies
OMG Ponies

Reputation: 332721

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.

Upvotes: 0

nos
nos

Reputation: 229274

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.

Upvotes: 0

user405725
user405725

Reputation:

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.

Upvotes: 2

Related Questions