Copyright (C) 2000-2012 |
GNU Info (mysql.info)LOCK TABLES`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 |