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 locks.
Other statements that implicitly cause transactions to be committed do not release existing locks. For a list of such statements, see Section 12.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.
Starting from 4.1.9,
InnoDB does not
acquire the internal table lock at all if
autocommit = 1, to help old
applications avoid unnecessary deadlocks.
does not release 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. See Section 18.104.22.168, “