MySQL被锁了(五)如何解锁:解决幻读的方法和功率demo?
概述
前面两篇文章介绍了MySQL的全局锁和表级锁,今天就介绍一下MySQL的行锁。
MySQL的行锁是各个引擎内部实现的,不是所有的引擎支持行锁,例如MyISAM就不支持行锁。
不支持行锁就意味着在并发操作时,就要使用表锁,在任意时刻都只能有一个更新操作在执行,这样会影响业务的并发性。这也是为什么MyISAM会被InnoDB取代的原因之一。
行锁是锁里最小粒度的锁,InnoDB引擎里的行锁的实现算法有三种:
- Record Lock:行锁,锁住记录本身
- Gap Lock:间隙锁,锁住某个范围,但不包括记录本身
- Next-Key Lock:Record Lock + Gap Lock,既锁范围,又锁记录
InnoDB是使用Next-Key Lock来解决幻读问题的。
什么是幻读?
我们看一下这个例子,有一个表 t,插入部分数据。
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);
![](https://static001.geekbang.org/resource/image/5b/8b/5bc506e5884d21844126d26bbe6fa68b.png)
图1 假设只在id=5这一行加行锁
有三个会话并发执行,Session A在T1,T3,T5时刻分别查询同一个语句,出现不同的结果。其中Q3读到的id=1这一行的现象,被称为幻读。
幻读,指同一个事务中,两次相同的查询操作,得到的结果行数不一样。
这里要对“幻读”做两点说明:
- 在可重复读隔离级别下,普通的查询是快照读,是不会看到别的事务插入的数据的。因此幻读在“当前读”下才会出现。
- 上面的Session B的修改结果,被Session A之后的select语句用“当前读”看到了,不能称为幻读。幻读仅专指“新插入的行”。
根据数据可见性规则分析,这三个查询都加了for update,都是“当前读”,符合数据可见性规则。
这么看来,好像没什么问题,是不是真的没有问题呢?
不,这里还真就有问题。
幻读有什么问题?
语义上不一致
Session A在T1时刻就声明了,“我要把所有d=5的行锁住,不准别的事务进行读写操作”。而实际上,这个语义被破坏了。
上面的例子可能还看不太出来,我们给Session B和Session C分别加两个语句,再看看会出现什么现象。
![](https://static001.geekbang.org/resource/image/7a/07/7a9ffa90ac3cc78db6a51ff9b9075607.png)
图2 假设只在id=5这一行加行锁--语义被破坏
Session B的第二条语句update t set c = 5 where id=0,语义是“我要把id=0、d=5的这一行的c的值改成了5”。
由于在T1时刻,Session A还只是给t=5这一行加了行锁,并没有给id=0这一行加锁。因此Session B在T2时刻,是可以执行这条语句的。
同理,Session C对id=1这行的修改,一样是破坏了Q1的加锁声明。
数据上不一致
其次是造成数据上不一致。锁的设计就是为了保证数据一致性的,这里的一致性除了内部数据在此刻的一致性外,还包含数据和日志在逻辑上的一致性。
![](https://static001.geekbang.org/resource/image/dc/92/dcea7845ff0bdbee2622bf3c67d31d92.png)
图 3 假设只在id=5这一行加行锁--数据一致性问题
我们来分析一下图3执行完成后,数据库的数据是什么:
- 经过T1时刻,id=5这一行变成 (5,5,100),当然这个结果最终是在T6时刻正式提交的
- 经过T2时刻,id=0这一行变成(0,5,5);
- 经过T4时刻,表里面多了一行(1,5,5);
我们再来看看binlog的内容:
// session B
update t set d=5 where id=0;
update t set c=5 where id=0;
// session C
insert into t values(1,1,5);
update t set c=5 where id=1;
update t set d=100 where d=5;
按照这个语句序列,这三行的结果变成:(0,5,100),(1,5,100),(5,5,100)。
也就是说id=0和id=1这两行,发生了数据不一致。这个问题很严重,是不行的。
那究竟这个数据不一致是怎样引入的呢?
![](https://static001.geekbang.org/resource/image/34/47/34ad6478281709da833856084a1e3447.png)
图 4 假设扫描到的行都被加上了行锁
假设我们对扫描到的行都加上行锁,来看看图4执行后会出现什么现象。
- 经过T1时刻,id=5这一行变成 (5,5,100),当然这个结果最终是在T6时刻正式提交的
- 经过T2时刻,Session B被阻塞,等到T6时刻Session A释放锁才能执行;
- 经过T4时刻,表里面多了一行(1,5,5);
- 经过T6时刻,id=1这一行变成(1,5,100);
id=1这一行还是出现数据不一致的问题。即使把所有的记录都加上锁,还是阻止不了新插入的记录。
如何解决幻读?
我们现在知道产生幻读的原因是,行锁只能锁住行,但是新插入记录这个动作,要更新的是记录之间的“间隙”。因此,为了解决幻读问题,InnoDB引入了间隙锁(Gap Lock)。
前面介绍过,间隙锁,锁住某个范围,但不包括记录本身。比如前面说到的表t,初始化有6条记录,这就产生了7个间隙。
图 5 表t主键索引上的行锁和间隙锁
当你执行select * from t where d=5 for update的时候,就不止是给数据库中6个记录加了行锁,还同时加了7个间隙锁。这样就确保了无法再插入新的记录。
也就是说这时候,在一行行扫描的过程中,不仅给行加上行锁,还给行两边的空隙也加上间隙锁。
我们回到上面的图4,再来看看加上间隙锁后,执行的效果如何。
- 经过T1时刻,id=5这一行变成 (5,5,100),当然这个结果最终是在T6时刻正式提交的。因为select * from t where d=6 for update,对6个记录加了行锁,同时加了7个间隙锁。
- 经过T2时刻,Session B被阻塞,因为id=0这一行被锁;
- 经过T4时刻,Session C被阻塞,因为主键索引上加了间隙锁(0,5),所以id=1这个值无法被插入;
Session B和Session C都要等待Session A释放锁后才能继续执行,这样就解决了幻读的问题。
行锁保证更新行,间隙锁保证插入行,而行锁+间隙锁=Next-Key Lock,也就是本文开头说到的,InnoDB是通过Next-Key Lock来解决幻读问题的。
但是间隙锁的引入,可能会导致同样的语句锁住更大的范围,这会影响并发度的。比如上面的select * from t where d=5 for update,相当于加了表锁。