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
acquires table locks
for the current client session. As of MySQL 4.0.2, to use
LOCK TABLES
you must have the
LOCK TABLES
privilege, and the
SELECT
privilege for each table to
be locked. In MySQL 3.23, you must have
SELECT
,
INSERT
,
DELETE
, and
UPDATE
privileges for the tables.
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.4.6.2, “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. The session 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 session attempts
one during an active transaction or while holding a table lock.
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.
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 7.3.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
as of MySQL 4.1.15. (Before that,
READ LOCAL
essentially does nothing: It
does not lock the table at all, so for
InnoDB
tables, the use of READ
LOCAL
is deprecated because a plain consistent-read
SELECT
does the same thing, and
no locks are needed.)
[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 affects lock
scheduling if the WRITE
lock request must
wait, as described later.
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
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;
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.
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
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 implicit
UNLOCK
TABLES
is performed, which causes existing locks to
be released. (For additional information about the interaction
between table locking and transactions, see
Section 12.3.5.1, “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 17.6.11, “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
'
. To handle this, lock the table again prior to
the second alteration. See also
Section A.5.7.1, “Problems with tbl_name
' was not locked with LOCK
TABLESALTER TABLE
”.
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.