The use of the MySQL lock tables and unlock tables

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. The following are respectively described below:

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:

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:

The use of the MySQL lock tables and unlock tables