InnoDB锁机制

1. 锁类型

锁是数据库区别与文件系统的一个关键特性，锁机制用于管理对共享资源的并发访问。
InnoDB使用的锁类型，分别有：

共享锁（S）和排他锁（X）
意向锁（IS和IX）
自增长锁（AUTO-INC Locks）

1.1. 共享锁和排他锁

InnoDB实现了两种标准的行级锁：共享锁（S）和排他锁（X）

共享锁：允许持有该锁的事务读取行记录。如果事务 T1 拥有记录 r 的 S 锁，事务 T2 对记录 r 加锁请求：若想要加 S 锁，能马上获得；若想要获得 X 锁，则请求会阻塞。

排他锁：允许持有该锁的事务更新或删除行记录。如果事务 T1 拥有记录 r 的 X 锁，事务 T2 对记录 r 加锁请求：无论想获取 r 的 S 锁或 X 锁都会被阻塞。

S 锁和 X 锁都是行级锁。

1.2. 意向锁

InnoDB 支持多粒度的锁，允许一行记录同时持有兼容的行锁和表锁。意向锁是表级锁，表明一个事务之后要获取表中某些行的 S 锁或 X 锁。

InnoDB中使用了两种意向锁

意向共享锁（IS）：事务 T 想要对表 t 中的某些记录加上 S 锁
意向排他锁（IX）：事务 T 想要对表 t 中的某些记录加上 X 锁

例如：

SELECT ... LOCK IN SHARE MODE，设置了 IS 锁
SELECT ... FOR UPDATE，设置了 IX 锁

意向锁协议如下所示：

在一个事务对表 t 中某一记录 r 加 S 锁之前，他必须先获取表 t 的 IS 锁
在一个事务对表 t 中某一记录 r 加 X 锁之前，他必须先获取表 t 的 IX 锁

这些规则可以总结为下面的图表（横向表示一个事务已经获取了对应的锁，纵向表示另外一个事务想要获取对应的锁)：

IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突

	X	IX	S	IS
X	不兼容	不兼容	不兼容	不兼容
IX	不兼容	兼容	不兼容	兼容
S	不兼容	不兼容	兼容	兼容
IS	不兼容	兼容	兼容	兼容

当请求的锁与已持有的锁兼容时，则加锁成功；如果冲突的话，事务将会等待已有的冲突的锁释放

IX 和 IS 锁的主要目的是表明：某个请求正在或者将要锁定一行记录。意向锁的作用：意向锁是在添加行锁之前添加。当再向一个表添加表级 X 锁的时候

如果没有意向锁的话，则需要遍历所有整个表判断是否有行锁的存在，以免发生冲突
如果有了意向锁，只需要判断该意向锁与即将添加的表级锁是否兼容即可。因为意向锁的存在代表了，有行级锁的存在或者即将有行级锁的存在。因而无需遍历整个表，即可获取结果

意向锁使用 SHOW ENGINE INNODB STATUS 查看当前锁请求的信息：

TABLE LOCK table `test`.`t` trx id 10080 lock mode IX

1.3. 自增长锁

InnoDB中，对每个含有自增长值的表都有一个自增长计数器（aito-increment counter）。当对含有自增长计数器的表进行插入操作时，这个计数器会被初始化。执行如下语句会获得自增长的值

SELECT MAX(auto_inc_col) FROM t FOR UPDATE;

插入操作会依据这个自增长的计数器值加1赋予到自增长列。这种实现方式是AUTO_INC Locking。这种锁采用了一种特殊的表锁机制，为提高插入的性能，锁不是在一个事务完成后释放，而是在完成对自增长值插入的SQL语句后立即释放。虽然AUTO-INC Locking一定方式提升了并发插入的效率，但还是存在性能上的一些问题：

首先，对自增长值的列并发插入性能较差，事务必须等待前一个插入SQL的完成
其次，对于 insert... select 的大数据量插入会影响插入的性能，因为另一个插入的事务会被阻塞

InnoDB提供了一种轻量级互斥量的自增长实现机制，大大提高了自增长值插入的性能。提供参数innodb_autoinc_lock_mode来控制自增长锁使用的算法，默认值为1。他允许你在可预测的自增长值和最大化并发插入操作之间进行权衡。

插入类型的分类：

插入类型	说明
insert-like	指所有的插入语句，例如：insert、replace、insert ... select、replace... select、load data
simple inserts	指再插入前就确定插入行数的语句。例如：insert、replace等。注意：simple inserts不包含 insert ... on duplicate key update 这类sql语句
bulk inserts	指在插入前不能确定得到插入行数的语句，例如：insert ... select、 replace ... select、load data
mixed-mode inserts	指插入中有一部分的值是自增长的，一部分是确定的。例如：`insert into t1(c1, c2) values (1, 'a'), (NULL, 'b'), (5, 'c'), (NULL,'d');` 也可以指 `insert ... on duplicate key update` 这类sql语句

innodb_autoinc_lock_mode 在不同设置下对自增长的影响：

innodb_autoinc_lock_mode = 0

MySQL 5.1.22版本之前自增长的实现方式，通过表锁的AUTO-INC Locking方式

innodb_autoinc_lock_mode = 1（默认值）

对于『simple inserts』，该值会用互斥量（mutex）对内存中的计数器进行累加操作。对于『bulk inserts』会用传统的AUTO-INC Locking方式。这种配置下，如果不考虑回滚，自增长列的增长还是连续的。需要注意的是：如果已经使用AUTO-INC Locking方式去产生自增长的值，而此时需要『simple inserts』操作时，还需要等待AUTO-INC Locking的释放

innodb_autoinc_lock_mode = 2

对于所有『insert-like』自增长的产生都是通过互斥量，而不是AUTO-INC Locking方式。这是性能最高的方式。但会带来一些问题：

因为并发插入的存在，每次插入时，自增长的值是不连续的
基于statement-base replication会出现问题

因此，使用这种方式，任何情况下都需要使用row-base replication，这样才能保证最大并发性能和replication的主从数据的一致 |

2. 锁的算法

InnoDB存储引擎行锁的算法

Record Locks：单个行记录上的锁
Gap Locks：间隙锁，锁定一个范围，不包含记录本身
Next-Key Locking：Record Locks + Gap Locks，锁住一个范围 + 记录本身
Insert Intention Locks：插入易向锁

2.1. 行锁

行锁是加在索引记录上的锁，例如：SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE，会阻止其他事务插入、更新或删除 t.c1 = 10 的记录

行锁总是在索引记录上面加锁，即使一张表没有设置任何索引，InnoDB会创建一个隐藏的聚簇索引，然后在这个索引上加上行锁。

行锁使用 SHOW ENGINE INNODB STATUS 的输出如下：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
 
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

2.2. 间隙锁

间隙锁是加在索引记录间隙之间的锁，或者在第一条索引记录之前、最后一条索引记录之后的区间上加的锁。例如：SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 这条语句阻止其他的事务插入一条 t.c1 = 15 的记录，因为在10-20的范围值都已经被加上了锁。

间隙锁只在RR隔离级别中使用。如果一条sql使用了唯一索引（包括主键索引），那么不会使用到间隙锁

例如：id 列是唯一索引，下面的语句只会在 id = 100 行上面使用Record Lock，而不会关心别的事务是否在上述的间隙中插入数据。如果 id 列没有索引或者不是唯一索引，这个语句会在上述的间隙上加锁。

SELECT * FROM child WHERE id = 100 FOR UPDATE;

2.3. Next-Key锁

Next-Key Lock是结合了Gap Lock 和 Record Lock的一种锁算法。

当扫描表的索引时，InnoDB以这种形式实现行级的锁：遇到匹配的的索引记录，在上面加上对应的 S 锁或 X 锁。因此，行级锁实际上是索引记录锁。如果一个事务拥有索引上记录 r 的一个 S 锁或 X 锁，另外的事务无法立即在 r 记录索引顺序之前的间隙上插入一条新的记录。

假设有一个索引包含值：10，11，13和20。下列的间隔上都可能加上一个Next-Key 锁（左开右闭）

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

在最后一个区间中，Next-Key锁锁定了索引中的最大值到正无穷。

默认情况下，InnoDB启用 RR 事务隔离级别。此时，InnoDB在查找和扫描索引时会使用 Next-Key 锁，其设计的目的是为了解决『幻读』的出现。

当查询的列是唯一索引情况下，InnoDB会对Next-Key Lock进行优化，降级为Record Lock，即只锁住索引本身，而不是范围。

next-key 锁使用 SHOW ENGINE INNODB STATUS 输出如下：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
 
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

2.4. 插入意向锁

插入意向锁是一种在数据行插入前设置的gap锁。这种锁用于在多事务插入同一索引间隙时，如果这些事务不是往这段gap的同一位置插入数据，那么就不用互相等待。假如有4和7两个索引记录值。不同的事务尝试插入5和6的值。在不同事务获取分别的 X 锁之前，他们都获得了4到7范围的插入意向锁，但是他们无需互相等待，因为5和6这两行不冲突。

例如：客户端A和B，在插入记录获取互斥锁之前，事务正在获取插入意向锁。

客户端A创建了一个表，包含90和102两条索引记录，然后去设置一个互斥锁在大于100的所有索引记录上。这个互斥锁包含了在102记录前的gap锁。

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);
 
mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

客户端B 开启一个事务在这段gap上插入新纪录，这个事务在等待获取互斥锁之前，获取了一把插入意向锁。

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

插入意向锁使用 SHOW ENGINE INNODB STATUS 输出如下：

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

3. SQL加锁分析

给定两个SQL来分析InnoDB下加锁的过程：

SQL1：select * from t1 where id = 10;

SQL2：delete * from t1 where id = 10;

事务隔离级别为默认隔离级别Repeatable Read。而对于id不同的索引类型，会有不同的结论。（总结自何登成大神的 MySQL 加锁处理分析）

SQL1：在RC和RR下，因为MVCC并发控制，select操作不需要加锁，采用快照读。读取记录的可见版本（可能是历史版本）

针对SQL2：如下分不同情况

3.1. id主键

将主键上，id=10的记录加上 X 锁

3.2. id唯一索引

id不是主键，而是一个唯一的二级索引，主键是name列。加锁步骤如下：

会选择走id列的索引进行where条件的过滤。找到id=10的记录后，首先将唯一索引上id=10的索引记录加上 X 锁
同时，根据读取到的name列回主键索引（聚簇索引），然后将聚簇索引上的 name='d' 对应的主键索引记录添加 X 锁

聚簇索引加锁的原因：如果并发的一个SQL是通过主键索引来更新：update t1 set id = 100 where name = 'd'; 此时，如果delete语句没有将主键索引上的记录加锁，那么并发的update就会感知不到delete语句的存在。违背同一条记录的更新/删除需要串行执行的约束。

3.3. id非唯一索引

加锁步骤如下：

通过id索引定位到第一条满足条件的记录，加上 X 锁
这条记录的间隙上加上 GAP锁
根据读取到的name列回主键聚簇索引，对应记录加上 X 锁
返回读取下一条，重复进行... 直到第一条不满足 where id = 10 条件的记录 [11, f]，此时不需要加 X 锁，仍旧需要加 GAP 锁。结束返回

幻读解决：
这幅图中多了个GAP锁，并不是加到记录上的，而是加在两个记录之间的位置。GAP 锁就是 RR 隔离级别相对于 RC 隔离级别，不会出现幻读的关键。GAP锁保证两次当前读之前，其他的事务不会插入新的满足条件的记录并提交。

所谓幻读，就是同一个事务，连续做两次当前读 (例如：select * from t1 where id = 10 for update;)，那么这两次当前读返回的是完全相同的记录 (记录数量一致，记录本身也一致)，第二次的当前读，不会比第一次返回更多的记录 (幻象)。

如图中所示：考虑到B+树索引的有序性，有哪些位置可以插入新的满足条件的项 (id = 10)：

[6,c] 之前，不会插入id=10的记录
[6,c] 与 [10,b] 间，可以插入 [10, aa]
[10,b] 与 [10,d] 间，可以插入[10,bb]，[10,c]
[10,d] 与 [11, f] 间，可以插入[10,e]，[10,z]
[11,f] 之后，不会插入id=10的记录

因此，不仅将满足条件的记录锁上 (X锁)，同时还通过GAP锁，将可能插入满足条件记录的3个GAP给锁上，保证后续的Insert不能插入新的id=10的记录，也就杜绝了同一事务的第二次当前读，出现幻象的情况。

当id是唯一索引时，则不需要加GAP锁。因为唯一索引能够保证唯一性，对于where id = 10 的查询，最多只能返回一条记录，而且新的 id= 10 的记录，一定不会插入进来。

3.4. id无索引

当id无索引时，只能进行全表扫描，加锁步骤：

聚簇索引上的所有记录都加 X 锁
聚簇索引每条记录间的GAP都加上了GAP锁。

如果表中有上千万条记录，这种情况是很恐怖的。这个情况下，MySQL也做了一些优化，就是所谓的semi-consistent read。semi-consistent read开启的情况下，对于不满足查询条件的记录，MySQL会提前放锁。针对上面的这个用例，就是除了记录[d,10]，[g,10]之外，所有的记录锁都会被释放，同时不加GAP锁

4. 死锁分析与案例

死锁避免的一些办法：

如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

5.参考

mysql 官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html
MySQL 加锁处理分析

posted @ 2017-11-13 17:20 butterfly100 阅读(915) 评论(0) 编辑收藏举报

刷新页面返回顶部

butterfly100