[+/-]
LOCK TABLEStbl_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
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 12.4.5.2, “LOCK TABLES
and Triggers”.
UNLOCK
TABLES
explicitly releases any table locks held by the
current session.
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 12.5.6.3, “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.)
The following discussion applies only to
non-TEMPORARY
tables. LOCK
TABLES
is allowed (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.
The following general rules apply to acquisition and release of locks by a given session:
Table locks are acquired with LOCK
TABLES
.
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.
Table locks are released explicitly with
UNLOCK
TABLES
.
Table locks are released implicitly under these conditions:
LOCK TABLES
releases any
table locks currently held by the session before acquiring
new locks.
Beginning a transaction (for example, with
START
TRANSACTION
) implicitly performs an
UNLOCK
TABLES
. (Additional information about the
interaction between table locking and transactions is
given in Section 12.4.5.1, “Interaction of Table Locking and Transactions”.)
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. 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 20.10.11, “Controlling Automatic Reconnection Behavior”.
If you use ALTER TABLE
on a
locked table, it may become unlocked. See
Section B.1.7.1, “Problems with ALTER TABLE
”.
A table lock protects only against inappropriate reads or writes
by other clients. The client holding the lock, even a read lock,
can perform table-level operations such as
DROP TABLE
. Truncate operations are
not transaction-safe, so an error occurs if the client attempts
one during an active transaction or while holding a table lock.
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;
If a session obtains a READ
lock on a table,
that session (and all other sessions) can only read from the
table. If a session obtains a WRITE
lock on a
table, only the session holding the lock can write to the table
(that session can also read from the table); other sessions are
blocked from reading or writing the locked table until the lock
has been released.
The difference between READ
and READ
LOCAL
is that READ LOCAL
allows
nonconflicting 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
acquires locks as
follows:
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. 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.
User Comments
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.
FLUSH TABLES WITH READ LOCK does _not_ seem to put InnoDB into a sufficiently quiesced state for Linux LVM snapshot.
@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
http://developer.amazonwebservices.com/connect/entry!default.jspa?categoryID=112&externalID=1663
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
SYSTEM xfs_freeze -f /var/lib/mysql;
SYSTEM YOUR_SCRIPT_TO_CREATE_SNAPSHOT.sh;
SYSTEM xfs_freeze -u /var/lib/mysql;
UNLOCK TABLES;
EXIT;
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.
Add your own comment.