MySQL 事務和鎖

低吟不作語發表於2022-04-29
MySql

事務概述

當多個使用者訪問同一份資料時，一個使用者在更改資料的過程中，可能有其他使用者同時發起更改請求，為保證資料庫記錄的更新從一個一致性狀態變為另外一個一致性狀態，使用事務處理是非常必要的，事務具有以下四個特性：

  1. 原子性（Atomicity）：事務中所有操作視為一個原子單位，即對事務所進行的資料修改等操作只能是完全回滾或完全提交
  2. 一致性（Consistency）：事務在完成時，必須使用所有的資料從一種一致性變更為另一種一致性狀態，所有的變更都必須應用於事務的修改，以確保資料的完整性。事務的一致性由原子性、永續性和隔離性一起實現
  3. 隔離性（Isolation）：一個事務中的操作語句所做的修改必須與其他事務所做的修改相隔離。在進行事務檢視資料時，資料所處的狀態，要麼是被另一個併發事務修改之前的狀態，要麼是被另一併發事務修改之後的狀態，即當前事務不會查詢由另一個併發事務正在修改的資料。隔離性由 MySQL 鎖機制實現
  4. 永續性（Durability）：事務完成之後，所做的修改對資料的影響是永久的，即使系統重啟或者出現系統故障，資料仍可恢復

MySQL 提供了多種事務型儲存引擎，如 InnoDB 和 BDB 等，而 MyISAM 不支援事務。為了支援事務，InnoDB 儲存引擎引入了與事務處理相關的 REDO 日誌和 UNDO 日誌，同時事務依賴於 MySQL 提供的鎖機制

1. REDO 日誌

事務執行時需要將執行的事務日誌寫入日誌檔案，對應的檔案為 REDO 日誌。當每條 SQL 進行資料更新操作時，首先將 REDO 日誌寫進日誌緩衝區。當客戶端執行 COMMIT 命令提交時，日誌緩衝區的內容將被重新整理到磁碟，日誌緩衝區的重新整理方式或者時間間隔可以通過引數 innodb_flush_log_at_trx_commit 控制

REDO 日誌對應磁碟上的 ib_logifleN 檔案，該檔案預設為 5MB，建議設定為 512MB，以便容納較大的事務。MySQL 崩潰恢復時會重新執行 REDO 日誌的記錄，恢復最新資料，保證已提交事務的永續性

2. UNDO 日誌

與 REDO 日誌相反，UNDO 日誌主要用於事務異常時的資料回滾，具體內容就是記錄資料被修改前的資訊到 UNDO 緩衝區，然後在合適的時間將內容重新整理到磁碟

假如由於系統錯誤或者 rollback 操作而導致事務回滾，可以根據 undo 日誌回滾到沒修改前的狀態，保證未提交事務的原子性

與 REDO 日誌不同的是，磁碟上不存在單獨的 UNDO 日誌檔案，所有的 UNDO 日誌均存在表空間對應的 .ibd 資料檔案中，即使 MySQL 服務啟動了獨立表空間


事務控制語句

在 MySQL 中，可以使用 BEGIN 開始事務，使用 COMMIT 結束事務，中間可以使用 ROLLBACK 回滾事務。MySQL 通過 SET AUTOCOMMIT、START TRANSACTION、COMMIT 和 ROLLBACK 等語句支援本地事務

START TRANSACTION | BEGIN [WORK]
COMMIT [WORK]
ROLLBACK [WORK]
SET AUTOCOMMIT = {0 | 1}
  • BEGIN | START TRANSACTION：開始事務
  • COMMIT：結束事務
  • ROLLBACK：回滾事務
  • WORK：SQL 語句
  • SET AUTOCOMMIT：是否自動提交，0 禁止，1 開啟，預設為 1

事務隔離級別

MySQL 定義了四種隔離級別，指定事務中哪些資料改變其他事務可見、哪些資料該表其他事務不可見。低階別的隔離級別可以支援更高的併發處理，同時佔用的系統資源更少

InnoDB 系統級事務隔離級別可以使用以下語句設定：

  • 未提交讀：SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  • 提交讀：SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
  • 可重複讀：SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  • 序列化：SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

檢視系統級事務隔離級別：

SELECT @@global.tx_isolation;

InnoDB 會話級事務隔離級別可以使用以下語句設定：

  • 未提交讀：SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  • 提交讀：SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  • 可重複讀：SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  • 序列化：SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

檢視會話級事務隔離級別：

SELECT @@tx_isolation;

1. 讀未提交

在該隔離級別，所有事務都可以看到其他未提交事務的執行結果。讀取未提交的資料稱為髒讀（Dirty Read），即是：首先開啟 A 和 B 兩個事務，在 B 事務更新但未提交之前，A 事務讀取到了更新後的資料，但由於 B 事務回滾，導致 A 事務出現了髒讀現象

2. 讀已提交

所有事務只能看見已經提交事務所做的改變，此級別可以解決髒讀，但也會導致不可重複讀（Nonrepeatable Read）：首先開啟 A 和 B 兩個事務，A事務讀取了 B 事務的資料，在 B 事務更新並提交後，A 事務又讀取到了更新後的資料，此時就出現了同一 A 事務中的查詢出現了不同的查詢結果

3. 可重複讀

MySQL 預設的事務隔離級別，能確保同一事務的多個例項在併發讀取資料時看到同樣的資料行，理論上會導致一個問題，幻讀（Phontom Read）。例如，第一個事務對一個表中的資料做了修改，這種修改會涉及表中的全部資料行，同時第二個事務也修改這個表中的資料，這次的修改是向表中插入一行新資料，此時就會發生操作第一個事務的使用者發現表中還有沒有修改的資料行

InnoDB 通過多版本併發控制機制（MVCC）解決了該問題：InnoDB 通過為每個資料行增加兩個隱含值的方式來實現，這兩個隱含值記錄了行的建立時間、過期時間以及每一行儲存時間發生時的系統版本號，每個查詢根據事務的版本號來查詢結果

4. 序列化

通過強制事務排序，使其不可能相互衝突，從而解決幻讀問題。簡而言之，就是在每個讀的資料行上加上共享鎖實現，這個級別會導致大量的超時現象和鎖競爭，一般不推薦使用


InnoDB 鎖機制

為了解決資料庫併發控制問題，如走到同一時刻客戶端對同一張表做更新或者查詢操作，需要對併發操作進行控制，因此產生了鎖

1. 鎖的型別

1.1 共享鎖

共享鎖的粒度是行或者元組（多個行），一個事務獲取了共享鎖以後，可以對鎖定範圍內的資料執行讀操作

1.2 排他鎖

排他鎖的粒度與共享鎖相同，一個事務獲取排他鎖以後，可以對鎖定範圍內的資料執行寫操作

有兩個事務 A 和 B，如果事務 A 獲取了一個元組的共享鎖，事務 B 還可以立即獲取這個元組的共享鎖，但不能獲取這個元組的排他鎖，必須等到事務 A 釋放共享鎖之後。如果事務 A 獲取了一個元組的排他鎖，事務 B 不能立即獲取這個元組的共享鎖，也不能立即獲取這個元組的排他鎖，必須等到 A 釋放排他鎖之後

1.3 意向鎖

意向鎖是一種表鎖，鎖定的粒度是整張表，分為意向共享鎖和意向排他鎖。意向共享鎖表示一個事務有意對資料上共享鎖或者排他鎖。有意表示事務想執行操作但還沒真正執行

2. 鎖的粒度

鎖的粒度主要分為表鎖和行鎖

表鎖的開銷最小，同時允許的併發量也是最小。MyISAM 儲存引擎使用該鎖機制。當要寫入資料時，整個表記錄被鎖，此時其他讀/寫動作一律等待。一些特定的動作，如 ALTER TABLE 執行時使用的也是表鎖

行鎖可以支援最大的併發，InnoDB 儲存引擎使用該鎖機制。如果要支援併發讀/寫，建議採用 InnoDB 儲存引擎


相關文章