【MySQL】锁与隔离级别

一、前言

1. 快照读的幻读通过 mvcc 解决
2. 当前读的幻读通过 next-key锁 解决
3. 读提交隔离级别一般没有 gap lock
4. 可重复读隔离级别下，如果触发了当前读，那也是要保证事务存续期间的数据一致性的，具体怎么保证呢？答案是加锁，阻塞破坏本事务数据一致性的其他事务。

二、表锁

三、MDL锁

四、行锁

行锁为InnoDB引擎独有的，即行锁是引擎层实现的

(一) 索引与锁

可重复读时：
  InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行，那么在InnoDB检索到数据并返回给服务器层以后， MySQL 服务器才能应用WHERE子句。 这时已经无法避免锁定行了： InnoDB 已经锁住了这些行，事务提交时才会释放。即在没有使用索引的情况下，InnoDB会锁住整张表。

example:

name字段无索引：
+----+---------------------+------+
| id | dat                 | name |
+----+---------------------+------+
|  1 | 2020-01-24 12:00:55 | xxx  |
|  2 | 2020-01-24 12:01:34 | yyy  |
|  3 | 2020-02-23 12:25:24 | zzz  |
+----+---------------------+------+

Session A：
START TRANSACTION WITH CONSISTENT SNAPSHOT;
UPDATE tb_date SET name='zzz' WHERE id=3;

Session B：
UPDATE tb_date SET name='yyy' WHERE name='yyy';

其中Session B会被阻塞住，原因是Session A锁住了 id=3 这一行，而Session B试图对所有行进行加锁

读提交时：
在MySQL 5.1和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁，该特性存在于读提交隔离级别下，可重复读无该特性

五、gap锁^[1]

  gap锁之间不互斥，与其他锁之间也不互斥，只与 INSERT 操作互斥，gap锁用于锁间隙。
   读提交隔离级别一般没有gap lock ( 不过也有例外情况， 比如insert 出现主键冲突的时候，也可能加间隙锁？在外键场景下还是有间隙锁？)

example:
有表格

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

执行语句:

Session A:
mysql> BEGIN;
mysql> SELECT d FROM t WHERE c=5 FOR UPDATE;
或
mysql> SELECT id FROM t WHERE c=5 FOR UPDATE;

Session B:
mysql> INSERT INTO t VALUES (8, 8, 8);

上述的 Session B 会被 Session A 阻塞，其原因在于，为保持Session A 事务存续期间的数据一致性，InnoDB给范围 c(0, 10) 之间加了间隙锁(因为这个范围内是有可能被其他事务插入c=5的行的)。
总结： Session A 给id=5这一行加了行锁，同时在索引页c上加了范围为(0, 10)的 gap 锁 (注：该语句不会在聚簇索引页上加gap锁)

覆盖索引的优化：
执行语句:

Session A:
mysql> BEGIN;
mysql> SELECT id FROM t WHERE c=5 LOCK IN SHARE MODE;

Session B:
mysql> UPDATE t SET d=d+1 WHERE c=5;

Session B 不会被阻塞，原因是只有访问到的对象才会加锁，Session A查询的是id，可以直接使用c索引页上的覆盖索引获取结果，因此没有在聚簇索引页上对 id=5 这一行加锁，但如果 Session A 执行的是 FOR UPDATE 就不一样了，因为系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。

Note：

1. 在使用索引查询的情况下，若所查询的行不在，则不会加行锁，但还是会在合适的范围内加上gap锁，这依然是为了保证事务存续期间的数据一致性
2. 若不使用索引查询，则会走全表查询，InnoDB会将所有行加上行锁，同时会对所有间隙加上gap锁，事实上，InnoDB不会去隐式的调用表级锁，锁整张表就是通过这种方式实现的

六、next-key lock

间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间，譬如语句

mysql> SELECT d FROM t WHERE c=5 FOR UPDATE;

加了 next-key锁(0, 5] 和 gap锁(5, 10)

加锁及优化规则^[2]：
规则1： InnoDB加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间
规则2： 查找过程中访问到的对象才会加锁
优化1： 索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁
优化2： 索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁
规则3： 范围查询，向右遍历查询时，先确定左边界的 next-key 锁，对于唯一性索引，向左搜索到第一个满足条件的值为止，对于非唯一索引，向左搜索到第一个不满足条件的值为止；再确定右边界的next-key锁，不管索引是否唯一，都是向右搜索到第一个不满足条件的值为止。向左遍历查询时则反之。
例如：

mysql> SELECT * FROM t WHERE id>=10 AND id<=20 FOR UPDATE;

先确定左边界，id为唯一性索引，向左搜索到第一个满足条件的值10，因此加上next-key lock (10, 15]，因为id>=10隐含着等值查询id=10，根据优化1，需在加上行锁10，再确定右边界，向右搜索到第一个不满足条件的值25，加next-key锁 (20, 25]，得出该语句的加锁范围为 10, (10, 15], (15, 20], (20, 25]
再例如：

mysql> SELECT * FROM t WHERE c>=10 AND c<=20 FOR UPDATE;

先确定左边界，c为非唯一性索引，向左搜索到第一个不满足条件的值5，因此加上next-key lock (5, 10]，再确定右边界，向右搜索到第一个不满足条件的值25，加next-key锁 (20, 25]，得出该语句的加锁范围为 (5, 10], (10, 15], (15, 20], (20, 25]
规则4： Limit 可减小加锁范围^[2][3]
规则5： ORDER BY DESC 是会改变遍历方向的哦^[2]！

Note： 我们在分析加锁规则的时候可以用 next-key lock 来分析。但是要知道，具体执行的时候，是要分成间隙锁和行锁两段来执行的，先加gap锁，再加行锁^[2]。

七、如何选择隔离级别

  互联网项目中一般把隔离级别设为读提交，然后把binlog的格式设为Row^[4] 。

Ref

[1] Mysql中的GAP锁(间隙锁)
[2] (极客时间)为什么我只改一行的语句，锁这么多？
[3] MySQL 中关于gap lock / next-key lock 的一个问题
[4] 互联网项目中mysql应该选什么事务隔离级别