咦～ MySQL怎么出現幻讀啦！

作者：孔德志 2024-09-02 00:00:00

當前讀可以通過鎖機制完全避免幻讀問題，快照讀如果中間對其他事務已提交的插入或更新的數據進行了更新，則會出現幻讀的問題。

1 背景

InnoDB默認的事務隔離級別是REPEATABLE-READ，它為了解決該隔離級別并發情況下的幻讀問題，使用了LBCC（基于鎖的并發控制）和MVCC（多版本的并發控制）兩種方案。其中LBCC解決的是當前讀情況下的幻讀問題，MVCC解決的是快照讀情況下的幻讀問題，那既然如此，該隔離級別下是否仍然還存在幻讀的問題呢？幻讀問題到底有沒有完全解決呢？基于這樣的疑問，下面我們來進行驗證下吧。

2 驗證

2.1 驗證準備

2.1.1 環境信息

MySQL版本：5.6.36
存儲引擎：InnoDB
隔離級別：REPEATABLE-READ

2.1.2 數據準備

為了進行驗證，在測試庫建立了一張測試使用的用戶信息表，并且插入了3條初始數據。

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 當前讀場景驗證

首先我們先來看看當前讀的場景下會不會出現幻讀的問題。

2.2.1 前言

什么是當前讀

當前讀（Locking Read）也稱為鎖定讀，讀取的是數據當前的最新版本，而且讀取到這個數據之后會對這個數據加鎖，防止別的事務進行更改，即通過next-key鎖（唯一索引next-key鎖會退化為記錄鎖）來解決當前讀中的臟讀，幻讀，不可重復讀問題，也就是LBCC的方式。在進行寫操作的時候也需要進行“當前讀”，讀取數據記錄的最新版本。當前讀包含以下SQL類型：select ... lock in share mode 、select ... for update、update 、delete 、insert。

什么是臨鍵鎖

圖片

我們將數據庫中存儲的每一行數據稱為記錄。如上圖中1、5、9、12分別代表id為當前數的記錄。對于鍵值在條件范圍內但不存在的記錄，叫做間隙（GAP）。則上圖中的（-∞，1）、（1，5）...（12，+∞）為數據庫中存在的間隙。而（-∞，1]、（1，5]...（12，+∞）我們稱之為臨鍵，即左開右閉的集合。當我們對上面的記錄和間隙共同加鎖時，添加的便是臨鍵鎖。

2.2.2 場景驗證

觸發當前讀的方式有很多種，這里僅使用select lock in share mode這種方式來進行當前讀幻讀問題驗證。

場景：

開啟事務1
在事務1中通過select lock in share mode進行當前讀查詢用戶信息
開啟事務2
在事務2中插入一條新數據
提交事務2
在事務1中再次查詢用戶信息
提交事務1

mysql> START TRANSACTION; -- 1.開啟事務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.開啟事務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.在事務2中插入一條新數據

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction  -- 因鎖等待插入未成功，最終等待超時，事務回滾終止

場景驗證結果

可以看到在事務1中開始事務執行了當前讀后，事務2在進行插入新數據時進入了鎖等待，最后發生了鎖等待超時，導致事務終止回滾。插入數據因鎖的原因是不會成功的，因此事務1第二次查詢時也不會查詢到新記錄，所以此場景下不會產生幻讀的問題。

2.2.3 小結

由場景驗證結果可以看到，由于臨鍵鎖的存在，會阻塞其他事務對加鎖間隙的數據插入，所以當前讀場景下通過LBCC是可以完全解決幻讀的問題。

2.3.快照讀場景驗證

那接下來我們再看看快照讀場景下是怎么樣的。

2.3.1 前言

什么是快照讀

由于當前讀是通過LBCC基于鎖的方式來進行并發控制，是悲觀鎖的實現，同時也會因為鎖的原因，造成鎖沖突的概率變大，也會導致性能的下降，因此基于提高并發性能的考慮，引入了快照讀，快照讀顧名思義即讀取的是數據的快照版本，快照讀的實現是基于MVCC多版本并發控制，它在很多情況下，避免了加鎖操作，降低了性能開銷。

2.3.2 場景驗證

場景一

開啟事務1
在事務1中查詢用戶信息
開啟事務2
在事務2中插入一條新數據
提交事務2
在事務1中再次查詢用戶信息
提交事務1

mysql> START TRANSACTION; -- 1.開啟事務1
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user_info; -- 2.在事務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.在事務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.提交事務1
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION; -- 3.開啟事務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.在事務2中插入一條新數據
Query OK, 1 row affected (0.00 sec)

mysql> commit; -- 5.提交事務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)

場景驗證結果

從場景一來看RR級別下是可以避免幻讀的問題，在意料之中。那如果我們在事務1中兩次查詢之間進行了當前讀更新操作呢，那會不會出現幻讀的問題呢，那接下來我們來看一看場景二。

場景二

開啟事務1
在事務1中查詢用戶信息
開啟事務2
在事務2中插入一條新數據
提交事務2
在事務1中將ID為1的數據的用戶姓名修改為Iversen
在事務1中再次查詢用戶信息
提交事務1

mysql> START TRANSACTION; -- 1. 開啟事務1
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user_info; -- 2. 在事務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; -- 在事務1中將ID為1的數據的用戶姓名修改為Iversen
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from user_info; -- 7. 在事務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. 提交事務1
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION; -- 3.開啟事務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.在事務2中插入一條新數據
Query OK, 1 row affected (0.00 sec)

mysql> commit; -- 5.提交事務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)

場景驗證結果

從場景二來看RR級別下仍然是可以避免幻讀的問題，那是不是就可以確定RR級別下已經完全解決了幻讀的問題呢。那我們再換一種更新方式來看看吧。

場景三

開啟事務1
在事務1中查詢用戶信息
開啟事務2
在事務2中插入一條新數據
提交事務2
在事務1中將所有用戶的郵箱信息的后綴更換為@gmail.com
在事務1中再次查詢用戶信息
提交事務1

mysql> START TRANSACTION; -- 1. 開啟事務1
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user_info; -- 2. 在事務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. 在事務1中將所有用戶的郵箱信息的后綴更換為@gmail.com
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0
mysql> select * from user_info;  -- 7. 在事務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. 提交事務1
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION; -- 3.開啟事務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.在事務2中插入一條新數據
Query OK, 1 row affected (0.00 sec)

mysql> commit; -- 5.提交事務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)

場景驗證結果

事務1在進行更新之后再次查詢讀取到了事務2新插入到數據，出現了幻讀。

2.3.3 小結

看來RR級別的確沒有完全解決幻讀問題，那為什么還會存在幻讀的問題呢，為什么更新的方式不同，會出現不同的結果，什么情況下還會出現幻讀問題呢。帶著這樣的疑問，我們來探索下～

3 分析

從驗證結果來看，當前讀是可以完全避免幻讀的問題，而對于快照讀如果在兩次讀取之間進行了當前讀，在某些情況下是會觸發幻讀的問題。那么下面我們可以從當前讀的實現(MVCC)的角度來分析幻讀問題的產生原因。

我們應該知道MVCC實現原理主要是依賴記錄中的3個隱式字段，undo日志，Read View來實現的，好，那么我們基于產生幻讀的場景結合MVCC的實現原理來一步步進行分析。

1.產生幻讀的場景

初始三條測試數據
開啟事務1
在事務1中查詢用戶信息
開啟事務2
在事務2中插入一條新數據
提交事務2
在事務1中將所有用戶的郵箱信息更換為@gmail.com
在事務1中再次查詢用戶信息
提交事務1

1.1 執行步驟0：初始三條測試數據

在初始化三條數據后三條初始數據分別會有三個隱式字段值，

DB_TRX_ID(事務id),DB_ROLL_PTR(回滾指針),DB_ROW_ID(隱式主鍵)。

如下：因為是新插入的數據,回滾指針字段的值均為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 執行步驟1：開啟事務1

在開啟事務1后會為事務1分配一個唯一的事務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 執行步驟2：在事務1中查詢用戶信息

因為是開啟事務后的首次查詢，所以此時會生成一張Read Veaw讀視圖，此時trx_list，up_limit_id，low_limit_id的值分別為：

trx_list：因為是測試驗證，無其他并發事務參與，所以活躍事務列表中只有當前的事務id[2335];

up_limit_id：活躍事務列表中最小的事務id，即當前事務id:2335;

low_limit_id：下一個未開始的事務id,即當前事務id+1為:2336;

此時查詢數據會使用當前生成的Read View并依據可見性算法來進行查詢，因為數據庫中數據的事務id均小于up_limit_id所以對當前事務均是可見的，所以三條初始數據會全部被查詢出來。

注: 可見性算法

首先比較 DB_TRX_ID < up_limit_id , 如果小于，則當前事務能看到 DB_TRX_ID 所在的記錄，如果大于等于進入下一個判斷
接下來判斷 DB_TRX_ID >= low_limit_id , 如果大于等于則代表 DB_TRX_ID 所在的記錄在 Read View 生成后才出現的，那對當前事務肯定不可見，如果小于則進入下一個判斷
判斷 DB_TRX_ID 是否在活躍事務之中，trx_list.contains (DB_TRX_ID)，如果在，則代表Read View 生成時刻，這個事務仍處于活躍中，還沒有commit，如果DB_TRX_ID=creator_trx_id，則說明是當前事務自己產生的數據，是可見的，如果不等于，則為其他事務修改的數據，當前事務也是看不見的；如果不在活躍事務之中，則說明，你這個事務在Read View生成之前就已經commit了，修改的結果，當前事務是能夠看見的。

1.4 執行步驟3：開啟事務2

在開啟事務2后會為事務2分配一個唯一的事務id。

事務id的分配是遞增的，因此事務2的事務id一定是大于事務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 執行步驟4：在事務2中插入一條新數據

此時會產生一條新插入數據的insert undolog日志

1.6 執行步驟5：提交事務2

由于事務提交插入的數據會實際生效，insert undolog日志會被刪除,此時表的數據情況如下：

+----+-------+--------+--------------+-----------+-----------+---------+
| 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 執行步驟6：在事務1中將所有用戶的郵箱信息的后綴更換為@gmail.com

因為是更新操作，所以是當前讀會將所有的符合條件的數據都讀取出來，進行更新。更新后的數據表中的數據如下：

+----+-------+--------+----------------+-----------+-----------+---------+
| 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 執行步驟7：在事務1中再次查詢用戶信息

當前是RR的隔離級別，所以此時使用的Read View讀視圖仍然是首次查詢生成的讀視圖。
依據Read View的可見性算法分析，分別對四條數據的undolog版本鏈從尾部至頭部逐一進行可見性判斷是否可見進行追溯，會看到四條數據的尾部版本就可對當前事務可見。所以四條數據是會在此次查詢中全部被查詢得到。

由此可以推斷產生幻讀的原因啦，因為事務1中的更新操作，對事務2中的新插入的數據也進行了更新，更新后新數據的undolog日志中會追加此次更新的回滾日志，并指向新插入數據的undolog記錄，此時根據MVCC的可見性算法，事務2新插入的數據此時對于事務1也變成了可見的，因此產生了幻讀的問題。

那同樣是更新場景二為什么沒有產生幻讀的問題呢？

在場景二中，更新語句更新的是事務1第一次查詢可見的數據，而對事務2中新插入的數據沒有進行任何操作，新插入數據的版本鏈中是不存在當前事務產生的版本數據的，因此新插入的數據對與事務1仍然不可見，所以沒有產生幻讀問題。

4 總結

當前讀可以通過鎖機制完全避免幻讀問題，快照讀如果中間對其他事務已提交的插入或更新的數據進行了更新，則會出現幻讀的問題。
如何進行避免呢？

采用串行化的隔離級別（不建議）；

開發時注意考慮這種產生幻讀的場景，盡量通過調整代碼邏輯規避幻讀問題的發生（建議）；

若不能通過調整代碼邏輯規避，可以考慮采用當前讀的方式避免（建議）；

責任編輯：武曉燕來源：轉轉技術

MySQL 幻讀數據

成人免费xxxxx在线视频软件_久久精品久久久_亚洲国产精品久久久_天天色天天色_亚洲人成一区_欧美一级欧美三级在线观看