Pre-General Availability Draft: 2017-09-22
LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ... lock_type: READ [LOCAL] | [LOW_PRIORITY] WRITE 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.
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
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 220.127.116.11, “LOCK TABLES and Triggers”.
Another use for
TABLES is to release the global read lock acquired with
FLUSH TABLES WITH READ LOCK
statement, which enables you to lock all tables in all databases.
See Section 18.104.22.168, “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 protects only against inappropriate reads or writes
by other sessions. A session holding a
lock can perform table-level operations such as
DROP TABLE or
TRUNCATE TABLE. For sessions
operations are not permitted.
The following discussion applies only to
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 22.214.171.124, “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
READlock for the table at the same time.
Other sessions can read the table without explicitly acquiring a
LOCALmodifier enables nonconflicting
INSERTstatements (concurrent inserts) by other sessions to execute while the lock is held. (See Section 8.11.3, “Concurrent Inserts”.) However,
READ LOCALcannot be used if you are going to manipulate the database using processes external to the server while you hold the lock. For
READ LOCALis the same as
[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
WRITElock is held.
LOW_PRIORITYmodifier has no effect. In previous versions of MySQL, it affected locking behavior, but this is no longer true. It is now deprecated and its use produces a warning. Use
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
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> LOCK TABLES t1 READ; 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
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;
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;
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
lock requests wait until the session that requested the
WRITE lock has obtained the lock and released
LOCK TABLES acquires locks as
Sort all tables to be locked in an internally defined order. From the user standpoint, this order is undefined.
If a table is to be locked with a read and a write lock, put the write lock request before the read lock request.
Lock one table at a time until the session gets all locks.
This policy ensures that table locking is deadlock free.
LOCK TABLES or
TABLES, when applied to a partitioned table, always
locks or unlocks the entire table; these statements do not
support partition lock pruning. See
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.
A session can release its locks explicitly with
If a session issues a
LOCK TABLESstatement to acquire a lock while already holding locks, its existing locks are released implicitly before the new locks are granted.
If a session begins a transaction (for example, with
START TRANSACTION), an implicit
UNLOCK TABLESis performed, which causes existing locks to be released. (For additional information about the interaction between table locking and transactions, see Section 126.96.36.199, “Interaction of Table Locking and Transactions”.)
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 27.7.24, “C API Automatic Reconnection Control”.
If you use
ALTER TABLE on a
locked table, it may become unlocked. For example, if you
attempt a second
operation, the result may be an error
'. To handle this, lock the table again prior to
the second alteration. See also
Section B.5.6.1, “Problems with ALTER TABLE”.
tbl_name' was not locked with LOCK