詳解MySQL 幻讀及如何消除
這是一篇數(shù)據(jù)庫隔離級(jí)別的科普文章,旨在了解數(shù)據(jù)庫中著名的幻讀現(xiàn)象,為了專注,對(duì)臟讀、不可重復(fù)讀不作討論。
事務(wù)隔離級(jí)別MySQL有四級(jí)事務(wù)隔離級(jí)別:
讀未提交 READ-UNCOMMITTED: 存在臟讀,不可重復(fù)讀,幻讀的問題讀已提交 READ-COMMITTED:不存在臟讀,但存在不可重復(fù)讀,幻讀問題可重復(fù)讀 REPEATABLE-READ:不存在臟讀,不可重復(fù)讀問題,但存在幻讀問題序列化SERIALIZABLE:解決臟讀,不可重復(fù)讀,幻讀問題,但完全串行執(zhí)行,性能最低
什么是幻讀幻讀錯(cuò)誤的理解:說幻讀是事務(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ì)幻讀的理解:
幻讀,并不是說事務(wù)中多次讀取獲取的結(jié)果集不同,幻讀更重要的是某次的 select 操作得到的結(jié)果集所表征的數(shù)據(jù)狀態(tài)無法支撐后續(xù)的業(yè)務(wù)操作。更為具體一些:select 記錄不存在,準(zhǔn)備插入此記錄,但執(zhí)行 insert 時(shí)發(fā)現(xiàn)此記錄已存在,無法插入,如同產(chǎn)生了幻覺
舉個(gè)例子可能會(huì)簡(jiǎn)化理解:
mysql> show create table userG*************************** 1. row *************************** Table: userCreate Table: CREATE TABLE `user` ( `id` int(11) NOT NULL, `name` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
分別開啟兩個(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 的記錄,沒有則插入
T2 插入干擾記錄,造成T1出現(xiàn)幻讀。
上例中需要確保T1事務(wù)執(zhí)行begin后才開始執(zhí)行事務(wù)T2。
上例中T1就發(fā)生了幻讀,因?yàn)?T1讀取的數(shù)據(jù)狀態(tài)與后面的動(dòng)作發(fā)生了語義上的沖突:查詢的時(shí)候明明提示記錄不存在,插入的時(shí)候去提示主鍵重復(fù),類似于出現(xiàn)幻影,因而稱之為幻讀。
如何消除幻讀MySQL當(dāng)前有兩種方式可以消除幻讀:
1. 通過對(duì)select操作手動(dòng)加行X鎖(SELECT ... FOR UPDATE )。原因是InnoDB中行鎖鎖定的是索引,縱然當(dāng)前記錄不存在,當(dāng)前事務(wù)也會(huì)獲得一把記錄鎖(記錄存在就加行X鎖,不存在就加next-key lock間隙X鎖),這樣其他事務(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來說,幻讀確實(shí)被消除了,但T2的插入會(huì)報(bào)主鍵重復(fù),這也符合預(yù)期。
至于另外一種提升隔離級(jí)別消除幻讀的方式感興趣的可以自己嘗試,這里不再重復(fù),其本質(zhì)是類似的,只是讓系統(tǒng)代替了手工加鎖。
總結(jié)RR作為 mysql 事務(wù)默認(rèn)隔離級(jí)別,是事務(wù)安全與性能的折中,正確認(rèn)識(shí)幻讀后,開發(fā)者便可以根據(jù)需求自行決定是否需要防止幻讀。
SERIALIZABLE則是悲觀的認(rèn)為幻讀時(shí)刻都會(huì)發(fā)生,故會(huì)自動(dòng)的隱式的對(duì)事務(wù)所需資源加排它鎖,其他事務(wù)訪問此資源會(huì)被阻塞等待,故事務(wù)是安全的,但需要認(rèn)真考慮性能。
InnoDB的鎖是針對(duì)索引,這點(diǎn)需要引起注意。對(duì)行記錄加鎖,如果存在,加X鎖,否則會(huì)加 next-key lock / gap 鎖 / 間隙鎖,故InnoDB可以實(shí)現(xiàn)事務(wù)對(duì)某記錄的預(yù)先占用,只要本事務(wù)還在,其他事務(wù)就別想占有它。關(guān)于鎖,后面還會(huì)再有專門的文章討論。
以上就是詳解MySQL 幻讀及如何消除的詳細(xì)內(nèi)容,更多關(guān)于MySQL 幻讀及消除的資料請(qǐng)關(guān)注好吧啦網(wǎng)其它相關(guān)文章!
相關(guān)文章:
1. sql語句LEFT JOIN拼接表詳解2. C# 連接Access數(shù)據(jù)時(shí)總報(bào)找不到dbo.mdb的問題3. Mysql優(yōu)化方法詳細(xì)介紹4. SQLServer創(chuàng)建索引的5種方法小結(jié)5. MySQL字段為 NULL的5大坑6. SQLite3 命令行操作指南7. MySQL實(shí)現(xiàn)批量推送數(shù)據(jù)到Mongo8. SQLite 性能優(yōu)化實(shí)例分享9. SQLite教程(二):C/C++接口簡(jiǎn)介10. SQLite中的WAL機(jī)制詳細(xì)介紹
