V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
fantastM
V2EX  ›  MySQL

在 MySQL 中,幻读的定义是什么?

  •  
  •   fantastM · 2020-12-09 17:17:44 +08:00 · 3179 次点击
    这是一个创建于 1443 天前的主题,其中的信息可能已经有所发展或是发生改变。

    一直没太完全明白幻读的定义,自己在 MySQL 手册也没找到幻读( Phantom Reads )的官方定义,另外我在另一篇帖子里 t/692386 也看到评论里对「可重复读隔离级别下是否解决了幻读问题」的回答不尽相同。因此就发个帖,想问一个权威的答案。

    先说一下我对隔离级别的一些理解,如果有误,恳请各位指出。


    快照读:读取 MVCC 快照中的数据。

    当前读:读取数据库中最新的数据。SELECT … FOR SHARE 语句和 SELECT … FOR UPDATE 语句属于当前读,并且还有显式加锁的语义。

    在 READ UNCOMMITTED 级别中,事务可以读取到其它事务中 尚未 提交的数据,这种问题被称为脏读。

    在 READ COMMITTED 级别中,事务不可以读取到其它事务中 尚未 提交的数据,但是可以读取到其它事务中 已经 提交的数据,这种问题被称为不可重复读。

    在 REPEATABLE READ 级别中,事务读取的是 MVCC 快照中的数据,因此事务中的 SELECT 语句是可重复读的。

    READ COMMITTED 级别会使用记录锁,不会使用间隙锁和 next-key 锁。(从 MySQL 手册 Transaction Isolation Levels 中的「 InnoDB locks only index records, not the gaps before them 」这句话得知。)

    REPEATABLE READ 级别中会使用记录锁、间隙锁和 next-key 锁( next-key 锁就是间隙锁 + 记录锁)。

    假设使用 SELECT … FOR UPDATE 读取一段范围内的记录。

    • 在 READ COMMITTED 中,事务虽然可以锁定这段范围内的记录,但是无法锁定这段范围的区间,因此其它事物不能 UPDATE 、DELETE 该范围内的记录(因为这些范围内的记录已经被记录锁锁定了),但是可以在这段范围的区间内 INSERT 新的数据(因为这个范围没有被间隙锁或者 next-key 锁锁定)。

      image

    • 在 REPEATABLE READ 中,事务可以锁定这段范围内的记录和区间,因此其它事务不能 UPDATE 、DELETE 该范围内的记录,也不能在这段范围的区间内 INSERT 新的数据。

      image

    在 REPEATABLE READ 级别中的 SELECT 语句默认是 一致性非锁定读取,只会读取 MVCC 快照中的数据,不会读取到其它事务中的数据(不论是 尚未 提交或者是 已经 提交的)。但是,其它事务对数据库中数据的操作又是真实存在的,因此 REPEATABLE READ 级别的隔离性和数据库中数据的一致性是存在冲突的。这个时候 REPEATABLE READ 级别中的 SELECT 语句读取的快照数据不是最新的(解决这个问题的话,就是使用 锁定读取 或者升级隔离级别至 SERIALIZABLE ),可以将这个情况理解为此时 SELECT 语句读取了一个幻影数据,这就是所谓的幻读吗?

    image

    9 条回复    2020-12-10 18:02:11 +08:00
    xuanbg
        1
    xuanbg  
       2020-12-09 17:45:34 +08:00
    在事务隔离的情况下,读到的数据不是期望的数据。
    wps353
        2
    wps353  
       2020-12-09 17:58:34 +08:00
    我是这么认为的,不知道对不对。
    MySQL 的幻读和 SQL 标准的幻读有所差异。
    MySQL 中幻读是针对当前读.
    举个例子:
    在 RC 级别下,user 表 id >=1 and id <=10 的数据现在只有 id=1 一条数据。这时候事务 A 执行 SQL(delete from user where id >=1 and id <=10),未 commit ; 如果此时事务 B 执行 SQL ( insert into user values(5)) 。这时候我们再提交事务 A,你会发现 user 里面居然还有一条 id=2 的记录,这和我们事务 A 执行的结果预期不符。
    那么这个 id=2 的记录就属于幻读。
    所以 MySQL 在 RR 级别下用 next-key 来保证例子中的事务 B 是插不进去的。
    wps353
        3
    wps353  
       2020-12-09 17:59:44 +08:00
    @wps353 应该是 insert into user values(2)
    Takamine
        4
    Takamine  
       2020-12-09 18:28:28 +08:00 via Android
    我觉得是因为当前事务只是锁了当前所有符合的数据行当做快照,进行基于这个快照的处理,但是挡不住新增。
    csl1995
        5
    csl1995  
       2020-12-09 18:59:04 +08:00 via iPhone
    幻读:同一个事务中,两次相同的查询取到的数据记录数不同。
    不可重复读:同一事务中,两次相同的查询取到的数据记录不同。

    幻读可以看作是一种特殊的不可重复读,幻读是数据数目增减,不可重复读是数据结果变化。

    mysql 通过 MVCC(读快照)可以完全避免幻读的问题(5.7 版本可以,其他版本没验证过。规避幻读最主要的方式)
    也可以通过加 next-key(空位置不允许插入)的方式避免幻读(比较少用)
    littlewing
        6
    littlewing  
       2020-12-09 19:39:03 +08:00 via iPhone
    P3: r1[P]...w2[y in P]...((c1 or a1) [and (c2 or a2) any order)]

    ref:
    Berenson H, Bernstein P, Gray J, et al. A critique of ANSI SQL isolation levels[C]//ACM SIGMOD Record. ACM, 1995, 24(2): 1-10.
    lewis89
        7
    lewis89  
       2020-12-09 19:58:33 +08:00
    @wps353 #2 不是间隙锁吗
    wps353
        8
    wps353  
       2020-12-10 09:35:02 +08:00
    @lewis89 next-key 是 gap lock 和 record lock 的结合体。
    Flourite
        9
    Flourite  
       2020-12-10 18:02:11 +08:00
    看 mysql 的定义,前后两次读返回的数据不一致就叫做幻读
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   5623 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 23ms · UTC 06:33 · PVG 14:33 · LAX 22:33 · JFK 01:33
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.