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.
