mysql幻讀怎麼解決

交易隔離等級（tx_isolation）

mysql 有四級交易隔離等級每個等級都有字元或數字編號

等級	symbol	值	描述
#已讀取未提交	READ-UNCOMMITTED	0	存在髒讀、不可重複讀取、幻讀的問題
讀已提交	READ-COMMITTED	1	解決髒讀的問題，有不可重複讀取、幻讀的問題
可重複讀取	REPEATABLE-READ	2	mysql 預設級別，解決髒讀、不可重複讀的問題，有幻讀的問題。使用MMVC機制實作可重複讀取
序列化	SERIALIZABLE	3	解決髒讀、無法重複讀取、幻讀，可保證事務安全，但完全串列執行，效能最低

我們可以透過以下指令 檢視/設定 全域/會話 的事務隔離等級

mysql> SELECT @@global.tx_isolation, @@tx_isolation;
+-----------------------+------------------+
| @@global.tx_isolation | @@tx_isolation   |
+-----------------------+------------------+
| REPEATABLE-READ       | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set (0.00 sec)

# 设定全局的隔离级别 设定会话 global 替换为 session 即可 把set语法温习一下
# SET [GLOABL] config_name = 'foobar';
# SET @@[session.|global.]config_name = 'foobar';
# SELECT @@[global.]config_name;

SET @@gloabl.tx_isolation = 0;
SET @@gloabl.tx_isolation = 'READ-UNCOMMITTED';

SET @@gloabl.tx_isolation = 1;
SET @@gloabl.tx_isolation = 'READ-COMMITTED';

SET @@gloabl.tx_isolation = 2;
SET @@gloabl.tx_isolation = 'REPEATABLE-READ';

SET @@gloabl.tx_isolation = 3;
SET @@gloabl.tx_isolation = 'SERIALIZABLE';

幻讀

#首先我們要搞清楚何謂幻讀，目前網路上的眾多解釋幻讀的博文個人感覺仔細設想一下就能找出推翻的例子，就像博文把非阻塞IO 等同為異步IO，然後好多文章都紛紛借用，其實這倆貨是完全不同，非阻塞IO 是同步IO 中的一種模式，並非異步IO。大眾錯誤的看法已經被「糾正」了，讓我們重新回歸主題。

幻讀會在 RU / RC / RR 級別下出現，SERIALIZABLE 則杜絕了 幻讀，但 RU / RC 下還會存在髒讀、不可重複讀，故我們就以 RR 級別來研究 讀，排除其他幹擾。

注意：RR 等級下有幻讀的可能，但也是可以使用對記錄手動加 X鎖定 的方法來消除幻讀。 SERIALIZABLE 正是對所有事務都加 X鎖 才杜絕了 幻讀，但許多場景下我們的業務 sql 並不會有 幻讀 的風險。儘管使用SERIALIZABLE可以確保交易的絕對安全性，但會對效能造成許多不必要的損失。故可以在 RR 下根據業務需求決定是否加鎖，存在幻讀風險我們加鎖，不存在就不加鎖，事務安全與性能兼備，這也是 RR 作為 mysql 默認隔是個事務離級別的原因，所以需要正確的理解 幻讀。

幻讀錯誤的理解：說幻讀是 事務A 執行兩次 select 操作得到不同的資料集，即 select 1 得到 10 筆記錄，select 2 得到 11 筆記錄。這其實不是幻讀，這是不可重複讀的一種，只會在 R-U R-C 等級下出現，而在 mysql 預設的 RR 隔離等級是不會出現的。

這裡給出我對幻讀的比較白話的理解：

幻讀，並不是說兩次讀取獲取的結果集不同，幻讀側重的方面是某一次的select 操作所得到的結果所表徵的資料狀態無法支撐後續的業務操作。更具體一些：select 某記錄是否存在，不存在，準備插入此記錄，但執行 insert 時發現此記錄已存在，無法插入，此時就發生了幻讀。

這裡給出mysql 幻讀的比較圖像的場景（借用我在知乎上的回答）：

table users: id primary key

交易T1

交易T2

step1 T1: SELECT * FROM \`users\` WHERE \`id\` = 1;
step2 T2: INSERT INTO \`users\` VALUES (1, 'big cat');
step3 T1: INSERT INTO \`users\` VALUES (1, 'big cat');
step4 T1: SELECT * FROM \`users \` WHERE \`id\` = 1;

T1 ：主事務，偵測表中是否有id 為1 的記錄，沒有則插入，這是我們期望的正常業務邏輯。

T2 ：幹擾事務，目的在於擾亂 T1 的正常的事務執行。

在RR 隔離等級下，step1、step2 是會正常執行的，step3 則會報錯主鍵衝突，對於T1 的業務來說是執行失敗的，這裡T1 就是發生了幻讀，因為T1 在step1 中讀取的資料狀態並不能支撐後續的業務操作，T1：「見鬼了，我剛才讀到的結果應該可以支持我這樣操作才對啊，為什麼現在不可以」。 T1 不敢相信的又執行了 step4，發現和 setp1 讀取的結果是一樣的（RR下的 MMVC機制）。此時，幻讀無疑已經發生，T1 無論讀取多少次，都查不到id = 1 的記錄，但它的確無法插入這條他透過讀取來認定不存在的記錄（此資料已被T2插入），對於T1 來說，它幻讀了。

其實 RR 也是可以避免幻讀的，透過對 select 操作手動加 行X鎖（SELECT ... FOR UPDATE 這也正是 SERIALIZABLE 隔離等級下會隱含為你做的事情），同時還要知道，即便目前記錄不存在，例如 id=1 是不存在的，當前事務也會獲得一把記錄鎖（因為InnoDB的行鎖鎖定的是索引，故記錄實體存在與否沒關係，存在就加行X鎖，不存在就加 next-key lock間隙X鎖定），其他事務則無法插入此索引的記錄，故杜絕了幻讀。

在 SERIALIZABLE 隔離等級下，step1 執行時是會隱式的新增行(X)鎖定/ gap(X)鎖定的，從而step2 會被阻塞，step3 會正常執行，待T1 提交後， T2 才能繼續執行（主鍵衝突執行失敗），對於T1 來說業務是正確的，成功的阻塞扼殺了擾亂業務的T2，對於T1來說他前期讀取的結果是可以支撐其後續業務的。

所以 mysql 的幻读并非什么读取两次返回结果集不同，而是事务在插入事先检测不存在的记录时，惊奇的发现这些数据已经存在了，之前的检测读获取到的数据如同鬼影一般。

这里要灵活的理解读取的意思，第一次select是读取，第二次的 insert 其实也属于隐式的读取，只不过是在 mysql 的机制中读取的，插入数据也是要先读取一下有没有主键冲突才能决定是否执行插入。

不可重复读侧重表达 读-读，幻读则是说 读-写，用写来证实读的是鬼影。

RR级别下防止幻读

RR级别下只要对 SELECT 操作也手动加行（X）锁即可类似 SERIALIZABLE 级别（它会对 SELECT 隐式加锁），即大家熟知的：

# 这里需要用 X锁， 用 LOCK IN SHARE MODE 拿到 S锁 后我们没办法做 写操作
SELECT `id` FROM `users` WHERE `id` = 1 FOR UPDATE;

如果 id = 1 的记录存在则会被加行（X）锁，如果不存在，则会加 next-lock key / gap 锁（范围行锁），即记录存在与否，mysql 都会对记录应该对应的索引加锁，其他事务是无法再获得做操作的。

这里我们就展示下 id = 1 的记录不存在的场景，FOR UPDATE 也会对此 “记录” 加锁，要明白，InnoDB 的行锁（gap锁是范围行锁，一样的）锁定的是记录所对应的索引，且聚簇索引同记录是直接关系在一起的。

id = 1 的记录不存在，开始执行事务：
step1: T1 查询 id = 1 的记录并对其加 X锁
step2: T2 插入 id = 1 的记录，被阻塞
step3: T1 插入 id = 1 的记录，成功执行（T2 依然被阻塞中），T1 提交（T2 唤醒但主键冲突执行错误）

T1事务符合业务需求成功执行，T2干扰T1失败。

SERIALIZABLE级别杜绝幻读

在这个层面上，我们不必对 SELECT 操作进行显式加锁，因为InnoDB会自动加锁以确保事务的安全性，但是这会导致性能较低

step1: T1 查询 id = 2 的记录，InnoDB 会隐式的对齐加 X锁
step2: T2 插入 id = 2 的记录，被阻塞
step3: T1 插入 id = 2 的记录，成功执行（T2 依然被阻塞中）
step4: T1 成功提交（T2 此时唤醒但主键冲突执行错误）

T1事务符合业务需求成功执行，T2干扰T1失败。

以上是mysql幻讀怎麼解決的詳細內容。更多資訊請關注PHP中文網其他相關文章！