MySQL lock tables and unlock tables using the detailed

The Lock Tables command locks the table for the current thread. Here are 2 types of locks, one is read lock, with command lock tables TableName read, another is write lock, with command lock tables tablename Write. The following are described separately:
1. Lock Table Read lock
If a thread obtains a read lock on a table, the thread and all other threads can only read data from the table, and cannot do any write operations.
Below we test, the test table is the user table.
Different threads can be implemented by opening multiple command line MySQL clients:

Moment Point
Thread A (Command line window a)
Thread B (Command line window B)

The code is as follows	Copy Code
mysql> lock tables User read; Query OK, 0 rows Affected (0.00 sec) Mysql>

Add a read lock to the user table.


2

The code is as follows	Copy Code
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

The code is as follows	Copy Code
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 from other threads is not blocked

3

The code is as follows	Copy Code
mysql> INSERT into user values (' test '); ERROR 1099 (HY000): Table ' user ' is locked with a READ lock and can ' t is updated Mysql>

The write operation for this thread was found to be blocked

The code is as follows	Copy Code
mysql> INSERT into user values (' 2test ');

Found no response, has been waiting, the description has not been written locked, has been waiting.

4

The code is as follows	Copy Code
mysql> unlock tables; Query OK, 0 rows Affected (0.00 sec) Mysql> Releases read locks. mysql> INSERT INTO user values ("ddd"); Query OK, 1 row affected (1 min 27.25 sec) Mysql>

Thread A frees up the read lock, threads B gets the resource, and the write that just waits executes.

5

The code is as follows	Copy Code
mysql> Lock tables User read local; Query OK, 0 rows Affected (0.00 sec) Mysql> Increase the local option when acquiring a read lock. mysql> INSERT INTO user values (2, ' B '); Query OK, 1 row Affected (0.00 sec) Mysql>

An insert from another thread was found to be not blocked.

6

The code is as follows	Copy Code
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 for all of the above tests to be OK, and if the user table is a InnoDB table, 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 is a better option for transaction tables, such as InnoDB and bdb,--single-transaction, because it doesn't require locking tables at all
2. Lock Table Write lock
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 MyISAM type.
Refer to the following test:

Moment Point
Thread A (Command line window a)
Thread B (Command line window B)

The code is as follows	Copy Code
mysql> lock tables user write; Query OK, 0 rows Affected (0.00 sec)

Write a lock on the user table.

The code is as follows	Copy Code
Mysql> select * from user; +----+-----------------------+ | ID | name | +----+-----------------------+ | 1 | aaaaaaaaaaaaaaaaaaaaa | | 2 | B | +----+-----------------------+ 2 rows in Set (0.00 sec)

You can continue the read operation yourself

The code is as follows	Copy Code
Mysql> select * from user;

Other thread read operations are blocked.

The code is as follows	Copy Code
mysql> unlock tables; Query OK, 0 rows Affected (0.00 sec)

Releases the lock.

code is as follows	copy code
Mysql> select * from user; +----+-----------------------+ | id | name                   | +----+-----------------------+ |  1 | aaaaaaaaaaaaaaaaaaaaa | |  2 | b    & nbsp;                | +----+-----------------------+ 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.

How do I use commands in MySQL to see if a table is a MyISAM type or a InnoDB type?

The code is as follows	Copy Code
Show CREATE TABLE * * * Engine=myisam auto_increment=14696 DEFAULT Charset=utf8 |

MySQL Lock-table problem

Show Processlist View the state of the table in the database and whether it is locked;

Kill ID//destroy the locked table

===================================================

code is as follows	copy code
Set autocommit=0; Select * from t1  where uid= ' xxxx ' for update   //row lock with index (for example, UID) or table lock INSERT into T1 values (1, ' xxxxx '); Commit;   Lock tables T1 write|read; INSERT into T1 values (2, ' xxxxx ');//Only Insert Unlock tables;