咦～ MySQL怎么出現(xiàn)幻讀啦！
1 背景
InnoDB默認(rèn)的事務(wù)隔離級別是REPEATABLE-READ，它為了解決該隔離級別并發(fā)情況下的幻讀問題，使用了LBCC（基于鎖的并發(fā)控制）和MVCC（多版本的并發(fā)控制）兩種方案。其中LBCC解決的是當(dāng)前讀情況下的幻讀問題，MVCC解決的是快照讀情況下的幻讀問題，那既然如此，該隔離級別下是否仍然還存在幻讀的問題呢？幻讀問題到底有沒有完全解決呢？基于這樣的疑問，下面我們來進行驗證下吧。
2 驗證
2.1 驗證準(zhǔn)備
2.1.1 環(huán)境信息
MySQL版本：5.6.36
存儲引擎：InnoDB
隔離級別：REPEATABLE-READ2.1.2 數(shù)據(jù)準(zhǔn)備
為了進行驗證，在測試庫建立了一張測試使用的用戶信息表，并且插入了3條初始數(shù)據(jù)。
CREATE TABLE `user_info` (
`id` BIGINT ( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵id',
`name` VARCHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '姓名',
`gender` VARCHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '性別',
`email` VARCHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '郵箱',
PRIMARY KEY ( `id` )
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = '用戶信息表';
INSERT INTO `user_info` (`id`, `name`, `gender`, `email`) VALUES (1, 'Curry', '男', 'curry@163.com');
INSERT INTO `user_info` (`id`, `name`, `gender`, `email`) VALUES (2, 'Wade', '男', 'wade@163.com');
INSERT INTO `user_info` (`id`, `name`, `gender`, `email`) VALUES (3, 'James', '男', 'james@163.com');
commit;2.2 當(dāng)前讀場景驗證
首先我們先來看看當(dāng)前讀的場景下會不會出現(xiàn)幻讀的問題。
2.2.1 前言
- 什么是當(dāng)前讀
當(dāng)前讀（Locking Read）也稱為鎖定讀，讀取的是數(shù)據(jù)當(dāng)前的最新版本，而且讀取到這個數(shù)據(jù)之后會對這個數(shù)據(jù)加鎖，防止別的事務(wù)進行更改，即通過next-key鎖（唯一索引next-key鎖會退化為記錄鎖）來解決當(dāng)前讀中的臟讀，幻讀，不可重復(fù)讀問題，也就是LBCC的方式。在進行寫操作的時候也需要進行“當(dāng)前讀”，讀取數(shù)據(jù)記錄的最新版本。當(dāng)前讀包含以下SQL類型：select ... lock in share mode 、select ... for update、update 、delete 、insert。
- 什么是臨鍵鎖
圖片
我們將數(shù)據(jù)庫中存儲的每一行數(shù)據(jù)稱為記錄。如上圖中1、5、9、12分別代表id為當(dāng)前數(shù)的記錄。對于鍵值在條件范圍內(nèi)但不存在的記錄，叫做間隙（GAP）。則上圖中的（-∞，1）、（1，5）...（12，+∞）為數(shù)據(jù)庫中存在的間隙。而（-∞，1]、（1，5]...（12，+∞）我們稱之為臨鍵，即左開右閉的集合。當(dāng)我們對上面的記錄和間隙共同加鎖時，添加的便是臨鍵鎖。
2.2.2 場景驗證
觸發(fā)當(dāng)前讀的方式有很多種，這里僅使用select lock in share mode這種方式來進行當(dāng)前讀幻讀問題驗證。
- 場景：
- 開啟事務(wù)1
- 在事務(wù)1中通過select lock in share mode進行當(dāng)前讀查詢用戶信息
- 開啟事務(wù)2
- 在事務(wù)2中插入一條新數(shù)據(jù)
- 提交事務(wù)2
- 在事務(wù)1中再次查詢用戶信息
- 提交事務(wù)1
mysql> START TRANSACTION; -- 1.開啟事務(wù)1
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user_info lock in share mode; -- 2.讀鎖方式查詢用戶信息
+----+-------+--------+---------------+
| id | name | gender | email |
+----+-------+--------+---------------+
| 1 | Curry | 男 | curry@163.com |
| 2 | Wade | 男 | wade@163.com |
| 3 | James | 男 | james@163.com |
+----+-------+--------+---------------+
3 rows in set (0.00 sec)mysql> START TRANSACTION; -- 3.開啟事務(wù)2
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `user_info` (`id`, `name`, `gender`, `email`) VALUES (4, 'White', '男', 'white@163.com'); -- 4.在事務(wù)2中插入一條新數(shù)據(jù)
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction -- 因鎖等待插入未成功，最終等待超時，事務(wù)回滾終止- 場景驗證結(jié)果
可以看到在事務(wù)1中開始事務(wù)執(zhí)行了當(dāng)前讀后，事務(wù)2在進行插入新數(shù)據(jù)時進入了鎖等待，最后發(fā)生了鎖等待超時，導(dǎo)致事務(wù)終止回滾。插入數(shù)據(jù)因鎖的原因是不會成功的，因此事務(wù)1第二次查詢時也不會查詢到新記錄，所以此場景下不會產(chǎn)生幻讀的問題。
2.2.3 小結(jié)
由場景驗證結(jié)果可以看到，由于臨鍵鎖的存在，會阻塞其他事務(wù)對加鎖間隙的數(shù)據(jù)插入，所以當(dāng)前讀場景下通過LBCC是可以完全解決幻讀的問題。
2.3.快照讀場景驗證
那接下來我們再看看快照讀場景下是怎么樣的。
2.3.1 前言
- 什么是快照讀
由于當(dāng)前讀是通過LBCC基于鎖的方式來進行并發(fā)控制，是悲觀鎖的實現(xiàn)，同時也會因為鎖的原因，造成鎖沖突的概率變大，也會導(dǎo)致性能的下降，因此基于提高并發(fā)性能的考慮，引入了快照讀，快照讀顧名思義即讀取的是數(shù)據(jù)的快照版本，快照讀的實現(xiàn)是基于MVCC多版本并發(fā)控制，它在很多情況下，避免了加鎖操作，降低了性能開銷。
2.3.2 場景驗證
- 場景一
- 開啟事務(wù)1
- 在事務(wù)1中查詢用戶信息
- 開啟事務(wù)2
- 在事務(wù)2中插入一條新數(shù)據(jù)
- 提交事務(wù)2
- 在事務(wù)1中再次查詢用戶信息
- 提交事務(wù)1
mysql> START TRANSACTION; -- 1.開啟事務(wù)1
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user_info; -- 2.在事務(wù)1中查詢用戶信息
+----+-------+--------+---------------+
| id | name | gender | email |
+----+-------+--------+---------------+
| 1 | Curry | 男 | curry@163.com |
| 2 | Wade | 男 | wade@163.com |
| 3 | James | 男 | james@163.com |
+----+-------+--------+---------------+
3 rows in set (0.00 sec)
mysql> select * from user_info; 6.在事務(wù)1中再次查詢用戶信息
+----+-------+--------+---------------+
| id | name | gender | email |
+----+-------+--------+---------------+
| 1 | Curry | 男 | curry@163.com |
| 2 | Wade | 男 | wade@163.com |
| 3 | James | 男 | james@163.com |
+----+-------+--------+---------------+
3 rows in set (0.00 sec)
mysql> commit; -- 7.提交事務(wù)1
Query OK, 0 rows affected (0.00 sec)mysql> START TRANSACTION; -- 3.開啟事務(wù)2
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `user_info` (`id`, `name`, `gender`, `email`) VALUES (4, 'White', '男', 'white@163.com'); -- 4.在事務(wù)2中插入一條新數(shù)據(jù)
Query OK, 1 row affected (0.00 sec)
mysql> commit; -- 5.提交事務(wù)2
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user_info;
+----+-------+--------+---------------+
| id | name | gender | email |
+----+-------+--------+---------------+
| 1 | Curry | 男 | curry@163.com |
| 2 | Wade | 男 | wade@163.com |
| 3 | James | 男 | james@163.com |
| 4 | White | 男 | white@163.com |
+----+-------+--------+---------------+
4 rows in set (0.00 sec)- 場景驗證結(jié)果
從場景一來看RR級別下是可以避免幻讀的問題，在意料之中。那如果我們在事務(wù)1中兩次查詢之間進行了當(dāng)前讀更新操作呢，那會不會出現(xiàn)幻讀的問題呢，那接下來我們來看一看場景二。
- 場景二
- 開啟事務(wù)1
- 在事務(wù)1中查詢用戶信息
- 開啟事務(wù)2
- 在事務(wù)2中插入一條新數(shù)據(jù)
- 提交事務(wù)2
- 在事務(wù)1中將ID為1的數(shù)據(jù)的用戶姓名修改為Iversen
- 在事務(wù)1中再次查詢用戶信息
- 提交事務(wù)1
mysql> START TRANSACTION; -- 1. 開啟事務(wù)1
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user_info; -- 2. 在事務(wù)1中查詢用戶信息
+----+-------+--------+---------------+
| id | name | gender | email |
+----+-------+--------+---------------+
| 1 | Curry | 男 | curry@163.com |
| 2 | Wade | 男 | wade@163.com |
| 3 | James | 男 | james@163.com |
+----+-------+--------+---------------+
3 rows in set (0.00 sec)
mysql> update user_info set name = 'Iversen' where id = 1; -- 在事務(wù)1中將ID為1的數(shù)據(jù)的用戶姓名修改為Iversen
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user_info; -- 7. 在事務(wù)1中再次查詢用戶信息
+----+---------+--------+---------------+
| id | name | gender | email |
+----+---------+--------+---------------+
| 1 | Iversen | 男 | curry@163.com |
| 2 | Wade | 男 | wade@163.com |
| 3 | James | 男 | james@163.com |
+----+---------+--------+---------------+
3 rows in set (0.00 sec)
mysql> commit; -- 8. 提交事務(wù)1
Query OK, 0 rows affected (0.00 sec)mysql> START TRANSACTION; -- 3.開啟事務(wù)2
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `user_info` (`id`, `name`, `gender`, `email`) VALUES (4, 'White', '男', 'white@163.com'); -- 4.在事務(wù)2中插入一條新數(shù)據(jù)
Query OK, 1 row affected (0.00 sec)
mysql> commit; -- 5.提交事務(wù)2
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user_info;
+----+-------+--------+---------------+
| id | name | gender | email |
+----+-------+--------+---------------+
| 1 | Curry | 男 | curry@163.com |
| 2 | Wade | 男 | wade@163.com |
| 3 | James | 男 | james@163.com |
| 4 | White | 男 | white@163.com |
+----+-------+--------+---------------+
4 rows in set (0.00 sec)- 場景驗證結(jié)果
從場景二來看RR級別下仍然是可以避免幻讀的問題，那是不是就可以確定RR級別下已經(jīng)完全解決了幻讀的問題呢。那我們再換一種更新方式來看看吧。
- 場景三
- 開啟事務(wù)1
- 在事務(wù)1中查詢用戶信息
- 開啟事務(wù)2
- 在事務(wù)2中插入一條新數(shù)據(jù)
- 提交事務(wù)2
- 在事務(wù)1中將所有用戶的郵箱信息的后綴更換為@gmail.com
- 在事務(wù)1中再次查詢用戶信息
- 提交事務(wù)1
mysql> START TRANSACTION; -- 1. 開啟事務(wù)1
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user_info; -- 2. 在事務(wù)1中查詢用戶信息
+----+-------+--------+---------------+
| id | name | gender | email |
+----+-------+--------+---------------+
| 1 | Curry | 男 | curry@163.com |
| 2 | Wade | 男 | wade@163.com |
| 3 | James | 男 | james@163.com |
+----+-------+--------+---------------+
3 rows in set (0.00 sec)
mysql> update user_info set email = REPLACE(email, '@163.com', '@gmail.com'); -- 6. 在事務(wù)1中將所有用戶的郵箱信息的后綴更換為@gmail.com
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select * from user_info; -- 7. 在事務(wù)1中再次查詢用戶信息
+----+-------+--------+-----------------+
| id | name | gender | email |
+----+-------+--------+-------------- --+
| 1 | Curry | 男 | curry@gmail.com |
| 2 | Wade | 男 | wade@gmail.com |
| 3 | James | 男 | james@gmail.com |
| 4 | White | 男 | white@gmail.com |
+----+-------+--------+-----------------+
4 rows in set (0.00 sec)
mysql> commit; -- 8. 提交事務(wù)1
Query OK, 0 rows affected (0.00 sec)mysql> START TRANSACTION; -- 3.開啟事務(wù)2
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `user_info` (`id`, `name`, `gender`, `email`) VALUES (4, 'White', '男', 'white@163.com'); -- 4.在事務(wù)2中插入一條新數(shù)據(jù)
Query OK, 1 row affected (0.00 sec)
mysql> commit; -- 5.提交事務(wù)2
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user_info;
+----+-------+--------+---------------+
| id | name | gender | email |
+----+-------+--------+---------------+
| 1 | Curry | 男 | curry@163.com |
| 2 | Wade | 男 | wade@163.com |
| 3 | James | 男 | james@163.com |
| 4 | White | 男 | white@163.com |
+----+-------+--------+---------------+
4 rows in set (0.00 sec)- 場景驗證結(jié)果
事務(wù)1在進行更新之后再次查詢讀取到了事務(wù)2新插入到數(shù)據(jù)，出現(xiàn)了幻讀。
2.3.3 小結(jié)
看來RR級別的確沒有完全解決幻讀問題，那為什么還會存在幻讀的問題呢，為什么更新的方式不同，會出現(xiàn)不同的結(jié)果，什么情況下還會出現(xiàn)幻讀問題呢。帶著這樣的疑問，我們來探索下～
3 分析
從驗證結(jié)果來看，當(dāng)前讀是可以完全避免幻讀的問題，而對于快照讀如果在兩次讀取之間進行了當(dāng)前讀，在某些情況下是會觸發(fā)幻讀的問題。那么下面我們可以從當(dāng)前讀的實現(xiàn)(MVCC)的角度來分析幻讀問題的產(chǎn)生原因。
我們應(yīng)該知道MVCC實現(xiàn)原理主要是依賴記錄中的3個隱式字段，undo日志，Read View來實現(xiàn)的，好，那么我們基于產(chǎn)生幻讀的場景結(jié)合MVCC的實現(xiàn)原理來一步步進行分析。
1.產(chǎn)生幻讀的場景
- 初始三條測試數(shù)據(jù)
- 開啟事務(wù)1
- 在事務(wù)1中查詢用戶信息
- 開啟事務(wù)2
- 在事務(wù)2中插入一條新數(shù)據(jù)
- 提交事務(wù)2
- 在事務(wù)1中將所有用戶的郵箱信息更換為@gmail.com
- 在事務(wù)1中再次查詢用戶信息
- 提交事務(wù)1
1.1 執(zhí)行步驟0：初始三條測試數(shù)據(jù)
在初始化三條數(shù)據(jù)后三條初始數(shù)據(jù)分別會有三個隱式字段值，
DB_TRX_ID(事務(wù)id),DB_ROLL_PTR(回滾指針),DB_ROW_ID(隱式主鍵)。
如下：因為是新插入的數(shù)據(jù),回滾指針字段的值均為NULL。
+----+-------+--------+--------------+-----------+-----------+---------+
| id | name | gender | email | DB_TRX_ID |DB_ROLL_PTR|DB_ROW_ID|
+----+-------+--------+--------------+-----------+-----------+---------+
| 1 | Curry | 男 | curry@163.com| 2334 | NULL | 1 |
| 2 | Wade | 男 | wade@163.com | 2334 | NULL | 2 |
| 3 | James | 男 | james@163.com| 2334 | NULL | 3 |
+----+-------+--------+--------------+-----------+-----------+---------+1.2 執(zhí)行步驟1：開啟事務(wù)1
在開啟事務(wù)1后會為事務(wù)1分配一個唯一的事務(wù)id
mysql> SELECT trx_id,trx_state,trx_started FROM INFORMATION_SCHEMA.INNODB_TRX;
+-----------------+-----------+---------------------+
| trx_id | trx_state | trx_started |
+-----------------+-----------+---------------------+
| 2335 | RUNNING | 2024-07-28 21:31:52 |
+-----------------+-----------+---------------------+
1 row in set (0.00 sec)1.3 執(zhí)行步驟2：在事務(wù)1中查詢用戶信息
因為是開啟事務(wù)后的首次查詢，所以此時會生成一張Read Veaw讀視圖，此時trx_list，up_limit_id，low_limit_id的值分別為：
trx_list：因為是測試驗證，無其他并發(fā)事務(wù)參與，所以活躍事務(wù)列表中只有當(dāng)前的事務(wù)id[2335];
up_limit_id：活躍事務(wù)列表中最小的事務(wù)id，即當(dāng)前事務(wù)id:2335;
low_limit_id：下一個未開始的事務(wù)id,即當(dāng)前事務(wù)id+1為:2336;
此時查詢數(shù)據(jù)會使用當(dāng)前生成的Read View并依據(jù)可見性算法來進行查詢，因為數(shù)據(jù)庫中數(shù)據(jù)的事務(wù)id均小于up_limit_id所以對當(dāng)前事務(wù)均是可見的，所以三條初始數(shù)據(jù)會全部被查詢出來。
注: 可見性算法
- 首先比較 DB_TRX_ID < up_limit_id , 如果小于，則當(dāng)前事務(wù)能看到 DB_TRX_ID 所在的記錄，如果大于等于進入下一個判斷
- 接下來判斷 DB_TRX_ID >= low_limit_id , 如果大于等于則代表 DB_TRX_ID 所在的記錄在 Read View 生成后才出現(xiàn)的，那對當(dāng)前事務(wù)肯定不可見，如果小于則進入下一個判斷
- 判斷 DB_TRX_ID 是否在活躍事務(wù)之中，trx_list.contains (DB_TRX_ID)，如果在，則代表Read View 生成時刻，這個事務(wù)仍處于活躍中，還沒有commit，如果DB_TRX_ID=creator_trx_id，則說明是當(dāng)前事務(wù)自己產(chǎn)生的數(shù)據(jù)，是可見的，如果不等于，則為其他事務(wù)修改的數(shù)據(jù)，當(dāng)前事務(wù)也是看不見的；如果不在活躍事務(wù)之中，則說明，你這個事務(wù)在Read View生成之前就已經(jīng)commit了，修改的結(jié)果，當(dāng)前事務(wù)是能夠看見的。
1.4 執(zhí)行步驟3：開啟事務(wù)2
在開啟事務(wù)2后會為事務(wù)2分配一個唯一的事務(wù)id。
事務(wù)id的分配是遞增的，因此事務(wù)2的事務(wù)id一定是大于事務(wù)1。
mysql> SELECT trx_id,trx_state,trx_started FROM INFORMATION_SCHEMA.INNODB_TRX;
+-----------------+-----------+---------------------+
| trx_id | trx_state | trx_started |
+-----------------+-----------+---------------------+
| 2336 | RUNNING | 2024-07-28 21:35:52 |
+-----------------+-----------+---------------------+
1 row in set (0.00 sec)1.5 執(zhí)行步驟4：在事務(wù)2中插入一條新數(shù)據(jù)
此時會產(chǎn)生一條新插入數(shù)據(jù)的insert undolog日志
1.6 執(zhí)行步驟5：提交事務(wù)2
由于事務(wù)提交插入的數(shù)據(jù)會實際生效，insert undolog日志會被刪除,此時表的數(shù)據(jù)情況如下：
+----+-------+--------+--------------+-----------+-----------+---------+
| id | name | gender | email | DB_TRX_ID |DB_ROLL_PTR|DB_ROW_ID|
+----+-------+--------+--------------+-----------+-----------+---------+
| 1 | Curry | 男 | curry@163.com| 2334 | NULL | 1 |
| 2 | Wade | 男 | wade@163.com | 2334 | NULL | 2 |
| 3 | James | 男 | james@163.com| 2334 | NULL | 3 |
| 4 | White | 男 | white@163.com| 2336 | NULL | 4 |
+----+-------+--------+--------------+-----------+-----------+---------+1.7 執(zhí)行步驟6：在事務(wù)1中將所有用戶的郵箱信息的后綴更換為@gmail.com
因為是更新操作，所以是當(dāng)前讀會將所有的符合條件的數(shù)據(jù)都讀取出來，進行更新。更新后的數(shù)據(jù)表中的數(shù)據(jù)如下：
+----+-------+--------+----------------+-----------+-----------+---------+
| id | name | gender | email | DB_TRX_ID |DB_ROLL_PTR|DB_ROW_ID|
+----+-------+--------+----------------+-----------+-----------+---------+
| 1 | Curry | 男 |curry@gmail.com | 2335 | 0x123825 | 1 |
| 2 | Wade | 男 |wade@gmail.com | 2335 | 0x153125 | 2 |
| 3 | James | 男 |james@gmail.com | 2335 | 0x115725 | 3 |
| 4 | White | 男 |white@gmail.com | 2335 | 0x163225 | 4 |
+----+-------+--------+----------------+-----------+-----------+---------+undolog情況如下：
圖片
1.8 執(zhí)行步驟7：在事務(wù)1中再次查詢用戶信息
- 當(dāng)前是RR的隔離級別，所以此時使用的Read View讀視圖仍然是首次查詢生成的讀視圖。
- 依據(jù)Read View的可見性算法分析，分別對四條數(shù)據(jù)的undolog版本鏈從尾部至頭部逐一進行可見性判斷是否可見進行追溯，會看到四條數(shù)據(jù)的尾部版本就可對當(dāng)前事務(wù)可見。所以四條數(shù)據(jù)是會在此次查詢中全部被查詢得到。
由此可以推斷產(chǎn)生幻讀的原因啦，因為事務(wù)1中的更新操作，對事務(wù)2中的新插入的數(shù)據(jù)也進行了更新，更新后新數(shù)據(jù)的undolog日志中會追加此次更新的回滾日志，并指向新插入數(shù)據(jù)的undolog記錄，此時根據(jù)MVCC的可見性算法，事務(wù)2新插入的數(shù)據(jù)此時對于事務(wù)1也變成了可見的，因此產(chǎn)生了幻讀的問題。
- 那同樣是更新場景二為什么沒有產(chǎn)生幻讀的問題呢？
在場景二中，更新語句更新的是事務(wù)1第一次查詢可見的數(shù)據(jù)，而對事務(wù)2中新插入的數(shù)據(jù)沒有進行任何操作，新插入數(shù)據(jù)的版本鏈中是不存在當(dāng)前事務(wù)產(chǎn)生的版本數(shù)據(jù)的，因此新插入的數(shù)據(jù)對與事務(wù)1仍然不可見，所以沒有產(chǎn)生幻讀問題。
4 總結(jié)
- 當(dāng)前讀可以通過鎖機制完全避免幻讀問題，快照讀如果中間對其他事務(wù)已提交的插入或更新的數(shù)據(jù)進行了更新，則會出現(xiàn)幻讀的問題。
- 如何進行避免呢？
采用串行化的隔離級別（不建議）；
開發(fā)時注意考慮這種產(chǎn)生幻讀的場景，盡量通過調(diào)整代碼邏輯規(guī)避幻讀問題的發(fā)生（建議）；
- 若不能通過調(diào)整代碼邏輯規(guī)避，可以考慮采用當(dāng)前讀的方式避免（建議）；
