Movatterモバイル変換

MySQL 5.7 Reference Manual

13.3.5 LOCK TABLES and UNLOCK TABLES Statements

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 inTable-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 theLOCK TABLES privilege, and theSELECT 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 withLOCK TABLES, any tables used in triggers are also locked implicitly, as described inLOCK 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 forUNLOCK TABLES is to release the global read lock acquired with theFLUSH TABLES WITH READ LOCK statement, which enables you to lock all tables in all databases. SeeSection 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 forLOCK TABLES;UNLOCK TABLE is a synonym forUNLOCK TABLES.

A table lock protects only against inappropriate reads or writes by other sessions. A session holding aWRITE lock can perform table-level operations such asDROP TABLE orTRUNCATE TABLE. For sessions holding aREAD lock,DROP TABLE andTRUNCATE TABLE operations are not permitted.

The following discussion applies only to non-TEMPORARY tables.LOCK TABLES is permitted (but ignored) for aTEMPORARY 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.

Table Lock Acquisition

To acquire table locks within the current session, use theLOCK TABLES statement, which acquires metadata locks (seeSection 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 aREAD lock for the table at the same time.
Other sessions can read the table without explicitly acquiring aREAD lock.
TheLOCAL modifier enables nonconflictingINSERT statements (concurrent inserts) by other sessions to execute while the lock is held. (SeeSection 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 theWRITE lock is held.
TheLOW_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 thanREAD locks to ensure that updates are processed as soon as possible. This means that if one session obtains aREAD lock and then another session requests aWRITE lock, subsequentREAD lock requests wait until the session that requested theWRITE lock has obtained the lock and released it. (An exception to this policy can occur for small values of themax_write_lock_count system variable; seeSection 8.11.4, “Metadata Locking”.)

If theLOCK 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 singleLOCK 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 accesst2 because it was not locked in theLOCK 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 theINFORMATION_SCHEMA database are an exception. They can be accessed without being locked explicitly even while a session holds table locks obtained withLOCK 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 TABLESmysql> INSERT INTO t SELECT * FROM t AS t1;

The error occurs for the firstINSERT because there are two references to the same name for a locked table. The secondINSERT 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 TABLESmysql> SELECT * FROM t AS myalias;

Note

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

Table 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 withUNLOCK TABLES.
If a session issues aLOCK 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, withSTART TRANSACTION), an implicitUNLOCK TABLES is performed, which causes existing locks to be released. (For additional information about the interaction between table locking and transactions, seeInteraction 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. SeeAutomatic Reconnection Control.

Note

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

Interaction of Table Locking and Transactions

LOCK TABLES andUNLOCK 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, withSTART 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. SeeSection 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, seeSection 13.3.3, “Statements That Cause an Implicit Commit”.
The correct way to useLOCK 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 callLOCK 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.

LOCK TABLES and Triggers

If you lock a table explicitly withLOCK TABLES, any tables used in triggers are also locked implicitly:

The locks are taken as the same time as those acquired explicitly with theLOCK 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 withLOCK 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 andt2, using this statement:

LOCK TABLES t1 WRITE, t2 READ;

Ift1 ort2 have any triggers, tables used within the triggers are also locked. Suppose thatt1 has a trigger defined like this:

CREATE TRIGGER t1_a_ins AFTER INSERT ON t1 FOR EACH ROWBEGIN  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 theLOCK TABLES statement is thatt1 andt2 are locked because they appear in the statement, andt3 andt4 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.

Table-Locking Restrictions and Conditions

You can safely useKILL to terminate a session that is waiting for a table lock. SeeSection 13.7.6.4, “KILL Statement”.

LOCK TABLES andUNLOCK TABLES cannot be used within stored programs.

Tables in theperformance_schema database cannot be locked withLOCK TABLES, except thesetup_xxx tables.

The scope of a lock generated byLOCK 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 ofmysqld. You can enforce locking in an API application instead. SeeSection 21.2.7.10, “Limitations Relating to Multiple NDB Cluster Nodes”, for more information.

The following statements are prohibited while aLOCK 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 themysql database must be accessed. For example, theHELP statement requires the contents of the server-side help tables, andCONVERT_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_categorymysql.help_keywordmysql.help_relationmysql.help_topicmysql.procmysql.time_zonemysql.time_zone_leap_secondmysql.time_zone_namemysql.time_zone_transitionmysql.time_zone_transition_type

If you want to explicitly place aWRITE lock on any of those tables with aLOCK 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 singleUPDATE 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 ofMyISAM 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 aREAD-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 useLOCK 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;
```
WithoutLOCK TABLES, it is possible that another session might insert a new row in thetrans table between execution of theSELECT andUPDATE statements.

You can avoid usingLOCK TABLES in many cases by using relative updates (UPDATE customer SETvalue=value+new_value) or theLAST_INSERT_ID() function.

You can also avoid locking tables in some cases by using the user-level advisory lock functionsGET_LOCK() andRELEASE_LOCK(). These locks are saved in a hash table in the server and implemented withpthread_mutex_lock() andpthread_mutex_unlock() for high speed. SeeSection 12.14, “Locking Functions”.

SeeSection 8.11.1, “Internal Locking Methods”, for more information on locking policy.

PREV HOME UP NEXT