MySQL选用可重复读之前一定要想到的事情

时间:2021-07-03 05:18:09

原文地址:http://blog.itpub.net/29254281/viewspace-1398273/ 

MySQL选用可重复读隔离级别之前一定要想到的事情.
间隙锁

MySQL在使用之前有三个务必要知道..(随着了解的深入这个极有可能再增加..)
1.DDL会引起metadata lock,导致请求连环阻塞,甚至是查询请求.
http://blog.itpub.net/29254281/viewspace-1383193/

2.MySQLDump和XtraBackup的flush table命令会引起waiting for table连环阻塞,同样也会阻塞查询请求.
http://blog.itpub.net/29254281/viewspace-1392757/

3.选用可重复读事务隔离级别,会开启间隙锁.他锁定的内容比实际需要的要多,并且很可能导致死锁.

本文是何登成大神文章的读后感和总结.
链接如下:
http://hedengcheng.com/?p=771
(关于这方面最好的文章,没有之一)

Oracle和MySQL(读提交和可重复读)都实现了MVCC多版本并发控制.
这意味着普通的Select(一致性读或者说快照读)可以以非锁定的形式读取数据.

而当前读(oracle的db block gets)都需要加锁.
比如:
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对记录上共享锁,其余都是排他锁.

MySQL虽然在可重复读事务隔离级别实现了避免幻读.
但是可重复读隔离级别的当前读,会启动间隙锁.

当前读(锁定读)加锁情况.

  读提交 可重复读
主键索引 锁定主键索引 锁定主键索引
唯一索引 锁定唯一索引的值和主键索引的值 锁定唯一索引的值和主键索引的值
普通索引 锁定普通索引的值和主键索引的值 锁定普通索引的值和主键索引的值,普通索引增加间隙锁
无索引 锁定所有的主键索引,在服务器层对不符合条件的记录解锁 锁定所有的主键索引和主键索引的间隙


根据何老师的例子(另记一份,现在的网站也是说没就没)
查看同样的SQL
(delete from t1 where id = 10;)
在不同的情况下,锁定的情况.

首先是读提交事务隔离级别,
如果id是主键索引,锁定主键索引的键值
MySQL选用可重复读之前一定要想到的事情

如果id是唯一索引,锁定唯一索引和主键索引的相关键值.
MySQL选用可重复读之前一定要想到的事情

如果id是普通索引,则锁定普通索引和主键索引的相关键值.
MySQL选用可重复读之前一定要想到的事情

最后,如果id没有索引,在InnoDB层,会对所有主键索引上排他锁,到MySQL服务器层对不符合条件的记录进行解锁.
MySQL选用可重复读之前一定要想到的事情

而在可重复读事务隔离级别,
如果id是主键索引,和读提交一样,会锁定主键索引的相关键值.

如果id是唯一索引,MySQL会将间隙锁退化为行级锁,仅仅锁定唯一索引和主键索引的相关键值.同读提交隔离级别一样.
例外:
如果是组合的唯一索引 create unique index inx_1 on test(a,b,c);
但是查询只用到了a,b select * from test where a=? and b=? for update
MySQL这时不会退化为行级锁,他的处理方式会等同于下面说到的普通索引.(行级锁+间隙锁)

如果id是普通索引,MySQL会锁定普通索引和主键索引的相关键值,并且锁定相关普通索引之间的间隙.
MySQL选用可重复读之前一定要想到的事情

以上图为例,select * from t1 where id=100 for update;
这个SQL没有查到任何的记录,但是他同样会上间隙锁.
(在这种情况下,即使id是主键索引或者唯一索引,也会产生间隙锁)

在可重复读的隔离级别下,如果id没有索引
他会锁定主键索引的所有记录和所有间隙.
和读提交不一样,在MySQL服务器层,并不会对不符合条件的记录解锁.并且它会锁定主键索引的所有间隙.
MySQL选用可重复读之前一定要想到的事情

参考何老师文章中的一个例子
MySQL选用可重复读之前一定要想到的事情

在可重复读隔离级别下,
Index key:pubtime > 1 and puptime < 20。此条件,用于确定SQL在idx_t1_pu索引上的查询范围。
Index Filter:userid = ‘hdc’ 。此条件,可以在idx_t1_pu索引上进行过滤,但不属于Index Key。
Table Filter:comment is not NULL。此条件,在idx_t1_pu索引上无法过滤,只能在聚簇索引上过滤。

他的加锁情况如下
MySQL选用可重复读之前一定要想到的事情

从图中可以看出,在Repeatable Read隔离级别下,由Index Key所确定的范围,被加上了GAP锁;Index Filter锁给定的条件 (userid = ‘hdc’)何时过滤,视MySQL的版本而定,在MySQL 5.6版本之前,不支持Index Condition Pushdown(ICP),因此Index Filter在MySQL Server层过滤,在5.6后支持了Index Condition Pushdown,则在index上过滤。若不支持ICP,不满足Index Filter的记录,也需要加上记录X锁,若支持ICP,则不满足Index Filter的记录,无需加记录X锁 (图中,用红色箭头标出的X锁,是否要加,视是否支持ICP而定);而Table Filter对应的过滤条件,则在聚簇索引中读取后,在MySQL Server层面过滤,因此聚簇索引上也需要X锁。最后,选取出了一条满足条件的记录[8,hdc,d,5,good],但是加锁的数量,要远远大于满足条件的记录数量。

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


以上是对何老师文章的总结.

这个文章看了很长时间,但是有一个问题一直很疑惑.
实验环境
MySQL选用可重复读之前一定要想到的事情

实验数据
create table t
(
    a int primary key,
    b int,
    c int,
    key (b,c)
) engine=innodb;

insert into t 
values
(1,10,10),
(3,10,20),
(5,20,30),
(7,20,40),
(9,20,50);
commit;

终端一:
select * from t where b=10 and c=10 for update;
理论上,他会锁定 (b=10 c=10)的辅助索引,(a=1)的主键索引并且会锁定(10,负无穷)至(10,10),(10,10)至(10,20)的范围,也就是间隙锁.

但是终端二:
select * from t where b=10 and c=15 for update;
查询并没有阻塞.这不对啊.因为在这个隔离级别下,即使没有数据,也会产生间隙锁.

终端三:
回滚终端二,在终端三输入
insert into t values(50,10,15);
我发现这个SQL被阻塞了,也就是说明终端一的间隙锁是存在的.


这个问题查阅了官方文档,
http://dev.mysql.com/doc/refman/5.6/en/innodb-record-level-locks.html
但其实没有看懂..
在缺乏理论依据的基础上,
我猜测虽然当前读的行锁是排他的(除了那个lock in share mode).
但是间隙锁有共享锁的性质,一旦出现间隙锁,在这个间隙的范围内不能新出现任何记录
无论是update修改过来的,还是insert新增过来的
只要间隙内不出现新的记录,间隙锁之间就可以共存.


参考:
开启锁监控,可以在show engine innodb status\G看到更多锁信息,并且定时将信息写入错误日志.
create table innodb_lock_monitor(a int) engine=innodb;

查看ICP是否开启
select @@optimizer_switch\G