MySQL commit and transaction

Question

I have a question regarding MySQL commits and transactions. I have a couple of PHP statements that execute MySQL queries. Do I just say the following?

mysql_query("START TRANSACTION");
//more queries here
mysql_query("COMMIT");

What exactly would this do? How does it help? For updates, deletes and insertions I also found this to block other queries from reading:

mysql_query("LOCK TABLES t1 WRITE, t2 WRITE");
//more queries here
mysql_query("UNLOCK TABLES t1, t2");

Would this block other queries whatever nature or only writes/selects?

Another question: Say one query is running and blocks other queries. Another query tries to access blocked data - and it sees that it is blocked. How does it proceed? Does it wait until the data is unblocked again and re-execute the query? Does it just fail and needs to be repeated? If so, how can I check?

Thanks a lot!

Dennis

Solution

In InnoDB, you do not need to explicitly start or end transactions for single queries if you have not changed the default setting of autocommit, which is "on". If autocommit is on, InnoDB automatically encloses every single SQL query in a transaction, which is the equivalent of START TRANSACTION; query; COMMIT;.

If you explicitly use START TRANSACTION in InnoDB with autocommit on, then any queries executed after a START TRANSACTION statement will either all be executed, or all of them will fail. This is useful in banking environments, for example: if I am transferring $500 to your bank account, that operation should only succeed if the sum has been subtracted from my bank balance and added to yours. So in this case, you'd run something like

START TRANSACTION;
UPDATE customers SET balance = balance - 500 WHERE customer = 'Daan';
UPDATE customers SET balance = balance + 500 WHERE customer = 'Dennis';
COMMIT;

This ensures that either both queries will run successfully, or none, but not just one. This post has some more on when you should use transactions.

In InnoDB, you will very rarely have to lock entire tables; InnoDB, unlike MyISAM, supports row-level locking. This means clients do not have to lock the entire table, forcing other clients to wait. Clients should only lock the rows they actually need, allowing other clients to continue accessing the rows they need.

You can read more about InnoDB transactions here. Your questions about deadlocking are answered in sections 14.2.8.8 and 14.2.8.9 of the docs. If a query fails, your MySQL driver will return an error message indicating the reason; your app should then reissue the queries if required.

Finally, in your example code, you used mysql_query. If you are writing new code, please stop using the old, slow, and deprecated mysql_ library for PHP and use mysqli_ or PDO instead :)

Comments

[Dennis Hackethal]

awesome, thank you! so does InnoDB automatically only lock rows? And how can I check whether my table has auto commit on? and how can I set an index on it (I am using phpmyadmin)

[Daan]

You can either issue a SHOW VARIABLES LIKE 'autocommit' query, or click variables on the phpMyAdmin home page. Creating an index is done by going to the appropriat database and table, clicking structure and using the form you see there to create an index. Or you can issue SQL statements, e.g. ALTER TABLE table ADD INDEX (field). I'm not sure what you mean by "does InnoBD automatically only lock rows" - it certainly will never automatically lock an entire table, if that's what you mean :)

[Dennis Hackethal]

Autocommit is on :) Do primary keys always serve as an index? I have primary keys in my tables and wanted to add an index but it said it could only be the same as the primary key...

[Daan]

Yes, primary keys serve as an index :)

[Atul]

From MySQL documentation: To disable autocommit mode implicitly for a single series of statements, use the START TRANSACTION statement. With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state.