(mysql.info) lock-tables
Info Catalog
(mysql.info) savepoints
(mysql.info) transactional-commands
(mysql.info) set-transaction
13.4.5 `LOCK TABLES' and `UNLOCK TABLES' Syntax
-----------------------------------------------
LOCK TABLES
TBL_NAME [AS ALIAS] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
[, TBL_NAME [AS ALIAS] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...
UNLOCK TABLES
`LOCK TABLES' locks tables for the current thread. If any of the tables
are locked by other threads, it blocks until all locks can be acquired.
`UNLOCK TABLES' releases any locks held by the current thread. All
tables that are locked by the current thread are implicitly unlocked
when the thread issues another `LOCK TABLES', or when the connection to
the server is closed.
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'.
Note the following regarding the use of `LOCK TABLES' with
transactional tables:
* `LOCK TABLES' is not transaction-safe and implicitly commits any
active transactions before attempting to lock the tables. Also,
beginning a transaction (for example, with `START TRANSACTION')
implicitly performs an `UNLOCK TABLES'. (See
implicit-commit.)
* The correct way to use `LOCK TABLES' with transactional tables,
like `InnoDB', is to set `AUTOCOMMIT = 0' and not to call `UNLOCK
TABLES' until you commit the transaction explicitly. When you call
`LOCK TABLES', `InnoDB' internally takes its own table lock, and
MySQL takes its own table lock. `InnoDB' releases its table lock
at the next commit, but for MySQL to release its table lock, you
have to call `UNLOCK TABLES'. You should not have `AUTOCOMMIT =
1', because then `InnoDB' releases its table lock immediately
after the call of `LOCK TABLES', and deadlocks can very easily
happen. Note that we do not acquire the `InnoDB' table lock at all
if `AUTOCOMMIT=1', to help old applications avoid unnecessary
deadlocks.
* `ROLLBACK' does not release MySQL's non-transactional table locks.
To use `LOCK TABLES', you must have the `LOCK TABLES' privilege and the
`SELECT' privilege for the involved tables.
The main reasons to use `LOCK TABLES' are to emulate transactions or to
get more speed when updating tables. This is explained in more detail
later.
If a thread obtains a `READ' lock on a table, that thread (and all
other threads) can only read from the table. If a thread obtains a
`WRITE' lock on a table, only the thread holding the lock can write to
the table. Other threads are blocked from doing so until the lock has
been released.
The difference between `READ LOCAL' and `READ' is that `READ LOCAL'
allows non-conflicting `INSERT' statements (concurrent inserts) to
execute while the lock is held. However, this cannot be used if you are
going to manipulate the database files outside MySQL while you hold the
lock. For `InnoDB' tables, `READ LOCAL' is the same as `READ' as of
MySQL 5.0.13. (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.)
When you use `LOCK TABLES', you must lock all tables that you are going
to use in your queries. While the locks obtained with a `LOCK TABLES'
statement are in effect, you cannot access any tables that were not
locked by the statement. Also, you cannot use a locked table multiple
times in a single query. Use aliases instead, in which case you must
obtain a lock for each alias separately.
mysql> LOCK TABLE t WRITE, t AS t1 WRITE;
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 queries 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 queries 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
thread obtains a `READ' lock and then another thread requests a `WRITE'
lock, subsequent `READ' lock requests wait until the `WRITE' thread has
gotten the lock and released it. You can use `LOW_PRIORITY WRITE' locks
to allow other threads to obtain `READ' locks while the thread is
waiting for the `WRITE' lock. You should use `LOW_PRIORITY WRITE' locks
only if you are sure that eventually there will be a time when no
threads have a `READ' lock.
`LOCK TABLES' works 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 locked with a read and a write lock, put the write
lock before the read lock.
3. Lock one table at a time until the thread 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 threads
that want a `READ' lock. When the thread has gotten the `WRITE' lock
and is waiting to get the lock for the next table in the lock table
list, all other threads 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.
You can safely use `KILL' to terminate a thread that is waiting for a
table lock. See kill.
Note that you should _not_ lock any tables that you are using with
`INSERT DELAYED' because in that case the `INSERT' is performed by a
separate thread.
Normally, you do not need to lock tables, because all single `UPDATE'
statements are atomic; no other thread 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 of `MyISAM'
tables, it is much faster to lock the tables you are going to use.
Locking `MyISAM' tables speeds up inserting, updating, or deleting
on them. The downside is that no thread can update a `READ'-locked
table (including the one holding the lock) and no thread can
access a `WRITE'-locked table other than the one holding the lock.
The reason some `MyISAM' operations are faster under `LOCK TABLES'
is that MySQL does not flush the key cache for the locked tables
until `UNLOCK TABLES' is called. Normally, the key cache is
flushed after each SQL statement.
* If you are using a storage engine in MySQL that does not support
transactions, you must use `LOCK TABLES' if you want to ensure
that no other thread comes between a `SELECT' and an `UPDATE'. The
example shown here requires `LOCK 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;
Without `LOCK TABLES', it is possible that another thread might
insert a new row in the `trans' table between execution of the
`SELECT' and `UPDATE' statements.
You can avoid using `LOCK TABLES' in many cases by using relative
updates (`UPDATE customer SET VALUE=VALUE+NEW_VALUE') or the
`LAST_INSERT_ID()' function. See ansi-diff-transactions.
You can also avoid locking tables in some cases by using the user-level
advisory lock functions `GET_LOCK()' and `RELEASE_LOCK()'. These locks
are saved in a hash table in the server and implemented with
`pthread_mutex_lock()' and `pthread_mutex_unlock()' for high speed. See
miscellaneous-functions.
See internal-locking, for more information on locking policy.
You can lock all tables in all databases with read locks with the
`FLUSH TABLES WITH READ LOCK' statement. See flush. This is a
very convenient way to get backups if you have a filesystem such as
Veritas that can take snapshots in time.
* If you use `ALTER TABLE' on a locked table, it may become
unlocked. See alter-table-problems.
Info Catalog
(mysql.info) savepoints
(mysql.info) transactional-commands
(mysql.info) set-transaction
automatically generated byinfo2html