mysql的并发处理机制_下篇 - 苏家小萝卜(4)_H5之家

注意这里有个误区，很多小伙伴会认为，那么这么加gap锁，则意味着，当update age=21这一列时， 9<age<25 ，这个范围内，是不允许进行 UPATE/DELETE/INSERT的。这种推测实际上是不完整的，因为它没考虑到跟主键！！！

注意，每次写gap lock的时候，都是有加上主键值的。比如这里，当更新 age=21这列时，加了（(9,2) ,（21,3)），（(21,3), (21,25)）这两个范围的 GAP LOCK，那么在当前update age=21的事务还没有结束的情况下，假设有两条修改SQL的语句：

update tbname set age=9 where id = 1;

update tbname set age=9 where age = 4;

这两条SQL，是能够正常执行，还是堵塞呢？

innodb中，索引按照二叉树排列顺序，而这两条SQL修改后在IX_AGE上的索引值分别为：（9，1）、（9，4），可以发现（9，1）在键值（9，2）的左边，不在GAP LOCK的范围内，所以，可以正常执行；而（9，4）在键值的右边，刚好在GAP LOCK的范围内，会被堵塞！总结：第一条UPDATE SQL，正常秩序；第二条UPDATE SQL会被堵塞。

所以，考虑GAP LOCK的时候，一定要注意结合整个索引键值来分析，而索引键值=索引值+主键。

2.3.2 RR下的唯一索引加锁情况

update tbname set name=... where age between .. and ...

因为唯一索引上面的索引键值都是唯一的，故不会出现重复值的插入的情况，下表罗列了同样的范围查询修改语句，在唯一索引及非唯一索引上加 GAP_LOCK的情况。

表格数据如下：

加GAP_LOCK的情况如下（注意注意，方便查看，省略了主键值，实际上是需要添加上主键键值的）：

update tbname set name=...

where age between 1 and 7

update tbname set name=...

where age between 2 and 7

update tbname set name=...

where age between 5 and 10

update tbname set name=...

where age between 15 and 50

tb_index

(-∞,2),(2,4),(4,7),(7,9)

(4,7),(7,9),(9,21)

(9,21),(21,25),(25,+∞)

tb_unique_index

(-∞,2),(2,4),(4,7)

(2,4),(4,7)

(4,7),(7,9),(9,21)

(9,21),(21,25),(25,+∞)

2.4 Read Serializable

所有事务隔离级别设置： set session transaction isolation level Serializable ;

表格 SQL

select * from tbname

where age/id ...

update tbname set name=...

where id = 4

update tbname set name=...

where age = 21

update tbname set name=...

where age between 5 and 15

tb_no_index

不支持快照读，所有SELECT都是当前读，所有SELECT操作都需要加S锁,除主键定值查找\唯一索引定值查找外,其他基于索引或者主键的范围查找都会添加 S GAP LOCK。并发度是四个隔离级别中性能最差的。

当前读，根据主键修改数据

tbname 加意向表锁 IX

id=4 加行锁 X

表格的age列无索引，所以update过程中

全表加X锁，期间全表堵塞UPDATE\DELETE\INSERT

同左

tb_index

表格的age列有索引，update过程中

tb_index 加表格意向锁 IX

age索引上面，age=21 行添加行锁 X

再在主键上，给id=3 这一行数据，添加行数 X

在age索引上添加两个gap lock （(9,2) ,（21,3)），（(21,3), (21,25)）

表格的age列有索引，update过程涉及age=7,9 两行数据

tb_index 加表格意向锁 IX

age索引上面，age=7，age=9 行添加行锁 X

再在主键上，给id=2，id=5 这一行数据，添加行数 X

同时会在索引 age的值上添加 3个 gap lock，分别为

（(4,4)，(7,5)）、（(7,5)，(9,2)）、（(9,2)，(21,3)）

tb_unique_index

唯一索引列上每一个age都是唯一的，也就是age=21只有一个，不会再INSERT一个新的 age =21进来，故在这里不需要加gap lock，加锁情况如下：

tb_index 加表格意向锁 IX

age=21 行添加行锁 X

表格的age列有索引，update过程涉及age=7,9 两行数据

tb_index 加表格意向锁 IX

age索引上面，age=7，age=9 行添加行锁 X

再在主键上，给id=2，id=5 这一行数据，添加行数 X

同时会在索引 age的值上添加 3个 gap lock，分别为

（(4,4)，(7,5)）、（(7,5)，(9,2)）、（(9,2)，(21,3)）

但是，范围查询添加到gap lock在其他情况下跟非唯一索引会有一些差别，可以看下表的例子。

这里详细的来分析下 RS 隔离级别下的SELECT操作加的锁：

表格 SQL

select * from tbname

where id=5

select * from tbname

where id betwee 5 and 15

select * from tbname

where age=21

select * from tbname

where age betwee 5 and 9

tb_no_index

主键定值查找

表格tbname 添加 IS 意向锁

id=5 添加 S锁

主键范围查找

表格tbname 添加 IS 意向锁

id=5,id=6 两行数据添加 S锁

同时添加2个 S GAP LOCK ,分别为（(5,7),(6,25)）跟（(6,25),+∞）

全表查找

表格 tbname 添加 IS 意向锁

由于全表查找，整个表格再次添加 S 表锁

全表查找

表格 tbname 添加 IS 意向锁

由于全表查找，整个表格再次添加S 表锁

tb_index

ix_age索引查找

表格tbname 添加 IS 意向锁

索引上 age = 21 添加 S 行锁

主键上 id=3 添加 S 行锁

同时添加 2个 S GAP LOCK ，分别为（(9,2) ,（21,3)），（(21,3), (21,25)）

ix_age索引查找

表格tbname 添加 IS 意向锁

age索引上面，age=7，age=9 行添加行锁 S

再在主键上，给id=2，id=5 这一行数据，添加行数 S

同时会在索引 age的值上添加 3个 S gap lock，分别为

（(4,4)，(7,5)）、（(7,5)，(9,2)）、（(9,2)，(21,3)）

tb_unique_index

ix_age索引查找

表格tbname 添加 IS 意向锁

索引上 age = 21 添加 S 行锁

主键上 id=3 添加 S 行锁

由于age列唯一，故不需要添加GAP LOCK

ix_age索引查找

表格tbname 添加 IS 意向锁

age索引上面，age=7，age=9 行添加行锁 S

再在主键上，给id=2，id=5 这一行数据，添加行数 S

同时会在索引 age的值上添加 2 个 S gap lock，分别为

（(4,4)，(7,5)）、（(7,5)，(9,2)）

至此，已说明了四个隔离级别是如何加锁，那么，释放锁呢？

在MySQL INNODB中，遵循的是 strong strict 2-PL，也就是所有的write lock 跟read lock 都是在事务 commit后才释放。

3 SQL分析

考虑到下文的例子，这里补充两个概念。

ICP：

MRR：

假设表格 tb_lock ( id int primary key not null, age int,score int,name varchar(10), key ix_age_score ( age, score ) ) 数据修改如下：

假设MySQL当前的隔离级别为 RR，执行 UPDATE tb_index WHERE age between 5 and 22 and score between 1 and 10 and name is not null，其执行计划如下：

那么，是如何加锁的呢？

首先，可以看到是根据索引 ix_age_score 查找，那么分为两种情况来分析，第一种，数据库支持ICP；第二种，数据不支持ICP。