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
UPDATE privileges for the tables.
TABLES explicitly releases any table locks held by the
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 22.214.171.124, “
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
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 126.96.36.199, “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
for the table at the same time.
Other sessions can read the table without explicitly acquiring
LOCAL modifier enables nonconflicting
INSERT statements (concurrent
inserts) by other sessions to execute while the lock is held.
(See Section 7.6.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
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
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.
LOW_PRIORITY modifier affects lock
scheduling if the
WRITE lock request must
wait, as described later.
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:
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:
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:
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:
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
it. A request for a
LOW_PRIORITY WRITE lock, by
contrast, permits subsequent
READ lock requests
by other sessions to be satisfied first if they occur while the
LOW_PRIORITY WRITE request is waiting. You
LOW_PRIORITY WRITE locks only if you
are sure that eventually there will be a time when no sessions
READ lock. For
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
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
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
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
If a session issues a
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
TRANSACTION), an implicit
TABLES is performed, which causes existing locks to
be released. (For additional information about the interaction
between table locking and transactions, see
Section 188.8.131.52, “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.14, “Controlling Automatic Reconnection Behavior”.
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.7.1, “Problems with
tbl_name' was not locked with LOCK