mysql的幻读

xvpenghao
101 阅读6分钟

mysql的幻读

参考链接

概念知识

什么是幻读

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.

翻译：在一个事务中，同样的select查询在不同的时间段查询出来的 记录集不一样，比如第二次查询出现了第一次没有的行，则这种现象就叫做 幻读。

  • 《高性能Mysql》1.3.1 隔离级别

所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务有在该范围内插入了新的记录。当之前事务再次读取该范围的记录是，会产生换行。

  • 幻读举例说明

mysql5.6，事务隔离级别RR

image.png

session1，前后都是在 当前读，【步骤-6】读到了别的 seesion2 插入的数据 id=5，则导致前后 读取范围的记录数不对，则这叫幻读。

  • 个人理解

保证前后都是同一概念的读（要么当前读，要么快照读），事务按照范围条件查询后，另一个事务在该范围内插入新记录，前一事务再次读取记录时，发现前后不一致，则叫幻读。

强调： 前后都是同一读概念，记录的插入，记录条目发生变化

怎么解决幻读

To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking

为了解决幻读，innodb使用 next-key lock(临间锁) 它是 行锁中-记录锁 + gap（间隙锁）解决的， 什么是记录锁，间隙锁，临间锁

使用 next-key lcok 其实是为了解决 当前读的出现的幻读，

幻读示例

我们这个幻读的示例该怎么举例呢？，从官方文档中我们了解到了，解决幻读使用 临间锁（next-key-lock） 实现的，从而在反推 临间锁的使用，

最后拿着 这个 临建锁的示例 去套 事务的隔离级别。

举例，临间锁的例子，来时每个事务

CREATE TABLE `t_a` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(16) NOT NULL DEFAULT '',
  `age` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

mysql> select * from t_a;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a1   |  10 |
|  2 | a2   |  20 |
|  3 | a3   |  30 |
| 10 | a10  |  40 |
+----+------+-----+


-- session1
SET autocommit=0;
SET session transaction isolation level REPEATABLE READ;

begin;
select * from t_a where age=20 for update;


-- session2
SET autocommit=0;
SET session transaction isolation level REPEATABLE READ;

begin;
-- 插入记录 age=21 等待释放锁，无法插入
insert into t_a values(11,'a21',21) 

-- 尝试插入 age=11 等待释放锁，无法插入
insert into t_a values(11,'a21',11)

分析为什么不能插入？

(negative infinity, 10]
(10, 20]
(20, 30]
(30, 40]
(40, positive infinity)

session=1的 age=20，则锁住了 (10,20] 和(20,30]之间的记录，所以session2不能插入，

CREATE TABLE `t_a` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(16) NOT NULL DEFAULT '',
  `age` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

mysql> select * from t_a;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a1   |  10 |
|  2 | a2   |  20 |
|  3 | a3   |  30 |
| 10 | a10  |  40 |
+----+------+-----+

可重复读(解决幻读)

  • 当前读
-- session1
SET autocommit=0;
SET session transaction isolation level REPEATABLE READ;

begin;
-- 它会锁住一个区间 
select * from t_a where age=20 for update;


-- session2
SET autocommit=0;
SET session transaction isolation level REPEATABLE READ;

begin;
-- 等待释放锁，无法插入
insert into t_a values(11,'a21',20) 



-- session1
-- 发现和第一次查询结果一致
select * from t_a where age=20 for update;

session1的查询指定范围的数据，

session2在指定的范围内插入数据，发现添加不上，

session1再次查询，发现和第一次查询结果一致，这幻读解决了

  • 快照读
-- session1
SET autocommit=0;
SET session transaction isolation level REPEATABLE READ;

begin;
select * from t_a where age=20 


-- session2
SET autocommit=0;
SET session transaction isolation level REPEATABLE READ;

begin;
insert into t_a values(11,'a21',20) 
commit;


-- session1
-- 发现和第一次查询结果一致
select * from t_a where age=20 ;

也解决了幻读，

读已经提交（未解决幻读）

对于锁定记录（for updae 和 share 的select 和 update，delete）只会锁定已经存在的记录，对于不存在的记录是无法锁定的，间隙锁是被禁止的，

-- session1
SET autocommit=0;
SET session transaction isolation level Read committed

begin;
-- 仅会锁住已经存在的行记录，id=2的这条记录
select * from t_a where age=20 for update;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  2 | a2   |  20 |
+----+------+-----+


-- session2
SET autocommit=0;
SET session transaction isolation level Read committed

begin;
insert into t_a values(11,'a21',20) 
commit;

-- session1
-- 发现和第一查询记录条数多个一条
select * from t_a where age=20;
+-----+------+-----+
| id  | name | age |
+-----+------+-----+
|   2 | a2   |  20 |
| 100 | a100 |  20 |
+-----+------+-----+

总结

不可重复读，到底解决了幻读没有，其实官方已经说了（只不过有点笼统），

不可重复读下，说幻读一定要区分 当前读和快照读，不要再一个事务中，拿着 开始是快照读 ，最后是 当前读 说前后不一致（自己更新自己的，再读不一致，不算）。说这个是幻读。

幻读的错误理解

不要在一个事务中将 快照读 和当前读 相提并论，要好好的理解MVCC 和 快照度和当前读的知识

mysq的MVCC

mysql的当前读和快照读

  • 测试数据
CREATE TABLE `t_a` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(16) NOT NULL DEFAULT '',
  `age` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

mysql> select * from t_a;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a10  |  10 |
|  2 | a20  |  20 |
|  3 | a30  |  30 |
|  4 | a40  |  40 |
+----+------+-----+
  • 幻读错误理解1

image.png

分析：

【步骤2】session1查询不到记录，

【步骤6】session1在插入的时候却添加不上，

【步骤7】session1 再次查询，发现确实查询不到

查询的结果状态无法支持后续的业务操作，这个就叫 “幻读”

解释：

【步骤2】session1的 执行的查询的是 快照度

【步骤6】session1的 它的插入是 当前读，发现 主键id已经存在了， 当然就插入不添加不上了。

【步骤7】session1的 执行的查询的是 快照度 和 【步骤2】查询的结果一致，

导致错误的理解的是因为吧， 快照读 和 当前读 放在一个事务中比较，一个是会根据数据版本来决定数据的可加性 一个是总是读取最新的记录。两者性质不一样

  • 幻读错误理解2

image.png

分析：

【步骤2】session1查询 age=30的只能查询到一条记录

【步骤3，4，5】session2 插入 age=50，并提交

【步骤6】session1再次查询，age=30，依然是一条记录

【步骤7】session1更新 全表中的age=30，

【步骤8】session1再次查询，发现 比之前的记录 多了 4条记录

在session1中，前后的查询的记录条数不一样，则叫 幻读

解释：

【步骤1】session1是 快照读

【步骤7】session1将全表的age更新成30，导致的问题就是 【步骤4】session2插入的记录age=50上面的 db_trx_id 也就变成了 session1的事务id了

【步骤8】session1再次查询的时候，发现，记录数和 第一次查询不对了，

导致错误理解的原因是，【步骤7】session1执行的更新，就表中所有的记录的 事务id都更新成了，session1的事务id，

快照读也会读 自己更新自己的记录

avatar
文章
阅读
粉丝