面試官問：說說MySQL的InnoDB的幻讀問題，瞬間懵了

MySQL InnoDB事務(wù)的隔離級(jí)別有四級(jí)，默認(rèn)是“可重復(fù)讀”（REPEATABLE READ）。

  • 未提交讀（READ UNCOMMITTED）。另一個(gè)事務(wù)修改了數(shù)據(jù)，但尚未提交，而本事務(wù)中的SELECT會(huì)讀到這些未被提交的數(shù)據(jù)（臟讀）。
  • 提交讀（READ COMMITTED）。本事務(wù)讀取到的是最新的數(shù)據(jù)（其他事務(wù)提交后的）。問題是，在同一個(gè)事務(wù)里，前后兩次相同的SELECT會(huì)讀到不同的結(jié)果（不重復(fù)讀）。
  • 可重復(fù)讀（REPEATABLE READ）。在同一個(gè)事務(wù)里，SELECT的結(jié)果是事務(wù)開始時(shí)時(shí)間點(diǎn)的狀態(tài)，因此，同樣的SELECT操作讀到的結(jié)果會(huì)是一致的。但是，會(huì)有幻讀現(xiàn)象（稍后解釋）。
  • 串行化（SERIALIZABLE）。讀操作會(huì)隱式獲取共享鎖，可以保證不同事務(wù)間的互斥。

四個(gè)級(jí)別逐漸增強(qiáng)，每個(gè)級(jí)別解決一個(gè)問題。

  • 臟讀，最容易理解。另一個(gè)事務(wù)修改了數(shù)據(jù)，但尚未提交，而本事務(wù)中的SELECT會(huì)讀到這些未被提交的數(shù)據(jù)。
  • 不重復(fù)讀。解決了臟讀后，會(huì)遇到，同一個(gè)事務(wù)執(zhí)行過程中，另外一個(gè)事務(wù)提交了新數(shù)據(jù)，因此本事務(wù)先后兩次讀到的數(shù)據(jù)結(jié)果會(huì)不一致。
  • 幻讀。解決了不重復(fù)讀，保證了同一個(gè)事務(wù)里，查詢的結(jié)果都是事務(wù)開始時(shí)的狀態(tài)（一致性）。但是，如果另一個(gè)事務(wù)同時(shí)提交了新數(shù)據(jù)，本事務(wù)再更新時(shí)，就會(huì)“驚奇的”發(fā)現(xiàn)了這些新數(shù)據(jù)，貌似之前讀到的數(shù)據(jù)是“鬼影”一樣的幻覺。

借鑒并改造了一個(gè)搞笑的比喻：

  • 臟讀。假如，中午去食堂打飯吃，看到一個(gè)座位被同學(xué)小Q占上了，就認(rèn)為這個(gè)座位被占去了，就轉(zhuǎn)身去找其他的座位。不料，這個(gè)同學(xué)小Q起身走了。事實(shí)：該同學(xué)小Q只是臨時(shí)坐了一小下，并未“提交”。
  • 不重復(fù)讀。假如，中午去食堂打飯吃，看到一個(gè)座位是空的，便屁顛屁顛的去打飯，回來后卻發(fā)現(xiàn)這個(gè)座位卻被同學(xué)小Q占去了。
  • 幻讀。假如，中午去食堂打飯吃，看到一個(gè)座位是空的，便屁顛屁顛的去打飯，回來后，發(fā)現(xiàn)這些座位都還是空的（重復(fù)讀），竊喜。走到跟前剛準(zhǔn)備坐下時(shí)，卻驚現(xiàn)一個(gè)恐龍妹，嚴(yán)重影響食欲。仿佛之前看到的空座位是“幻影”一樣。

一些文章寫到InnoDB的可重復(fù)讀避免了“幻讀”（phantom read），這個(gè)說法并不準(zhǔn)確。

做個(gè)試驗(yàn)：(以下所有試驗(yàn)要注意存儲(chǔ)引擎和隔離級(jí)別)

mysql> show create table t_bitfly\G;
CREATE TABLE `t_bitfly` (
`id` bigint(20) NOT NULL default '0',
`value` varchar(32) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk

mysql> select @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+-----------------+
| REPEATABLE-READ      | REPEATABLE-READ |
+-----------------------+-----------------+

試驗(yàn)一：

t Session A                  Session B
|
| START TRANSACTION;         START TRANSACTION;
|
| SELECT * FROM t_bitfly;
| empty set
|                            INSERT INTO t_bitfly
|                            VALUES (1, 'a');
|
| SELECT * FROM t_bitfly;
| empty set
|                            COMMIT;
|
| SELECT * FROM t_bitfly;
| empty set
|
| INSERT INTO t_bitfly VALUES (1, 'a');
| ERROR 1062 (23000):
| Duplicate entry '1' for key 1
v (shit, 剛剛明明告訴我沒有這條記錄的)

如此就出現(xiàn)了幻讀，以為表里沒有數(shù)據(jù)，其實(shí)數(shù)據(jù)已經(jīng)存在了，傻乎乎的提交后，才發(fā)現(xiàn)數(shù)據(jù)沖突了。

試驗(yàn)二：

t Session A                 Session B
|
| START TRANSACTION;        START TRANSACTION;
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id   | value |
| +------+-------+
| |    1 | a    |
| +------+-------+
|                           INSERT INTO t_bitfly
|                           VALUES (2, 'b');
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id   | value |
| +------+-------+
| |    1 | a    |
| +------+-------+
|                           COMMIT;
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id   | value |
| +------+-------+
| |    1 | a    |
| +------+-------+
|
| UPDATE t_bitfly SET value='z';
| Rows matched: 2  Changed: 2 Warnings: 0
| (怎么多出來一行)
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id   | value |
| +------+-------+
| |    1 | z    |
| |    2 | z    |
| +------+-------+
|
v

本事務(wù)中第一次讀取出一行，做了一次更新后，另一個(gè)事務(wù)里提交的數(shù)據(jù)就出現(xiàn)了。也可以看做是一種幻讀。

那么，InnoDB指出的可以避免幻讀是怎么回事呢？

準(zhǔn)備的理解是，當(dāng)隔離級(jí)別是可重復(fù)讀，且禁用innodb_locks_unsafe_for_binlog的情況下，在搜索和掃描index的時(shí)候使用的next-key locks可以避免幻讀。

關(guān)鍵點(diǎn)在于，是InnoDB默認(rèn)對(duì)一個(gè)普通的查詢也會(huì)加next-key locks，還是說需要應(yīng)用自己來加鎖呢？如果單看這一句，可能會(huì)以為InnoDB對(duì)普通的查詢也加了鎖，如果是，那和序列化（SERIALIZABLE）的區(qū)別又在哪里呢？

我的理解是說，InnoDB提供了next-key locks，但需要應(yīng)用程序自己去加鎖。manual里提供一個(gè)例子：

SELECT * FROM child WHERE id > 100 FOR UPDATE;

這樣，InnoDB會(huì)給id大于100的行（假如child表里有一行id為102），以及100-102，102+的gap都加上鎖。

可以使用show innodb status來查看是否給表加上了鎖。

再看一個(gè)實(shí)驗(yàn)，要注意，表t_bitfly里的id為主鍵字段。實(shí)驗(yàn)三：

t Session A                Session B
|
| START TRANSACTION;       START TRANSACTION;
|
| SELECT * FROM t_bitfly
| WHERE id<=1
| FOR UPDATE;
| +------+-------+
| | id   | value |
| +------+-------+
| |    1 | a    |
| +------+-------+
|                          INSERT INTO t_bitfly
|                          VALUES (2, 'b');
|                          Query OK, 1 row affected
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id   | value |
| +------+-------+
| |    1 | a    |
| +------+-------+
|                          INSERT INTO t_bitfly
|                          VALUES (0, '0');
|                          (waiting for lock ...
|                          then timeout)
|                          ERROR 1205 (HY000):
|                          Lock wait timeout exceeded;
|                          try restarting transaction
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id   | value |
| +------+-------+
| |    1 | a    |
| +------+-------+
|                          COMMIT;
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id   | value |
| +------+-------+
| |    1 | a    |
| +------+-------+
v

可以看到，用id<=1加的鎖，只鎖住了id<=1的范圍，可以成功添加id為2的記錄，添加id為0的記錄時(shí)就會(huì)等待鎖的釋放。

一致性讀和提交讀，先看實(shí)驗(yàn)，實(shí)驗(yàn)四：

t Session A                     Session B
|
| START TRANSACTION;            START TRANSACTION;
|
| SELECT * FROM t_bitfly;
| +----+-------+
| | id | value |
| +----+-------+
| |  1 | a    |
| +----+-------+
|                               INSERT INTO t_bitfly
|                               VALUES (2, 'b');
|                               COMMIT;
|
| SELECT * FROM t_bitfly;
| +----+-------+
| | id | value |
| +----+-------+
| |  1 | a    |
| +----+-------+
|
| SELECT * FROM t_bitfly LOCK IN SHARE MODE;
| +----+-------+
| | id | value |
| +----+-------+
| |  1 | a    |
| |  2 | b    |
| +----+-------+
|
| SELECT * FROM t_bitfly FOR UPDATE;
| +----+-------+
| | id | value |
| +----+-------+
| |  1 | a    |
| |  2 | b    |
| +----+-------+
|
| SELECT * FROM t_bitfly;
| +----+-------+
| | id | value |
| +----+-------+
| |  1 | a    |
| +----+-------+
v

如果使用普通的讀，會(huì)得到一致性的結(jié)果，如果使用了加鎖的讀，就會(huì)讀到“最新的”“提交”讀的結(jié)果。

本身，可重復(fù)讀和提交讀是矛盾的。在同一個(gè)事務(wù)里，如果保證了可重復(fù)讀，就會(huì)看不到其他事務(wù)的提交，違背了提交讀；如果保證了提交讀，就會(huì)導(dǎo)致前后兩次讀到的結(jié)果不一致，違背了可重復(fù)讀。

可以這么講，InnoDB提供了這樣的機(jī)制，在默認(rèn)的可重復(fù)讀的隔離級(jí)別里，可以使用加鎖讀去查詢最新的數(shù)據(jù)。

總結(jié)

如果你覺得這篇文章不錯(cuò)，請(qǐng)別忘記點(diǎn)個(gè)贊跟關(guān)注哦~??

**更多學(xué)習(xí)內(nèi)容請(qǐng)閱讀我的知乎專欄： 打造全網(wǎng)web高級(jí)前端工程師資料庫（總目錄）看完學(xué)的更加快，知識(shí)更牢固。你值得擁有（持續(xù)更新）~ **

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

友情鏈接更多精彩內(nèi)容