Usage of the MySQL lock tables and unlock tables (reprint)

I have long heard that the two commands, lock tables and unlock tables, literally know that the function of the former is to lock the table and the latter is to unlock it. But how to use, how to use, not very clear. Today, in a detailed study, we finally figured out the usage of 2 people.

The Lock Tables command locks the table for the current thread. There are 2 types of locking, one is read lock, with command lock tables TableName read, the other is write lock, with command lock tables tablename Write. Below are the following:

1. Lock Table Read locks

If a thread obtains a read lock on a table, the thread and all other threads can only read data from the table, and no write operations are allowed.

Below we test, the test table is the user table.

Different threads can be implemented by opening multiple command-line MySQL clients:

Time points	Thread A (Command line window a)	Thread B (Command-line window B)
1	mysql> lock tables User read; Query OK, 0 rows Affected (0.00 sec) Mysql> Read-Lock the user table.
2	Mysql> select * from user; +------+-----------+ | ID | name | +------+-----------+ | 22 | ABC | | 223 | DABC | | 2232 | DDDABC | | 45 | Asdsagd | | 23 | ddddddddd | +------+-----------+ 5 rows in Set (0.00 sec) Mysql> Your own read operation is not blocked	Mysql> select * from user; +------+-----------+ | ID | name | +------+-----------+ | 22 | ABC | | 223 | DABC | | 2232 | DDDABC | | 45 | Asdsagd | | 23 | ddddddddd | +------+-----------+ 5 rows in Set (0.00 sec) Mysql> Read by other threads is not blocked
3	mysql> INSERT into user values (' test '); ERROR 1099 (HY000): Table ' user ' is locked with a READ lock and can ' t be updated Mysql> The write operation of this thread was found to be blocked	mysql> INSERT into user values (2test '); Found no response, has been waiting, the description did not get write lock, has been waiting.
4	mysql> unlock tables; Query OK, 0 rows Affected (0.00 sec) Mysql> Release the read lock.	mysql> INSERT into user values (' ddd '); Query OK, 1 row affected (1 min 27.25 sec) Mysql> After thread a releases the read lock, threads B gets the resource and the write operation that was just waiting is executed.
5	mysql> Lock tables User read local; Query OK, 0 rows Affected (0.00 sec) Mysql> The local option is added when the read lock is obtained.	mysql> INSERT INTO user values (2, ' B '); Query OK, 1 row Affected (0.00 sec) Mysql> Inserts from other threads are not blocked.
6		mysql> Update user Set name = ' AAAAAAAAAAAAAAAAAAAAA ' where id = 1; However, the update operation for other threads is blocked.

Note: The user table must be a MyISAM table, the above test can be all OK, if the user table is a innodb table, then the lock tables user read local command may not be effective, that is, if the user table is a InnoDB table, the 6th moment will not be blocked , because the InnoDB table is transactional, and for transactional tables, such as InnoDB and Bdb,--single-transaction, is a better option because it does not need to lock the table at all

2. Lock Table Write locks

If a thread gets a write lock on a table, only the thread that owns the lock can read and write the table from the table. The other threads are blocked.

Write locked command: Lock tables User Write.user table is a MyISAM type table.

Refer to the following test:

Time points	Thread A (Command line window a)	Thread B (Command-line window B)
1	mysql> lock tables user write; Query OK, 0 rows Affected (0.00 sec) Write lock on user table.
2	mysql > select * from User; +----+-----------------------+ | ID | name                   | +----+-----------------------+ |  1 | aaaaaaaaaaaaaaaaaaaaa | |  2 | b                      | +----+-----------------------+ 2 rows in Set (0.00 sec)	mysql> select * from user; Other thread read operations are blocked.
3	mysql > Unlock tables; query OK, 0 rows Affected (0.00 sec) release lock.	 
4		Mysql> select * from user; +----+-----------------------+ | ID | name | +----+-----------------------+ | 1 | aaaaaaaaaaaaaaaaaaaaa | | 2 | B | +----+-----------------------+ 2 rows in Set (32.56 sec) Other threads get resources and can read the data.

All of the above results are tested and passed under MySQL 5.4.3.

Usage of the MySQL lock tables and unlock tables (reprint)