資料庫事務與 MySQL 事務總結

zhangdeTalk發表於2020-02-23
資料庫MySql

事務特點：ACID

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

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

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

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），要麼完全回滾（兩個表的餘額都不發生變化）
  • 一致性：這個例子的一致性體現在 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] 
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] 
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] 
SET AUTOCOMMIT = {0 | 1}
  • 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 //共享鎖 
select ... for update //排他鎖

MySQL Server層的顯示鎖定：

lock table和unlock table

（更多閱讀：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]

雖然 MySQL 支援分散式事務，但是在測試過程中，還是發現存在一些問題：
如果分支事務在達到 prepare 狀態時，資料庫異常重新啟動，伺服器重新啟動以後，可以繼續對分支事務進行提交或者回滾得操作，但是提交的事務沒有寫 binlog，存在一定的隱患，可能導致使用 binlog 恢復丟失部分資料。如果存在複製的資料庫，則有可能導致主從資料庫的資料不一致。

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

如果分支事務的客戶端連線異常中止，那麼資料庫會自動回滾未完成的分支事務，如果此時分支事務已經執行到 prepare 狀態， 那麼這個分散式事務的其他分支可能已經成功提交，如果這個分支回滾，可能導致分散式事務的不完整，丟失部分分支事務的內容。
總之， MySQL 的分散式事務還存在比較嚴重的缺陷， 在資料庫或者應用異常的情況下，
可能會導致分散式事務的不完整。如果應用對於資料的完整性要求不是很高，則可以考慮使
用。如果應用對事務的完整性有比較高的要求，那麼對於當前的版本，則不推薦使用分散式
事務。

文章轉載於微信公眾號：EnjoyMoving

本作品採用《CC 協議》，轉載必須註明作者和本文連結

阿德

相關文章