首页 > 数据库 >Mysql 之 幻读

Mysql 之 幻读

时间:2022-10-21 14:08:24浏览次数:53  
标签:语句 事务 幻读 next 索引 key Mysql


文章目录

1.面试问题

MySQL 记录锁+间隙锁可以防止删除操作而导致的幻读吗

可以

2.什么是幻读

SELECT * FROM t_test WHERE id > 100;

只要 T1 和 T2 时刻执行产生的结果集是不相同的,那就发生了幻读的问题

  • T1 时间执行的结果是有 5 条行记录,而 T2 时间执行的结果是有 6 条行记录,那就发生了幻读的问题。
  • T1 时间执行的结果是有 5 条行记录,而 T2 时间执行的结果是有 4 条行记录,也是发生了幻读的问题。

3.如何结局幻读

  • 针对​​快照读​​​(普通 select 语句),是通过​​MVCC​​ 方式解决了幻读,因为可重复读隔离级别下,事务执行过程中看到的数据,一直跟这个事务启动时看到的数据是一致的,即使中途有其他事务插入了一条数据,是查询不出来这条数据的,所以就很好了避免幻读问题。
  • 针对​​当前读​​​(select … for update 等语句),是通过​​next-key lock​​(记录锁+间隙锁)方式解决了幻读,因为当执行 select … for update 语句的时候,会加上 next-key lock,如果有其他事务在 next-key lock 锁范围内插入了一条记录,那么这个插入语句就会被阻塞,无法成功插入,所以就很好了避免幻读问题。

4.实验验证

用户表(t_user):

Mysql 之 幻读_数据库

现在有一个 A 事务执行了一条查询语句,查询到年龄大于 20 岁的用户共有 6 条行记录

Mysql 之 幻读_mysql_02

然后, B 事务执行了一条删除 id = 2 的语句

Mysql 之 幻读_数据库_03


此时,B 事务的删除语句就陷入了等待状态,说明是无法进行删除的。

因此,MySQL 记录锁+间隙锁可以防止删除操作而导致的幻读问题

5.加锁分析

A 事务在执行 select … for update 语句时,具体加了什么锁呢

我们可以通过 select * from performance_schema.data_locks\G; 这条语句,查看事务执行 SQL 过程中加了什么锁。

Mysql 之 幻读_sql_04

  • 表锁(LOCK_TYPE: TABLE):X 类型的意向锁;
  • 行锁(LOCK_TYPE: RECORD):X 类型的 next-key 锁;

图中 LOCK_TYPE 中的 RECORD 表示行级锁,而不是记录锁的意思:

  • 如果 LOCK_MODE 为 X,说明是 next-key 锁;
  • 如果 LOCK_MODE 为 X, REC_NOT_GAP,说明是记录锁;
  • 如果 LOCK_MODE 为 X, GAP,说明是间隙锁;

因此,此时事务 A 在主键索引(INDEX_NAME : PRIMARY)上加了 10 个 next-key 锁,如下:

X 型的 next-key 锁,范围:(-∞, 1]
X 型的 next-key 锁,范围:(1, 2]
X 型的 next-key 锁,范围:(2, 3]
X 型的 next-key 锁,范围:(3, 4]
X 型的 next-key 锁,范围:(4, 5]
X 型的 next-key 锁,范围:(5, 6]
X 型的 next-key 锁,范围:(6, 7]
X 型的 next-key 锁,范围:(7, 8]
X 型的 next-key 锁,范围:(8, 9]
X 型的 next-key 锁,范围:(9, +∞]

这相当于把整个表给锁住了,其他事务在对该表进行增、删、改操作的时候都会被阻塞。

事务 A 的这条查询语句是全表扫描,锁是在遍历索引的时候加上的,并不是针对输出的结果加锁。

在线上在执行 update、delete、select … for update 等具有加锁性质的语句,一定要检查语句是否走了索引,如果是全表扫描的话,会对每一个索引加 next-key 锁,相当于把整个表锁住了,

如果对 age 建立索引,事务 A 这条查询会加什么锁呢

Mysql 之 幻读_sql_05

因为表中有两个索引,分别是主键索引和 age 索引,所以会分别对这两个索引加锁。

Mysql 之 幻读_加锁_06


Mysql 之 幻读_数据库_07

事务 A 加上锁后,事务 B、C、D、E 在执行以下语句都会被阻塞。

Mysql 之 幻读_sql_08

6.总结

在 MySQL 的可重复读隔离级别下,针对「当前读」的查询语句会对索引加记录锁+间隙锁,这样可以避免其他事务执行「增、删、改」时导致幻读的现象。

有一点要注意的是,在执行 update、delete、select … for update 等具有加锁性质的语句,一定要检查语句是否走了索引,如果是全表扫描的话,会对每一个索引加 next-key 锁,相当于把整个表锁住了,这是挺严重的问题。


标签:语句,事务,幻读,next,索引,key,Mysql
From: https://blog.51cto.com/c959c/5782571

相关文章

  • MySQL常用语句整理
    该文章整理于牛客刷题中遇到的语法等问题该文章整理于牛客刷题中遇到的问题​​SQL​​​执行语句:​​**select–>where–>groupby–>having–>orderby......
  • MySQL中value和values的区别
    1.概况value和values都是用于插入数据时的关键字,从单词我们会联想到是不是value用于插入单挑数据,values用于插入多条数据,实际上他们并不是这样的,value可以用于插入单......
  • MySQL数据库员工部门表练习
    1.两表的数据和结构员工表:部门表:              2.新建......
  • 【TPC-DS】trino+S3+hive+postgresql性能测试----hadoop+mysql+hive安装部署(三)
    总体过程1、安装hadoop-3.2.02、安装hive-standalone-metastore-3.0.03、配置环境变量 安装hadoop-3.2.0下载hadoop的链接,选择hadoop3.2.0:​​https://archive.apache.org/......
  • Rust 开源 MySQL 库连接地址带特殊字符问题
    描述插件地址:https://crates.io/crates/mysql如果使用默认的连接方式此扩展并不会对连接地址中的#等符号进行转义:leturl=encode("mysql://root:abc#1234@12......
  • [转]Mysql字符集和字符序及修改方法
    原文地址:RDSMySQL字符集相关说明(aliyun.com)总结:有表情或者特殊字符,字符集使用utf8mb4;需要区分大小写,字符序使用_bin等,_ci不区分大小写概述本文主要介绍RDSM......
  • mysql mysql-8.0.31-winx "Access denied for user 'root'@'localhost' (using
    参考:https://blog.csdn.net/bbirdsky/article/details/8134528在使用mysql版本为mysql-8.0.31-winx,配置好后,设置密码后可以正常登录,但是在用python程序链接mysq......
  • mysql高级查询-实例
    mysql高级查询-实例#2.黏贴如下sql,直接建表#1、创建表#创建班级表createtableclass(cidintprimarykeyauto_increment,captionvarchar(32)notnull);......
  • mysql基础
    mysql基础数据库-表增删改####mysqlctrl+l清屏ctrl+c终止[linux]servicemysqlstart  启动mysqlservicemysqlstop   停止mysqlservicemysqlre......
  • mysql-增删改查
    mysql-增删改查####mysqlctrl+l清屏ctrl+c终止[linux]servicemysqlstart启动mysqlservicemysqlstop停止mysqlservicemysqlrestart重启mysql......