首页 > 数据库 > 数据库其它

MySQL如何解决幻读和不可重复度?

admin 数据库其它 2021-04-25 18:47:07  
后台-系统设置-扩展变量-手机广告位-内容正文底部

文章参考:

  • https://juejin.im/post/5b8577c26fb9a01a143fe04e
  • https://joonwhee.blog.csdn.net/article/details/106893197
  • http://blog.51cto.com/14344203/2402076

注意:探讨MySQL如何防止不可重复度和幻读问题之前,默认大家已经理解脏读、幻读、不可重复读的区别,以及数据库事务的3种隔离级别

1. MySQL中的3种锁算法

首先了解下MySQL中的3种锁算法:

  • Record lock:记录锁(行锁),单条索引记录上加锁,锁住的永远是索引,而非记录本身。

  • Gap lock:间隙锁,在索引记录之间的间隙中加锁,或者是在某一条索引记录之前或者之后加锁,并不包括该索引记录本身。

  • Next-key lock:临键锁,Record lock 和 Gap lock 的结合,即除了锁住记录本身,也锁住索引之间的间隙。

下面来详细介绍下这三种锁:

1.1 记录锁Record Lock

顾名思义,记录锁就是为某行记录加锁,它封锁该行的索引记录:

-- id 列为主键列或唯一索引列
SELECT * FROM 表名称 WHERE id = 1 FOR UPDATE;

这时候 id 为 1 的记录行会被锁住。

需要注意的是:

  • id 列必须为唯一索引列或主键列,否则上述语句加的锁就会变成临键锁。(行锁在 InnoDB 中是基于索引实现的,所以一旦某个加锁操作没有使用索引,那么该锁就会退化为表锁)

  • 同时查询语句必须为精准匹配=,不能为>、<、like等,否则也会退化成临键锁。

在通过 主键索引 与 唯一索引 对数据行进行 UPDATE 操作时,也会对该行数据加记录锁:

-- id 列为主键列或唯一索引列
UPDATE SET age = 50 WHERE id = 1;

1.2 间隙锁Gap Locks

间隙锁基于非唯一索引,它锁定一段范围内的索引记录。间隙锁基于下面将会提到的Next-Key Locking 算法,请务必牢记:使用间隙锁锁住的是一个区间,而不仅仅是这个区间中的每一条数据。

SELECT * FROM 表名称 WHERE id BETWEN 1 AND 10 FOR UPDATE;
  • 即所有在(1,10)区间内的记录行都会被锁住,所有id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞,但是 1 和 10 两条记录行并不会被锁住。

  • 除了手动加锁外,在执行完某些 SQL 后,InnoDB 也会自动加间隙锁,这个我们在下面会提到。

1.3 临键锁Next-Key Locks

Next-Key 可以理解为一种特殊的间隙锁,也可以理解为一种特殊的算法。通过临建锁可以解决幻读的问题。 每个数据行上的非唯一索引列上都会存在一把临键锁,当某个事务持有该数据行的临键锁时,会锁住一段左开右闭区间的数据。需要强调的一点是,InnoDB 中行级锁是基于索引实现的,临键锁只与非唯一索引列有关,在唯一索引列(包括主键列)上不存在临键锁

假设有如下表:

引擎:InnoDB,隔离级别:Repeatable-Read:table(id PK, age KEY, name)

idagename110Lee324Soraka532Zed745Talon

该表中 age 列潜在的临键锁有:

(-∞, 10],
(10, 24],
(24, 32],
(32, 45],
(45, +∞],

在事务 A 中执行如下命令:

-- 根据非唯一索引列 UPDATE 某条记录
UPDATE table SET name = Vladimir WHERE age = 24;
-- 或根据非唯一索引列 锁住某条记录
SELECT * FROM table WHERE age = 24 FOR UPDATE;

不管执行了上述 SQL 中的哪一句,之后如果在事务 B 中执行以下命令,则该命令会被阻塞:

INSERT INTO table VALUES(100, 26, 'Ezreal');

很明显,事务 A 在对 age 为 24 的列进行 UPDATE 操作的同时,也获取了 (24, 32] 这个区间内的临键锁。

不仅如此,在执行以下 SQL 时,也会陷入阻塞等待:

INSERT INTO table VALUES(100, 30, 'Ezreal');

那最终我们就可以得知,在根据非唯一索引对记录行进行UPDATE \ FOR UPDATE \ LOCK IN SHARE MODE 操作时,InnoDB 会获取该记录行的临键锁 ,并同时获取该记录行下一个区间的间隙锁。

即事务 A在执行了上述的 SQL 后,最终被锁住的记录区间为 (10, 32)

1.4 小结

  • InnoDB 中的行锁的实现依赖于索引,一旦某个加锁操作没有使用到索引,那么该锁就会退化为表锁。
  • 记录锁存在于包括主键索引在内的唯一索引中,锁定单条索引记录。
  • 间隙锁存在于非唯一索引中,锁定开区间范围内的一段间隔,它是基于临键锁实现的。在索引记录之间的间隙中加锁,或者是在某一条索引记录之前或者之后加锁,并不包括该索引记录本身。
  • 临键锁存在于非唯一索引中,该类型的每条记录的索引上都存在这种锁,它是一种特殊的间隙锁,锁定一段左开右闭的索引区间。即,除了锁住记录本身,也锁住索引之间的间隙。

注意:

InnoDB 行锁是通过索引上的索引项来实现的。意味者:只有通过索引条件检索数据,InnoDB 才会使用行级锁,否则,InnoDB将使用表锁!

  • 对于主键索引:直接锁住锁住主键索引即可。
  • 对于普通索引:先锁住普通索引,接着锁住主键索引,这是因为一张表的索引可能存在多个,通过主键索引才能确保锁是唯一的,不然如果同时有2个事务对同1条数据的不同索引分别加锁,那就可能存在2个事务同时操作一条数据了。

扩展:MySQL 如何实现悲观锁和乐观锁?

  • 乐观锁:更新时带上版本号(cas更新)
  • 悲观锁:使用共享锁和排它锁,select...lock in share mode,select…for update

2. MySQL如何解决不可重复读

  • MySQL中,默认使用的事务隔离界别是可重复读,为了解决不可重复读问题,InnoDB采用了MVCC(多版本并发控制)【基于乐观锁】来解决!
  • MVCC(多版本并发控制)是利用在每条数据后面加了隐藏的两列(创建版本号和删除版本号),每个事务在开始的时候都会有一个递增的当前事务版本号!

例如:

-- MVCC新增
begin; -- 假设获取的 当前事务版本号=1
insert into user (id,name,age) values (1,"张三",10); -- 新增,当前事务版本号是1
insert into user (id,name,age) values (2,"李四",12); -- 新增,当前事务版本号是1
commit; -- 提交事务
idnameagecreate_versiondelete_version1张三101NULL2李四121NULL
-- 上表可以看到,插入的过程中会把当前事务版本号记录到列 create_version 中去!

-- MVCC删除:删除操作是直接将行数据的删除版本号更新为当前事务的版本号
begin; --假设获取的 当前事务版本号=3
delete from user where id = 2;
commit; -- 提交事务
idnameagecreate_versiondelete_version1张三101NULL2李四1213
-- MVCC更新操作:采用 delete + add 的方式来实现,首先将当前数据标志为删除,然后再新增一条新的数据
begin;-- 假设获取的 当前事务版本号=10
update user set age = 11 where id = 1; -- 更新,当前事务版本号是10
commit; -- 提交事务
idnameagecreate_versiondelete_version1张三101102李四12131张三1110NULL
-- MVCC查询操作:
begin;-- 假设拿到的系统事务ID为 12
select * from user where id = 1;
commit; -- 提交事务

查询操作为了避免查询到旧数据或已经被其他事务更改过的数据,需要满足如下条件:

1、查询时当前事务的版本号需要大于或等于创建版本号create_version

2、查询时当前事务的版本号需要小于删除的版本号delete_version,或者当前删除版本号delete_version=NULL

即:(create_version <= current_version < delete_version) || (create_version <= current_version && delete_version-=NULL) ,这样就可以避免查询到其他事务修改的数据,同一个事务中,实现了可重复读!

执行结果应该是:

idnameagecreate_versiondelete_version1张三1110NULL

3. MySQL如何解决幻读

3.1 MySQL采用的MVCC可以解决幻读吗?

幻读:在一个事务中使用相同的 SQL 两次读取,第二次读取到了其他事务新插入的行,则称为发生了幻读。

例如:

  • 1)事务1第一次查询:select * from user where id < 10 时查到了 id = 1 的数据

  • 2)事务2插入了id = 2 的数据

  • 3)事务1使用同样的语句第二次查询时,查到了 id = 1、id = 2 的数据,出现了幻读。

谈到幻读,首先我们要引入“当前读”和“快照读”的概念,通过名字就可以理解:

  • 快照读:生成一个事务快照(ReadView),之后都从这个快照获取数据。普通 select 语句就是快照读。
  • 当前读:读取数据的最新版本。常见的 update/insert/delete、还有 select ... for updateselect ... lock in share mode 都是当前读。

对于快照读,MVCC 因为因为从 ReadView 读取,所以必然不会看到新插入的行,所以天然就解决了幻读的问题。

而对于当前读的幻读,MVCC 是无法解决的。需要使用 Gap Lock 或 Next-Key Lock(Gap Lock + Record Lock)来解决。

其实原理也很简单,用上面的例子稍微修改下以触发当前读:select * from user where id < 10 for update,当使用了 Gap Lock 时,Gap 锁会锁住 id < 10 的整个范围,因此其他事务无法插入 id < 10 的数据,从而防止了幻读。

3.2 有人说 RR 解决了幻读是什么情况?

SQL 标准中规定的 RR 并不能消除幻读,但是 MySQL 的 RR 可以,靠的就是 Gap 锁。在 RR 级别下,Gap 锁是默认开启的,而在 RC 级别下,Gap 锁是关闭的。

文章参考:[MySQL 到底是怎么解决幻读的?](https://blog.csdn.net/weixin_33795833/article/details/93036775]

文章来源:https://blog.csdn.net/weixin_43591980/article/details/116069114

后台-系统设置-扩展变量-手机广告位-内容正文底部
版权声明

本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
本文地址:https://jcdi.cn/shujuku/171c1e8dcec7aae13dc7f09b229f8fe0.html

留言与评论(共有 0 条评论)
   
验证码:
后台-系统设置-扩展变量-手机广告位-评论底部广告位

教程弟

https://www.jcdi.cn/

统计代码 | 京ICP1234567-2号

Powered By 教程弟 教程弟

使用手机软件扫描微信二维码