LOCK TABLES is not
transaction-safe and implicitly commits any active
transaction before attempting to lock the tables.
TABLES implicitly commits any active transaction,
but only if
LOCK TABLES has
been used to acquire table locks. For example, in the
following set of statements,
TABLES releases 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
TRANSACTION) implicitly commits any current
transaction and releases existing table locks.
FLUSH TABLES WITH
READ LOCK acquires a global read lock and not
table locks, so it is not subject to the same behavior as
LOCK TABLES and
TABLES with respect to table locking and implicit
commits. For example,
TRANSACTION does not release the global read lock.
See Section 18.104.22.168, “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
TABLES with transactional tables, such as
InnoDB tables, is to begin a transaction
SET autocommit = 0 (not
TRANSACTION) followed by
TABLES, and to not call
TABLES until you commit the transaction
explicitly. For example, if you need to write to table
t1 and 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
InnoDB internally takes its own table
lock, and MySQL takes its own table lock.
InnoDB releases its internal table lock
at the next commit, but for MySQL to release its table lock,
you have to call
TABLES. You should not have
autocommit = 1, because
InnoDB releases its internal table
lock immediately after the call of
TABLES, and deadlocks can very easily happen.
InnoDB does not acquire the internal
table lock at all if
1, to help old applications avoid unnecessary
does not release table locks.