摘要：MySQL中的lock tables和unlock tables，用来干什么，和事务锁有什么关系，我们来一起探索下。
MySQL允许客户端会话显式地获取表锁，以便与其他会话协作访问表，或者防止其他会话在其需要独占表时使用表。这个能力就是通过LOCK TABLES和UNLOCK TABLES实现的。
LOCK TABLES用于显示的获取表锁，UNLOCK TABLES显式释放当前会话持有的任何表锁。
MySQL 5.7版本中它们的完整语法是：
LOCK TABLES
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] …lock_type: {
READ [LOCAL]
| [LOW_PRIORITY] WRITE
}UNLOCK TABLES
LOCK TABLES可以为基表或视图获取表锁。必须具有要锁定的每个对象的“LOCK TABLES”权限和“SELECT”权限。
不同的锁类型有不同的效果。
READ [LOCAL]锁:
- 持有锁的会话可以读取表（但不能写入表）。
- 多个会话可以同时获取表的read锁。
- 其它会话可以在不显式获取读锁的情况下读取表。
- 加上LOCAL关键字，表示在持有锁的同时允许其它会话执行非冲突插入语句（并发插入）。但是，如果要在持有锁的同时使用外部进程操作数据库，则不能使用READ LOCAL。对于InnoDB表，READ LOCAL与READ相同。
会话1登入，并修改提示符包含session1字样，然后对emp表读取，可以进行，然后用lock tables给emp表加read锁定。
root@database-one 13:57: [(none)]> prompt \u@database-one \R:\m:\s [\d] session1>
PROMPT set to '\u@database-one \R:\m:\s [\d] session1>'
root@database-one 13:57:55 [(none)] session1>use gftest;
Database changed
root@database-one 13:58:31 [gftest] session1>select * from emp;
+--------+------+---------+------------+--------+
| ename | age | sal | hiredate | deptno |
+--------+------+---------+------------+--------+
| 郭军 | 27 | 8400.00 | 2019-12-08 | 10 |
| 刘杰 | 30 | 9100.00 | 2018-04-09 | 10 |
| 王艳 | 24 | 6000.00 | 2020-01-05 | 20 |
| 马丽 | 26 | 7200.00 | 2018-07-06 | 30 |
| 肖伟 | 29 | 8700.00 | 2017-05-28 | 30 |
+--------+------+---------+------------+--------+
5 rows in set (0.00 sec)
root@database-one 14:03:18 [gftest] session1>lock tables emp read;
Query OK, 0 rows affected (0.00 sec)
root@database-one 14:03:20 [gftest] session1>insert into emp values('陈实',31,9000,'2019-07-01',10);
ERROR 1099 (HY000): Table 'emp' was locked with a READ lock and can't be updated
可以看到，read锁定时只能读取，无法修改数据。
会话2登入，并修改提示符包含session2字样，然后对emp表读取。
root@database-one 14:04: [(none)]> prompt \u@database-one \R:\m:\s [\d] session2> PROMPT set to '\u@database-one \R:\m:\s [\d] session2>' root@database-one 14:05:04 [(none)] session2>use gftest; Database changed root@database-one 14:05:10 [gftest] session2>select * from emp; +--------+------+---------+------------+--------+ | ename | age | sal | hiredate | deptno | +--------+------+---------+------------+--------+ | 郭军 | 27 | 8400.00 | 2019-12-08 | 10 | | 刘杰 | 30 | 9100.00 | 2018-04-09 | 10 | | 王艳 | 24 | 6000.00 | 2020-01-05 | 20 | | 马丽 | 26 | 7200.00 | 2018-07-06 | 30 | | 肖伟 | 29 | 8700.00 | 2017-05-28 | 30 | +--------+------+---------+------------+--------+ 5 rows in set (0.00 sec) root@database-one 14:05:20 [gftest] session2>lock tables emp read; Query OK, 0 rows affected (0.00 sec)
可以看到，继续能够读取，同时也能加read锁。
[LOW_PRIORITY] WRITE锁：
- 持有锁的会话可以读写表。
- 只有持有锁的会话才能访问表。在释放锁之前，其他会话无法访问它。
- 当保持WRITE锁时，其他会话对表的锁请求被阻塞。
- LOW_PRIORITY关键字已经作废。在以前的MySQL版本中，它影响锁定行为，但现在已经不是这样了。现在不推荐使用它，它的使用会产生一个警告。
会话1登入，并修改提示符包含session1字样，然后对emp表写入，可以进行，然后用lock tables给emp表加write锁定。
root@database-one 21:32: [(none)]> prompt \u@database-one \R:\m:\s [\d] session1>
PROMPT set to '\u@database-one \R:\m:\s [\d] session1>'
root@database-one 21:32:57 [(none)] session1>use gftest;
Database changed
root@database-one 21:33:07 [gftest] session1>select * from emp;
+--------+------+---------+------------+--------+
| ename | age | sal | hiredate | deptno |
+--------+------+---------+------------+--------+
| 郭军 | 27 | 8400.00 | 2019-12-08 | 10 |
| 刘杰 | 30 | 9100.00 | 2018-04-09 | 10 |
| 王艳 | 24 | 6000.00 | 2020-01-05 | 20 |
| 马丽 | 26 | 7200.00 | 2018-07-06 | 30 |
| 肖伟 | 29 | 8700.00 | 2017-05-28 | 30 |
+--------+------+---------+------------+--------+
5 rows in set (0.00 sec)
root@database-one 21:33:20 [gftest] session1>insert into emp values('陈实',31,9000,'2019-07-01',10);
Query OK, 1 row affected (0.01 sec)
root@database-one 21:34:50 [gftest] session1>lock tables emp write;
Query OK, 0 rows affected (0.00 sec)
会话2登入，并修改提示符包含session2字样，然后对emp表进行查询。
root@database-one 21:38: [(none)]> prompt \u@database-one \R:\m:\s [\d] session2> PROMPT set to '\u@database-one \R:\m:\s [\d] session2>' root@database-one 21:38:13 [(none)] session2>use gftest; Database changed root@database-one 21:38:21 [gftest] session2>select * from emp; <卡住，闪动的光标>
可以看到，表被以write方式锁定后，其它会话读取都无法进行，更不用提写入了。
UNLOCK TABLES显式释放当前会话持有的任何表锁。我们去session1中释放锁。
root@database-one 21:36:20 [gftest] session1> root@database-one 21:45:29 [gftest] session1>unlock tables; Query OK, 0 rows affected (0.00 sec) root@database-one 21:45:43 [gftest] session1>
root@database-one 21:38:21 [gftest] session2>select * from emp; +--------+------+---------+------------+--------+ | ename | age | sal | hiredate | deptno | +--------+------+---------+------------+--------+ | 郭军 | 27 | 8400.00 | 2019-12-08 | 10 | | 刘杰 | 30 | 9100.00 | 2018-04-09 | 10 | | 王艳 | 24 | 6000.00 | 2020-01-05 | 20 | | 马丽 | 26 | 7200.00 | 2018-07-06 | 30 | | 肖伟 | 29 | 8700.00 | 2017-05-28 | 30 | | 陈实 | 31 | 9000.00 | 2019-07-01 | 10 | +--------+------+---------+------------+--------+ 6 rows in set (7 min 11.08 sec) root@database-one 21:45:43 [gftest] session2>
可以看到，session1在21:45:29后释放了锁，session2在21:45:43前就立即读取出了emp表的数据，整整排队等待了7分多钟，这些时间都被计入了这个SELECT的执行时间。
如果语句通过表名锁定，则不能使用别名。
root@database-one 21:57:54 [gftest] session1>lock table emp read; Query OK, 0 rows affected (0.01 sec) root@database-one 21:58:02 [gftest] session1>select * from emp as e; ERROR 1100 (HY000): Table 'e' was not locked with LOCK TABLES root@database-one 21:58:06 [gftest] session1>select * from emp; +--------+------+---------+------------+--------+ | ename | age | sal | hiredate | deptno | +--------+------+---------+------------+--------+ | 郭军 | 27 | 8400.00 | 2019-12-08 | 10 | | 刘杰 | 30 | 9100.00 | 2018-04-09 | 10 | | 王艳 | 24 | 6000.00 | 2020-01-05 | 20 | | 马丽 | 26 | 7200.00 | 2018-07-06 | 30 | | 肖伟 | 29 | 8700.00 | 2017-05-28 | 30 | | 陈实 | 31 | 9000.00 | 2019-07-01 | 10 | +--------+------+---------+------------+--------+ 6 rows in set (0.00 sec)
如果使用别名锁定，则必须在语句中使用别名引用该表：
root@database-one 22:00:54 [gftest] session1>unlock tables; Query OK, 0 rows affected (0.00 sec) root@database-one 22:01:23 [gftest] session1> root@database-one 22:01:25 [gftest] session1>lock table emp as e read; Query OK, 0 rows affected (0.00 sec) root@database-one 22:01:30 [gftest] session1>select * from emp; ERROR 1100 (HY000): Table 'emp' was not locked with LOCK TABLES root@database-one 22:01:34 [gftest] session1>select * from emp as e; +--------+------+---------+------------+--------+ | ename | age | sal | hiredate | deptno | +--------+------+---------+------------+--------+ | 郭军 | 27 | 8400.00 | 2019-12-08 | 10 | | 刘杰 | 30 | 9100.00 | 2018-04-09 | 10 | | 王艳 | 24 | 6000.00 | 2020-01-05 | 20 | | 马丽 | 26 | 7200.00 | 2018-07-06 | 30 | | 肖伟 | 29 | 8700.00 | 2017-05-28 | 30 | | 陈实 | 31 | 9000.00 | 2019-07-01 | 10 | +--------+------+---------+------------+--------+ 6 rows in set (0.00 sec)
会话可以显式释放其锁，或者在某些条件下隐式释放锁。
- 会话可以使用UNLOCK TABLES显式释放其锁。
- 如果会话已经持有锁，还发出LOCK TABLES语句来获取新锁，那么在授予新锁之前，现有锁将被隐式释放。
- 如果会话开始事务（例如，使用START transaction），则隐式执行UNLOCK TABLES，从而释放现有锁。
无论客户端会话的连接正常结束或异常终止，服务器均隐式释放该会话持有的所有表锁（事务性和非事务性）。
LOCK TABLES和UNLOCK TABLES与事务的交互如下：
- LOCK TABLES不是事务安全的，在尝试锁定表之前隐式提交任何活动事务。
- UNLOCK TABLES隐式提交任何活动事务，前提是LOCK TABLES已用于获取表锁。例如，在以下语句集中，UNLOCK TABLES释放全局读取锁，但不提交事务，因为没有有效的表锁：
FLUSH TABLES WITH READ LOCK; START TRANSACTION; SELECT ... ; UNLOCK TABLES;
- 开始事务（例如，使用START transaction）隐式提交任何当前事务并释放现有表锁。
- 对事务表（如InnoDB TABLES）使用LOCK TABLES和UNLOCK TABLES的正确方法是，以SET autocommit=0（不是START transaction）后跟LOCK TABLES开始事务，并且在显式提交事务之前不调用UNLOCK TABLES。例如，如果需要写入表t1并读取表t2，可以执行以下操作：
SET autocommit=0; LOCK TABLES t1 WRITE, t2 READ, ...; ... do something with tables t1 and t2 here ... COMMIT; UNLOCK TABLES;
- ROLLBACK不会释放表锁。
综上所述，当我们需要独占式的对表做一些运维操作时，使用LOCK TABLES和UNLOCK TABLES是个很不错的选择。
© 2020, morinson. 版权所有. 欢迎转载，但请保留作者及出处。