6.7.2 Lock Tables / UNLOCK TABLES SMAN
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 the current thread. Unlock Tables Releases all locks owned by the front thread. When the thread issues another Lock Table, or when the connection to the server is turned off, all tables that are locked by the current thread will be automatically unlocked.
To use Lock Tables in MySQL 4.0.2, you must have a global Lock Tables permission and a SELECT permission on the relevant table. In MySQL 3.23, you need SELECT, INSERT, DELETE, and UPDATE permission to the table.
The main reason for using Lock Tables is that faster speeds are faster when the emulation transaction is processed or when the table is updated. There will be a more detailed description thereafter.
If a thread gets a READ lock on a table, the thread (and all other threads) can only be read from the table. If a thread gets a WRITE lock on a table, only threads with this lock can be read and writing from the table. Other threads are blocked.
The difference between READ LOCAL and READ is that when the lock is loaded, the READ LOCAL allows non-conflicting INSERT statements. This will not be used when you operate the database file outside of MySQL when you load a lock.
When you use Lock Tables, you must lock all you will use, and you must use the alias that will be used in your query! If you use a table multiple times in a query (with alias), you must get a lock for every alias.
The WRITE lock has higher permissions than the READ lock to ensure that the update is processed as quickly as possible. This means that if a thread get a READ lock, while another thread requests a Write lock, concurrent Read lock request will wait until the Write thread gets the lock and release it. You can use the low_priority write lock when the thread is waiting for the Write lock, it will allow other threads to get the READ lock. You should only use the low_priority write lock, if you are confident that this will be the last time, when there is no thread will have an READ lock.
Lock Tables work as follows:
Sorted in the order of internal definitions All locked tables (said from the user stand, this order is unclear). If a table is locked with a read lock and a write lock, the write lock is placed before reading the lock. Only one table is locked once, only the thread gets all locks.
This solution is to ensure that the table locks the deadlock release. For this mode, you still have some other things to know:
If you lock a Low_Priority Write to a table, this means that mysql will wait for this lock until no thread requests an READ lock. When the thread gets the WRITE lock, all other threads will wait for the WRITE lock to be released when the lock is locked in the lock table list. If this will cause a serious problem in your application, you should consider converting some of your tables to a transaction security table.
You can securely kill a thread that is locked in the table with KILL. View chapter 4.5.5 kill syntax.
Note that you should not lock you are using the INSERT DELAYED table. This is because in this case, INSERT is done by a separate thread.
Typically, you don't need to lock any table because all single UPDATE statements are atoms; other threads cannot interfere with the currently executed SQL statement. When you want to lock the table, there are some situations:
If you run a lot of operations on a bunch of tables, lock you will use, which will be faster. Of course, there is an unfavorable aspect, and other threads will not update a READ lock table, and there is no other thread to read a Write lock table. Under Lock Tables, some things are run faster. The reason is that mysql will not dump clearly cleared the locked table key cache until UNLOCK TABLES is called (normally keys to the cache) after each SQL statement Clear). This will accelerate insert, update, delete on the Myisam table. If you are using a storage engine that doesn't support transaction in MySQL, if you want to make sure that there is no other thread, you have to use Lock Tables. The following example shows in order to safely perform needed here LOCK TABLES: 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; does not use Lock Tables, there will be another thread that may happen during execution of SELECT and UPDATE statements, may insert a new line of records in the TRANS table.
By using an incremental update (Update Customer Set Value = Value New_Value) or a Last_Insert_ID () function, you can avoid using LOCK TABLES in many cases.
You can also use user-level lock function GET_LOCK () and release_lock () to solve some situations, which are saved in a hash table on the server and implemented with pthread_mutex_lock () and pthread_mutex_unlock () to achieve high speed. View chapter 6.3.6.2 Assist function.
View chapter 5.3.1 Mysql how to lock the table to get more information about the lock scheme.
You can use the flush table with read lock command to read locks all the tables in all databases. View chapter 4.5.3 Flush syntax. If you have a file system that can build a file snapshot in time, such as Veritas, this will be a very convenient way to get backup.
Note: Lock Tables are not transaction security, and all active transactions will be automatically submitted before attempting to lock a table.
