What are the LOCK TABLES and UNLOCK TABLES statements in MySQL 5.5
This article mainly introduces what are the LOCK TABLES and UNLOCK TABLES sentences in MySQL 5.5, which have a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.
LOCK TABLES can lock the table of the current client session. Table locks can be added to a table or view. You need to have LOCK TABLES permission and query access to the table. Triggers in the table are also locked when the LOCK TABLES statement is executed.
Table locks prevent other sessions from reading or writing to the table. Sessions that hold write locks can execute DROP TABLE or TRUNCATE TABLE statements, and sessions that hold read locks are not allowed to execute DROP TABLE or TRUNCATE TABLE statements.
Read lock (READ [LOCAL] lock)
A session that holds a lock can read the table, but cannot perform write operations.
Multiple sessions can acquire read locks for the same table at the same time.
Other sessions that do not acquire a read lock can read the contents of the table.
The LOCAL identifier enables other sessions to execute concurrent INSERT statements. For InnoDB tables, READ LOCAL and READ have the same effect.
Example 1:
-- session ①
Mysql > lock table T10 read
Query OK, 0 rows affected (0.00 sec)
Mysql > select * from T10
+-+ +
| | name | email |
+-+ +
| | Neo | feelpurple@163.com |
| | fire | real_neo@163.com |
| | xxx | real_xiaomm@163.com |
+-+ +
3 rows in set (0.00 sec)
Session ②, you can execute query statements, and DML and DDL operations on the table will be blocked until the session ① table lock is released
Mysql > select * from T10
+-+ +
| | name | email |
+-+ +
| | Neo | feelpurple@163.com |
| | fire | real_neo@163.com |
| | xxx | real_xiaomm@163.com |
+-+ +
3 rows in set (0.00 sec)
Mysql > insert into T10 values ('Jason','real_xiaoyu@163.com')
-- session ①
Mysql > unlock tables
Query OK, 0 rows affected (0.00 sec)
-- session ②
Mysql > insert into T10 values ('Jason','real_xiaoyu@163.com')
Query OK, 1 row affected (2 min 7.82 sec)
When a session acquires a table lock, the session can only access the locked table until the table lock is released.
Example 2:
-- session ①
Mysql > lock table T10 read
Query OK, 0 rows affected (0.00 sec)
Mysql > select count (*) from T10
+-+
| | count (*) |
+-+
| | 3 |
+-+
1 row in set (0.00 sec)
Mysql > select count (*) from T11
ERROR 1100 (HY000): Table 't11' was not locked with LOCK TABLES
Mysql > unlock tables
Query OK, 0 rows affected (0.00 sec)
Mysql > select count (*) from T10
+-+
| | count (*) |
+-+
| | 3 |
+-+
1 row in set (0.00 sec)
Mysql > select count (*) from T11
+-+
| | count (*) |
+-+
| | 1 |
+-+
1 row in set (0.00 sec)
Write lock (WRITE lock)
A session that holds a lock can read and write to the table.
Only the session that acquired the lock can access the table. Only when the lock is released can other sessions access the table.
When a table is written locked, other lock requests for the table are blocked.
-- example 3:
-- session ①
Mysql > lock table T10 write
Query OK, 0 rows affected (0.00 sec)
Mysql > select count (*) from T10
+-+
| | count (*) |
+-+
| | 4 |
+-+
1 row in set (0.00 sec)
Session ②, query, DML and DDL operations on the table will all be blocked until the session ① table lock is released
Mysql > select * from T10
-- session ①
Mysql > unlock tables
Query OK, 0 rows affected (0.00 sec)
-- session ②
Mysql > select * from T10
+-+
| | email |
+-+
| | feelpurple@163.com |
| | real_neo@163.com |
| | real_xiaomm@163.com |
| | real_xiaoyu@163.com |
+-+
4 rows in set (1 min 35.10 sec)
-- session ①
Mysql > lock table T10 write
Query OK, 0 rows affected (0.00 sec)
The DML operation of the session ② will be in a blocking state
Mysql > insert into T10 values ('real_xiaolan@163.com')
-- session ①
Mysql > unlock tables
Query OK, 0 rows affected (0.01 sec)
-the DML operation of the session ② was successful
Mysql > insert into T10 values ('real_xiaolan@163.com')
Query OK, 1 row affected (35.04 sec)
Session ①, if the session ② does not commit or roll back the transaction, the session ① executes the LOCK TABLE statement again will block
Mysql > lock table T10 write
-- session ②
Mysql > rollback
Query OK, 0 rows affected (0.00 sec)
-- session ①
Mysql > lock table T10 write
Query OK, 0 rows affected (24.75 sec)
UNLOCK TABLES releases the table lock for the current session, or you can release the global read-only lock added by the FLUSH TABLES WITH READ LOCK (this command locks all databases in MySQL) statement.
Thank you for reading this article carefully. I hope the article "what are the LOCK TABLES and UNLOCK TABLES sentences in MySQL 5.5" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!
