MySQL_怎么解决当前读的幻读问题
幻读 学习检测 gap lock 和next-key lock 在什么隔离级别下?加锁规则是什么?有索引字段、普通索引字段、唯一索引字段加锁格则是什么,会产生什么后果?limit 语句加锁格则怎么查看表的锁定情况范围锁遵循锁优化规则吗? 是什么读,什么情况需要gap lock 和next-key lock?记录不存在的时候,产生什么锁?加锁范围读已提交的锁的特点最后踢出问题,问什么要有锁来防止幻读呢? 学习总结
在 read 隔离级别下才会产生
加锁规则
limit 语句查找的时候不会有向后遍历的操作,数据 1,5,5,10加锁范围是(1,5]就结束了,不会产生(5,10)的gap lock
select * from information_schema.innodb_locks;
6. 范围查询不遵循规则
是一致性读,gap lock next-key lock是为了当前读的幻读
记录不存在的时候查询也会产生间隙锁,例如 1,4,7,9,对4 开始是(1,4],(4,7),插入3 就变成了 (1,3],(3,4],(4,7)
读已提交的时候会锁住行,当语句提交的时候就不释放“不满足条件的行”,不是在事务提交的时候才释放锁的
因为MVCC的原则是
行锁 间隙锁 next key lock
行锁 Lock
单个记录上的锁
Lock总会锁定索引记录,如果没有设置任何一个索引,那么存储引擎会使用隐式的 主键来进行锁定
间隙锁 Gap Lock
锁定一个范围,但不包含记录本身,开区间(5,10)
Next-Key Lock
Gap Lock + Lock,锁定一个范围,并且锁定记录本身 前开后闭区间 (5,10],(10,15]
什么是幻读
幻读: 新插入的行,隔离级别是可重复读隔离级别,幻读在当前读才可出现
在可重读隔离级别下,普通的查询是快照读,是不会看到别的事务插入的数据的。因为,幻读在“当前读”下才会出现。幻读指的是新插入的行不是修改的行 ☆ 锁的几个原则 和优化
行锁
间隙锁
next-key lock
查看当前锁的信息
select * from information_schema.innodb_locks;
准备数据**
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);
1-等值查询间隙锁
表中数据
执行步骤
由于表中没有id=7的记录:
2-非唯一索引等值查询 索引覆盖
C是普通索引
表内数据
执行流程
但是 C要插入一个(7,7,7)的记录就会被的间隙锁(5,10)锁住
需要注意
在这个例子中,lock in share mode 只锁覆盖索引,但是如果是 for 就不一样了。 执行 for 时,系统会认为你接下来要更新数据,因此会顺便给主键索引上满足条件的行加上行锁。
这个例子说明,锁是加在索引上的;同时,它给我们的指导是,如果你要用 lock in share mode 来给行加读锁避免数据被更新的话,就必须得绕过覆盖索引的优化,在查询字段中加入索引中不存在的字段。比如,将 A 的查询语句改成 d from t where c=5 lock in share mode。你可以自己验证一下效果。
3-主键索引范围查询
mysql> select * from t where id=10 for update;
mysql> select * from t where id>=10 and id<11 for update;
表内数据
加锁过程
所以这时候锁的范围就是行锁id=10,和next-key lock(10,15] ,这样再看和 C
这里注意一点,定位查找id=10的行进行的时候,是当做等值查询来判断的,向后扫描代15的时候,用的是范围判断。
4-非唯一索引范围锁
表内数据
执行过程
5-唯一索引范围bug
表内数据
执行步骤
A 是一个范围查询,按照规则1,应该索引id上只加(10,15]这个next-key lock,并且因为id是唯一键,所以循环到15这一行就应该停止。
但实际上 会往前扫描到第一个不满足条件的行为止,也就是 id=20。而且由于这是个范围扫描,因此索引 id 上的 (15,20] 这个 next-key lock 也会被锁上。
所以你看到了, B 要更新 id=20 这一行,是会被锁住的。同样地, C 要插入 id=16 的一行,也会被锁住。
照理说,这里锁住 id=20 这一行的行为,其实是没有必要的。因为扫描到 id=15,就可以确定不用往后再找了。但实现上还是这么做了。
6-非唯一索引上存在“等值"的例子
插入一条数据
mysql> insert into t values(30,10,30);
表内数据
新插入的这一行c=10,也就说表里有两个c=10的行
可以看到,虽然有两个 c=10,但是它们的主键值 id 是不同的(分别是 10 和 30),因此这两个 c=10 的记录之间,也是有间隙的。
执行过程
语句加锁的逻辑,其实跟 … for 是类似的,也就是我在文章开始总结的两个“原则”、两个“优化”和一个“bug”。
主键被锁情况
7-limit 语句加锁
表内数据
执行流程
这个例子里, A 的 语句加了 limit 2。你知道表 t 里 c=10 的记录其实只有两条,因此加不加 limit 2,删除的效果都是一样的,但是加锁的效果却不同。
可以看到, B 的 语句执行通过了,跟案例六的结果不同。这是因为,案例七里的 语句明确加了 limit 2 的限制,因此在遍历到 (c=10, id=30) 这一行之后,满足条件的语句已经有两条,循环就结束了。
因此,索引 c 上的加锁范围就变成了从(c=5,id=5) 到(c=10,id=30) 这个前开后闭区间,如下图所示:
可以看到,(c=10,id=30)之后的这个间隙并没有在加锁范围里,因此 语句插入 c=12 是可以执行成功的。
这个例子对我们实践的指导意义就是,在删除数据的时候尽量加 limit。这样不仅可以控制删除数据的条数,让操作更安全,还可以减小加锁的范围。
8-一个死锁的案例
前面的例子中,我们在分析的时候,是按照 next-key lock 的逻辑来分析的,因为这样分析比较方便。最后我们再看一个案例,目的是说明:next-key lock 实际上是间隙锁和行锁加起来的结果。
执行流程
的next-key lock 不是还没申请成功吗?
其实是这样的, B 的“加 next-key lock(5,10] ”操作,实际上分成了两步,先是加 (5,10) 的间隙锁,加锁成功;然后加 c=10 的行锁,这时候才被锁住的。
也就是说,我们在分析加锁规则的时候可以用 next-key lock 来分析。但是要知道,具体执行的时候,是要分成间隙锁和行锁两段来执行的。
9-不存在记录的加锁
建表&数据
CREATE TABLE `test` (`id` int(1) NOT NULL AUTO_INCREMENT,`name` varchar(8) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `test` VALUES ('1', '小罗');
INSERT INTO `test` VALUES ('5', '小黄');
INSERT INTO `test` VALUES ('7', '小明');
INSERT INTO `test` VALUES ('11', '小红');
执行流程
读提交锁的特点
读提交隔离级别下还有一个优化,即:**语句执行过程中加上的行锁,在语句执行完成后,就要把“不满足条件的行”上的行锁直接释放了,不需要等到事务提交。也就是说,读提交隔离级别下,锁的范围更小,锁的时间更短,**这也是不少业务都默认使用读提交隔离级别的原因。
思考题
建表语句和数据
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);
因此:
A 的语句锁的范围就是:
索引c上的(5,25)主键索引上id=10 id=15 id=20 三个行锁 有趣的例子
*表内数据
执行流程
分析过程
正解