Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 30.1Mb
PDF (A4) - 30.3Mb
PDF (RPM) - 30.2Mb
EPUB - 7.8Mb
HTML Download (TGZ) - 7.6Mb
HTML Download (Zip) - 7.6Mb
HTML Download (RPM) - 6.6Mb
Eclipse Doc Plugin (TGZ) - 8.3Mb
Eclipse Doc Plugin (Zip) - 10.2Mb
Man Pages (TGZ) - 200.0Kb
Man Pages (Zip) - 311.5Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  LOCK TABLES and UNLOCK TABLES Syntax


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



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.

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.

LOCK TABLES explicitly acquires 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. If you lock a table explicitly with LOCK TABLES, any tables used in triggers are also locked implicitly, as described in Section, “LOCK TABLES and Triggers”.

UNLOCK TABLES explicitly releases any table locks held by the current session. LOCK TABLES implicitly releases any table locks held by the current session before acquiring new locks.

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

A table lock only protects against inappropriate reads or writes by other sessions. A session holding a WRITE lock can perform table-level operations such as DROP TABLE or TRUNCATE TABLE. For sessions holding a READ lock, DROP TABLE and TRUNCATE TABLE operations are not permitted.

The following discussion applies only to non-TEMPORARY tables. LOCK TABLES is permitted (but ignored) for a TEMPORARY table. The table can be accessed freely by the session within which it was created, regardless of what other locking may be in effect. No lock is necessary because no other session can see the table.

For information about other conditions on the use of LOCK TABLES and statements that cannot be used while LOCK TABLES is in effect, see Section, “Table-Locking Restrictions and Conditions”

Rules for Lock Acquisition

To acquire table locks within the current session, use the LOCK TABLES statement. The following lock types are available:

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 nonconflicting INSERT statements (concurrent inserts) by other sessions to execute while the lock is held. (See Section 8.11.3, “Concurrent Inserts”.) 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.


  • 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 has no effect. In previous versions of MySQL, it affected locking behavior, but this is no longer true. As of MySQL 5.6.5, it is deprecated and its use produces a warning. Use WRITE without LOW_PRIORITY instead.

If the LOCK TABLES statement must wait due to locks held by other sessions on any of the tables, it blocks until all locks can be acquired.

A session that requires locks must acquire all the locks that it needs in a single LOCK TABLES statement. While the locks thus obtained are held, the session can access only the locked tables. For example, in the following sequence of statements, an error occurs for the attempt to access t2 because it was not locked in the LOCK TABLES statement:

mysql> SELECT COUNT(*) FROM t1;
| COUNT(*) |
|        3 |
mysql> SELECT COUNT(*) FROM t2;
ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES

Tables in the INFORMATION_SCHEMA database are an exception. They can be accessed without being locked explicitly even while a session holds table locks obtained with LOCK TABLES.

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;
ERROR 1100: Table 't' was not locked with LOCK TABLES

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> 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;

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.

LOCK TABLES acquires locks as follows:

  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.


LOCK TABLES or UNLOCK TABLES, when applied to a partitioned table, always locks or unlocks the entire table; these statements do not support partition lock pruning. See Section 19.6.4, “Partitioning and Locking”.

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.

If the connection for a client session terminates, whether normally or abnormally, the server implicitly releases all table locks held by the session (transactional and nontransactional). If the client reconnects, the locks will no longer be in effect. In addition, if the client had an active transaction, the server rolls back the transaction upon disconnect, and if reconnect occurs, the new session begins with autocommit enabled. 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 or current transaction 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 or redoing the transaction. See Section 23.8.16, “Controlling Automatic Reconnection Behavior”.


If you use ALTER TABLE on a locked table, it may become unlocked. For example, if you attempt a second ALTER TABLE operation, the result may be an error Table 'tbl_name' was not locked with LOCK TABLES. To handle this, lock the table again prior to the second alteration. See also Section B.5.7.1, “Problems with ALTER TABLE”.

Download this Manual
PDF (US Ltr) - 30.1Mb
PDF (A4) - 30.3Mb
PDF (RPM) - 30.2Mb
EPUB - 7.8Mb
HTML Download (TGZ) - 7.6Mb
HTML Download (Zip) - 7.6Mb
HTML Download (RPM) - 6.6Mb
Eclipse Doc Plugin (TGZ) - 8.3Mb
Eclipse Doc Plugin (Zip) - 10.2Mb
Man Pages (TGZ) - 200.0Kb
Man Pages (Zip) - 311.5Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
User Comments
  Posted by Max Matson on May 21, 2007
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 Rick James on February 23, 2009
FLUSH TABLES WITH READ LOCK does _not_ seem to put InnoDB into a sufficiently quiesced state for Linux LVM snapshot.
  Posted by Daniel Kadosh on October 26, 2009
@Rick James -- a solution I use to lock InnoDB tables before snapshots that I use is below. CAVEAT: I haven't done any real test for "is mysql fully quiesced".

1) I use an XFS filesystem, which you can freeze right before taking the LVM snapshot. I mount this filesystem on /var/lib/mysql (RedHat/CentOS default location).

2) I have a mysql script that does the whole thing like this, and it's based on an Amazon EC2 tutorial I read!default.jspa?categoryID=112&externalID=1663

SYSTEM xfs_freeze -f /var/lib/mysql;
SYSTEM xfs_freeze -u /var/lib/mysql;

  Posted by Andrew Atkinson on February 25, 2010
For a filesystem snapshot of innodb, we find that setting innodb_max_dirty_pages_pct to zero; doing a 'flush tables with readlock'; and then waiting for the innodb state to reach 'Main thread process no. \d+, id \d+, state: waiting for server activity' is sufficient to quiesce innodb.

You will also need to issue a slave stop if you're backing up a slave whose relay logs are being written to its data directory.

Don't forget to set innodb_max_dirty_pages_pct back to it's normal value and resume slaving afterwards. :-)

Hope this helps.
  Posted by Ilguiz Latypov on August 5, 2010
The chapter does not explain what MySQL sessions are. I could not find an explanation at all. If session is client-server activity limited by connection, this could be explicitly stated somewhere in the glossary and linked to from this chapter of the manual.

  Posted by Frederic Marand on November 30, 2010
Note that, while a WRITE lock will lock new SELECT queries until it is converted to a READ lock or released via UNLOCK, it will not prevent SELECT queries already in the query cache from returning the cached results.

This marginal effect will be limited, though: in most cases the session LOCKing a table will be writing to the same table before UNLOCKing, which will cause the query cache to be flushed for that table.
  Posted by Larry Clapp on September 13, 2011
If you need to do things with tables not normally supported by read or write locks (like dropping or truncating a table), and you're able to cooperate, you can try this: Use a semaphore table, and create two sessions per process. In the first session, get a read or write lock on the semaphore table, as appropriate. In the second session, do all the stuff you need to do with all the other tables.

This is just using the database to create and manage a semaphore instead of using the OS directly (and of course it's not "real" locking but only advisory locking, so it won't work if you can't cooperate with other users), but on the other hand using the database allows the semaphore to be accessed by multiple other servers accessing the DB over a network.
Sign Up Login You must be logged in to post a comment.