以下实验数据基于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