MySQL: LOCK TABLES with autocommit vs. START TRANSACTION

Question

In the MySQL docs there is a statement I don't understand:

The correct way to use LOCK TABLES and UNLOCK TABLES with transactional tables, such as InnoDB tables, is to begin a transaction with SET autocommit = 0 (not START TRANSACTION) followed by LOCK TABLES, and to not call UNLOCK TABLES until you commit the transaction explicitly. (https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html)

Even after searching a lot in the docs and studying the description for "autocommit" and "START TRANSACTION", I really don't understand why to use autocommit instead of START TRANSACTION. Any ideas? Thanks

Answer 1

LOCK TABLES implicitly commits a transaction, according to https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html

So if you were to do the following sequence, it would not do what you intend:

BEGIN;
/* I think I'm in transaction 1 */
LOCK TABLES ...;
/* but that implicitly committed transaction 1 */

UPDATE blab blah ...;
/* I think I'm still in transaction 1, but this update caused 
   a new transaction 2 to begin and autocommit */ 
UPDATE yadda yadda ...;
/* I think I'm still in transaction 1, but this update caused 
   a new transaction 3 to begin and autocommit */ 
COMMIT;

The point of using transactions is when you need to run multiple updates, but you want the result of all updates to commit together or not at all.

The above example does not commit the two updates together atomically. They each have their own autocommitted transaction. Therefore one might succeed but the other doesn't.

The recommendation is to do the following:

SET autocommit=0;
LOCK TABLES ...;
/* that implicitly committed any outstanding transaction, but that's OK */

UPDATE blab blah ...;
/* because autocommit=0, a new DML statement implicitly starts a new transaction */ 
UPDATE yadda yadda ...;
/* the new transaction is still open */
COMMIT;

UNLOCK TABLES;

This commits both updates together, atomically.

Why not just LOCK TABLES and then BEGIN to start a transaction?

https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html says:

Beginning a transaction (for example, with START TRANSACTION) implicitly commits any current transaction and releases existing table locks.

That seems strange to me. An explicit BEGIN (or START TRANSACTION) releases table locks, but an implicit start of a transaction does not? That feature is full of WTF, in my opinion. But that's what's documented.