LOCK TABLES
and
UNLOCK TABLES
Syntax
1116
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;
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,
permits 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:
1. Sort all tables to be locked in an internally defined order. From the user standpoint, this order is
undefined.
2. If a table is to be locked with a read and a write lock, put the write lock request before the read lock
request.
3. Lock one table at a time until the session gets all locks.
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
