LOCK TABLESis not transaction-safe and implicitly commits any active transaction before attempting to lock the tables.
UNLOCK TABLESimplicitly commits any active transaction, but only if
LOCK TABLEShas been used to acquire table locks. For example, in the following set of statements,
UNLOCK TABLESreleases the global read lock but does not commit the transaction because no table locks are in effect:
FLUSH TABLES WITH READ LOCK; START TRANSACTION; SELECT ... ; UNLOCK TABLES;
Beginning a transaction (for example, with
START TRANSACTION) implicitly commits any current transaction and releases existing table locks.
FLUSH TABLES WITH READ LOCKacquires a global read lock and not table locks, so it is not subject to the same behavior as
UNLOCK TABLESwith respect to table locking and implicit commits. For example,
START TRANSACTIONdoes not release the global read lock. See Section 188.8.131.52, “FLUSH Syntax”.
Other statements that implicitly cause transactions to be committed do not release existing table locks. For a list of such statements, see Section 13.3.3, “Statements That Cause an Implicit Commit”.
The correct way to use
UNLOCK TABLESwith transactional tables, such as
InnoDBtables, is to begin a transaction with
SET autocommit = 0(not
START TRANSACTION) followed by
LOCK TABLES, and to not call
UNLOCK TABLESuntil you commit the transaction explicitly. For example, if you need to write to table
t1and read from table
t2, you can do this:
SET autocommit=0; LOCK TABLES t1 WRITE, t2 READ, ...; ... do something with tables t1 and t2 here ... COMMIT; UNLOCK TABLES;
When you call
InnoDBinternally takes its own table lock, and MySQL takes its own table lock.
InnoDBreleases its internal table lock at the next commit, but for MySQL to release its table lock, you have to call
UNLOCK TABLES. You should not have
autocommit = 1, because then
InnoDBreleases its internal table lock immediately after the call of
LOCK TABLES, and deadlocks can very easily happen.
InnoDBdoes not acquire the internal table lock at all if
autocommit = 1, to help old applications avoid unnecessary deadlocks.
ROLLBACKdoes not release table locks.