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 存儲引擎