MySQL 表锁

---

MySQL 表锁

MySQL 中提供了锁定表 lock tables 和解锁表 unlock tables 的法语，用来对指定对表进行锁定和解锁限制。

---

涉及命令

命令	含义	常用
select connection_id()	显示会话 id	select connection_id() 显示当前会话 id
show open tables	显示所有表占用锁的信息（包含全部表）	show open tables 显示所有表占用锁的信息（包含全部表）
show open tables where in_user >= 1	显示所有表占用锁的信息（只包含已加锁的表）	显示所有表占用锁的信息（只包含已加锁的表）
lock table tableName1, tableName2… read	对指定表添加读锁（共享锁）	lock table user read 对 user 表添加读锁
		lock table user, score read 对 user 与 score 表添加读锁
lock table tableName1, tableName2	对指定表添加写锁	lock table user write 对 user 表添加写锁
		lock table user, score write 对 user 与 score 表添加写锁
unlock tables	对指定表进行解锁	unlock tables user 释放 user 表的锁

---

示例

• lock table read 锁表会将当前会话指定表进行锁定。限制当前会话只能查询该表，如果查询其他表则报错。如果对该表进行写操作也会报错。

-- 查看当前会话 id
select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|              64 |
+-----------------+

-- 使用读锁锁定 student 表
lock table student read;
Query OK, 0 rows affected (0.00 sec)

-- 查看被锁住对表信息，如下表示 student 表被一个会话锁定（in_use = 1）
show open tables where in_use >= 1;
+----------+---------+--------+-------------+
| Database | Table   | In_use | Name_locked |
+----------+---------+--------+-------------+
| backup   | student |      1 |           0 |
+----------+---------+--------+-------------+

-- 查询带锁对表没问题
select * from student;
+--------+----------+
| std_id | std_name |
+--------+----------+
|   1001 | zhangsan |
|   1002 | lisi     |
|   1003 | wangwu   |
+--------+----------+

-- 查询未锁定对表则报错
select * from teacher;
ERROR 1100 (HY000): Table 'teacher' was not locked with LOCK TABLES

-- 对锁定的表进行插入操作
insert into student (std_id, std_name) values (1004, 'liuliu');
ERROR 1100 (HY000): Table 'studentstudent' was not locked with LOCK TABLES

-- 对未锁定对表进行插入操作
insert into teacher (teacher_id, teacher_name) values (1004, 'liuliu');
ERROR 1100 (HY000): Table 'teacher' was not locked with LOCK TABLES

-- 再次执行锁表，之前的锁表将自动解锁
lock table teacher read;
Query OK, 0 rows affected (0.00 sec)

-- 查看被锁住对表信息，如下表示 student 表被一个会话锁定（in_use = 1）
show open tables where in_use >= 1;
+----------+---------+--------+-------------+
| Database | Table   | In_use | Name_locked |
+----------+---------+--------+-------------+
| backup   | teacher |      1 |           0 |
+----------+---------+--------+-------------+

-- 客户端断开链接后，所有表锁将自动解锁, 重新链接后不再持有锁
exit -- 断开链接
mysql -uroot -p -- 再次链接

show open tables where in_use >= 1;
Empty set (0.00 sec)

---

注意点

锁表获取方式：

LOCK TABLES acquires locks as follows:
Sort all tables to be locked in an internally defined order. From the user standpoint, this order is undefined.
If a table is to be locked with a read and a write lock, put the write lock request before the read lock request.
Lock one table at a time until the session gets all locks.
This policy ensures that table locking is deadlock free.
LOCK TABLES acquires locks as follows:
Sort all tables to be locked in an internally defined order. From the user standpoint, this order is undefined.
If a table is to be locked with a read and a write lock, put the write lock request before the read lock request.
Lock one table at a time until the session gets all locks.
This policy ensures that table locking is deadlock free.
锁表获取锁的方式如下:
按照内部定义的顺序对所有要锁定的表进行排序。
从用户的角度来看，这个顺序是未定义的。
如果要用读锁和写锁锁定表，请将写锁请求放在读锁请求之前。
一次锁定一个表，直到会话获得所有锁。
此策略确保表锁定没有死锁。

释放锁对条件：

• 当会话持有的表锁被释放时，它们都同时被释放会话可以显式地释放锁，也可以在某些条件下隐式地释放锁
• 可以使用 unlock tables 命令显示对释放锁
• 如果一个会话在已经持有锁的情况下发出一个 LOCK TABLES 命令再次获取锁，那么在授予新锁之前，MySQL 会将已经存在的锁隐式地解除
• 如果一个会话开始了一个事务(例如：start transaction)，就会执行一个隐式的 UNLOCK TABLES，从而释放现有的锁。
• 如果客户端会话的连接终止，无论是否正常，服务器都会隐式释放会话持有的所有表锁 (事务性和非事务性)。
  如果客户端重新连接，锁将不再有效。

关联锁表：

lock table 命令可能会锁定比我们指定表更多对表。这是因为，如果表中有 trigger，那么 MySQL 为了让功能正常运行，
那么会将 trigger 中涉及对表一同 lock

note: 以上测试都是基于 InnoDB 引擎

---