(mysql.info.gz) LOCK TABLES
Info Catalog
(mysql.info.gz) Savepoints
(mysql.info.gz) Transactional Commands
(mysql.info.gz) 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. `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.
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'.
* 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 will very easily happen.
Starting from 4.1.9, we do not acquire the InnoDB table lock at all
if `AUTOCOMMIT=1'. That helps old applications to avoid unnecessary
deadlocks.
As of MySQL 4.0.2, to use `LOCK TABLES' you must have the `LOCK TABLES'
privilege and a `SELECT' privilege for the involved tables. In MySQL
3.23, you must have `SELECT', `INSERT', `DELETE', and `UPDATE'
privileges for the tables.
The main reasons to use `LOCK TABLES' are for emulating 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 can't be used if you are
going to manipulate the database files outside MySQL while you hold the
lock. For InnoDB, `READ LOCAL' essentially does nothing: it does not
lock the table at all. The use of `READ LOCAL' for InnoDB tables is
deprecated, because for InnoDB, 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 one query - use aliases for that. Note that in that case you
must get 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 using an alias, then you must lock the
table using that same alias. It will 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 will 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 there will eventually be a time
when no threads will 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 will wait 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 will 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. `KILL' KILL.
Note that you should _not_ lock any tables that you are using with
`INSERT DELAYED' because in that case the `INSERT' is done by a
separate thread.
Normally, you don't have to lock tables, because all single `UPDATE'
statements are atomic; no other thread can interfere with any other
currently executing SQL statement. There are a few cases when you would
like to lock tables anyway:
* If you are going to run many operations on a set of `MyISAM'
tables, it's 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 will 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 doesn't 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:
mysql> LOCK TABLES trans READ, customer WRITE;
mysql> SELECT SUM(value) FROM trans WHERE customer_id=SOME_ID;
mysql> UPDATE customer
-> SET total_value=SUM_FROM_PREVIOUS_STATEMENT
-> WHERE customer_id=SOME_ID;
mysql> 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, 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.
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. `FLUSH' 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. ALTER TABLE problems.
Info Catalog
(mysql.info.gz) Savepoints
(mysql.info.gz) Transactional Commands
(mysql.info.gz) SET TRANSACTION
automatically generated byinfo2html