MySQL中幻讀及消除的示例分析

小編給大家分享一下MySQL中幻讀及消除的示例分析，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

事務(wù)隔離級(jí)別

MySQL有四級(jí)事務(wù)隔離級(jí)別:

讀未提交 READ-UNCOMMITTED： 存在臟讀，不可重復(fù)讀，幻讀的問(wèn)題
讀已提交 READ-COMMITTED：不存在臟讀，但存在不可重復(fù)讀，幻讀問(wèn)題
可重復(fù)讀 REPEATABLE-READ：不存在臟讀，不可重復(fù)讀問(wèn)題，但存在幻讀問(wèn)題
序列化SERIALIZABLE：解決臟讀，不可重復(fù)讀，幻讀問(wèn)題，但完全串行執(zhí)行，性能最低

什么是幻讀

幻讀錯(cuò)誤的理解：說(shuō)幻讀是事務(wù)A 執(zhí)行兩次 select 操作得到不同的數(shù)據(jù)集，即 select 1 得到10條記錄，select 2 得到11條記錄。這其實(shí)并不是幻讀，這是不可重復(fù)讀的一種，只會(huì)在 R-U R-C 級(jí)別下出現(xiàn)，而在 mysql 默認(rèn)的 RR 隔離級(jí)別是不會(huì)出現(xiàn)的。

這里給出我對(duì)幻讀的理解：

幻讀，并不是說(shuō)事務(wù)中多次讀取獲取的結(jié)果集不同，幻讀更重要的是某次的 select 操作得到的結(jié)果集所表征的數(shù)據(jù)狀態(tài)無(wú)法支撐后續(xù)的業(yè)務(wù)操作。更為具體一些：select 記錄不存在，準(zhǔn)備插入此記錄，但執(zhí)行 insert 時(shí)發(fā)現(xiàn)此記錄已存在，無(wú)法插入，如同產(chǎn)生了幻覺(jué)

舉個(gè)例子可能會(huì)簡(jiǎn)化理解：

mysql> show create table user\G
*************************** 1. row ***************************
    Table: user
Create Table: CREATE TABLE `user` (
 `id` int(11) NOT NULL,
 `name` varchar(32) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

分別開(kāi)啟兩個(gè)事務(wù)T1 & T2，并設(shè)置其隔離級(jí)別為Reaptable-Read：

T1：

mysql> set global transaction isolation level repeatable read;                                         

mysql> begin;
mysql> select * from user;
mysql> insert into user values (1, 'jeff');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> select * from user;

T2:

mysql> set global transaction isolation level repeatable read;                                         

mysql> begin;
mysql> insert into user values (1, 'jeff');
mysql> commit;

T1 事務(wù)檢測(cè)表中是否有 id 為 1 的記錄，沒(méi)有則插入

T2 插入干擾記錄，造成T1出現(xiàn)幻讀。

上例中需要確保T1事務(wù)執(zhí)行begin后才開(kāi)始執(zhí)行事務(wù)T2。

上例中T1就發(fā)生了幻讀，因?yàn)?T1讀取的數(shù)據(jù)狀態(tài)與后面的動(dòng)作發(fā)生了語(yǔ)義上的沖突：查詢的時(shí)候明明提示記錄不存在，插入的時(shí)候去提示主鍵重復(fù)，類似于出現(xiàn)幻影，因而稱之為幻讀。

如何消除幻讀

MySQL當(dāng)前有兩種方式可以消除幻讀：

1. 通過(guò)對(duì)select操作手動(dòng)加行X鎖(SELECT ... FOR UPDATE )。原因是InnoDB中行鎖鎖定的
是索引，縱然當(dāng)前記錄不存在，當(dāng)前事務(wù)也會(huì)獲得一把記錄鎖（記錄存在就加行X鎖，不
存在就加next-key lock間隙X鎖），這樣其他事務(wù)則無(wú)法插入此索引的記錄，杜絕幻
讀。
2. 進(jìn)一步提升隔離級(jí)別為SERIALIZABLE
測(cè)試一下效果

mysql> begin;

mysql> select * from user where id = 2 for update;
mysql> insert into user values (2, 'tony');

mysql> commit;

T2:

mysql> begin;

mysql> insert into user values (2, 'jimmy');
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

現(xiàn)在T1查詢時(shí)攜帶了for update，在Innodb內(nèi)會(huì)對(duì)該索引加鎖（即使當(dāng)前不存在），于是事務(wù)T2的insert會(huì)被阻塞直到T1顯示提交，這樣T1成功了，對(duì)于T1來(lái)說(shuō)，幻讀確實(shí)被消除了，但T2的插入會(huì)報(bào)主鍵重復(fù)，這也符合預(yù)期。

至于另外一種提升隔離級(jí)別消除幻讀的方式感興趣的可以自己嘗試，這里不再重復(fù)，其本質(zhì)是類似的，只是讓系統(tǒng)代替了手工加鎖。

總結(jié)

RR作為 mysql 事務(wù)默認(rèn)隔離級(jí)別，是事務(wù)安全與性能的折中，正確認(rèn)識(shí)幻讀后，開(kāi)發(fā)者便可以根據(jù)需求自行決定是否需要防止幻讀。

SERIALIZABLE則是悲觀的認(rèn)為幻讀時(shí)刻都會(huì)發(fā)生，故會(huì)自動(dòng)的隱式的對(duì)事務(wù)所需資源加排它鎖，其他事務(wù)訪問(wèn)此資源會(huì)被阻塞等待，故事務(wù)是安全的，但需要認(rèn)真考慮性能。

InnoDB的鎖是針對(duì)索引，這點(diǎn)需要引起注意。對(duì)行記錄加鎖，如果存在，加X(jué)鎖，否則會(huì)加 next-key lock / gap 鎖 / 間隙鎖，故InnoDB可以實(shí)現(xiàn)事務(wù)對(duì)某記錄的預(yù)先占用，只要本事務(wù)還在，其他事務(wù)就別想占有它。關(guān)于鎖，后面還會(huì)再有專門的文章討論。

以上是“MySQL中幻讀及消除的示例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！