標籤：

資料庫事務與MySQL事務總結

02-05

事務特點：ACID

從業務角度出發，對資料庫的一組操作要求保持4個特徵：

  • Atomicity（原子性）：一個事務必須被視為一個不可分割的最小工作單元，整個事務中的所有操作要麼全部提交成功，要麼全部失敗回滾，對於一個事務來說，不可能只執行其中的一部分操作。
  • Consistency（一致性）：資料庫總是從一個一致性狀態轉換到另一個一致狀態。下面的銀行列子會說到。
  • Isolation（隔離性）：通常來說，一個事務所做的修改在最終提交以前，對其他事務是不可見的。注意這裡的「通常來說」，後面的事務隔離級級別會說到。
  • Durability（持久性）：一旦事務提交，則其所做的修改就會永久保存到資料庫中。此時即使系統崩潰，修改的數據也不會丟失。（持久性的安全性與刷新日誌級別也存在一定關係，不同的級別對應不同的數據安全級別。）

為了更好地理解ACID，以銀行賬戶轉賬為例：

START TRANSACTION;nSELECT balance FROM checking WHERE customer_id = 10233276;nUPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;nUPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;nCOMMIT;n

  • 原子性：要麼完全提交（10233276的checking餘額減少200，savings 的餘額增加200），要麼完全回滾（兩個表的餘額都不發生變化）
  • 一致性：這個例子的一致性體現在 200元不會因為資料庫系統運行到第3行之後，第4行之前時崩潰而不翼而飛，因為事務還沒有提交。
  • 隔離性：允許在一個事務中的操作語句會與其他事務的語句隔離開，比如事務A運行到第3行之後，第4行之前，此時事務B去查詢checking餘額時，它仍然能夠看到在事務A中被減去的200元（賬戶錢不變），因為事務A和B是彼此隔離的。在事務A提交之前，事務B觀察不到數據的改變。
  • 持久性：這個很好理解。
  • 事務的隔離性是通過鎖、MVCC等實現 （MySQL鎖總結）
  • 事務的原子性、一致性和持久性則是通過事務日誌實現（見下）

事務的隔離級別

並發事務帶來的問題

  • 更新丟失（Lost Update）：當兩個或多個事務選擇同一行，然後基於最初選定的值更新該行時，由於每個事務都不知道其他事務的存在，就會發生丟失更新問題 －－最後的更新覆蓋了由其他事務所做的更新。例如，兩個編輯人員製作了同一 文檔的電子副本。每個編輯人員獨立地更改其副本，然後保存更改後的副本，這樣就覆蓋了原始文檔。 最後保存其更改副本的編輯人員覆蓋另一個編輯人員所做的更改。如果在一個編輯人員完成並提交事務之前，另一個編輯人員不能訪問同 一文件，則可避免此問題。
  • 臟讀（Dirty Reads）：一個事務正在對一條記錄做修改，在這個事務完成並提交前， 這條記錄的數據就處於不一致狀態； 這時， 另一個事務也來讀取同一條記錄，如果不加控制，第二個事務讀取了這些「臟」數據，並據此做進一步的處理，就會產生未提交的數據依賴關係。這種現象被形象地叫做"臟讀"。
  • 不可重複讀（Non-Repeatable Reads）：一個事務在讀取某些數據後的某個時間，再次讀取以前讀過的數據，卻發現其讀出的數據已經發生了改變、或某些記錄已經被刪除了！這種現象就叫做「不可重複讀」 。
  • 幻讀 （Phantom Reads）： 一個事務按相同的查詢條件重新讀取以前檢索過的數據，卻發現其他事務插入了滿足其查詢條件的新數據，這種現象就稱為「幻讀」 。

幻讀和不可重複讀的區別：

  • 不可重複讀的重點是修改：在同一事務中，同樣的條件，第一次讀的數據和第二次讀的數據不一樣。（因為中間有其他事務提交了修改）
  • 幻讀的重點在於新增或者刪除：在同一事務中，同樣的條件,，第一次和第二次讀出來的記錄數不一樣。（因為中間有其他事務提交了插入/刪除）

並發事務處理帶來的問題的解決辦法：

  • 「更新丟失」通常是應該完全避免的。但防止更新丟失，並不能單靠資料庫事務控制器來解決，需要應用程序對要更新的數據加必要的鎖來解決，因此，防止更新丟失應該是應用的責任。
  • 「臟讀」 、 「不可重複讀」和「幻讀」 ，其實都是資料庫讀一致性問題，必須由資料庫提供一定的事務隔離機制來解決：
    • 一種是加鎖：在讀取數據前，對其加鎖，阻止其他事務對數據進行修改。
    • 另一種是數據多版本並發控制（MultiVersion Concurrency Control，簡稱 MVCC 或 MCC），也稱為多版本資料庫：不用加任何鎖， 通過一定機制生成一個數據請求時間點的一致性數據快照 （Snapshot)， 並用這個快照來提供一定級別 （語句級或事務級） 的一致性讀取。從用戶的角度來看，好象是資料庫可以提供同一數據的多個版本。

SQL標準定義了4類隔離級別，每一種級別都規定了一個事務中所做的修改，哪些在事務內和事務間是可見的，哪些是不可見的。低級別的隔離級一般支持更高的並發處理，並擁有更低的系統開銷。

第1級別：Read Uncommitted(讀取未提交內容)

  • 所有事務都可以看到其他未提交事務的執行結果
  • 本隔離級別很少用於實際應用，因為它的性能也不比其他級別好多少
  • 該級別引發的問題是——臟讀(Dirty Read)：讀取到了未提交的數據

第2級別：Read Committed(讀取提交內容)

  • 這是大多數資料庫系統的默認隔離級別（但不是MySQL默認的）
  • 它滿足了隔離的簡單定義：一個事務只能看見已經提交事務所做的改變
  • 這種隔離級別出現的問題是——不可重複讀(Nonrepeatable Read)：不可重複讀意味著我們在同一個事務中執行完全相同的select語句時可能看到不一樣的結果。導致這種情況的原因可能有：
    • 有一個交叉的事務有新的commit，導致了數據的改變;
    • 一個資料庫被多個實例操作時,同一事務的其他實例在該實例處理其間可能會有新的commit

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

  • 這是MySQL的默認事務隔離級別
  • 它確保同一事務的多個實例在並發讀取數據時，會看到同樣的數據行
  • 此級別可能出現的問題——幻讀(Phantom Read)：當用戶讀取某一範圍的數據行時，另一個事務又在該範圍內插入了新行，當用戶再讀取該範圍的數據行時，會發現有新的「幻影」 行
  • InnoDB和Falcon存儲引擎通過多版本並發控制(MVCC，Multiversion Concurrency Control)機制解決幻讀問題；InnoDB還通過間隙鎖解決幻讀問題

多版本並發控制 :

Mysql的大多數事務型存儲引擎實現都不是簡單的行級鎖。基於提升並發性考慮，一般都同時實現了多版本並發控制（MVCC），包括Oracle、PostgreSQL。不過實現各不相同。

MVCC的實現是通過保存數據在某一個時間點快照來實現的。也就是說不管實現時間多長，每個事物看到的數據都是一致的。

分為樂觀（optimistic）並發控制和悲觀（pressimistic）並發控制。

MVCC是如何工作的：

InnoDB的MVCC是通過在每行記錄後面保存兩個隱藏的列來實現。這兩個列一個保存了行的創建時間，一個保存行的過期時間（刪除時間）。當然存儲的並不是真實的時間而是系統版本號（system version number）。每開始一個新的事務，系統版本號都會自動新增。事務開始時刻的系統版本號會作為事務的版本號，用來查詢到每行記錄的版本號進行比較。

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

  • SELECT

InnoDB會根據以下條件檢查每一行記錄：

  1. InnoDB只查找版本早於當前事務版本的數據行，這樣可以確保事務讀取的行要麼是在開始事務之前已經存在要麼是事務自身插入或者修改過的
  2. 行的刪除版本號要麼未定義，要麼大於當前事務版本號，這樣可以確保事務讀取到的行在事務開始之前未被刪除

只有符合上述兩個條件的才會被查詢出來

  • INSERT

InnoDB為新插入的每一行保存當前系統版本號作為行版本號

  • DELETE

InnoDB為刪除的每一行保存當前系統版本號作為行刪除標識

  • UPDATE

InnoDB為插入的一行新紀錄保存當前系統版本號作為行版本號，同時保存當前系統版本號到原來的行作為刪除標識

保存這兩個版本號，使大多數操作都不用加鎖。使數據操作簡單，性能很好，並且能保證只會讀取到複合要求的行。不足之處是每行記錄都需要額外的存儲空間，需要做更多的行檢查工作和一些額外的維護工作。

MVCC只在COMMITTED READ（讀提交）和REPEATABLE READ（可重複讀）兩種隔離級別下工作。

可以認為MVCC是行級鎖一個變種，但是他很多情況下避免了加鎖操作，開銷更低。雖然不同資料庫的實現機制有所不同，但大都實現了非阻塞的讀操作（讀不用加鎖，且能避免出現不可重複讀和幻讀），寫操作也只鎖定必要的行（寫必須加鎖，否則不同事務並發寫會導致數據不一致）。

第4級別：Serializable(可串列化)

  • 這是最高的隔離級別
  • 它通過強制事務排序，使之不可能相互衝突，從而解決幻讀問題。簡言之,它是在每個讀的數據行上加上共享鎖。MySQL鎖總結
  • 在這個級別，可能導致大量的超時現象和鎖競爭

隔離級別比較

各具體資料庫並不一定完全實現了上述 4 個隔離級別，例如：

  • Oracle 只提供 Read committed 和 Serializable 兩個標準隔離級別，另外還提供自己定義的 Read only 隔離級別；
  • SQL Server 除支持上述 ISO/ANSI SQL92 定義的 4 個隔離級別外，還支持一個叫做「快照」的隔離級別，但嚴格來說它是一個用 MVCC 實現的 Serializable 隔離級別。
  • MySQL 支持全部 4 個隔離級別，但在具體實現時，有一些特點，比如在一些隔離級別下是採用 MVCC一致性讀，但某些情況下又不是。
    • Mysql可以通過執行 set transaction isolation level命令來設置隔離級別，新的隔離級別會在下一個事務開始的時候生效。 例如：set session transaction isolation level read committed;

事務日誌

事務日誌可以幫助提高事務效率：

  • 使用事務日誌，存儲引擎在修改表的數據時只需要修改其內存拷貝，再把該修改行為記錄到持久在硬碟上的事務日誌中，而不用每次都將修改的數據本身持久到磁碟。
  • 事務日誌採用的是追加的方式，因此寫日誌的操作是磁碟上一小塊區域內的順序I/O，而不像隨機I/O需要在磁碟的多個地方移動磁頭，所以採用事務日誌的方式相對來說要快得多。
  • 事務日誌持久以後，內存中被修改的數據在後台可以慢慢刷回到磁碟。
  • 如果數據的修改已經記錄到事務日誌並持久化，但數據本身沒有寫回到磁碟，此時系統崩潰，存儲引擎在重啟時能夠自動恢復這一部分修改的數據。

目前來說，大多數存儲引擎都是這樣實現的，我們通常稱之為預寫式日誌（Write-Ahead Logging），修改數據需要寫兩次磁碟。

Mysql中的事務實現原理

事務的實現是基於資料庫的存儲引擎。不同的存儲引擎對事務的支持程度不一樣。mysql中支持事務的存儲引擎有innoDB和NDB。

innoDB是mysql默認的存儲引擎，默認的隔離級別是RR（Repeatable Read），並且在RR的隔離級別下更進一步，通過多版本並發控制（MVCC，Multiversion Concurrency Control ）解決不可重複讀問題，加上間隙鎖（也就是並發控制）解決幻讀問題。因此innoDB的RR隔離級別其實實現了串列化級別的效果，而且保留了比較好的並發性能。

事務的隔離性是通過鎖實現，而事務的原子性、一致性和持久性則是通過事務日誌實現。說到事務日誌，不得不說的就是redo和undo。

1.redo log

在innoDB的存儲引擎中，事務日誌通過重做(redo)日誌和innoDB存儲引擎的日誌緩衝(InnoDB Log Buffer)實現。事務開啟時，事務中的操作，都會先寫入存儲引擎的日誌緩衝中，在事務提交之前，這些緩衝的日誌都需要提前刷新到磁碟上持久化，這就是DBA們口中常說的「日誌先行」(Write-Ahead Logging)。當事務提交之後，在Buffer Pool中映射的數據文件才會慢慢刷新到磁碟。此時如果資料庫崩潰或者宕機，那麼當系統重啟進行恢復時，就可以根據redo log中記錄的日誌，把資料庫恢復到崩潰前的一個狀態。未完成的事務，可以繼續提交，也可以選擇回滾，這基於恢復的策略而定。

在系統啟動的時候，就已經為redo log分配了一塊連續的存儲空間,以順序追加的方式記錄Redo Log,通過順序IO來改善性能。所有的事務共享redo log的存儲空間，它們的Redo Log按語句的執行順序，依次交替的記錄在一起。如下一個簡單示例：

記錄1：<trx1, insert...>

記錄2：<trx2, delete...>

記錄3：<trx3, update...>

記錄4：<trx1, update...>

記錄5：<trx3, insert...>

2.undo log

undo log主要為事務的回滾服務。在事務執行的過程中，除了記錄redo log，還會記錄一定量的undo log。undo log記錄了數據在每個操作前的狀態，如果事務執行過程中需要回滾，就可以根據undo log進行回滾操作。單個事務的回滾，只會回滾當前事務做的操作，並不會影響到其他的事務做的操作。

以下是undo+redo事務的簡化過程

假設有2個數值，分別為A和B,值為1，2

1. start transaction;

2. 記錄 A=1 到undo log;

3. update A = 3；

4. 記錄 A=3 到redo log；

5. 記錄 B=2 到undo log；

6. update B = 4；

7. 記錄B = 4 到redo log；

8. 將redo log刷新到磁碟

9. commit

在1-8的任意一步系統宕機，事務未提交，該事務就不會對磁碟上的數據做任何影響。如果在8-9之間宕機，恢復之後可以選擇回滾，也可以選擇繼續完成事務提交，因為此時redo log已經持久化。若在9之後系統宕機，內存映射中變更的數據還來不及刷回磁碟，那麼系統恢復之後，可以根據redo log把數據刷回磁碟。

所以，redo log其實保障的是事務的持久性和一致性，而undo log則保障了事務的原子性。

Mysql中的事務使用

MySQL的服務層不管理事務，而是由下層的存儲引擎實現。比如InnoDB。

MySQL支持本地事務的語句：

START TRANSACTION | BEGIN [WORK] nCOMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] nROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] nSET AUTOCOMMIT = {0 | 1}n

  • START TRANSACTION 或 BEGIN 語句：開始一項新的事務。
  • COMMIT 和 ROLLBACK：用來提交或者回滾事務。
  • CHAIN 和 RELEASE 子句：分別用來定義在事務提交或者回滾之後的操作，CHAIN 會立即啟動一個新事物，並且和剛才的事務具有相同的隔離級別，RELEASE 則會斷開和客戶端的連接。
  • SET AUTOCOMMIT 可以修改當前連接的提交方式， 如果設置了 SET AUTOCOMMIT=0，則設置之後的所有事務都需要通過明確的命令進行提交或者回滾

事務使用注意點：

  • 如果在鎖表期間，用 start transaction 命令開始一個新事務，會造成一個隱含的 unlock

    tables 被執行。
  • 在同一個事務中，最好不使用不同存儲引擎的表，否則 ROLLBACK 時需要對非事

    務類型的表進行特別的處理，因為 COMMIT、ROLLBACK 只能對事務類型的表進行提交和回滾。
  • 和 Oracle 的事務管理相同，所有的 DDL 語句是不能回滾的，並且部分的 DDL 語句會造成隱式的提交。
  • 在事務中可以通過定義 SAVEPOINT（例如：mysql> savepoint test; 定義 savepoint，名稱為 test），指定回滾事務的一個部分，但是不能指定提交事務的一個部分。對於複雜的應用，可以定義多個不同的 SAVEPOINT，滿足不同的條件時，回滾

    不同的 SAVEPOINT。需要注意的是，如果定義了相同名字的 SAVEPOINT，則後面定義的SAVEPOINT 會覆蓋之前的定義。對於不再需要使用的 SAVEPOINT，可以通過 RELEASE SAVEPOINT 命令刪除 SAVEPOINT， 刪除後的 SAVEPOINT， 不能再執行 ROLLBACK TO SAVEPOINT命令。

自動提交（autocommit）：

Mysql默認採用自動提交模式，可以通過設置autocommit變數來啟用或禁用自動提交模式

  • 隱式鎖定

InnoDB在事務執行過程中，使用兩階段鎖協議：

隨時都可以執行鎖定，InnoDB會根據隔離級別在需要的時候自動加鎖；

鎖只有在執行commit或者rollback的時候才會釋放，並且所有的鎖都是在同一時刻被釋放。

  • 顯式鎖定

InnoDB也支持通過特定的語句進行顯示鎖定（存儲引擎層）：

select ... lock in share mode //共享鎖 nselect ... for update //排他鎖 n

MySQL Server層的顯示鎖定：

lock table和unlock tablen

（更多閱讀：MySQL鎖總結）

MySQL對分散式事務的支持

分散式事務的實現方式有很多，既可以採用innoDB提供的原生的事務支持，也可以採用消息隊列來實現分散式事務的最終一致性。這裡我們主要聊一下innoDB對分散式事務的支持。

MySQL 從 5.0.3 開始支持分散式事務，當前分散式事務只支持 InnoDB 存儲引擎。一個分散式事務會涉及多個行動，這些行動本身是事務性的。所有行動都必須一起成功完成，或者一起被回滾。

如圖，mysql的分散式事務模型。模型中分三塊：應用程序（AP）、資源管理器（RM）、事務管理器（TM）:

  • 應用程序：定義了事務的邊界，指定需要做哪些事務；
  • 資源管理器：提供了訪問事務的方法，通常一個資料庫就是一個資源管理器；
  • 事務管理器：協調參與了全局事務中的各個事務。

分散式事務採用兩段式提交（two-phase commit）的方式：

  • 第一階段所有的事務節點開始準備，告訴事務管理器ready。
  • 第二階段事務管理器告訴每個節點是commit還是rollback。如果有一個節點失敗，就需要全局的節點全部rollback，以此保障事務的原子性。

分散式事務（XA 事務）的 SQL 語法主要包括：

XA {START|BEGIN} xid [JOIN|RESUME]n

雖然 MySQL 支持分散式事務，但是在測試過程中，還是發現存在一些問題：

如果分支事務在達到 prepare 狀態時，資料庫異常重新啟動，伺服器重新啟動以後，可以繼續對分支事務進行提交或者回滾得操作，但是提交的事務沒有寫 binlog，存在一定的隱患，可能導致使用 binlog 恢復丟失部分數據。如果存在複製的資料庫，則有可能導致主從資料庫的數據不一致。

如果分支事務在執行到 prepare 狀態時，資料庫異常，且不能再正常啟動，需要使用備份和 binlog 來恢複數據，那麼那些在 prepare 狀態的分支事務因為並沒有記錄到 binlog，所以不能通過 binlog 進行恢復，在資料庫恢復後，將丟失這部分的數據。

如果分支事務的客戶端連接異常中止，那麼資料庫會自動回滾未完成的分支事務，如果此時分支事務已經執行到 prepare 狀態， 那麼這個分散式事務的其他分支可能已經成功提交，如果這個分支回滾，可能導致分散式事務的不完整，丟失部分分支事務的內容。

總之， MySQL 的分散式事務還存在比較嚴重的缺陷， 在資料庫或者應用異常的情況下，

可能會導致分散式事務的不完整。如果應用對於數據的完整性要求不是很高，則可以考慮使

用。如果應用對事務的完整性有比較高的要求，那麼對於當前的版本，則不推薦使用分散式

事務。

參考來源：

《高性能MySQL》

《深入淺出MySQL》

cnblogs.com/maypattis/p cnblogs.com/snsdzjlz320 blog.csdn.net/maybeneve

（本文首發於公眾號：EnjoyMoving）

推薦閱讀：

※MySQL 對於千萬級的大表要怎麼優化？
※雲資料庫MySQL測試的正確姿勢
※【原創】MYSQL 的那些「坑」
※數據管理DMS 全量SQL診斷：你的SQL是健康的藍色，還是危險的紅色？

TAG:MySQL | 数据库 | 事务并发 |