mysql教程總結(jié)數(shù)據(jù)庫(kù)事務(wù)與 MySQL 事務(wù)

推薦（免費(fèi)）：mysql教程

事務(wù)特點(diǎn)：ACID

從業(yè)務(wù)角度出發(fā)，對(duì)數(shù)據(jù)庫(kù)的一組操作要求保持4個(gè)特征：

• Atomicity（原子性）：一個(gè)事務(wù)必須被視為一個(gè)不可分割的最小工作單元，整個(gè)事務(wù)中的所有操作要么全部提交成功，要么全部失敗回滾，對(duì)于一個(gè)事務(wù)來(lái)說(shuō)，不可能只執(zhí)行其中的一部分操作。
• Consistency（一致性）：數(shù)據(jù)庫(kù)總是從一個(gè)一致性狀態(tài)轉(zhuǎn)換到另一個(gè)一致?tīng)顟B(tài)。下面的銀行列子會(huì)說(shuō)到。
• Isolation（隔離性）：通常來(lái)說(shuō)，一個(gè)事務(wù)所做的修改在最終提交以前，對(duì)其他事務(wù)是不可見(jiàn)的。注意這里的“通常來(lái)說(shuō)”，后面的事務(wù)隔離級(jí)級(jí)別會(huì)說(shuō)到。
• Durability（持久性）：一旦事務(wù)提交，則其所做的修改就會(huì)永久保存到數(shù)據(jù)庫(kù)中。此時(shí)即使系統(tǒng)崩潰，修改的數(shù)據(jù)也不會(huì)丟失。（持久性的安全性與刷新日志級(jí)別也存在一定關(guān)系，不同的級(jí)別對(duì)應(yīng)不同的數(shù)據(jù)安全級(jí)別。）

為了更好地理解ACID，以銀行賬戶轉(zhuǎn)賬為例：

START TRANSACTION;SELECT balance FROM checking WHERE customer_id = 10233276;UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;COMMIT;

• 原子性：要么完全提交（10233276的checking余額減少200，savings 的余額增加200），要么完全回滾（兩個(gè)表的余額都不發(fā)生變化）
• 一致性：這個(gè)例子的一致性體現(xiàn)在 200元不會(huì)因?yàn)閿?shù)據(jù)庫(kù)系統(tǒng)運(yùn)行到第3行之后，第4行之前時(shí)崩潰而不翼而飛，因?yàn)槭聞?wù)還沒(méi)有提交。
• 隔離性：允許在一個(gè)事務(wù)中的操作語(yǔ)句會(huì)與其他事務(wù)的語(yǔ)句隔離開(kāi)，比如事務(wù)A運(yùn)行到第3行之后，第4行之前，此時(shí)事務(wù)B去查詢checking余額時(shí)，它仍然能夠看到在事務(wù)A中被減去的200元（賬戶錢不變），因?yàn)槭聞?wù)A和B是彼此隔離的。在事務(wù)A提交之前，事務(wù)B觀察不到數(shù)據(jù)的改變。
• 持久性：這個(gè)很好理解。
• 事務(wù)的隔離性是通過(guò)鎖、MVCC等實(shí)現(xiàn) （MySQL鎖總結(jié)）
• 事務(wù)的原子性、一致性和持久性則是通過(guò)事務(wù)日志實(shí)現(xiàn)（見(jiàn)下）

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

并發(fā)事務(wù)帶來(lái)的問(wèn)題

• 更新丟失（Lost Update）：當(dāng)兩個(gè)或多個(gè)事務(wù)選擇同一行，然后基于最初選定的值更新該行時(shí)，由于每個(gè)事務(wù)都不知道其他事務(wù)的存在，就會(huì)發(fā)生丟失更新問(wèn)題 －－最后的更新覆蓋了由其他事務(wù)所做的更新。例如，兩個(gè)編輯人員制作了同一 文檔的電子副本。每個(gè)編輯人員獨(dú)立地更改其副本，然后保存更改后的副本，這樣就覆蓋了原始文檔。 最后保存其更改副本的編輯人員覆蓋另一個(gè)編輯人員所做的更改。如果在一個(gè)編輯人員完成并提交事務(wù)之前，另一個(gè)編輯人員不能訪問(wèn)同 一文件，則可避免此問(wèn)題。
• 臟讀（Dirty Reads）：一個(gè)事務(wù)正在對(duì)一條記錄做修改，在這個(gè)事務(wù)完成并提交前， 這條記錄的數(shù)據(jù)就處于不一致?tīng)顟B(tài)； 這時(shí)， 另一個(gè)事務(wù)也來(lái)讀取同一條記錄，如果不加控制，第二個(gè)事務(wù)讀取了這些“臟”數(shù)據(jù)，并據(jù)此做進(jìn)一步的處理，就會(huì)產(chǎn)生未提交的數(shù)據(jù)依賴關(guān)系。這種現(xiàn)象被形象地叫做”臟讀”。
• 不可重復(fù)讀（Non-Repeatable Reads）：一個(gè)事務(wù)在讀取某些數(shù)據(jù)后的某個(gè)時(shí)間，再次讀取以前讀過(guò)的數(shù)據(jù)，卻發(fā)現(xiàn)其讀出的數(shù)據(jù)已經(jīng)發(fā)生了改變、或某些記錄已經(jīng)被刪除了！這種現(xiàn)象就叫做“不可重復(fù)讀” 。
• 幻讀 （Phantom Reads）： 一個(gè)事務(wù)按相同的查詢條件重新讀取以前檢索過(guò)的數(shù)據(jù)，卻發(fā)現(xiàn)其他事務(wù)插入了滿足其查詢條件的新數(shù)據(jù)，這種現(xiàn)象就稱為“幻讀” 。

幻讀和不可重復(fù)讀的區(qū)別：

• 不可重復(fù)讀的重點(diǎn)是修改：在同一事務(wù)中，同樣的條件，第一次讀的數(shù)據(jù)和第二次讀的數(shù)據(jù)不一樣。（因?yàn)橹虚g有其他事務(wù)提交了修改）
• 幻讀的重點(diǎn)在于新增或者刪除：在同一事務(wù)中，同樣的條件,，第一次和第二次讀出來(lái)的記錄數(shù)不一樣。（因?yàn)橹虚g有其他事務(wù)提交了插入/刪除）

并發(fā)事務(wù)處理帶來(lái)的問(wèn)題的解決辦法：

• “更新丟失”通常是應(yīng)該完全避免的。但防止更新丟失，并不能單靠數(shù)據(jù)庫(kù)事務(wù)控制器來(lái)解決，需要應(yīng)用程序?qū)σ碌臄?shù)據(jù)加必要的鎖來(lái)解決，因此，防止更新丟失應(yīng)該是應(yīng)用的責(zé)任。

• “臟讀” 、 “不可重復(fù)讀”和“幻讀” ，其實(shí)都是數(shù)據(jù)庫(kù)讀一致性問(wèn)題，必須由數(shù)據(jù)庫(kù)提供一定的事務(wù)隔離機(jī)制來(lái)解決：

• 一種是加鎖：在讀取數(shù)據(jù)前，對(duì)其加鎖，阻止其他事務(wù)對(duì)數(shù)據(jù)進(jìn)行修改。

• 另一種是數(shù)據(jù)多版本并發(fā)控制（MultiVersion Concurrency Control，簡(jiǎn)稱 MVCC 或 MCC），也稱為多版本數(shù)據(jù)庫(kù)：不用加任何鎖， 通過(guò)一定機(jī)制生成一個(gè)數(shù)據(jù)請(qǐng)求時(shí)間點(diǎn)的一致性數(shù)據(jù)快照 （Snapshot)， 并用這個(gè)快照來(lái)提供一定級(jí)別 （語(yǔ)句級(jí)或事務(wù)級(jí)） 的一致性讀取。從用戶的角度來(lái)看，好象是數(shù)據(jù)庫(kù)可以提供同一數(shù)據(jù)的多個(gè)版本。

SQL標(biāo)準(zhǔn)定義了4類隔離級(jí)別，每一種級(jí)別都規(guī)定了一個(gè)事務(wù)中所做的修改，哪些在事務(wù)內(nèi)和事務(wù)間是可見(jiàn)的，哪些是不可見(jiàn)的。低級(jí)別的隔離級(jí)一般支持更高的并發(fā)處理，并擁有更低的系統(tǒng)開(kāi)銷。

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

• 所有事務(wù)都可以看到其他未提交事務(wù)的執(zhí)行結(jié)果
• 本隔離級(jí)別很少用于實(shí)際應(yīng)用，因?yàn)樗男阅芤膊槐绕渌?jí)別好多少
• 該級(jí)別引發(fā)的問(wèn)題是——臟讀(Dirty Read)：讀取到了未提交的數(shù)據(jù)

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

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

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

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

• 有一個(gè)交叉的事務(wù)有新的commit，導(dǎo)致了數(shù)據(jù)的改變;

• 一個(gè)數(shù)據(jù)庫(kù)被多個(gè)實(shí)例操作時(shí),同一事務(wù)的其他實(shí)例在該實(shí)例處理其間可能會(huì)有新的commit

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

• 這是MySQL的默認(rèn)事務(wù)隔離級(jí)別
• 它確保同一事務(wù)的多個(gè)實(shí)例在并發(fā)讀取數(shù)據(jù)時(shí)，會(huì)看到同樣的數(shù)據(jù)行
• 此級(jí)別可能出現(xiàn)的問(wèn)題——幻讀(Phantom Read)：當(dāng)用戶讀取某一范圍的數(shù)據(jù)行時(shí)，另一個(gè)事務(wù)又在該范圍內(nèi)插入了新行，當(dāng)用戶再讀取該范圍的數(shù)據(jù)行時(shí)，會(huì)發(fā)現(xiàn)有新的“幻影” 行
• InnoDB和Falcon存儲(chǔ)引擎通過(guò)多版本并發(fā)控制(MVCC，Multiversion Concurrency Control)機(jī)制解決幻讀問(wèn)題；InnoDB還通過(guò)間隙鎖解決幻讀問(wèn)題

多版本并發(fā)控制 :

Mysql的大多數(shù)事務(wù)型存儲(chǔ)引擎實(shí)現(xiàn)都不是簡(jiǎn)單的行級(jí)鎖。基于提升并發(fā)性考慮，一般都同時(shí)實(shí)現(xiàn)了多版本并發(fā)控制（MVCC），包括Oracle、PostgreSQL。不過(guò)實(shí)現(xiàn)各不相同。

MVCC的實(shí)現(xiàn)是通過(guò)保存數(shù)據(jù)在某一個(gè)時(shí)間點(diǎn)快照來(lái)實(shí)現(xiàn)的。也就是說(shuō)不管實(shí)現(xiàn)時(shí)間多長(zhǎng)，每個(gè)事物看到的數(shù)據(jù)都是一致的。

分為樂(lè)觀（optimistic）并發(fā)控制和悲觀（pressimistic）并發(fā)控制。

MVCC是如何工作的：

InnoDB的MVCC是通過(guò)在每行記錄后面保存兩個(gè)隱藏的列來(lái)實(shí)現(xiàn)。這兩個(gè)列一個(gè)保存了行的創(chuàng)建時(shí)間，一個(gè)保存行的過(guò)期時(shí)間（刪除時(shí)間）。當(dāng)然存儲(chǔ)的并不是真實(shí)的時(shí)間而是系統(tǒng)版本號(hào)（system version number）。每開(kāi)始一個(gè)新的事務(wù)，系統(tǒng)版本號(hào)都會(huì)自動(dòng)新增。事務(wù)開(kāi)始時(shí)刻的系統(tǒng)版本號(hào)會(huì)作為事務(wù)的版本號(hào)，用來(lái)查詢到每行記錄的版本號(hào)進(jìn)行比較。

REPEATABLE READ（可重讀）隔離級(jí)別下MVCC如何工作：

• SELECT

InnoDB會(huì)根據(jù)以下條件檢查每一行記錄：

1. InnoDB只查找版本早于當(dāng)前事務(wù)版本的數(shù)據(jù)行，這樣可以確保事務(wù)讀取的行要么是在開(kāi)始事務(wù)之前已經(jīng)存在要么是事務(wù)自身插入或者修改過(guò)的
2. 行的刪除版本號(hào)要么未定義，要么大于當(dāng)前事務(wù)版本號(hào)，這樣可以確保事務(wù)讀取到的行在事務(wù)開(kāi)始之前未被刪除

只有符合上述兩個(gè)條件的才會(huì)被查詢出來(lái)

• INSERT

InnoDB為新插入的每一行保存當(dāng)前系統(tǒng)版本號(hào)作為行版本號(hào)

• DELETE

InnoDB為刪除的每一行保存當(dāng)前系統(tǒng)版本號(hào)作為行刪除標(biāo)識(shí)

• UPDATE

InnoDB為插入的一行新紀(jì)錄保存當(dāng)前系統(tǒng)版本號(hào)作為行版本號(hào)，同時(shí)保存當(dāng)前系統(tǒng)版本號(hào)到原來(lái)的行作為刪除標(biāo)識(shí)

保存這兩個(gè)版本號(hào)，使大多數(shù)操作都不用加鎖。使數(shù)據(jù)操作簡(jiǎn)單，性能很好，并且能保證只會(huì)讀取到復(fù)合要求的行。不足之處是每行記錄都需要額外的存儲(chǔ)空間，需要做