<-- home

InnoDB存储引擎之——锁(2)

2019/07/25

锁的算法
- 锁的例子
阻塞&超时
死锁
- 死锁例子
锁升级

锁的算法

Record Lock
只锁住行。总是去锁定索引，如果没有建索引，就锁主键。
Gap Lock
锁住一个范围，不包含记录本身
Next-Key Lock (Previous-Key Lock)
锁住一个范围，包含记录本身。

假设一个索引有10,11,13,20这四个值。那么有如下五个区间：

(-∞, 10)
(10, 11)
(11, 13)
(13, 20)
(20, +∞)

Gap Lock 锁的就是上述区间。
Next-Key Locking 锁的是上述区间的左开右闭区间。
Previous-Key Locking 锁的是上述区间的左闭右开区间。

InnoDB 加锁有如下三条特殊规则：

当查询的索引仅含有唯一索引的时候，Next-Key Lock 会降级为 Record Lock。（联合唯一索引，每一个索引列都要查）
InnoDB 还会对锁住的辅助索引加 Next-Key Lock，并且会给下一个键值加 Gap Lock
插入某记录时候，会检查插入记录的下一条记录是否被锁住了，如果锁住了，则不允许插入（阻塞）。

InnoDB 在默认的 REPETABLE READ 隔离级别下，使用 Next-Key Lock，在 READ COMMITED 隔离级别下，仅使用 Record Lock

锁的例子

下面举几个例子来讲解：

CREATE TABLE z (a INT, b, INT, PRIMARY KEY(a), KEY(b))

INSERT INTO z SELECT 1, 1;
INSERT INTO z SELECT 3, 1;
INSERT INTO z SELECT 5, 3; 
INSERT INTO z SELECT 7, 6;
INSERT INTO z SELECT 10, 8

会话A：执行

SELECT * FROM z WHERE b=3 FOR UPDATE;

我们来分析会锁住哪些记录:
a 是聚集索引，加 Record Lock
b 是辅助索引，加 Next-Key Lock，下一个键值加 Gap Lock

索引 a 被锁住的记录为：

5 — Record Lock

索引 b 被锁住的记录为：

(1, 3] — Next-Key Lock
(3, 6) — Gap Lock

会话B：如果执行下面这些 SQL ，则都会被阻塞：

```
SELECT FROM z WHERE a = 5 LOCK IN SHARE MODE;
```
原因：a = 5 的索引已经被加了 Record Lock 的排它锁，所以无法再加一个共享锁了。
```
INSERT INTO z SELECT 4, 2;
```
原因： 2 落在区间 (1, 3] Next-Key Lock 中
```
INSERT INTO z SELECT 6, 5;
```
原因： 5 落在区间 (3, 6) Gap Lock 中
```
INSERT INTO z SELECT 2, 2;
```
原因： 欲插入 b = 2 的记录，而 b = 3 的记录已经被锁住了。因此插入被阻塞。插入 b = 0 的记录没有问题，因为 b = 1 没有被锁。（实践发现这里 b 等于 1 的记录也可以插入，但是 b = 2 的记录被 next-key lock 锁住了呀，为什么呢？）

实践了一些其它例子：

```
select * from z where b > 3;
```
不会锁住 a = 5 的记录。同时 b 的 next-key lock 区间为 (3, +∞)
可以给 b = 3 的记录加 S 锁 : select * from z where b = 3 lock in share mode;
不可以 insert b = 3 的记录: INSERT INTO z SELECT 2, 3;
```
select * from z where b >= 3;
```
会锁住 a = 5 的记录。同时 b 的 next-key lock 区间为 [3, +∞)
不可以给 b = 3 的记录加 S 锁 : select * from z where b = 3 lock in share mode;
不可以 insert b = 3 的记录: INSERT INTO z SELECT 2, 3;

阻塞&超时

错误代码: 1205

innodb_lock_wait_timeout: 用来控制等待的时间（默认是 50 秒），可以在 MYSQL 数据库运行时进行调整。
innodb_rollback_on_timeout: 用来设定是否在等待超时时对进行中的事务进行回滚操作（默认是 OFF，代表不回滚）。不可在启动后进行修改。

当发生超时，MYSQL 数据库会抛出一个 1205 的错误，事务出现异常，在大多数情况下不会自动回滚，需要应用层自己去控制是commit还是rollback。

死锁

多个事务因争夺锁资源，造成相互等待，若无外力作用，无法推进下去。
错误代码: 1213
两种方案：

超时机制
FIFO。谁先等待，谁先回滚。
wait-for graph
主动监测死锁，回滚 undo 代价最小的事务。
具体实现细节需要看源码，可能是通过如下两个数据结构去构造有向图：
- Transaction Wait Lists
- Lock Lists
  
  死锁检测通常采用深度优先的算法实现，在 INNODB1.2 版本之前，都是采用递归方式实现。而从 1.2 版本开始，对 wait- for graph 的死锁检测进行了优化，用非递归的方式实现。

死锁例子

例子一
例子二

锁升级

锁升级（Lock Escalation）是指将当前锁的粒度降低。举例来说，数据库可以把个表的 1000 个行锁升级为一个页锁，或者将页锁升级为表锁。如果在数据库的设计中认为锁是一种稀有资源，而且想避免锁的开销，那数据库中会频繁出现锁升级现象。
Microsoft SQL Server 数据库的设计认为锁是一种稀有的资源，在适合的时候会自动地将行、键或分页锁升级为更粗粒度的表级锁。这种升级保护了系统资源，防止系统使用太多的内存来维护锁，在一定程度上提高了效率。
即使在 Microsoft SQL Server20 版本之后，SQL Server 数据库支持了行锁，但是其设计和 INNODB 存储引擎完全不同，在以下情况下依然可能发生锁升级：

由一句单独的 SQL 语句在一个对象上持有的锁的数量超过了阈值，默认这个阈值为 5000。值得注意的是，如果是不同对象，则不会发生锁升级口

锁资源占用的内存超过了激活内存的 40%时就会发生锁升级

在 Microsoft SQL Server 数据库中，由于锁是一种稀有的资源，因此锁升级会带来一定的效率提高。但是锁升级带来的一个问题却是因为锁粒度的降低而导致并发性能的降低。

INNODB 存储引擎不存在锁升级的问题。因为其不是根据每个记录来产生行锁的，相反，其根据每个事务访问的每个页对锁进行管理的，采用的是位图的方式。因此不管个事务锁住页中一个记录还是多个记录，其开销通常都是一致的。