mysql 幻读问题

本贴最后更新于 727 天前,其中的信息可能已经天翻地覆

1.什么是幻读

幻读指的是一个事务在前后两次查询同一个范围的时候,后一次查询看到了前一次查询没有看到的行,这里的读取指的是当前读

2.幻读有什么问题

2.1 破坏语义

Q1 要锁住所有 d=5 的行,但是 session B 与 session C 破坏了这个语义

2.2 一致性问题

binlog 日志实在提交时按顺序写入的

//1.session B先写入
update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/
//2. session C再写入
insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/
//3. session A最后写入
update t set d=100 where d=5;/*所有d=5的行,d改成100*/

如果 binlog 同步到从库,就会产生主从不一致的问题

3.解决方案 间隙锁

innoDB 为解决幻读引入了间隙锁, 间隙锁 + 行锁共同解决了 RR 级别下的幻读问题

间隙锁的出现是为了解决幻读,间隙锁只有再可重复读下才能使用

3.1 加锁原则

  1. 加锁基本单位为 next-key lock(左开右闭);
  2. 查找过程中访问的对象才会加锁(二级索引的间隙锁有可能会传递到主键上)
  3. 唯一索引等值查询,next-key lock 退化为行锁
  4. 索引等值查询(包含唯一和普通索引),向右遍历时最后一个不满足等值条件的时候,next-key lock 退化为间隙锁(左开右开);
  5. 范围查询会访问到不满足条件的第一个值为止.

简述:

  • 等值查询:
    若是唯一索引,退化成行锁;
    若是普通索引,需要访问到第一个不满足条件的值,退化成间隙锁;
  • 范围查询:
    均需访问到不满足条件的第一个值为止
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);

3.2 案例一

image.png
id=7 不存在,扫描到 10,加锁(5,10]
唯一索引等值查询,根据原则 4,退化为(5,10)

3.3 案例二

image.png
根据原则 1,加锁范围为(0,5],(5,10]
根据原则 4,加锁范围为(0,5],(5,10)
由于使用覆盖索引,id=5 并不会加锁

3.4 案例三

image.png

id=10,唯一索引等值查询,加行锁 10,
id>10,id<11,唯一索引范围查询,加锁(10,15],
综上,加锁范围为[10,15]

3.5 案例四

image.png

c>=10,=10 为普通索引等值查询,加锁范围为(5,10],
c<11,为普通索引范围查询,加锁范围为(10,15]
综上,加锁范围为(5,15]

3.6 案例五

image.png

id>10,唯一索引范围查询,加锁范围(10,15]
id<15,唯一索引范围查询,加锁(10,15],
id=15,唯一索引等值查询,加行锁 15,
根据原则 5,唯一索引再扫描到不满足条件的第一个值,加锁(15,20],
综上,加锁范围为(10,20]

3.7 案例六

mysql> insert into t values(30,10,30);

image.png

普通索引等值查询,加锁(5,10],
普通索引等值下扫到不满足条件的索引,加锁(10,15),
综上,加锁范围为(5,15)

image.png

3.8 案例七

image.png

受 limit 影响,加锁范围为

image.png

所以,删除数据时尽量加上 limit,以减少加锁范围

3.9 案例八

image.png

session A 加锁(5,15)
session B 等待加锁 (5,10)(分步骤加锁),阻塞等待 session A 的锁
session A insert 语句等待加锁(5,10)(分步骤加锁),阻塞等待 session B 的锁,形成死锁

间隙锁可以共享,但是间隙锁已 insert 互斥, sessionA 和 sessionB 其实是行锁 10 互斥

3.10 案例九

image.png

c=20,普通索引等值查询,下扫到 25,加锁(15,25)
c=15,普通索引等值查询,加锁(10,15]
由于索引 c 向左遍历,扫到 10,加锁(5,10]
综上 加锁范围为 c 索引(5,25),主键 id 上是 15 和 20 两个行锁

  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    673 引用 • 535 回帖

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...