1、MySQL事務(wù)隔離級(jí)別詳解SQL標(biāo)準(zhǔn)定義了 4類隔離級(jí)別，包括了一些具體規(guī)則，用來限定事務(wù)內(nèi)外 的哪些改變是可見的，哪些是不可見的。低級(jí)別的隔離級(jí)一般支持更高的 并發(fā)處理，并擁有更低的系統(tǒng)開銷。Read Un committed （讀取未提交內(nèi)容）在該隔離級(jí)別，所有事務(wù)都可以看到其他未提交事務(wù)的執(zhí)行結(jié)果。本 隔離級(jí)別很少用于實(shí)際應(yīng)用，因?yàn)樗男阅芤膊槐绕渌?jí)別好多少。讀取 未提交的數(shù)據(jù)，也被稱之為臟讀（Dirty Read ）。Read Committed （讀取提交內(nèi)容）這是大多數(shù)數(shù)據(jù)庫系統(tǒng)的默認(rèn)隔離級(jí)別（但不是MySQ默認(rèn)的）。它滿足了隔離的簡單定義：一個(gè)事務(wù)只能看見已經(jīng)提交事務(wù)所做的改變

2、。這 種隔離級(jí)另也支持所謂的不可重復(fù)讀（Non repeatable Read），因?yàn)橥?事務(wù)的其他實(shí)例在該實(shí)例處理其間可能會(huì)有新的commit,所以同一 select可能返回不同結(jié)果。Repeatable Read （可重讀）這是MySQL勺默認(rèn)事務(wù)隔離級(jí)別，它確保同一事務(wù)的多個(gè)實(shí)例在并發(fā) 讀取數(shù)據(jù)時(shí)，會(huì)看到同樣的數(shù)據(jù)行。不過理論上，這會(huì)導(dǎo)致另一個(gè)棘手的 問題：幻讀 （PhantomRead）。簡單的說，幻讀指當(dāng)用戶讀取某一范圍的 數(shù)據(jù)行時(shí)，另一個(gè)事務(wù)又在該范圍內(nèi)插入了新行，當(dāng)用戶再讀取該范圍的數(shù)據(jù)行時(shí)， 會(huì)發(fā)現(xiàn)有新的“幻影” 行。 InnoDB 和 Falcon 存儲(chǔ)引擎通過多版本并發(fā)控

3、制( MVC，C Multiversion Concurrency Control)機(jī)制解決了該問題。Serializable (可串行化) 這是最高的隔離級(jí)別，它通過強(qiáng)制事務(wù)排序，使之不可能相互沖突， 從而解決幻讀問題。簡言之，它是在每個(gè)讀的數(shù)據(jù)行上加上共享鎖。在這 個(gè)級(jí)別，可能導(dǎo)致大量的超時(shí)現(xiàn)象和鎖競(jìng)爭(zhēng)。這四種隔離級(jí)別采取不同的鎖類型來實(shí)現(xiàn)，若讀取的是同一個(gè)數(shù)據(jù)的 話，就容易發(fā)生問題。例如：臟讀(Drity Read):某個(gè)事務(wù)已更新一份數(shù)據(jù)，另一個(gè)事務(wù)在此時(shí)讀 取了同一份數(shù)據(jù)， 由于某些原因， 前一個(gè) RollBack 了操作， 則后一個(gè)事務(wù) 所讀取的數(shù)據(jù)就會(huì)是不正確的。不可重復(fù)讀 (N

4、on-repeatable read): 在一個(gè)事務(wù)的兩次查詢之中數(shù)據(jù) 不一致，這可能是兩次查詢過程中間插入了一個(gè)事務(wù)更新的原有的數(shù)據(jù)?；米x(Phantom Read):在一個(gè)事務(wù)的兩次查詢中數(shù)據(jù)筆數(shù)不一致，例如有一個(gè)事務(wù)查詢了幾列(Row)數(shù)據(jù)，而另一個(gè)事務(wù)卻在此時(shí)插入了新的幾列數(shù)據(jù)，先前的事務(wù)在接下來的查詢中，就會(huì)發(fā)現(xiàn)有幾列數(shù)據(jù)是它先前所沒有的。在MySQ中，實(shí)現(xiàn)了這四種隔離級(jí)別，分別有可能產(chǎn)生問題如下所示:隔離級(jí)別臟讀不可重復(fù)讀幻讀讀未提交（R巳ad uncommitted）VFvV讀已提交（R巳日dcommitted）XVV可重復(fù)讀(Repemtmble remci)XXV可串行化(S

5、erializable)XXX下面，將利用MySQ的客戶端程序，分別測(cè)試幾種隔離級(jí)別。測(cè)試數(shù)據(jù)庫 為test，表為test ;表結(jié)構(gòu)：idintnumintCmd line:mysql - uroot - pUse test;SELECT tx_isolatio n;set sessi on tran sacti on isolati on level read un committed;set autocommit=off;start transaction;兩個(gè)命令行客戶端分別為 A, B;不斷改變A的隔離級(jí)別，在B端修改數(shù)據(jù)。（一）、將A的隔離級(jí)別設(shè)置為read uncommitted（

6、未提交讀）set session transaction isolation level read uncommitted;在 B 未更新數(shù)據(jù)之前：客戶端 A：卩:xdeuelope Xtoo Is jauaMiiysqlM1ySQL5Xbin nysql -urootIJelcome to the MvSQL mon itoj?. Commands end with ； or .Your HySQL connect ion id is 16 to seruer uers ion: 5 _B.22community-ntType Jhelp；* or 1 iJ For* lielp. Type

7、 c to clear the tjuffei.mvs(ll use test ；Database changedmysql SELECT G(?tx_isoltion ；+! Pt x_is alat io n i! REPEAT口BLEREAD :leve丄 read, unconmitted；1 row in set tnysiql set sess ion transact ion isolation Query OK, 0 rous affected se lect P(?tx-isolation ; * Petx_isolation 1 fiEfiD-UNCOMMITTED1 ro

8、w in set 0.00 sec mysql set aut ocomiTiit=of ；Querv OK, 0 rows afFected mysql start transact ion；Query OK” rows affected mysiql se lect * f ront test ； Enptv set mysql select * front 七est；I _ i ! id ! name *B更新數(shù)據(jù):客戶端B:mysql use test: Database changed mysql select (?0tx_isolat ion :! (?etx_isolation

9、!*! REPEATABLE-READ !十1 row in set nysql set session transaction isolation level read unconnitted；Quer OK, 0 rows affected nysql set autocomnit=off:Query OK. 0 rows affected mysql start transact ion； Query OK. 0 rows affected |mysql select* from test；! id ! name i3 rows in set update test set nane=/

10、a_new# where id=l: Query OK. 1 rovi affected Rows matched： 1 Changed： 1 Warnings: 0nysql select * fron test:id : name!1 ! a_new：2 ： b!3 ! c3 rows in set nuarrl、will hadmi/s(il mllhack；Qucrjr OK, 0 rows afFected m5P5(il select * from；i+1-idiname!+1:1：a!2!h!:3：c!3rowsinsetmysql客戶端A:mysql select * fFon

11、 test;id11namei! i-+-111aiP211b111311c113 rows in set nastily sc lect * from test ；+ -11idf! name=+1+ -_*-I-111! a_new11112! bV1I13! c113 rows in cet mvql select * from test；+11id11name-+ii i+=+11111ai i1 i211bJi i311ci i3 r-ows in set 經(jīng)過上面的實(shí)驗(yàn)可以得出結(jié)論，事務(wù)B更新了一條記錄，但是沒有提交，此時(shí)事務(wù)A可以查詢出未提交記錄。造成臟讀現(xiàn)象。未提交讀是最低 的

12、隔離級(jí)別。（二）、將客戶端 A的事務(wù)隔離級(jí)別設(shè)置為read committed（已提交讀）set session transaction isolation level read committed;在B未更新數(shù)據(jù)之前:客戶端A：mu 11 in iijp li aiuR i. j.rwi i1壟rows affected setQuersr OK, 0msql set sess ion transact ion iso lation leue 1 iead conmitted ； Quer/ OK. 0 autoconnit =ofF: rous affected select P(?tx_

13、isolation;! P(?tx_isolat ion I+1J REftD-COMMITTED !mysql? select * From test ；id I name3 pous in set ipsqlB更新數(shù)據(jù):客戶端B:nysqlSet： Session tFan&ACtion isolation level I-ead cOmnitted； Quep OK, 0 poms affected mysql set autoconmit=off; Ok, P rows affected nysql se lect P(?tx_iso lat ion ；4-i! PPtx_isolat

14、ion :-+! READCOtimTTED Jl row in setsecstart transaction:Uuery Ok, 0 pows affected nysql update test set nane=,ajiew* uhere id-1;Query OK, 1 rou affected Bous natcJie(1： 1 ChAhged： 1 Uarnings: Smysql select * from test；!1!a_new *:2：b:：3：c:+4F+3 rows in &ut nysql 客戶端A：3 roos in set se lect * f r-on t

15、est ；+- =+i tid11namei i+-= +-+11111ai i11211bi in n311ci i-+-善3 pqw5 in set 客戶端B:3 roos in set mysql conmit；Query OK, 0 rows affected select * from test ；id ! name !rows in setivsq 1 select * from test;+idIname!1 1a_new*2 !b!3 !c:-+rows in set select * fpon test；+-+-1 Vid11n arte4-1n i1i ia_new i2i

16、 ibi1+3i ic3 pous in set mysql conmit：；Querp OK, 0 rows affected CO.05 sec rysql insert into test ualues ；Queri， OH 1 row affected conmit；Que 1*5 OK, 0 pows affected C0R02 sec n5f&ql客戶端A：nysq;lselect w frontest ；! id !fr+1 1 !a_new 11 2 1b:!3 !c!+J rowsin set select * fromtest ；! id !name !1 1 1a_ne

17、w 1:2 !bS!3 !CA14 1dI卜 4#1 ro wsin set set session transaction isolation level pepeatnble pead; Que pt/ OK, B poos affected select (?tx_ioolation ；! REPEfiTfiBLE-RERD Ia. row in set secsec)n)9ql set autoconmit=of F ； Query OK. 0 rows afFected 0.00start transact ion ；Query OK, 0 rows afFected select

18、+* from test:+! id 1 name I1 i a_new2 t b4 rous in set select： * from test:111 :a_new !112 :b:113 :c!114 :d:+ -+.書4rowsin set! id i name+B更新數(shù)據(jù):客戶端B:)ysql set session transaction isolation level repeatable read； OK, 0 rows affected )ysql set autocDnrit=ofF ； jue*y OK, 0 rows affected *5fsql s e lectl

19、at ion EEPEATABLE-REfiDL roi? in set . 00 sec Ji2/Sql start tajnSAC t ion i3ueiy OK, 0 *ou affected jiysqlJ update test set nAne：*bjeu1 uhere id2；QiiEpy OK, 1 1ou affected (0.02 secRows notched: 1 Changed： 1 Warnings: 0tiysql connit；3uery OK, 0 rows affected tiysql inser-t into test ualues ； OK, 1 r

20、ow affected nystjl coRiiit;9uev*y OK, & rows affectcd nysql客戶端A:n)9sql set session transaction isolation level pepeatnble pead; Que pt/ OK. G poos affected mysql select (?tx_isolation ；! C(?tx_isolation! REPEATABLE-RERD :a. row in set m9ql set autoconmit=ofF； Query OK. 0 rows afFected m start transa

21、ct ion ； Query OK, 0 rows afFected select +* from test:+! id 1 name I1 i a_new2 ! b4 rows in set select： * from test:namea_new4 pous in set 客戶端A提交，重新開始事物:4 fows io set C0-09 sec由以上的實(shí)驗(yàn)可以得出結(jié)論，可重復(fù)讀隔離級(jí)別只允許讀取已提交記錄，而且在一個(gè)事務(wù)兩次讀取一個(gè)記錄期間，其他事務(wù)部的更新該記錄。但該事務(wù)不要求與其他 事務(wù)可串行化。例如，當(dāng)一個(gè)事務(wù)可以找到由一個(gè) 已提交事務(wù)更新的記錄，但是可能產(chǎn)生幻讀問題（注意是可能

22、，因?yàn)閿?shù)據(jù)庫 對(duì)隔離級(jí)別的實(shí)現(xiàn)有所差別）。像以上 的實(shí)驗(yàn)，就沒有出現(xiàn)數(shù)據(jù)幻讀的問(四) 、將A的隔離級(jí)別設(shè)置為 可串行化(Serializable) set sessi on tran sacti on isolati on levelSerializableA端打開事務(wù)，B端插入一條記錄事務(wù)A端:set secs ion transact ion isolation level Serializable；OK, 0 tows affected 0.00 sec)set autocammit*oFF；OK, 0 tows affected 0.00 sec)se lect D6tx_is la

23、t ion ；+t P(?tx_iolation ! SERIALIZABLE !+1 FO忖 in sec myEql start transact ion；Query OK, O rov?3 affected fnysql se lect * f rom test ；! id ! name !1 ! _new I2 i b_new !3 : cI4 : d:5 : e:石 rows in set 事務(wù)B端:set sess ion transact ion iso lat ion Leve 1 Sepialisable ； Suery OK, 0 rous affec七Ed 0_00 se

24、c5RywQl select PPtx_isolation；:PPtx_islat io n :SEBIALIZABLEL row in set nysql set AUtocommit =off；9uei*P OK# 0 rows affected start transact Ion ；血cry OK# 0 rows affected ni/sql insert into test values ；因?yàn)榇藭r(shí)事務(wù)A的隔離級(jí)別設(shè)置為serializable ,開始事務(wù)后，并沒有提 交，所以事務(wù)B只能等待。事務(wù)A提交事務(wù):事務(wù)A端my卑丄conmit:Query OK, 0 rows affec

25、ted C0-0R secmuscrlburirv 0Kr H i-bwe afFa-uLcd CB.JHHiq：l ccpimlt;iwk-y :山M i-mi6 tinwwt vxQdhdlt ytmncActinH 河 q：serializable完全鎖定字段，若一個(gè)事務(wù)來查詢同一份數(shù)據(jù)就必須等待，直到前一個(gè)事務(wù)完成并解除鎖定為止 。是完整的隔離級(jí)別，會(huì)鎖定 對(duì)應(yīng)的數(shù)據(jù)表格，因而會(huì)有效率的問題。MySQL InnoDB存儲(chǔ)引擎的事務(wù)隔離級(jí)別我們知道，在關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)中有四個(gè)事務(wù)隔離級(jí)別:*未提交讀(Read Un committed):允許臟讀，也就是可能讀取到其他會(huì)話中未 提交事務(wù)修改

26、的數(shù)據(jù)提交讀(Read Committed):只能讀取到已經(jīng)提交的數(shù)據(jù)。Oracle等多數(shù)數(shù)據(jù)庫默認(rèn)都是該級(jí)別*可重復(fù)讀(Repeated Read)：可重復(fù)讀。在同一個(gè)事務(wù)內(nèi)的查詢都是事務(wù)開始 時(shí)刻一致的，InnoDB默認(rèn)級(jí)別。在SQL標(biāo)準(zhǔn)中，該隔離級(jí)別消除了不可重復(fù) 讀，但是還存在幻象讀*串行讀(Serializable):完全串行化的讀，每次讀都需要獲得表級(jí)共享鎖， 讀寫相互都會(huì)阻塞查看InnoDB系統(tǒng)級(jí)別的事務(wù)隔離級(jí)別：mysql SELECT global.tx_isolatio n;+| global.tx_isolati on |+| REPEATABLE-READ |+1 ro

27、w in set (0.00 sec)查看 InnoDB 會(huì)話級(jí)別的事務(wù)隔離級(jí)別：mysql SELECT tx_isolation;+| tx_isolation |+| REPEATABLE-READ |+1 row in set (0.00 sec)修改事務(wù)隔離級(jí)別：mysql set global transaction isolation level read committed;Query OK, 0 rows affected (0.00 sec)mysql set session transaction isolation level read committed;Query OK, 0 rows affect