Skip navigation links

Additional languages
12.4.6 SET TRANSACTION Syntax »
Section Navigation      [Toggle]

12.4.5. LOCK TABLES and UNLOCK TABLES Syntax

LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type:
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
  | IN SHARE MODE [NOWAIT]
  | IN EXCLUSIVE MODE [NOWAIT]

UNLOCK TABLES

MySQL enables client sessions to acquire table locks explicitly for the purpose of cooperating with other sessions for access to tables, or to prevent other sessions from modifying tables during periods when a session requires exclusive access to them. A session can acquire or release locks only for itself. One session cannot acquire locks for another session or release locks held by another session.

LOCK TABLES explicitly acquires non-transactional or transactional table locks for the current client session.

Table locks can be acquired for base tables or views. You must have the LOCK TABLES privilege, and the SELECT privilege for each object to be locked. (For view locking, LOCK TABLES adds all base tables used in the view to the set of tables to be locked and locks them automatically.)

MySQL 6.0 supports non-transactional and transactional locks. These are intended for use in non-transactional or transactional context, respectively. Non-transactional locks may be used to emulate transactions or to get more speed when updating tables. This is explained in more detail later in this section.

UNLOCK TABLES explicitly releases non-transactional table locks held by the current session. Transactional table locks are released by ending the current transaction.

Another use for UNLOCK TABLES is to release the global read lock acquired with FLUSH TABLES WITH READ LOCK. (You can lock all tables in all databases with a read lock with the FLUSH TABLES WITH READ LOCK statement. See Section 12.5.6.2, “FLUSH Syntax”. This is a very convenient way to get backups if you have a filesystem such as Veritas that can take snapshots in time.)

A table lock protects only against inappropriate reads or writes by other sessions. The session holding the lock can perform table-level operations such as DROP TABLE. Truncate operations are not transaction-safe, so an error occurs if the session attempts one during an active transaction or while holding a table lock.

As of MySQL 6.0.3, DROP TABLE is allowed only if you have acquired a WRITE lock with LOCK TABLES, or if you hold no locks, or if the table is a TEMPORARY table. (Previously, if other tables were locked, you could drop a table while holding a read lock or no lock for it, which could lead to deadlocks between sessions. The current stricter behavior means that some usage scenarios will fail when previously they did not.)

The following discussion applies only to non-TEMPORARY tables. It is allowable (but unnecessary) to lock a TEMPORARY table because the table can be accessed freely by the session within which it was created, regardless of what other locking may be in effect.

Rules for Lock Acquisition

To acquire table locks, use the LOCK TABLES statement.

Non-transactional locks. MySQL supports non-transactional read and write table locks. These can be acquired in non-transactional contexts (that is, when autocommit is enabled).

READ [LOCAL] lock:

  • The session that holds the lock can read the table (but not write it).

  • Multiple sessions can acquire a READ lock for the table at the same time.

  • Other sessions can read the table without explicitly acquiring a READ lock.

  • The LOCAL modifier enables concurrent inserts by other sessions to proceed while the lock is held. (See Section 7.3.3, “Concurrent Inserts”.)

[LOW_PRIORITY] WRITE lock:

  • The session that holds the lock can read and write the table.

  • Only the session that holds the lock can access the table. No other session can access it until the lock is released.

  • Lock requests for the table by other sessions block while the WRITE lock is held.

  • The LOW_PRIORITY modifier affects lock scheduling if the WRITE lock request must wait. Normally, requests for WRITE locks take priority over requests for READ locks. With LOW_PRIORITY, the WRITE lock will not be granted until there are no READ lock requests by other sessions.

A session that requires non-transactional locks must acquire all the locks that it needs in a single LOCK TABLES statement.

A session that holds non-transactional locks can access only the locked tables while the locks are held.

A session cannot hold non-transactional locks and use transactions at the same time. Acquisition of a non-transactional lock implicitly commits any active transaction for the current session, and beginning a transaction implicitly releases all locks held by the session. (Additional information about the interaction between table locking and transactions is given later in this section.)

The difference between READ and READ LOCAL is that READ LOCAL allows non-conflicting INSERT statements (concurrent inserts) to execute while the lock is held. However, READ LOCAL cannot be used if you are going to manipulate the database using processes external to the server while you hold the lock. For InnoDB tables, READ LOCAL is the same as READ.

WRITE locks normally have higher priority than READ locks to ensure that updates are processed as soon as possible. This means that if one session obtains a READ lock and then another session requests a WRITE lock, subsequent READ lock requests wait until the session that requested the WRITE lock has obtained the lock and released it. A request for a LOW_PRIORITY WRITE lock, by contrast, allows subsequent READ lock requests by other sessions to be satisfied first if they occur while the LOW_PRIORITY WRITE request is waiting. You should use LOW_PRIORITY WRITE locks only if you are sure that eventually there will be a time when no sessions have a READ lock. For InnoDB tables in transactional mode (autocommit = 0), a waiting LOW_PRIORITY WRITE lock acts like a regular WRITE lock and causes subsequent READ lock requests to wait.

LOCK TABLES works as follows for non-transactional locks:

  1. Sort all tables to be locked in an internally defined order. From the user standpoint, this order is undefined.

  2. If a table is to be locked with a read and a write lock, put the write lock request before the read lock request.

  3. Lock one table at a time until the session gets all locks.

This policy ensures that table locking is deadlock free. There are, however, other things you need to be aware of about this policy: If you are using a LOW_PRIORITY WRITE lock for a table, it means only that MySQL waits for this particular lock until there are no other sessions that want a READ lock. When the session has gotten the WRITE lock and is waiting to get the lock for the next table in the lock table list, all other sessions wait for the WRITE lock to be released. If this becomes a serious problem with your application, you should consider converting some of your tables to transaction-safe tables.

Transactional locks. As of MySQL 6.0.3, MySQL supports transactional shared and exclusive table locks that do not commit transactions automatically. These locks apply only for transactional storage engines that support them and only during a transaction (that is, when autocommit is disabled).

Currently, only InnoDB supports transactional locks. For other transactional storage engines, for non-transactional storage engines, or when autocommit is enabled, requests for transactional locks are converted to requests for non-transactional locks, as described later.

IN SHARE MODE [NOWAIT] lock:

  • The session that holds the lock can read the table, and can also write the table under some circumstances.

  • Multiple sessions can acquire an IN SHARE MODE lock for the table at the same time.

  • Other sessions can read the table without explicitly acquiring an IN SHARE MODE lock.

IN EXCLUSIVE MODE [NOWAIT] lock:

  • The session that holds the lock can read and write the table.

  • Only the session that holds the lock can access the table. No other session can access it until the lock is released.

  • Lock requests for the table by other sessions block while the IN EXCLUSIVE MODE lock is held.

By default, LOCK TABLES waits if all requested locks cannot be acquired immediately (for example, if the requests cannot be granted due to locks held by other sessions). For transactional locks, the NOWAIT modifier can be given. The intent of this modifier is that the lock request will fail with an error if the lock cannot be acquired immediately, but NOWAIT is not currently implemented.

A session that requires transactional locks need not acquire them all in a single LOCK TABLES statement. A session can acquire transactional locks sequentially with multiple LOCK TABLES statements, each one adding new locks to the current set of locks. It is even possible to acquire additional transactional locks on a table for which the session already holds transactional locks.

A session that holds transactional locks can access non-locked tables while the locks are held.

Transactional locks are not specific to reading or writing. Both operations are allowed to the holder of the lock, whether it is shared or exclusive, with some restrictions:

  • The holder of an IN EXCLUSIVE MODE lock has exclusive access to read and write the table and no other session can lock the table.

  • The holder of an IN SHARE MODE lock has shared access to read the table. The lock holder can also write the table, as long as no other session also has a shared lock for the table. If a session that holds a shared lock has written to the table, no other session can acquire a lock for the table.

For LOCK TABLES statements that involve a mix of non-transactional and transactional locks, requests for transactional locks are converted to requests for non-transactional locks. This occurs because a session can hold multiple locks at a time, but cannot hold a mix of non-transactional and transactional locks:

  • It is permissible to hold multiple READ or WRITE locks at the same time.

  • It is permissible to hold multiple IN SHARE MODE or IN EXCLUSIVE MODE locks at the same time.

  • It is not permissible to hold a READ or WRITE lock at the same time as an IN SHARE MODE or IN EXCLUSIVE MODE lock.

For example, these operations are allowed because they request only non-transactional locks, or only transactional locks: 

LOCK TABLES t1 READ, t2 WRITE, t3 READ;
LOCK TABLES t4 IN SHARE MODE, t5 IN EXCLUSIVE MODE, t6 IN SHARE MODE;

But these operations cannot be processed as requested because they attempt to acquire a mix of non-transactional and transactional locks: 

LOCK TABLES t1 READ, t2 IN SHARE MODE;
LOCK TABLES t3 WRITE, t4 IN EXCLUSIVE MODE;

For the latter statements, the requests for transactional locks are converted to requests for non-transactional locks. Lock conversions may succeed or fail, as described later.

You cannot refer to a locked table multiple times in a single query using the same name. Use aliases instead, and obtain a separate lock for the table and each alias: 

mysql> LOCK TABLE t WRITE, t AS t1 READ;
mysql> INSERT INTO t SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> INSERT INTO t SELECT * FROM t AS t1;

The error occurs for the first INSERT because there are two references to the same name for a locked table. The second INSERT succeeds because the references to the table use different names.

If your statements refer to a table by means of an alias, you must lock the table using that same alias. It does not work to lock the table without specifying the alias: 

mysql> LOCK TABLE t READ;
mysql> SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias' was not locked with LOCK TABLES

Conversely, if you lock a table using an alias, you must refer to it in your statements using that alias: 

mysql> LOCK TABLE t AS myalias READ;
mysql> SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> SELECT * FROM t AS myalias;

Rules for Lock Release

When the table locks held by a session are released, they are all released at the same time. A session can release its locks explicitly, or locks may be released implicitly under certain conditions.

Rules for lock release when a session holds non-transactional locks:

  • A session can release its locks explicitly with UNLOCK TABLES.

  • If a session issues a LOCK TABLES statement to acquire a lock while already holding non-transactional locks, its existing locks are released implicitly before the new locks are granted.

  • If a session begins a transaction, an implicit UNLOCK TABLES is performed, which causes existing locks to be released.

  • If the connection terminates, the server releases the session's locks.

Rules for lock release when a session holds transactional locks:

  • UNLOCK TABLES does not release transactional locks.

  • Ending a transaction explicitly, by either COMMIT or ROLLBACK, releases existing locks.

  • Beginning a transaction implicitly commits the current transaction, which releases existing locks.

  • If the session issues a LOCK TABLES request for a non-transactional lock, that implicitly commits the current transaction, which releases existing locks.

  • Any other statement that causes an implicit commit releases the existing locks. For a list, see Section 12.4.3, “Statements That Cause an Implicit Commit”.

  • If the connection terminates, the server implicitly rolls back the current transaction and releases the session's locks.

If a client connection drops, the server releases table locks held by the client. If the client reconnects, the locks will no longer be in effect. For this reason, clients may wish to disable auto-reconnect. With auto-reconnect in effect, the client is not notified if reconnect occurs but any table locks will have been lost. With auto-reconnect disabled, if the connection drops, an error occurs for the next statement issued. The client can detect the error and take appropriate action such as reacquiring the locks. See Section 27.2.13, “Controlling Automatic Reconnect Behavior”.

Note

If you use ALTER TABLE on a locked table, it may become unlocked. See Section B.1.7.1, “Problems with ALTER TABLE”.

Rules for Lock Conversion

Under some circumstances, a request for a transactional lock cannot be granted:

  • A session cannot use LOCK TABLES to simultaneously acquire transactional and non-transactional locks.

  • A session cannot acquire transactional locks while currently holding non-transactional locks.

  • A session cannot acquire transactional locks when not in transactional context or for storage engines that do not support them. This includes the following conditions:

    • Autocommit mode is enabled because the session has not used START TRANSACTION or SET AUTOCOMMIT = 0.

    • The table to be locked is non-transactional (for example, MyISAM).

    • The table to be locked is transactional but the storage engine does not support transactional locks. (Currently, only InnoDB supports transactional locks.)

When a transactional lock cannot be granted, the request is converted to a request for a non-transactional lock. The conversion is handled as follows:

  • If strict SQL mode is enabled, lock conversion is prohibited and an error occurs. 

    mysql> SET sql_mode = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> LOCK TABLES t1 READ, t2 IN SHARE MODE;
ERROR 1615 (HY000): Cannot convert to non-transactional lock in
strict mode on 't2'

  • Otherwise, conversion occurs and a warning is generated. 

    mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> LOCK TABLES t1 READ, t2 IN SHARE MODE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1614 | Converted to non-transactional lock on 't2' | 
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

  • When conversion occurs, IN SHARE MODE is converted to READ and IN EXCLUSIVE MODE is converted to WRITE.

The following notes describe what happens when a session already holds one type of lock and then requests another lock:

  • Session holds a non-transactional lock, requests a non-transactional lock:

    1. An implicit UNLOCK TABLES occurs, which releases the existing non-transactional lock.

    2. The new non-transactional lock is granted.

  • Session holds a non-transactional lock, requests a transactional lock:

    1. The request is converted to a request for a non-transactional lock.

    2. An implicit UNLOCK TABLES occurs, which releases the existing non-transactional lock.

    3. The new non-transactional lock is granted.

  • Session holds a transactional lock, requests a transactional lock

    The new transactional lock is granted without releasing the existing transactional lock.

  • Session holds a transactional lock, requests a non-transactional lock

    1. An implicit commit occurs, which releases the existing transactional lock.

    2. The new non-transactional lock is granted.

    Thus, the following sequence results in insertion of a row, even though there is no explicit commit: 

    DROP TABLE IF EXISTS t;
CREATE TABLE t (i INT) ENGINE = InnoDB;
START TRANSACTION;
LOCK TABLE t IN EXCLUSIVE MODE;
INSERT INTO t VALUES(1);
LOCK TABLE t READ;
SELECT * FROM t;

Interaction of Table Locking and Transactions

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

  • When used to acquire non-transactional locks, 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 non-transactional 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 non-transactional 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 locks.

  • Several other statements also implicitly cause transactions to be committed and release existing locks. For a list, see Section 12.4.3, “Statements That Cause an Implicit Commit”.

  • The correct way to use LOCK TABLES and UNLOCK TABLES with non-tranactional locks and 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. 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. Note that we do not acquire the InnoDB internal table lock at all if AUTOCOMMIT=1, to help old applications avoid unnecessary deadlocks.

  • ROLLBACK does not release non-transactional 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. See Section 12.5.6.2, “FLUSH Syntax”.

Other Table-Locking Notes

You can safely use KILL to terminate a session that is waiting for a table lock. See Section 12.5.6.3, “KILL Syntax”.

You should not lock any tables that you are using with INSERT DELAYED because in that case the INSERT is performed by a separate thread.

For some operations, system tables in the mysql database must be accessed. For example, the HELP statement requires the contents of the server-side help tables, and CONVERT_TZ() might need to read the time zone tables. The server implicitly locks the system tables for reading as necessary so that you need not lock them explicitly. These tables are treated as just described: 

mysql.help_category
mysql.help_keyword
mysql.help_relation
mysql.help_topic
mysql.proc
mysql.time_zone
mysql.time_zone_leap_second
mysql.time_zone_name
mysql.time_zone_transition
mysql.time_zone_transition_type

If you want to explicitly place a WRITE lock on any of those tables with a LOCK TABLES statement, the table must be the only one locked; no other table can be locked with the same statement.

Normally, you do not need to lock tables, because all single UPDATE statements are atomic; no other session can interfere with any other currently executing SQL statement. However, there are a few cases when locking tables may provide an advantage:

  • If you are going to run many operations on a set of MyISAM tables, it is much faster to lock the tables you are going to use. Locking MyISAM tables speeds up inserting, updating, or deleting on them because MySQL does not flush the key cache for the locked tables until UNLOCK TABLES is called. Normally, the key cache is flushed after each SQL statement.

    The downside to locking the tables is that no session can update a READ-locked table (including the one holding the lock) and no session can access a WRITE-locked table other than the one holding the lock.

  • If you are using tables for a non-transactional storage engine, you must use LOCK TABLES if you want to ensure that no other session modifies the tables between a SELECT and an UPDATE. The example shown here requires LOCK TABLES to execute safely: 

    LOCK TABLES trans READ, customer WRITE;
SELECT SUM(value) FROM trans WHERE customer_id=some_id;
UPDATE customer
  SET total_value=sum_from_previous_statement
  WHERE customer_id=some_id;
UNLOCK TABLES;

    Without LOCK TABLES, it is possible that another session might insert a new row in the trans table between execution of the SELECT and UPDATE statements.

You can avoid using LOCK TABLES in many cases by using relative updates (UPDATE customer SET value=value+new_value) or the LAST_INSERT_ID() function. See Section 1.8.5.2, “Transactions and Atomic Operations”.

You can also avoid locking tables in some cases by using the user-level advisory lock functions GET_LOCK() and RELEASE_LOCK(). These locks are saved in a hash table in the server and implemented with pthread_mutex_lock() and pthread_mutex_unlock() for high speed. See Section 11.11.4, “Miscellaneous Functions”.

See Section 7.3.1, “Internal Locking Methods”, for more information on locking policy.


User Comments

Posted by Eivind Eklund on October 19 2005 2:08pm[Delete] [Edit]

WARNING
WARNING
WARNING

Carefully notice the "LOCK TABLES causes an implict commit" and "A new transaction implictly does UNLOCK TABLES" above, as that means "MySQL will implictly make your code run and usually work, just including the race condition you very carefully wrote the code to avoid."

WARNING
WARNING
WARNING

Posted by Max Matson on May 21 2007 7:05pm[Delete] [Edit]

Note that while you are allowed to drop a table that you have a lock on, you can not subsequently create the table. Attempts to create the table without first issueing unlock tables results in a "table not locked" error. Therefore, you can't use lock tables to process data through several staging tables where you would drop and create the intermidiate tables. Truncate also won't work, as stated in the manual. The only option that will work is to delete from the table, but this is a slow operation.

Posted by Mark Garrett on November 9 2007 1:35pm[Delete] [Edit]

It appears that tables affected by triggers need to be locked - even if the none of the trigger conditions evalute to true.

1100: Table 'tblQuestionsArchive' was not locked with LOCK TABLES - No link, or no result set created. Query: UPDATE validation NATURAL JOIN tblResponses NATURAL JOIN tblQuestions, tblCodes
SET br = CodeSASBrand
WHERE QuestionCodingName = 'br'
AND tblCodes.CodeID = tblResponses.CodeID

The column "br" is in the validation temp table. Updates on tblQuestions tigger an insert into tblQuestionsArchive-

CREATE TRIGGER questionUpdate AFTER UPDATE ON tblQuestions
FOR EACH ROW BEGIN
IF NEW.ParentQuestionID != OLD.ParentQuestionID OR
[...SNIP...]
THEN
INSERT INTO tblQuestionsArchive SET
QuestionID=OLD.QuestionID,
ParentQuestionID=OLD.ParentQuestionID,
[...SNIP...]
QuestionArchiveDate=UNIX_TIMESTAMP();
END IF;
END
//

Add your own comment.