MySQL事務處理

陳彥斌發表於2020-06-14
MySql

概述

  • 在MySQL中只有使用了InnoDB資料庫儲存引擎的資料庫或表才支援事務。
  • 事務處理可以用來維護資料庫的完整性，保證成批的SQL語句要麼全部成功，要麼全部失敗。
  • 事務用來管理DDL、DML、DCL操作，比如：insert、update、delete語句，預設是自動提交的。

一般來講，事務是必須滿足4個條件（ACID）

  1. Atomicity(原子性)
  2. Consistency(一致性)
  3. Isolation(隔離性) -->由MVCC的鎖機制來實現的
    1. MVCC：優化讀寫效能(讀不加鎖，讀寫不衝突。不能優化讀讀和寫寫這種情況)
  4. Durabolity(永續性)

對於ACID的解釋如下：

  1. 原子性：構成事務的所有操作必須是一個邏輯單元，要麼全部成功，要麼全部失敗。
  2. 一致性：資料庫再事務執行前後狀態都必須是確定的或者是一致的。
  3. 隔離性：事務之間不會相互影響。

事務支援

　　在MySQL命令列的預設設定下，事務都是自動提交的，既執行SQL語句後就會馬上執行COMMIT操作，因此要顯式地開啟一個事務必須使用命令BEGIN或START TRANSACTION，或者執行命令 SET AUTOCOMMIT=0，用來禁止使用當前會話的自動提交。

常見的操作

  • BEGIN或START TRANSACTION：顯式地開啟一個事務；
  • COMMIT也可以使用COMMIT WORK，不過二者是等價的，COMMIT會提交事務，並使已對資料庫進行的所有修改成為永久性的
  • ROLLBACK也可以使用ROLLBACK WORK，不過二者是等價的，回滾回話結束使用者的事務，並撤銷正在進行的所有未提交的修改。

事務併發問題

在事務的併發操作中可能會出現一些問題：

  • 丟失更新：一個事務更新之後，另一個事務也更新了，但是第二個事務回滾了，則第一個事務也被回滾了
  • 髒讀：一個事務讀取到另一個事務未提交的資料
  • 不可重複讀：一個事務因讀取到另一個事務已提交的資料，導致對同一條記錄讀取兩次以上的結果不一致，update操作。
  • 幻讀：一個事務因讀取到另一個事務已提交的資料。導致對同一張表讀取兩次以上的結果不一致，insert、delete操作

事務隔離級別

　　為了避免上面出現的幾種情況，在MySQL規範中，定義了4個事務隔離級別，不同隔離級別對事物的處理不同

由低到高：

  1. Read uncommitted(讀未提交)：最低階別，任何情況都無法保證。
  2. Read committed(讀已提交)：可避免髒讀的發生。
  3. Repeatable read(可重複讀)：可避免髒讀，不可重複讀的發生。
  4. Serializable(序列化)：可避免髒讀，不可重複讀，幻讀的發生。

預設隔離級別

　　大多資料庫的預設隔離級別是Read committed

檢視隔離級別

select @@tx_isolation

 

修改事務隔離級別

set [ global | session ] transaction isolation level Read uncommitted | Read committed | Repeatable | Serializable;

注意事項

　　隔離級別越高，越能保證資料的完整性和一致性，但是對併發效能的影響也越大。

　　對於多數應用程式，可以優先考慮把資料庫系統的隔離級別設定為Read Committed。他能夠避免髒讀，而且具有較好的併發效能，儘管他會導致不可重複度、幻讀這些併發問題，在可能出現這類問題的個別場合，可以由應用程式採用悲觀鎖或樂觀鎖來控制。

相關文章