GNU Info

Info Node: (mysql.info)LOCK TABLES

(mysql.info)LOCK TABLES


Next: SET TRANSACTION Prev: COMMIT Up: Transactional Commands
Enter node , (file) or (file)node

`LOCK TABLES/UNLOCK TABLES' Syntax
----------------------------------

     LOCK TABLES tbl_name [AS alias] {READ | [READ LOCAL] | [LOW_PRIORITY] WRITE}
                 [, tbl_name {READ | [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 automatically unlocked when the thread
issues another `LOCK TABLES', or when the connection to the server is
closed.

The main reasons to use `LOCK TABLES' are for emulating transactions or
getting 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, then only the thread holding the lock can `READ' from
or `WRITE' to the table.  Other threads are blocked.

The difference between `READ LOCAL' and `READ' is that `READ LOCAL'
allows non-conflicting `INSERT' statements to execute while the lock is
held.  This can't however be used if you are going to manipulate the
database files outside MySQL while you hold the lock.

When you use `LOCK TABLES', you must lock all tables that you are going
to use and you must use the same alias that you are going to use in
your queries!  If you are using a table multiple times in a query (with
aliases), you must get a lock for each alias!

`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 only
use `LOW_PRIORITY WRITE' locks 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 a internally defined order (from
     the user standpoint the 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 is
however other things one needs to be aware of with this schema:

If you are using a `LOW_PRIORITY_WRITE' lock for a table, this means
only that MySQL will wait for this particlar lock until there is no
threads that wants a `READ' lock.  When the thread has got 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 transactions safe
tables.

You can safely kill a thread that is waiting for a table lock with
`KILL'.  Note: KILL.

Note that you should *NOT* lock any tables that you are using with
`INSERT DELAYED'.  This is because that in this case the `INSERT' is
done by a separate thread.

Normally, you don't have to lock tables, as 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 bunch of tables, it's
     much faster to lock the tables you are going to use.  The downside
     is, of course, that no other thread can update a `READ'-locked
     table and no other thread can read a `WRITE'-locked table.

     The reason some things 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). This speeds up inserting/updateing/deletes on
     `MyISAM' tables.

   * If you are using a table handler 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 below requires `LOCK TABLES' in order 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', there is a chance that another thread might
     insert a new row in the `trans' table between execution of the
     `SELECT' and `UPDATE' statements.

By using incremental updates (`UPDATE customer SET
value=value+new_value') or the `LAST_INSERT_ID()' function, you can
avoid using `LOCK TABLES' in many cases.

You can also solve some cases by using the user-level 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.  Note: Miscellaneous
functions.

See Note: 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' command. Note: FLUSH. This is very
convenient way to get backups if you have a file system, like Veritas,
that can take snapshots in time.

*NOTE*: `LOCK TABLES' is not transaction-safe and will automatically
commit any active transactions before attempting to lock the tables.


automatically generated by info2www version 1.2.2.9