MySQL | 表锁——排他锁、共享锁、意向锁、元数据锁

排他锁、共享锁

对于数据库中并发事务的读-读情况并不会引起什么问题。对于写-写、读-写或写-读这些情况可能会引起一些问题，需要使用MVCC或者加锁的方式解决它们。在使用加锁的方式解决问题时，由于既要允许读-读情况不受影响，又要使写-写、读-写或写-读情况中的操作相互阻塞，所以MySQL实现由两种类型的锁组成的锁系统来解决。这两种类型的锁通常被称为共享锁(Shared Lock，S Lock ) 和排他锁(Exclusive Lock , X Lock)，又称读锁 (Read Lock) 和写锁 (Write Lock)。

• 读锁：也称共享锁、英文使用S表示。针对同一份数据，多个事务的读操作可以同时进行而不会互相影响，相互不阻塞的。
• 写锁：也称排他锁、英文使用X表示。当前写操作没有完成前，它会阻塞其他写锁和读锁。这样就能确保在给定的时间里，只要一个事务能执行写入，并防止其他用户读取正在写入的同一资源。

需要注意的是对于InnoDB引擎来说，读锁和写锁可以加在表上，也可以加载行上。

	X锁	S锁
X锁	不兼容	不兼容
S锁	不兼容	兼容

举例：读、写锁的使用场景模拟

场景1：事务1和事务2开启共享锁，事务3开启排他锁

事务1和事务2：开启共享锁

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select name,id from user lock in share mode;
+--------+----+
| name   | id |
+--------+----+
| 张三   |  1 |
| 李四   |  2 |
| 王五   |  3 |
+--------+----+
3 rows in set (0.00 sec)

事务3：开启排他锁

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select name,id from user for update;

此时事务3进入阻塞状态，直到事务1和事务2提交为止

场景2：事务1和事务2同时开启写锁

事务1：开启排他锁

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select name,id from user for update;

事务2：开启排他锁

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select name,id from user for update;

事务2进入阻塞状态

场景3：事务1开启排他锁事务2开启共享锁

事务1：开启排他锁

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select name,id from user for update;

事务2：开启共享锁

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select name,id from user lock in share mode;

事务2进入阻塞状态

MySQL8.0新特性

在5.7及之前的版本，SELECT … FOR UPDATE ,如果获取不到锁，会一直等待，直到超时。在8.0版本中，添加SELECT … FROM TABLE FRO UPDATE NOWAIT、SKIP LOCKED语法，跳过锁等待，或者跳锁定

通过添加NOWAIT、SKIP LOCKED语法，能够立即返回。如果查询已经加了锁

• 那么NOWAIT会立即报错返回
• 而SKIP LOCKED也会立即返回，只是返回的结果中不包含被锁定的行。

 select name,id from user for update nowait;
 select name,id from user for update SKIP LOCKED;

意向锁 (intention lock)

InnoDB支持多粒度锁 (multiple granularity locking)，它允许行级锁与表级锁共存，而意向锁就是其中的一种表锁。

1、意向锁的存在就是为了协调行锁和表锁的关系，支持多粒度 (表锁与行锁) 的锁并存。

2、意向锁是一种不与行级锁冲突表级锁，这一点非常重要。

3、表明某个事务正在某些行持有了锁或该事务准备去持有锁。

意向锁分为两种：

• 意向共享锁 (IS)：事务有意向对表中的某些行加共享锁 (S锁)

  --事务要获取某些行的S锁，必须要先获得表得IS锁---
  SELECT column FROM 	table ...... LOCK IN SHARE MODE;
  

• 意向排他锁 (IX)：事务有意向对表中得某些行加排他锁 (X锁)

  --事务要获取某些行的X锁，必须要先获得表得IX锁---
  SELECT column FROM 	table ...... FOR UPDATE;
  

  意向锁是由存储引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InnoDB会先获取该数据行所在数据表的对应意向锁

  意向锁解决的问题

  现在有两个事务，分别是T1和T2，其中T2试图在该表级别上应用共享锁和排他锁，如果没有意向锁存在，那么T2就需要去检查各个页或行是否存在锁；如果存在锁，那么此时就会受到由T1控制的表级别意向锁的阻塞。T2在锁定该表前不必检查各个页或行锁，而只需检查表上的意向锁。简单来说就是给更大一级别的空间示意里面是否已经上过锁。

  在数据表的场景中，**如果我们给某一行数据加上了排他锁，数据库会自动给更大一级的空间，比如数据页或数据表加上意向锁，告诉其他人这个数据页或数据表已经有人上过排他锁了，**这样当其他人想要获取数据表排他锁的时候，只需要了解是否有人已经获取了这个数据表的意向排他锁即可。

  • 如果事务想要获取数据表中某些记录的共享锁，就需要在数据表上添加意向共享锁
  • 如果事务想要获取数据表中某些记录的排他锁，就需要在数据表上添加意向排他锁

  这时，意向锁会告诉其他事务已经有人锁定了表中的某些记录。

举例：意向锁的使用场景模拟

场景1：事务1开启意向排他锁，事务2尝试开启表锁

事务1：开启意向排他锁

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select name,id from user where id = 1 for update;
+--------+----+
| name   | id |
+--------+----+
| 张三   |  1 |
+--------+----+
1 row in set (0.00 sec)

事务2：尝试开启表锁

mysql> lock tables user read;

此时事务2开启表锁失败进入阻塞状态

场景2：事务1 和事务2同时对某条记录开启意向排他锁

事务1：开启意向排他锁

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select name,id from user where id = 1 for update;
+--------+----+
| name   | id |
+--------+----+
| 张三   |  1 |
+--------+----+
1 row in set (0.00 sec)

事务2：开启意向排他锁

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select name,id from user where id = 1 for update;

此时事务2进入阻塞状态。原因并不是意向锁进行互斥，而且X锁进行互斥，所以事务2是阻塞状态。

场景3：意向排他锁是兼容的

事务1：开启意向排他锁

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select name,id from user where id = 1 for update;
+--------+----+
| name   | id |
+--------+----+
| 张三   |  1 |
+--------+----+
1 row in set (0.00 sec)

事务2：开启意向排他锁

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select name,id from user where id = 3 for update;
+--------+----+
| name   | id |
+--------+----+
| 王五   |  3 |
+--------+----+
1 row in set (0.00 sec)

可以看到两个意向排他锁是兼容的，没有进入阻塞状态。

从上面的案例可以得到如下结论：

1. InnoDB支持多粒度锁，特定情境下，行级锁与表级锁共存。
2. 意向锁之间互不排斥，但除了Is与S兼容外，意向锁会与共享锁/排他锁互斥。
3. IS、IX是表级锁，不会和行级X，S发生冲突。只会和表级得S、X发生冲突。
4. 意向锁在保证并发性的前提下，实现了行锁和表锁共存且满足事务隔离性的要求。

元数据锁 (MDL锁)

MySQL5.5引入了meta data lock ,简称DML锁，属于表锁范畴。MDL的作用是，保证读写的正确性。比如，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，增加一列，那么查询线程拿到的结构跟表结构对不上，肯定是不行的。

因此，当对一个表做增删改查时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性，解决了DML和DDL操作之间的一致性问题。不需要显示使用，在访问一个表的时候会自动加上。

举例：元数据锁的使用场景模拟

场景1：事务1查询数据并且不提交，事务2修改更改表结构操作

会话A：从表中查询数据

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select name from user;
+--------+
| name   |
+--------+
| 李四   |
| 张三   |
+--------+
2 rows in set (0.00 sec)

会话B：进行DDL操作

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table user add age int;

此时会话B会进入阻塞状态，直到会话A提交为止

场景2：读读操作是兼容的，事务1和事务2 同时开启查询数据，不会受影响

会话A：从表中查询数据

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select name from user;
+--------+
| name   |
+--------+
| 李四   |
| 张三   |
+--------+
2 rows in set (0.00 sec)

会话B：从表中查询数据

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select name from user;
+--------+
| name   |
+--------+
| 李四   |
| 张三   |
+--------+
2 rows in set (0.00 sec)

场景3：事务1开启查询操作，事务2进行DDL操作，事务3开启查询操作

会话A：从表中查询数据

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select name from user;
+--------+
| name   |
+--------+
| 李四   |
| 张三   |
+--------+
2 rows in set (0.00 sec)

会话B：进行DDL操作

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table user add id int;

会话C：开启查询操作

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select name from user;

由于会话B进行DDL操作导致阻塞了，所以会话C也进入阻塞状态
MySQL | 行锁——记录锁、间隙锁 、临键锁、插入意向锁