Skip navigation links
13.3.5.2 LOCK TABLES and Triggers »
Section Navigation      [Toggle]

13.3.5.1. Interaction of Table Locking and Transactions

LOCK TABLES and UNLOCK TABLES interact with the use of transactions as follows:

  • 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: 

    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 LOCK acquires a global read lock and not table locks, so it is not subject to the same behavior as LOCK TABLES and UNLOCK TABLES with respect to table locking and implicit commits. For example, START TRANSACTION does not release the global read lock. See Section 13.7.6.3, “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 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: 

    SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

    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.

  • ROLLBACK does not release table locks.

Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices

User Comments

Posted by lvaro G. Vicario on February 9 2011 10:25am[Delete] [Edit]

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:

SET autocommit=0;
LOCK TABLES foo WRITE;
INSERT INTO foo (foo_name) VALUES ('John');
LOCK TABLES bar WRITE; -- Implicit commit
ROLLBACK; -- No effect: data already committed

In many cases, LOCK TABLES can be replaced by SELECT ... FOR UPDATE which is fully transaction aware and doesn't need any special syntax:

START TRANSACTION;
SELECT COUNT(*) FROM foo FOR UPDATE; -- Lock issued
INSERT INTO foo (foo_name) VALUES ('John');
SELECT COUNT(*) FROM bar FOR UPDATE; -- Lock issued, no side effects
ROLLBACK; -- Rollback works as expected

See "13.6.8.3 SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE Locking Reads" for the details—please note that "SELECT COUNT(*)" was just a (meaningless) example.

Posted by anerudh balaji on February 20 2012 3:08am[Delete] [Edit]

I really doubt that! I tried executing these transactions step by step in two instances of Mysql workbench and they were going fine :

Instance 1 :
START TRANSACTION;
set autocommit=0; -- i had the doubt that select statements triggered automatic commits.
SELECT COUNT(*) FROM student FOR UPDATE; -- Lock issued
INSERT INTO student VALUES (5,'John');

Instance 2 :
Select* from student ; -- after the select count statement
select* from student; -- after the insert

There was no lock problem at all, which is kind of odd. However when I used the explicit lock approach there were lock problems.

Add your own comment.