最近,遇到了一个关于mysql 加锁的问题,将当时的情形简化如下,有一个index_test表,表结构如下所示:
mysql> CREATE TABLE `index_test` (
`priv_id` int(11) NOT NULL DEFAULT '0',
`index_id` int(11) DEFAULT NULL,
PRIMARY KEY (`priv_id`),
KEY `index_id` (`index_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
priv_id为主键,index_id上建有非唯一索引。
初始情况表中有如下记录:
mysql> select * from index_test;
+---------+----------+
| priv_id | index_id |
+---------+----------+
| 1 | 1 |
| 3 | 3 |
| 5 | 5 |
| 7 | 6 |
| 9 | 9 |
+---------+----------+
开启两个client,首先,两个client分别按时间顺序执行如下命令:
time | client1 | client2 |
---|---|---|
step1 | begin; | |
step2 | select * from index_test where index_id=5 for update; | |
step3 | begin; | |
step4 | insert into index_test values(4,4); |
结果client2中(4,4)插不进去,事务被阻塞,为何我只锁住了index_id=5的记录,插入index_id=4的数据也插不进去了,难道是client1锁全表了么?
带着这样的疑问,先查阅了mysql官方文档关于MySQL锁的章节,InnoDB本身支持3种锁:
那么这3种锁在哪些场合下使用呢?
这里不得不提令一个概念:隔离级别
事务在并发执行的过程中会导致的几个问题如下:
为了解决上面几个问题,ANSI/ISO SQL定义的标准隔离级别如下:
不同的DBMS默认隔离级别也不同,一般的DBMS系统,默认都会使用读提交(Read-Comitted,RC)作为默认隔离级别,如Oracle、SQL Server等,而InnoDB默认隔离级别为可重复读(Repeatable reads),但是InnoDB的隔离级别与标准的的隔离级别有少许不同,其在RR隔离级别下通过next-key locks防止幻读的产生。
mysql官方文档上介绍next-key locks一节有这么一段话:
By default, InnoDB operates in REPEATABLE READ transaction isolation level and with the innodb_locks_unsafe_for_binlog system variable disabled. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows。
即默认情况下,InnoDB默认隔离级别为RR,并且不开启binlog,这种情况下会加next-key locks,看了自己机器上的InnoDB隔离级别以及innodb_locks_unsafe_for_binlog果真是默认配置,说明上述问题1中的client1加的就是next-key locks而不是record locks。
那么next-key locks的范围如何确定呢?
让我们继续看官方文档:
Suppose that an index contains the values 10, 11, 13, and 20. The possible next-key locks for this index cover the following intervals, where ( or ) denote exclusion of the interval endpoint and or denote inclusion of the endpoint:(negative infinity, 10] (10, 11] (11, 13] (13, 20] (20, positive infinity)
按照这种方法,我们上例中,对于index_id上的索引,可能的next-key locks为
因为问题1中,client1 上的锁为index_id=5 for update,所以在此,加的是next-key锁,并且锁的范围是(3,5],因为client2插入的数据(priv_id=4,index_id=4)其在(3,5]范围之内,因此插不进去,问题解决。
解决了上述index_id=5时,(4,4)记录插不进去的问题之后,为了验证官方文档上所说的锁范围,我进一步做了如下实验:
mysql> select * from index_test;
+---------+----------+
| priv_id | index_id |
+---------+----------+
| 1 | 1 |
| 3 | 3 |
| 5 | 5 |
| 7 | 6 |
| 9 | 9 |
+---------+----------+
time | client1 | client2 |
---|---|---|
step1 | begin; | |
step2 | select * from index_test where index_id=6 for update; | |
step3 | begin; | |
step4 | insert into index_test values(6,5); |
结果出现client2插不进去,事务阻塞的情况。为何我只锁住了index_id=6的记录,插入index_id=5的数据也插不进去了,即使是client1加了next-key锁,根据InnoDB关于next-key locks这一节所述,这里next-key locks的范围也应该是(5,6]才对,即index_id=6的记录插不进去,但是index_id=5的记录应该可以差的进去,为何(priv_id=6,index_id=5)这条记录没有插进去呢?难道官方文档把next-key locks的锁范围说错了么?这不科学啊。
然后在网上搜索相关的资料,看看别人有没有遇到过这样的问题,在一篇关于MySQL加锁处理分析的blog中得到了启示,按照blog中组合七:id非唯一索引+RR的理论,gap锁的范围不仅跟被锁定的键有关,还跟主键有关,同时在MySQL官方文档中也得到了验证
Each secondary index record also contains all the primary key fields defined for the clustered index key that are not in the secondary index.
即每一条辅助索引记录同样还包含主键。
按照该blog中的理论:上述client1中gap锁的范围应该如下图所示:
即当执行:
mysql> select * from index_test where index_id=6 for update;,
这条语句时,所有从(priv_id=5,index_id=5)到(priv_id=9,index_id=9)的区间都会被锁住,这也就解释了为什么把(priv_id=6,index_id=5)这条记录却插不进去的原因,因为本身(6,5)就在上述的gap锁区间范围之内。
显然(priv_id=4,index_id=5)是不在上述锁区间的,那么(priv_id=4,index_id=5)理论上来分析是可以插入进去的
验证:执行如下sql语句
time | client1 | client2 |
---|---|---|
step1 | begin; | |
step2 | select * from index_test where index_id=6 for update; | |
step3 | begin; | |
step4 | insert into index_test values(4,5); |
结果:client2中(priv_id=4,index_id=5)记录顺利插入成功。
但是:为何gap锁的范围会是上述描述的那样呢,gap锁的范围如何确定呢?其实确定gap锁范围,我们只要把握一点:就是让后续不能插入满足条件的新纪录,然后按照这个点,去考虑哪些地方需要加gap锁。仍然拿下面这个例子来说:
mysql> select * from index_test;
+---------+----------+
| priv_id | index_id |
+---------+----------+
| 1 | 1 |
| 3 | 3 |
| 5 | 5 |
| 7 | 6 |
| 9 | 9 |
+---------+----------+
mysql> begin;
mysql> select * from index_test where index_id=6 for update;,
加了gap锁之后就是不能够再让其他index_id=6的记录被插入,因为InnoDB索引结构都是B+树,索引记录都是按顺序排序的,想要再插入一条index_id=6的记录,其必定只能是在(priv_id>5,index_id=5)~(priv_id<9,index_id=9)记录之间插入,由于为了防止幻读,为了不让满足index_id=6的记录再插入进去,因此那段区间就被加了锁,而不是别的区间。
##最后:
开启binlog(需要重启mysql),或者设置mysql隔离级别为RC:
mysql> set session transaction isolation level read committed;
再执行问题(2)中的例子:
mysql> select * from index_test;
+---------+----------+
| priv_id | index_id |
+---------+----------+
| 1 | 1 |
| 3 | 3 |
| 5 | 5 |
| 7 | 6 |
| 9 | 9 |
+---------+----------+
time | client1 | client2 |
---|---|---|
step1 | begin; | |
step2 | select * from index_test where index_id=6 for update; | |
step3 | begin; | |
step4 | insert into index_test values(6,5); |
这一次,client2中的(6,5)顺利插入,这是因为client1仅仅对(7,6)这一条记录加了record lock,而不会对旁边区间加gap锁,这种情况下是会发生幻读现象的。
因此,在我们使用mysql加锁过程中,也首先需要搞清楚,我们的隔离级别是什么,是否开启了binlog等等,然后才能正确分析加锁的范围。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。