13.3.5.1 Interaction of Table Locking and Transactions

LOCK TABLES is not
transaction-safe and implicitly commits any active
transaction before attempting to lock the tables.

UNLOCK
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,
UNLOCK
TABLES releases the global read lock but does not
commit the transaction because no table locks are in effect:

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. For example, if you need to write to table
t1 and read from table
t2, you can do this:

When you call LOCK TABLES,
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
UNLOCK
TABLES. You should not have
autocommit = 1, because
then InnoDB releases its internal table
lock immediately after the call of LOCK
TABLES, and deadlocks can very easily happen.
InnoDB does not acquire the internal
table lock at all if autocommit =
1, to help old applications avoid unnecessary
deadlocks.

LOCK TABLES does not play well with transactions. Even if you use the "SET autommit=0" syntax you can find undesired side effects. For instance, issuing a second LOCK TABLES query within a transaction will COMMIT your pending changes: