mysql innodb 加锁_mysql InnoDB加锁分析

以下实验数据基于MySQL 5.7。

假设已知一张表my_table，id列为主键

id

name

num

1

aaa

100

5

bbb

200

8

bbb

300

10

ccc

400

1. 查询命中聚簇索引(主键索引)

1.1 如果是精确查询，那么会在命中的索引上加record lock

// 在id=1的聚簇索引上加X锁

update my_table set name='a' where id=1;

// 在id=1的聚簇索引上加S锁

select * from my_table where id=1 lock in share mode;

1.2 如果是范围查询，那么

1.2.1 在RC隔离级别下，会在所有命中的行的聚簇索引上加record locks(只锁行)

// 在id=8和10的聚簇索引上加X锁

update my_table set name='a' where id>7;

// 在id=1的聚簇索引上加X锁

update my_table set name='a' where id<=1;

1.2.2 在RR隔离级别下，会在所有命中的行的聚簇索引上加next-key locks(锁住行和间隙)。最后命中的索引的后一条记录，也会被加上next-key lock。

// 在id=8、10(、+∞)的聚簇索引上加X锁

// 在(5,8)(8,10)(10,+∞)加gap lock

update my_table set name='a' where id>7;

// 在id=1、5的聚簇索引上加X锁

// 在(-∞,1)(1,5)加gap lock

update my_table set name='a' where id<=1;

1.3 如果查询结果为空，那么

1.3.1 在RC隔离级别下，什么也不会锁

1.3.2 在RR隔离级别下，会锁住查询目标所在的间隙。

// 在(1,5)加gap lock

update my_table set name='a' where id=2;

2. 查询命中唯一索引

假设上述表中，num列加了唯一索引

2.1 如果是精确查询，那么会在命中的唯一索引，和对应的聚簇索引上加record lock。

// 在num=100的唯一索引上加X锁

// 并在id=1的聚簇索引上加X锁

update my_table set name='a' where num=100;

2.2 如果是范围查询，那么

2.2.1 在RC隔离级别下，会在所有命中的唯一索引和聚簇索引上加record lock。同2.1

2.2.2 在RR隔离级别下，会在所有命中的行的唯一索引上加next-key locks。最后命中的索引的后一条记录，也会被加上next-key lock。

// 在num=100和num=200的唯一索引上加X锁

// 并在id=1和id=5的聚簇索引上加X锁

// 并在唯一索引的间隙(-∞,100)(100,200)加gap lock

update my_table set name='a' where num<150;

3. 查询命中二级索引(非唯一索引)

3.1 如果是精确查询，那么

3.1.1 在RC隔离级别下，同2.1，对命中的二级索引和聚簇索引加record lock

// 在name='bbb'的两条索引记录上加X锁

// 并在id=5和id=8的聚簇索引上加X锁

update my_table set num=10 where name='bbb';

3.1.2 在RR隔离级别下，会在命中的二级索引上加next-key lock，最后命中的索引的后面的间隙会加上gap lock。对应的聚簇索引上加record lock。

// 在name='bbb'的两条索引记录上加X锁

// 并在id=5和id=8的聚簇索引上加X锁

// 并在二级索引的间隙('aaa','bbb')('bbb','bbb')('bbb','ccc')加gap lock

update my_table set num=10 where name='bbb';

3.2 范围查询、模糊查询的情况比较复杂，此处不详述。可以用上述方法自己实验。

4.查询没有命中索引

假设上述表中，name列加了普通二级索引，num列没有索引

4.1 如果查询条件没有命中索引

4.1.1 在RC隔离级别下，对命中的数据的聚簇索引加X锁。根据MySQL官方手册[4]，对于update和delete操作，RC只会锁住真正执行了写操作的记录，这是因为尽管innodb会锁住所有记录，MySQL Server层会进行过滤并把不符合条件的锁当即释放掉[5]。同时对于UPDATE语句，如果出现了锁冲突(要加锁的记录上已经有锁)，innodb不会立即锁等待，而是执行semi-consistent read：返回改数据上一次提交的快照版本，供MySQL Server层判断是否命中，如果命中了才会交给innodb锁等待。因此加锁情况可以这样来认为：

// 在id=5的聚簇索引上加X锁

update my_table set num=1 where num=200;

// 先在id=1,5,8,10(全表所有记录)的聚簇索引上加X锁

// 然后马上释放id=1,8,10的锁，只保留id=5的锁

delete from my_table where num=200;

4.1.2 在RR隔离级别下，事情就很糟糕了，对全表的所有聚簇索引数据加next-key lock

// 在id=1,5,8,10(全表所有记录)的聚簇索引上加X锁

// 并在聚簇索引的所有间隙(-∞,1)(1,5)(5,8)(8,10)(10,+∞)加gap lock

update my_table set num=100 where num=200;

// 尽管name列有索引，但是like '%%'查询不使用索引，因此此时也是锁住所有聚簇索引，情况和上面一模一样

update my_table set num=100 where name like '%b%';

5. 对索引键值有修改

假设上述表中，name列加了二级索引

??如果一条update语句，对索引键值有修改，那么修改前后的数据如何加锁呢。这点要结合数据多版本的可见性来考虑：无论是聚簇索引，还是二级索引，只要其键值更新，就会产生新版本。将老版本数据deleted bti设置为1；同时插入新版本[6]。因此可以认为，一次索引键值的修改实际上操作了两条索引数据：原索引和修改后的新索引。

??从innodb的事务的角度来看，如果一个事务操作(写)了一条数据，那么这条数据一定要加锁。因此可以认为，如果修改了索引键值，那么修改前和修改后的索引都会加锁。另外，由于修改的数据并没有被作为查询条件，那么也不会有“不可重复读”和“幻读”的问题，因此无需加gap lock，索引修改只会加X record lock。

如果被修改的数据也作为查询条件，那么加锁方式与上面提及的相同。

示例(RC和RR级别效果一样)：

// 在id=1的聚簇索引上加X锁

// 并在name='aaa'(name列索引原键值)和name='eee'(新键值)的索引上加锁

update my_table set name='eee' where id=1;

6. 插入数据

假设上述表中，name列加了二级索引

insert加锁过程：

唯一索引冲突检查：表中一定有至少一个唯一索引，那么首先会做唯一索引的冲突检查。innodb检查唯一索引冲突的方式是，对目标的索引项加S锁(因为不能依赖快照读，需要一个彻底的当前读)，读到数据则唯一索引冲突，返回异常，否则检查通过。

对插入的间隙加上插入意向锁(Insert Intention Lock)

对插入记录的所有索引项加X锁

示例：

// 先对id=15加S锁

// 再对间隙id(10,+∞)和name('ccc',+∞)加Insert Intention Lock

// 然后在id=15的聚簇索引上加X锁(S锁升级为X锁)

// 并在name='fff'的索引上加X锁

insert into my_table (`id`, `name`, `num`) values ('15', 'fff', '800');

注意，如果有其他事务同时插入insert into my_table (`id`, `name`, `num`) values (‘16‘, ‘fff‘, ‘900‘);也是可以插入的，因为虽然name列的索引值相同，但是聚集索引列不同，这并不算同一位置。

还有一个有趣的问题，如果插入的二级索引键值已经存在，那么这个插入意向锁会加在哪个间隙中呢？

??顾名思义，插入意向锁锁定的间隙一定是将要插入的索引的位置，如果二级索引键值相同，默认会按照聚簇索引的大小来排序(二级索引在存储上其实就是{索引值,主键值})。例如：

sql / 插入意向锁加在间隙 ({‘aaa‘,1},{‘bbb‘,5}) 上 insert into my_table (`id`, `name`, `num`) values (‘4‘, ‘bbb‘, ‘800‘); // 插入意向锁加在间隙 ({‘bbb‘,5},{‘bbb‘,8}) 上 insert into my_table (`id`, `name`, `num`) values (‘6‘, ‘bbb‘, ‘800‘); // 插入意向锁加在间隙 ({‘bbb‘,8},{‘ccc‘,10}) 上 insert into my_table (`id`, `name`, `num`) values (‘11‘, ‘bbb‘, ‘800‘);

7.隐式锁

为了降低锁的开销，innodb采用了延迟加锁机制，即隐式锁(implicit lock)[7]。

从数据存储结构上看，每张表的数据都是挂在聚簇索引的B+树下面的叶子节点上(每个节点代表一个page，每个page存放着多行数据)。每行存储的信息项中都会存有一隐藏列事务id。当有事务对这条记录进行修改时，需要先判断该行记录是否有隐式锁(原记录的事务id是否是活动的事务)，如果有则为其真正创建锁并等待，否则直接更新数据并写入自己的事务id。

二级索引虽然存储上没有记录事务id，但同样可以存在隐式锁，只不过判断逻辑复杂一些，需要依赖对应的聚簇索引做计算。

当然，隐式锁只是一个实现细节，显示还是隐式加锁并不影响上文对加锁的判断。

另外，聚簇索引每行记录的事务id，还有一个重要作用就是实现MVCC快照读：由于事务id是全局递增的，那么进行快照读的时候，如果数据的事务id小于当前事务id并且不在活跃事务列表内(尚未提交)，则直接返回当前行数据。否则需要根据roll pointer(和事务id一样，也在每行的隐藏列中)去查找undo日志.

### 8.一个RC隔离级别下的死锁

其实可以看到，RC隔离级别下的加锁已经很少了，用官方文档的话说”greatly reduces the probability of deadlocks”。因此尽管MySQL的默认隔离级别是RR，但是互联网应用更倾向与使用RC来避免死锁+提高并发能力。例如阿里电商的MySQL默认级别就是RC。

还是以这个表来举例，假设id为主键，num列无索引。

id

name

num

1

aaa

100

5

bbb

200

8

bbb

300

按以下顺序执行事务：

trx1

trx2

insert into my_table (id, name, num) values (‘16’, ‘rrr’, ‘888’);

-

-

insert into my_table (id, name, num) values (‘17’, ‘ttt’, ‘999’);

delete from sys.my_table where num=300; // waiting

-

-

delete from sys.my_table where num=400; // deadlock

对照上文的加锁逻辑，insert会对聚簇索引加X锁，因此trx1和trx2首先会分别持有id=16和id=17的X锁。

接下来坑爹的事情来了，对于无索引字段，delete操作不会执行semi-consistent read，而是先直接锁住所有数据的聚簇索引(尽管后面会马上释放，但也需要先获取锁)。这样一来，事务1的delete需要锁住所有记录，等待事务2持有的id=17的X锁，而事务2的delete需要等待事务1的id=16的X锁。死锁就产生了。

在这个例子中，如果insert和delete的顺序都颠倒一下，或者delete都变为update，死锁都不会发生。

9.小结

索引记录的间隙上用来避免幻读。

Select(Serializable隔离级别除外)不会加锁，而是执行快照读。

写操作都会加锁，具体加锁方式取决于隔离级别、索引命中情况以及修改的索引情况。

为了减少锁的范围，避免死锁的发生，应该尽量让查询条件命中索引，而且命中的越精确加锁越少。同时如果能接受RC级别对一致性的破坏，可以将隔离级别调整成RC。

10.参考资料

原文：https://www.cnblogs.com/bo2256321/p/10511846.html