MySQL 事務(wù)的四種隔離級(jí)別

1 事務(wù)的基本要素（ACID）

原子性（Atomicity）：事務(wù)開始后所有操作棵里，要么全部做完义黎，要么全部不做禾进，不可能停滯在中間環(huán)節(jié)。事務(wù)執(zhí)行過程中出錯(cuò)廉涕，會(huì)回滾到事務(wù)開始前的狀態(tài)泻云，所有的操作就像沒有發(fā)生一樣。也就是說事務(wù)是一個(gè)不可分割的整體狐蜕，就像化學(xué)中學(xué)過的原子宠纯，是物質(zhì)構(gòu)成的基本單位。

一致性（Consistency）：事務(wù)開始前和結(jié)束后层释，數(shù)據(jù)庫(kù)的完整性約束沒有被破壞 婆瓜。比如A向B轉(zhuǎn)賬，不可能A扣了錢贡羔，B卻沒收到廉白。

隔離性（Isolation）：同一時(shí)間，只允許一個(gè)事務(wù)請(qǐng)求同一數(shù)據(jù)乖寒，不同的事務(wù)之間彼此沒有任何干擾猴蹂。比如A正在從一張銀行卡中取錢，在A取錢的過程結(jié)束前楣嘁，B不能向這張卡轉(zhuǎn)賬磅轻。

持久性（Durability）：事務(wù)完成后，事務(wù)對(duì)數(shù)據(jù)庫(kù)的所有更新將被保存到數(shù)據(jù)庫(kù)逐虚，不能回滾聋溜。

2 事務(wù)的并發(fā)問題

臟讀：事務(wù)A讀取了事務(wù)B更新的數(shù)據(jù)，然后B回滾操作叭爱，那么A讀取到的數(shù)據(jù)是臟數(shù)據(jù)

不可重復(fù)讀：事務(wù) A 多次讀取同一數(shù)據(jù)勤婚，事務(wù) B 在事務(wù)A多次讀取的過程中，對(duì)數(shù)據(jù)作了更新并提交涤伐，導(dǎo)致事務(wù)A多次讀取同一數(shù)據(jù)時(shí)馒胆，結(jié)果 不一致缨称。

幻讀：系統(tǒng)管理員A將數(shù)據(jù)庫(kù)中所有學(xué)生的成績(jī)從具體分?jǐn)?shù)改為ABCDE等級(jí)，但是系統(tǒng)管理員B就在這個(gè)時(shí)候插入了一條具體分?jǐn)?shù)的記錄祝迂，當(dāng)系統(tǒng)管理員A改結(jié)束后發(fā)現(xiàn)還有一條記錄沒有改過來睦尽，就好像發(fā)生了幻覺一樣，這就叫幻讀型雳。

小結(jié)：不可重復(fù)讀的和幻讀很容易混淆当凡，不可重復(fù)讀側(cè)重于修改，幻讀側(cè)重于新增或刪除纠俭。解決不可重復(fù)讀的問題只需鎖住滿足條件的行沿量，解決幻讀需要鎖表

3 事務(wù)的四種隔離級(jí)別

隔離級(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ù)庫(kù)默認(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ì)阻塞

SQL標(biāo)準(zhǔn)定義了4種隔離級(jí)別，包括了一些具體規(guī)則损话，用來限定事務(wù)內(nèi)外的哪些改變是可見的侦啸，哪些是不可見的。

低級(jí)別的隔離級(jí)一般支持更高的并發(fā)處理丧枪，并擁有更低的系統(tǒng)開銷匹中。

按照SQL:1992 事務(wù)隔離級(jí)別，InnoDB默認(rèn)是可重復(fù)讀的（REPEATABLE READ）豪诲。

MySQL/InnoDB 提供SQL標(biāo)準(zhǔn)所描述的所有四個(gè)事務(wù)隔離級(jí)別顶捷。

4 設(shè)置默認(rèn)隔離級(jí)別

4.1 查詢?nèi)趾蜁?huì)話事務(wù)隔離級(jí)別：

SELECT @@global.tx_isolation;

SELECT @@session.tx_isolation;

SELECT @@tx_isolation;

4.2 啟動(dòng)時(shí)指定隔離級(jí)別（臨時(shí)生效）

在命令行中啟動(dòng)mysql服務(wù)時(shí)用--transaction-isolation {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE} 選項(xiàng)指定隔離級(jí)別。

4.3 配置文件添加（每次重啟時(shí)生效）

在配置my.cnf文件的[mysqld]節(jié)里添加如下設(shè)置：

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}

4.4 客戶端命令行

用戶可以用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í)別缺厉。

5 第1級(jí)別：Read Uncommitted(讀取未提交內(nèi)容)

(1)所有事務(wù)都可以看到其他未提交事務(wù)的執(zhí)行結(jié)果

(2)本隔離級(jí)別很少用于實(shí)際應(yīng)用，因?yàn)樗男阅芤膊槐绕渌?jí)別好多少

(3)該級(jí)別引發(fā)的問題是——臟讀(Dirty Read)：讀取到了未提交的數(shù)據(jù)

#首先，修改隔離級(jí)別set tx_isolation='READ-UNCOMMITTED';select @@tx_isolation;+------------------+| @@tx_isolation? |+------------------+| READ-UNCOMMITTED |+------------------+#事務(wù)A：?jiǎn)?dòng)一個(gè)事務(wù)start transaction;select * from tx;+------+------+| id? | num? |+------+------+|? ? 1 |? ? 1 ||? ? 2 |? ? 2 ||? ? 3 |? ? 3 |+------+------+#事務(wù)B：也啟動(dòng)一個(gè)事務(wù)(那么兩個(gè)事務(wù)交叉了)在事務(wù)B中執(zhí)行更新語句提针，且不提交start transaction;update tx set num=10 where id=1;select * from tx;+------+------+| id? | num? |+------+------+|? ? 1 |? 10 ||? ? 2 |? ? 2 ||? ? 3 |? ? 3 |+------+------+#事務(wù)A：那么這時(shí)候事務(wù)A能看到這個(gè)更新了的數(shù)據(jù)嗎?select * from tx;+------+------+| id? | num? |+------+------+|? ? 1 |? 10 |? ? --->可以看到命爬！說明我們讀到了事務(wù)B還沒有提交的數(shù)據(jù)|? ? 2 |? ? 2 ||? ? 3 |? ? 3 |+------+------+#事務(wù)B：事務(wù)B回滾,仍然未提交rollback;select * from tx;+------+------+| id? | num? |+------+------+|? ? 1 |? ? 1 ||? ? 2 |? ? 2 ||? ? 3 |? ? 3 |+------+------+#事務(wù)A：在事務(wù)A里面看到的也是B沒有提交的數(shù)據(jù)select * from tx;+------+------+| id? | num? |+------+------+|? ? 1 |? ? 1 |? ? --->臟讀意味著我在這個(gè)事務(wù)中(A中)，事務(wù)B雖然沒有提交辐脖，但它任何一條數(shù)據(jù)變化饲宛，我都可以看到！|? ? 2 |? ? 2 ||? ? 3 |? ? 3 |+------+------+

6 第2級(jí)別：Read Committed(讀取提交內(nèi)容)

(1)這是大多數(shù)數(shù)據(jù)庫(kù)系統(tǒng)的默認(rèn)隔離級(jí)別（但不是MySQL默認(rèn)的）

(2)它滿足了隔離的簡(jiǎn)單定義：一個(gè)事務(wù)只能看見已經(jīng)提交事務(wù)所做的改變

(3)這種隔離級(jí)別出現(xiàn)的問題是——不可重復(fù)讀(Nonrepeatable Read)：不可重復(fù)讀意味著我們?cè)谕粋€(gè)事務(wù)中執(zhí)行完全相同的select語句時(shí)可能看到不一樣的結(jié)果嗜价。

|——>導(dǎo)致這種情況的原因可能有：(1)有一個(gè)交叉的事務(wù)有新的commit艇抠，導(dǎo)致了數(shù)據(jù)的改變;(2)一個(gè)數(shù)據(jù)庫(kù)被多個(gè)實(shí)例操作時(shí),同一事務(wù)的其他實(shí)例在該實(shí)例處理其間可能會(huì)有新的commit

#首先修改隔離級(jí)別set tx_isolation='read-committed';select @@tx_isolation;+----------------+| @@tx_isolation |+----------------+| READ-COMMITTED |+----------------+#事務(wù)A：?jiǎn)?dòng)一個(gè)事務(wù)start transaction;select * from tx;+------+------+| id? | num? |+------+------+|? ? 1 |? ? 1 ||? ? 2 |? ? 2 ||? ? 3 |? ? 3 |+------+------+#事務(wù)B：也啟動(dòng)一個(gè)事務(wù)(那么兩個(gè)事務(wù)交叉了)在這事務(wù)中更新數(shù)據(jù)，且未提交start transaction;update tx set num=10 where id=1;select * from tx;+------+------+| id? | num? |+------+------+|? ? 1 |? 10 ||? ? 2 |? ? 2 ||? ? 3 |? ? 3 |+------+------+#事務(wù)A：這個(gè)時(shí)候我們?cè)谑聞?wù)A中能看到數(shù)據(jù)的變化嗎?select * from tx; --------------->+------+------+? ? ? ? ? ? ? ? || id? | num? |? ? ? ? ? ? ? ? |+------+------+? ? ? ? ? ? ? ? ||? ? 1 |? ? 1 |--->并不能看到久锥！ ||? ? 2 |? ? 2 |? ? ? ? ? ? ? ? ||? ? 3 |? ? 3 |? ? ? ? ? ? ? ? |+------+------+? ? ? ? ? ? ? ? |——>相同的select語句家淤，結(jié)果卻不一樣? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |#事務(wù)B：如果提交了事務(wù)B呢?? ? ? ? ? ? |commit;? ? ? ? ? ? ? ? ? ? ? ? |? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |#事務(wù)A:? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |select * from tx; --------------->+------+------+| id? | num? |+------+------+|? ? 1 |? 10 |--->因?yàn)槭聞?wù)B已經(jīng)提交了，所以在A中我們看到了數(shù)據(jù)變化|? ? 2 |? ? 2 ||? ? 3 |? ? 3 |+------+------+

7 第3級(jí)別：Repeatable Read(可重讀)

(1)這是MySQL的默認(rèn)事務(wù)隔離級(jí)別?APP開發(fā)找上海捌躍網(wǎng)絡(luò)科技有限公司

(2)它確保同一事務(wù)的多個(gè)實(shí)例在并發(fā)讀取數(shù)據(jù)時(shí)瑟由，會(huì)看到同樣的數(shù)據(jù)行

(3)此級(jí)別可能出現(xiàn)的問題——幻讀(Phantom Read)：當(dāng)用戶讀取某一范圍的數(shù)據(jù)行時(shí)絮重，另一個(gè)事務(wù)又在該范圍內(nèi)插入了新行，當(dāng)用戶再讀取該范圍的數(shù)據(jù)行時(shí)错妖，會(huì)發(fā)現(xiàn)有新的“幻影” 行

(4)InnoDB和Falcon存儲(chǔ)引擎通過多版本并發(fā)控制(MVCC，Multiversion Concurrency Control)機(jī)制解決了該問題

#首先疚沐，更改隔離級(jí)別set tx_isolation='repeatable-read';select @@tx_isolation;+-----------------+| @@tx_isolation? |+-----------------+| REPEATABLE-READ |+-----------------+#事務(wù)A：?jiǎn)?dòng)一個(gè)事務(wù)start transaction;select * from tx;+------+------+| id? | num? |+------+------+|? ? 1 |? ? 1 ||? ? 2 |? ? 2 ||? ? 3 |? ? 3 |+------+------+#事務(wù)B：開啟一個(gè)新事務(wù)(那么這兩個(gè)事務(wù)交叉了)在事務(wù)B中更新數(shù)據(jù)暂氯，并提交start transaction;update tx set num=10 where id=1;select * from tx;+------+------+| id? | num? |+------+------+|? ? 1 |? 10 ||? ? 2 |? ? 2 ||? ? 3 |? ? 3 |+------+------+commit;#事務(wù)A：這時(shí)候即使事務(wù)B已經(jīng)提交了,但A能不能看到數(shù)據(jù)變化？select * from tx;+------+------+| id? | num? |+------+------+|? ? 1 |? ? 1 | --->還是看不到的亮蛔！(這個(gè)級(jí)別2不一樣痴施，也說明級(jí)別3解決了不可重復(fù)讀問題)|? ? 2 |? ? 2 ||? ? 3 |? ? 3 |+------+------+#事務(wù)A：只有當(dāng)事務(wù)A也提交了，它才能夠看到數(shù)據(jù)變化commit;select * from tx;+------+------+| id? | num? |+------+------+|? ? 1 |? 10 ||? ? 2 |? ? 2 ||? ? 3 |? ? 3 |+------+------+

8 第4級(jí)別：Serializable(可串行化)

(1)這是最高的隔離級(jí)別究流；

(2)它通過強(qiáng)制事務(wù)排序辣吃，使之不可能相互沖突，從而解決幻讀問題芬探。簡(jiǎn)言之,它是在每個(gè)讀的數(shù)據(jù)行上加上共享鎖神得；

(3)在這個(gè)級(jí)別，可能導(dǎo)致大量的超時(shí)現(xiàn)象和鎖競(jìng)爭(zhēng)偷仿。

#首先修改隔離界別set tx_isolation='serializable';select @@tx_isolation;+----------------+| @@tx_isolation |+----------------+| SERIALIZABLE? |+----------------+#事務(wù)A：開啟一個(gè)新事務(wù)start transaction;#事務(wù)B：在A沒有commit之前哩簿，這個(gè)交叉事務(wù)是不能更改數(shù)據(jù)的start transaction;insert tx values('4','4');ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionupdate tx set num=10 where id=1;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

轉(zhuǎn)自：http://blog.51cto.com/moerjinrong/2314867