(mysql.info.gz) LOCK TABLES

(mysql.info.gz) Transactional Commands
 
 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