MySQl 加锁分析(转)

本文整理自:http://hedengcheng.com/?p=771

本文,就 MySQL/InnoDB 的加锁问题,展开分析。MySQL 支持多种存储引擎,不同的存储引擎特性不同,下面的所有介绍,都是基于 InnoDB 存储引擎。

InnoDB 实现的是基于多版本的并发控制协议 —— MVCC,读不加锁,读写不冲突。

在 MVCC 并发控制中,读操作可以分成两类:快照读(snapshot read)与当前读(current read)。快照读,读取的是记录的可见版本(有可能是历史版本),不用加锁。当前读,读取的是记录的最新版本,并且,当前读返回的记录,都会加上锁,保证其他事务不会再并发修改这条记录。

在一个支持 MVCC 并发控制的系统中,哪些读操作是快照读?哪些操作又是当前读呢?以 MySQL InnoDB 为例:

  • 快照读:简单的 select 操作,属于快照读,不加锁。读取的是记录的可见版本:
select * from table where 
  • 当前读:特殊的读操作,插入/更新/删除操作,属于当前读,需要加锁。读取的是记录的最新版本,当前读返回的记录,都会加上锁:
select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;

一条简单 SQL 的加锁实现分析

下面两条简单的SQL,他们加什么锁?

SQL1: select * from t1 where id = 10;
SQL2: delete from t1 where id = 10;

SQL1:不加锁。因为 MySQL 是使用多版本并发控制的,读不加锁。
SQL2:对 id = 10 的记录加写锁。

可能是正确的,也有可能是错误的,已知条件不足,要回答这个问题,一些前提条件?

前提一:id列是不是主键?
前提二:当前系统的隔离级别是什么?
前提三:id列如果不是主键,那么id列上有索引吗?
前提四:id列上如果有二级索引,那么这个索引是唯一索引吗?
前提五:两个 SQL 的执行计划是什么?索引扫描?全表扫描?

缺少这些前提,直接就给定一条SQL,然后问这个SQL会加什么锁,都是很业余的表现。而当这些问题有了明确的答案之后,给定的SQL会加什么锁,也就一目了然。下面,将这些问题的答案进行组合,然后按照从易到难的顺序,逐个分析每种组合下,对应的SQL会加哪些锁?


组合一:id列是主键,RC隔离级别

id 是主键,Read Committed 隔离级别,给定SQL:

delete from t1 where id = 10; 

只需要将主键上,id=10 的记录加上X锁即可。

组合二:id列是唯一索引,RC隔离级别

id 是唯一索引,主键是 name 列,给定SQL:

delete from t1 where id = 10; 

由于 id 是唯一索引,因此 delete 语句会选择 id 列的索引进行 where 条件的过滤,在找到 id=10 的记录后,首先会将唯一索引上的 id=10 索引记录加上X锁,同时,会根据读取到的 name 列(主键),将主键索引(聚簇索引)上的 name='d' 对应的主键索引项加X锁

为什么聚簇索引上的记录也要加锁?

如果并发的一个SQL,是通过主键索引来更新:

update t1 set id = 100 where name = 'd';

此时,如果 delete 语句没有将主键索引上的记录加锁,那么并发的 update 就会感知不到 delete 语句的存在,违背了同一记录上的更新/删除需要串行执行的约束。

组合三:id列是非唯一索引,RC隔离级别

id列索引不再是唯一索引,,给定SQL:

delete from t1 where id = 10; 

满足 id=10 查询条件的记录,均已加X锁。同时,这些记录对应的主键索引上的记录也都加上了X锁。组合二唯一的区别在于,组合二最多只有一个满足查询条件的记录,而组合三会将所有满足查询条件的记录都加锁。

组合四:id列无索引,RC隔离级别

这个过滤条件,没法通过索引进行过滤,那么只能通过全表扫描做过滤,会对所有记录加上X锁(不是加表锁,也不是对满足条件的记录加锁)。

即全表扫描时,由于 id 列上没有索引,只能走聚簇索引,进行全部扫描。聚簇索引上所有的记录,都被加上了 X锁

为什么不是只在满足条件的记录上加锁呢?

这是由于 MySQL 的实现决定的。如果一个条件无法通过索引快速过滤,那么存储引擎层面就会将所有记录加锁后返回,然后由 MySQL Server 层进行过滤。因此也就把所有的记录,都锁上了。

在实际的实现中,MySQL 有一些改进,在 MySQL Server 过滤条件,发现不满足后,会调用 unlock_row 方法,把不满足条件的记录释放锁 (违背了2PL的约束)。这样做保证了最后只会持有满足条件记录上的锁,但是每条记录的加锁操作还是不能省略的。*

组合五:id列是主键,RR隔离级别

上面的四个组合,都是在 Read Committed 隔离级别下的加锁行为,接下来分析在 Repeatable Read 隔离级别下的加锁行为。对于 SQL:

delete from t1 where id = 10; 

加锁情况与组合一:[id列是主键,RC隔离级别]一致。

组合六:id列是唯一索引,RR隔离级别

加锁情况与组合二:[id列是唯一索引,RC隔离级别]一致。

组合七:id列是非唯一索引,RR隔离级别

RC隔离级别允许幻读,RR隔离级别下不允许幻读,如何防止幻读呢?对于 SQL:

delete from t1 where id = 10;

加锁情况与组合三:[id列是非唯一索引,RC隔离级别]最大的区别是多了GAP锁GAP锁是在两条记录之间的。

GAP锁有什么用?

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

如何保证两次当前读返回一致的记录,需要在第一次当前读与第二次当前读之间,保证其他的事务不会插入新的满足条件的记录并提交。GAP锁就是为了实现这个功能。

如上图所示,考虑到B+树索引的有序性,有以下位置可以插入新的满足条件的项(id=10):[6,c]与[10,b]间、[10,b]与[10,d]间、[10,d]与[11,f]。因此,为了这些区间不会插入新的满足条件的记录,使用GAP锁,将这三个GAP给锁起来。

如果此时 insert 插入一条[10,aa]记录,首先会定位到[6,c]与[10,b]间,然后在插入前,会检查这个GAP是否已经被锁上,如果被锁上,insert 不能插入记录。

既然防止幻读,需要靠GAP锁的保护,为什么组合五、组合六,也是RR隔离级别,却不需要加GAP锁呢?

GAP锁的目的,是为了防止同一事务的两次当前读,出现幻读的情况。而组合五,id 列是主键;组合六,id 列是唯一键,都能够保证唯一性。一个等值查询,最多只能返回一条记录,不存在GAP。

组合八:id列无索引,RR隔离级别

同样的这个过滤条件,没法通过索引进行过滤,那么只能通过全表扫描做过滤。最终的加锁情况,如下图所示:

可以看到,首先,聚簇索引上的所有记录,都被加上了X锁。其次,聚簇索引每条记录间的GAP,都被加上了GAP锁。这个示例表,只有6条记录,一共需要6个X锁,7个GAP锁。试想,如果表上有1000万条记录,会是非常恐怖的情况。

在这种情况下,这个表上,除了不加锁的快照读,其他任何加锁的并发 SQL,均不能执行,不能更新,不能删除,不能插入,全表被锁死。

与组合四:[id列无索引, Read Committed]类似,MySQL 做了一些优化,对于不满足查询条件的记录,MySQL 会提前放锁。

组合九:Serializable隔离级别

在 Serializable 隔离级别,对于SQL2:

delete from t1 where id = 10; 

与 Repeatable Read 隔离级别完全一致

而受到影响的是SQL1:

select * from t1 where id = 10;

在RC,RR隔离级别下,都是快照读,不加锁。但是在 Serializable 隔离级别,SQL1会加读锁,也就是说快照读不复存在,MVCC并发控制降级为 Lock-Based CC(基于锁的并发控制)。

在 MySQL/InnoDB 中,所谓的读不加锁,并不适用于所有的情况,而是与隔离级别相关的。Serializable 隔离级别,读不加锁就不再成立,所有的读操作,都是当前读。


一条复杂 SQL 的加锁分析

id 作为主键,有联合索引 idx_t1_pu(时间戳,UserId),SQL如下:

delete from t1 
where pubtime > 1 and pubtime < 20 and userid='hdc' and comment is not null;

假定在 Repeatable Read 隔离级别下,SQL使用的是 idx_t1_pu 索引,首先分析 SQL where 构成

Index key:
    pubtime > 1 and puptime < 20。 在 idx_t1_pu 索引上的查询范围。
Index Filter:
    userid = 'hdc' 。在 idx_t1_pu 索引上进行过滤。
Table Filter:
    comment is not NULL。在 idx_t1_pu 索引上无法过滤,只能在聚簇索引上过滤。

再来看看这条SQL的加锁情况 (RR隔离级别)

由 Index Key 所确定的范围,被加上了GAP锁。Index Filter 锁给定的条件何时过滤,视MySQL的版本而定,在MySQL 5.6版本之前,不支持Index Condition Pushdown,在MySQL Server层过滤,在5.6后支持了ICP,则在index上过滤。若不支持ICP,不满足Index Filter的记录,也需要加上记录X锁,若支持ICP,则不满足Index Filter的记录,无需加记录X锁(图中,用红色箭头标出的X锁)。Table Filter对应的过滤条件,则在聚簇索引中读取后,在MySQL Server层面过滤,因此聚簇索引上也需要X锁。

最后,选取出了一条满足条件的记录[8,hdc,d,5,good],但是加锁的数量,要远远大于满足条件的记录数量。

在Repeatable Read隔离级别下,针对一个复杂的SQL,首先需要提取其where条件。Index Key确定的范围,需要加上GAP锁;Index Filter过滤条件,根据MySQL版本是否支持ICP,加X锁;Table Filter过滤条件,无论是否满足,都需要加X锁。


死锁原理与分析

理解MySQL如何加锁,有两个比较重要的作用:
写出不会发生死锁的SQL;定位出线上产生死锁的原因;

两个死锁的例子

1 两个Session的两条SQL产生死锁

非常好理解,也是最常见的死锁,每个事务执行两条SQL,分别持有了一把锁,然后加另一把锁,产生死锁。

2 两个Session的一条SQL,产生死锁

第二个用例,虽然每个Session都只有一条语句,仍旧会产生死锁。需要用到本文前面提到的MySQL加锁的规则。
Session 1,从name索引出发,读到的[hdc, 1],[hdc, 6]均满足条件,不仅会加name索引上的记录X锁,而且会加聚簇索引上的记录X锁,加锁顺序为先[1,hdc,100],后[6,hdc,10]。
Session 2,从pubtime索引出发,[10,6],[100,1]均满足过滤条件,同样也会加聚簇索引上的记录X锁,加锁顺序为[6,hdc,10],后[1,hdc,100],跟Session 1的加锁顺序正好相反,如果两个Session恰好都持有了第一把锁,请求加第二把锁,死锁就发生了。

死锁的发生与否,并不在于事务中有多少条SQL语句,死锁的关键在于:两个(或以上)的Session加锁的顺序不一致。分析MySQL每条SQL语句的加锁规则,分析出每条语句的加锁顺序,然后检查多个并发SQL间是否存在以相反的顺序加锁的情况,就可以分析出各种潜在的死锁情况,也可以分析出线上死锁发生的原因。


Tags:,

Add a Comment

电子邮件地址不会被公开。 必填项已用*标注

18 + 10 =