LOCK {TABLE | TABLES}
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] ...
lock_type: {
READ [LOCAL]
| [LOW_PRIORITY] WRITE
}
UNLOCK {TABLE | 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 in Table-Locking Restrictions and Conditions.
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. As of MySQL 5.7.32,
LOCK TABLES
checks that the view
definer has the proper privileges on the tables underlying the
view.
If you lock a table explicitly with LOCK
TABLES
, any tables used in triggers are also locked
implicitly, as described in
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 13.7.6.3, “FLUSH Statement”. (This is a very convenient way to get
backups if you have a file system such as Veritas that can take
snapshots in time.)
LOCK TABLE
is a synonym for LOCK
TABLES
; UNLOCK TABLE
is a synonym for
UNLOCK TABLES
.
A table lock protects only 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.
To acquire table locks within the current session, use the
LOCK TABLES
statement, which
acquires metadata locks (see
Section 8.11.4, “Metadata Locking”).
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 nonconflictingINSERT
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. ForInnoDB
tables,READ LOCAL
is the same asREAD
.
[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 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. UseWRITE
withoutLOW_PRIORITY
instead.
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. (An exception to this policy can occur
for small values of the
max_write_lock_count
system
variable; see Section 8.11.4, “Metadata Locking”.)
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> 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
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;
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;
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 22.6.4, “Partitioning and Locking”.
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
UNLOCK TABLES
.If a session issues a
LOCK TABLES
statement 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 implicitUNLOCK TABLES
is performed, which causes existing locks to be released. (For additional information about the interaction between table locking and transactions, see 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 are longer 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 transactions are 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 Automatic Reconnection Control.
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
'
. To handle this, lock the table again prior
to the second alteration. See also
Section B.3.6.1, “Problems with ALTER TABLE”.
tbl_name
' was not locked with LOCK
TABLES
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 ifLOCK 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 asLOCK TABLES
andUNLOCK 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 Statement”.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
andUNLOCK TABLES
with transactional tables, such asInnoDB
tables, is to begin a transaction withSET autocommit = 0
(notSTART TRANSACTION
) followed byLOCK TABLES
, and to not callUNLOCK TABLES
until you commit the transaction explicitly. For example, if you need to write to tablet1
and read from tablet2
, 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 callUNLOCK TABLES
. You should not haveautocommit = 1
, because thenInnoDB
releases its internal table lock immediately after the call ofLOCK TABLES
, and deadlocks can very easily happen.InnoDB
does not acquire the internal table lock at all ifautocommit = 1
, to help old applications avoid unnecessary deadlocks.ROLLBACK
does not release table locks.
If you lock a table explicitly with LOCK
TABLES
, any tables used in triggers are also locked
implicitly:
The locks are taken as the same time as those acquired explicitly with the
LOCK TABLES
statement.The lock on a table used in a trigger depends on whether the table is used only for reading. If so, a read lock suffices. Otherwise, a write lock is used.
If a table is locked explicitly for reading with
LOCK TABLES
, but needs to be locked for writing because it might be modified within a trigger, a write lock is taken rather than a read lock. (That is, an implicit write lock needed due to the table's appearance within a trigger causes an explicit read lock request for the table to be converted to a write lock request.)
Suppose that you lock two tables, t1
and
t2
, using this statement:
LOCK TABLES t1 WRITE, t2 READ;
If t1
or t2
have any
triggers, tables used within the triggers are also locked.
Suppose that t1
has a trigger defined like
this:
CREATE TRIGGER t1_a_ins AFTER INSERT ON t1 FOR EACH ROW
BEGIN
UPDATE t4 SET count = count+1
WHERE id = NEW.id AND EXISTS (SELECT a FROM t3);
INSERT INTO t2 VALUES(1, 2);
END;
The result of the LOCK TABLES
statement is that t1
and
t2
are locked because they appear in the
statement, and t3
and t4
are locked because they are used within the trigger:
t1
is locked for writing per theWRITE
lock request.t2
is locked for writing, even though the request is for aREAD
lock. This occurs becauset2
is inserted into within the trigger, so theREAD
request is converted to aWRITE
request.t3
is locked for reading because it is only read from within the trigger.t4
is locked for writing because it might be updated within the trigger.
You can safely use KILL
to
terminate a session that is waiting for a table lock. See
Section 13.7.6.4, “KILL Statement”.
LOCK TABLES
and
UNLOCK
TABLES
cannot be used within stored programs.
Tables in the performance_schema
database
cannot be locked with LOCK
TABLES
, except the
setup_
tables.
xxx
The scope of a lock generated by LOCK TABLES
is a single MySQL server. It is not compatible with NDB Cluster,
which has no way of enforcing an SQL-level lock across multiple
instances of mysqld. You can enforce locking
in an API application instead. See
Section 21.2.7.10, “Limitations Relating to Multiple NDB Cluster Nodes”, for
more information.
The following statements are prohibited while a
LOCK TABLES
statement is in
effect: CREATE TABLE
,
CREATE TABLE ...
LIKE
, CREATE VIEW
,
DROP VIEW
, and DDL statements on
stored functions and procedures and events.
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. LockingMyISAM
tables speeds up inserting, updating, or deleting on them because MySQL does not flush the key cache for the locked tables untilUNLOCK 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 aWRITE
-locked table other than the one holding the lock.If you are using tables for a nontransactional storage engine, you must use
LOCK TABLES
if you want to ensure that no other session modifies the tables between aSELECT
and anUPDATE
. The example shown here requiresLOCK 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 thetrans
table between execution of theSELECT
andUPDATE
statements.
You can avoid using LOCK TABLES
in many cases by using relative updates (UPDATE
customer SET
)
or the value
=value
+new_value
LAST_INSERT_ID()
function.
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 12.14, “Locking Functions”.
See Section 8.11.1, “Internal Locking Methods”, for more information on locking policy.