.jpg)
Mysql關(guān)系型數(shù)據(jù)庫管理系統(tǒng)
mysql是一個(gè)開放源碼的小型關(guān)聯(lián)式數(shù)據(jù)庫管理系統(tǒng)，開發(fā)者為瑞典mysql ab公司。mysql被廣泛地應(yīng)用在internet上的中小型網(wǎng)站中。由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點(diǎn)，許多中小型網(wǎng)站為了降低網(wǎng)站總體擁有成本而選擇了mysql作為網(wǎng)站數(shù)據(jù)庫。
這篇文章主要介紹了MySQL 四種事務(wù)隔離級(jí)別詳解及對(duì)比的相關(guān)資料,這里對(duì)Mysql 的基礎(chǔ)知識(shí)做了詳細(xì)介紹及對(duì)其事務(wù)隔離進(jìn)行了比較，需要的朋友可以參考下
MySQL 四種事務(wù)隔離級(jí)別詳解及對(duì)比
按照SQL:1992 事務(wù)隔離級(jí)別，InnoDB默認(rèn)是可重復(fù)讀的（REPEATABLE READ）。MySQL/InnoDB 提供SQL標(biāo)準(zhǔn)所描述的所有四個(gè)事務(wù)隔離級(jí)別。你可以在命令行用–transaction-isolation選項(xiàng)，或在選項(xiàng)文件里，為所有連接設(shè)置默認(rèn)隔離級(jí)別。
例如，你可以在my.inf文件的[mysqld]節(jié)里類似如下設(shè)置該選項(xiàng)：
transaction-isolation?=?{READ-UNCOMMITTED?|?READ-COMMITTED?|?REPEATABLE-READ?|?SERIALIZABLE}
用戶可以用SET TRANSACTION語句改變單個(gè)會(huì)話或者所有新進(jìn)連接的隔離級(jí)別。它的語法如下：
SET?[SESSION?|?GLOBAL]?TRANSACTION?ISOLATION?LEVEL?{READ?UNCOMMITTED?|?READ?COMMITTED?|?REPEATABLE?READ?|?SERIALIZABLE}
注意：默認(rèn)的行為（不帶session和global）是為下一個(gè)（未開始）事務(wù)設(shè)置隔離級(jí)別。如果你使用GLOBAL關(guān)鍵字，語句在全局對(duì)從那點(diǎn)開始創(chuàng)建的所有新連接（除了不存在的連接）設(shè)置默認(rèn)事務(wù)級(jí)別。你需要SUPER權(quán)限來做這個(gè)。使用SESSION 關(guān)鍵字為將來在當(dāng)前連接上執(zhí)行的事務(wù)設(shè)置默認(rèn)事務(wù)級(jí)別。 任何客戶端都能自由改變會(huì)話隔離級(jí)別（甚至在事務(wù)的中間），或者為下一個(gè)事務(wù)設(shè)置隔離級(jí)別。
你可以用下列語句查詢?nèi)趾蜁?huì)話事務(wù)隔離級(jí)別：
SELECT?@@global.tx_isolation; SELECT?@@session.tx_isolation; SELECT?@@tx_isolation;
—-以上手冊(cè)中的理論知識(shí);
===========================================================================================
?????? 隔離級(jí)別?????????????? 臟讀（Dirty Read）????????? 不可重復(fù)讀（NonRepeatable Read）???? 幻讀（Phantom Read）?
===========================================================================================
未提交讀（Read uncommitted）??????? 可能??????????????????????????? 可能?????????????????????? 可能
已提交讀（Read committed）????????? 不可能????????????????????????? 可能??????????????????????? 可能
可重復(fù)讀（Repeatable read）????????? 不可能????????????????????????? 不可能???????????????????? 可能
可串行化（Serializable ）??????????????? 不可能????????????????????????? 不可能???????????????????? 不可能
===========================================================================================
·未提交讀(Read Uncommitted)：允許臟讀，也就是可能讀取到其他會(huì)話中未提交事務(wù)修改的數(shù)據(jù)
·提交讀(Read Committed)：只能讀取到已經(jīng)提交的數(shù)據(jù)。Oracle等多數(shù)數(shù)據(jù)庫默認(rèn)都是該級(jí)別 (不重復(fù)讀)
·可重復(fù)讀(Repeated Read)：可重復(fù)讀。在同一個(gè)事務(wù)內(nèi)的查詢都是事務(wù)開始時(shí)刻一致的，InnoDB默認(rèn)級(jí)別。在SQL標(biāo)準(zhǔn)中，該隔離級(jí)別消除了不可重復(fù)讀，但是還存在幻象讀
·串行讀(Serializable)：完全串行化的讀，每次讀都需要獲得表級(jí)共享鎖，讀寫相互都會(huì)阻塞
用例子說明各個(gè)級(jí)別的情況：
① 臟讀:?臟讀就是指當(dāng)一個(gè)事務(wù)正在訪問數(shù)據(jù)，并且對(duì)數(shù)據(jù)進(jìn)行了修改，而這種修改還沒有提交到數(shù)據(jù)庫中，這時(shí)，另外一個(gè)事務(wù)也訪問這個(gè)數(shù)據(jù)，然后使用了這個(gè)數(shù)據(jù)。
session?1: mysql>?select?@@global.tx_isolation; +-----------------------+ |?@@global.tx_isolation?| +-----------------------+ |?REPEATABLE-READ????| +-----------------------+ 1?row?in?set?(0.00?sec) mysql>?select?@@session.tx_isolation; +-----------------------+ |?@@session.tx_isolation?| +-----------------------+ |?REPEATABLE-READ????| +-----------------------+ 1?row?in?set?(0.00?sec) mysql>?start?transaction; Query?OK,?0?rows?affected?(0.00?sec) mysql>?insert?into?ttd?values(1); Query?OK,?1?row?affected?(0.05?sec) mysql>?select?*?from?ttd; +------+ |?id??| +------+ |??1?| +------+ 1?row?in?set?(0.00?sec) session?2: mysql>?select?@@session.tx_isolation; +------------------------+ |?@@session.tx_isolation?| +------------------------+ |?REPEATABLE-READ????| +------------------------+ 1?row?in?set?(0.00?sec) mysql>?select?@@global.tx_isolation; +-----------------------+ |?@@global.tx_isolation?| +-----------------------+ |?REPEATABLE-READ??|????--------該隔離級(jí)別下(除了?read?uncommitted) +-----------------------+ 1?row?in?set?(0.00?sec) mysql>?select?*?from?ttd; Empty?set?(0.00?sec)???????--------不會(huì)出現(xiàn)臟讀 mysql>?set?session?transaction?isolation?level?read?uncommitted; Query?OK,?0?rows?affected?(0.00?sec) mysql>?select?@@session.tx_isolation; +------------------------+ |?@@session.tx_isolation?| +------------------------+ |?READ-UNCOMMITTED????|??--------該隔離級(jí)別下 +------------------------+ 1?row?in?set?(0.00?sec) mysql>?select?*?from?ttd; +------+ |?id??| +------+ |??1?|????????????????????--------REPEATABLE-READ級(jí)別出現(xiàn)臟讀 +------+ 1?row?in?set?(0.00?sec)
結(jié)論:session 2 在READ-UNCOMMITTED 下讀取到session 1 中未提交事務(wù)修改的數(shù)據(jù).
② 不可重復(fù)讀:是指在一個(gè)事務(wù)內(nèi)，多次讀同一數(shù)據(jù)。在這個(gè)事務(wù)還沒有結(jié)束時(shí)，另外一個(gè)事務(wù)也訪問該同一數(shù)據(jù)。那么，在第一個(gè)事務(wù)中的兩次讀數(shù)據(jù)之間，由于第二個(gè)事務(wù)的修改，那么第一個(gè)事務(wù)兩次讀到的的數(shù)據(jù)可能是不一樣的。這樣就發(fā)生了在一個(gè)事務(wù)內(nèi)兩次讀到的數(shù)據(jù)是不一樣的，因此稱為是不可重復(fù)讀。
session?1: mysql>?select?@@session.tx_isolation; +------------------------+ |?@@session.tx_isolation?| +------------------------+ |?READ-COMMITTED?????| +------------------------+ 1?row?in?set?(0.00?sec) mysql>?start?transaction; Query?OK,?0?rows?affected?(0.00?sec) mysql>?select?*?from?ttd; +------+ |?id??| +------+ |??1?| +------+ 1?row?in?set?(0.00?sec) session?2?: mysql>?select?@@session.tx_isolation; +------------------------+ |?@@session.tx_isolation?| +------------------------+ |?REPEATABLE-READ????| +------------------------+ 1?row?in?set?(0.00?sec) mysql>?start?transaction; Query?OK,?0?rows?affected?(0.00?sec) mysql>?select?*?from?ttd; +------+ |?id??| +------+ |??1?| +------+ 1?row?in?set?(0.00?sec) mysql>?insert?into?ttd?values(2);?/也可以更新數(shù)據(jù) Query?OK,?1?row?affected?(0.00?sec) mysql>?select?*?from?ttd; +------+ |?id??| +------+ |??1?| |??2?| +------+ 2?rows?in?set?(0.00?sec) mysql>?commit; Query?OK,?0?rows?affected?(0.02?sec) session?2?提交后,查看session?1?的結(jié)果; session?1: mysql>?select?*?from?ttd; +------+ |?id??| +------+ |??1?|???????????????--------和第一次的結(jié)果不一樣,READ-COMMITTED?級(jí)別出現(xiàn)了不重復(fù)讀 |??2?| +------+ 2?rows?in?set?(0.00?sec) ③?可重復(fù)讀: session?1: mysql>?select?@@session.tx_isolation; +------------------------+ |?@@session.tx_isolation?| +------------------------+ |?REPEATABLE-READ????| +------------------------+ 1?row?in?set?(0.00?sec) mysql>?start?transaction; Query?OK,?0?rows?affected?(0.00?sec) mysql>?select?*?from?ttd; +------+ |?id??| +------+ |??1?| |??2?| +------+ 2?rows?in?set?(0.00?sec) session?2?: mysql>?select?@@session.tx_isolation; +------------------------+ |?@@session.tx_isolation?| +------------------------+ |?REPEATABLE-READ????| +------------------------+ 1?row?in?set?(0.00?sec) mysql>?start?transaction; Query?OK,?0?rows?affected?(0.00?sec) mysql>?insert?into?ttd?values(3); Query?OK,?1?row?affected?(0.00?sec) mysql>?commit; Query?OK,?0?rows?affected?(0.03?sec) session?2?提交后,查看session?1?的結(jié)果; session?1: mysql>?select?*?from?ttd; +------+ |?id??| +------+ |??1?|???????????????????--------和第一次的結(jié)果一樣,REPEATABLE-READ級(jí)別出現(xiàn)了重復(fù)讀 |??2?| +------+ 2?rows?in?set?(0.00?sec) (commit?session?1?之后?再select?*?from?ttd?可以看到session?2?插入的數(shù)據(jù)3)
④ 幻讀:第一個(gè)事務(wù)對(duì)一個(gè)表中的數(shù)據(jù)進(jìn)行了修改，這種修改涉及到表中的全部數(shù)據(jù)行。同時(shí)，第二個(gè)事務(wù)也修改這個(gè)表中的數(shù)據(jù)，這種修改是向表中插入一行新數(shù)據(jù)。那么，以后就會(huì)發(fā)生操作第一個(gè)事務(wù)的用戶發(fā)現(xiàn)表中還有沒有修改的數(shù)據(jù)行，就好象發(fā)生了幻覺一樣。
mysql>CREATE?TABLE?`t_bitfly`?( `id`?bigint(20)?NOT?NULL?default?'0', `value`?varchar(32)?default?NULL, PRIMARY?KEY?(`id`) )?ENGINE=InnoDB mysql>?select?@@global.tx_isolation,?@@tx_isolation; +-----------------------+-----------------+ |?@@global.tx_isolation?|?@@tx_isolation?| +-----------------------+-----------------+ |?REPEATABLE-READ????|?REPEATABLE-READ?| +-----------------------+-----------------+ 實(shí)驗(yàn)一： t?Session?A??????????Session?B | |?START?TRANSACTION;?????START?TRANSACTION; | |?SELECT?*?FROM?t_bitfly; |?empty?set |???????????????INSERT?INTO?t_bitfly |???????????????VALUES?(1,?'a'); | |?SELECT?*?FROM?t_bitfly; |?empty?set |???????????????COMMIT; | |?SELECT?*?FROM?t_bitfly; |?empty?set | |?INSERT?INTO?t_bitfly?VALUES?(1,?'a'); |?ERROR?1062?(23000): |?Duplicate?entry?'1'?for?key?1 v?(shit,?剛剛明明告訴我沒有這條記錄的) 如此就出現(xiàn)了幻讀，以為表里沒有數(shù)據(jù)，其實(shí)數(shù)據(jù)已經(jīng)存在了，傻乎乎的提交后，才發(fā)現(xiàn)數(shù)據(jù)沖突了。 實(shí)驗(yàn)二： t?Session?A?????????Session?B | |?START?TRANSACTION;?????START?TRANSACTION; | |?SELECT?*?FROM?t_bitfly; |?+------+-------+ |?|?id??|?value?| |?+------+-------+ |?|??1?|?a???| |?+------+-------+ |??????????????INSERT?INTO?t_bitfly |??????????????VALUES?(2,?'b'); | |?SELECT?*?FROM?t_bitfly; |?+------+-------+ |?|?id??|?value?| |?+------+-------+ |?|??1?|?a???| |?+------+-------+ |??????????????COMMIT; | |?SELECT?*?FROM?t_bitfly; |?+------+-------+ |?|?id??|?value?| |?+------+-------+ |?|??1?|?a???| |?+------+-------+ | |?UPDATE?t_bitfly?SET?value='z'; |?Rows?matched:?2?Changed:?2?Warnings:?0 |?(怎么多出來一行) | |?SELECT?*?FROM?t_bitfly; |?+------+-------+ |?|?id??|?value?| |?+------+-------+ |?|??1?|?z???| |?|??2?|?z???| |?+------+-------+
本事務(wù)中第一次讀取出一行，做了一次更新后，另一個(gè)事務(wù)里提交的數(shù)據(jù)就出現(xiàn)了。也可以看做是一種幻讀。
當(dāng)隔離級(jí)別是可重復(fù)讀，且禁用innodb_locks_unsafe_for_binlog的情況下，在搜索和掃描index的時(shí)候使用的next-key locks可以避免幻讀。
再看一個(gè)實(shí)驗(yàn)，要注意，表t_bitfly里的id為主鍵字段。
實(shí)驗(yàn)三： t?Session?A?????????Session?B | |?START?TRANSACTION;????START?TRANSACTION; | |?SELECT?*?FROM?t_bitfly |?WHERE?id<p>可以看到，用id</p><pre class="brush:php;toolbar:false">實(shí)驗(yàn)四：一致性讀和提交讀 t?Session?A???????????Session?B | |?START?TRANSACTION;???????START?TRANSACTION; | |?SELECT?*?FROM?t_bitfly; |?+----+-------+ |?|?id?|?value?| |?+----+-------+ |?|?1?|?a???| |?+----+-------+ |????????????????INSERT?INTO?t_bitfly |????????????????VALUES?(2,?'b'); |????????????????COMMIT; | |?SELECT?*?FROM?t_bitfly; |?+----+-------+ |?|?id?|?value?| |?+----+-------+ |?|?1?|?a???| |?+----+-------+ | |?SELECT?*?FROM?t_bitfly?LOCK?IN?SHARE?MODE; |?+----+-------+ |?|?id?|?value?| |?+----+-------+ |?|?1?|?a???| |?|?2?|?b???| |?+----+-------+ | |?SELECT?*?FROM?t_bitfly?FOR?UPDATE; |?+----+-------+ |?|?id?|?value?| |?+----+-------+ |?|?1?|?a???| |?|?2?|?b???| |?+----+-------+ | |?SELECT?*?FROM?t_bitfly; |?+----+-------+ |?|?id?|?value?| |?+----+-------+ |?|?1?|?a???| |?+----+-------+
如果使用普通的讀，會(huì)得到一致性的結(jié)果，如果使用了加鎖的讀，就會(huì)讀到“最新的”“提交”讀的結(jié)果。
本身，可重復(fù)讀和提交讀是矛盾的。在同一個(gè)事務(wù)里，如果保證了可重復(fù)讀，就會(huì)看不到其他事務(wù)的提交，違背了提交讀；如果保證了提交讀，就會(huì)導(dǎo)致前后兩次讀到的結(jié)果不一致，違背了可重復(fù)讀。
可以這么講，InnoDB提供了這樣的機(jī)制，在默認(rèn)的可重復(fù)讀的隔離級(jí)別里，可以使用加鎖讀去查詢最新的數(shù)據(jù)（提交讀）。
MySQL InnoDB的可重復(fù)讀并不保證避免幻讀，需要應(yīng)用使用加鎖讀來保證。而這個(gè)加鎖度使用到的機(jī)制就是next-key locks。
以上就是MySQL 四種事務(wù)隔離級(jí)別詳解及對(duì)比的內(nèi)容，更多相關(guān)內(nèi)容請(qǐng)關(guān)注PHP中文網(wǎng)（www.php.cn）！
.png)
