【MySQL】MySQL 事務和鎖詳解

一、MySQL 事務

1.1 事務介紹

  • 在 MySQL 中，事務（Transaction） 是一組不可分割的 SQL 操作序列，這些操作要么全部成功執行，要么全部失敗回滾，以此保證數據庫操作的完整性和一致性。

  • 事務將數據庫從一種一致性狀態轉換為另一種一致性狀態，保證系統始終處于一個完整且正確的狀態；

在這里插入圖片描述

1.1.1 事務執行流程

假設用戶 A（賬戶余額 1000 元）要向用戶 B（賬戶余額 2000 元）轉賬 500 元，這個過程需要執行兩步操作：

  1. 從 A 的賬戶扣減 500 元（UPDATE account SET balance = balance - 500 WHERE id = 'A'）；
  2. 向 B 的賬戶增加 500 元（UPDATE account SET balance = balance + 500 WHERE id = 'B'）。

這兩步操作必須同時成功或同時失敗，否則會導致數據不一致（比如 A 扣了錢但 B 沒收到，或 A 沒扣錢但 B 多了錢）。此時，事務就是保證這一點的核心機制。

1. 開啟事務
BEGIN;  -- 或 START TRANSACTION，標記事務開始
2. 執行操作
-- 第一步：A賬戶扣500元
UPDATE account SET balance = balance - 500 WHERE id = 'A';
-- 第二步：B賬戶加500元
UPDATE account SET balance = balance + 500 WHERE id = 'B';
3. 提交事務（操作成功時）

如果兩步更新都沒有問題，執行COMMIT確認修改：

COMMIT;

此時，A 的余額變為 500 元，B 的余額變為 2500 元，修改永久生效（符合持久性）。

4. 回滾事務（操作失敗時）

假設第一步執行成功（A 扣了 500 元），但第二步因網絡故障或 SQL 錯誤未能執行（B 的余額未變），此時需執行ROLLBACK撤銷所有操作：

ROLLBACK;

A 的余額會恢復到 1000 元，B 的余額仍為 2000 元

1.2.1 事務的特征（ACID）

事務必須滿足以下四個基本特性，簡稱 ACID：

原子性（Atomicity）

事務中包含的所有操作（如插入、更新、刪除等）是一個不可分割的整體，要么全部成功執行并提交，要么一旦發生錯誤就全部撤銷（回滾），不會出現 “部分執行” 的中間狀態

例如：

  • 事務的 “原子性” 類似生活中 “要么全做，要么全不做” 的場景。例如：銀行轉賬時，“A 賬戶扣除 100 元” 和 “B 賬戶增加 100 元” 這兩個操作必須同時成功 —— 若 A 扣錢后 B 加錢失敗，整個事務需回滾（A 的錢恢復，B 的錢不變），避免出現 “錢憑空消失” 的錯誤。

實現機制：

  • 通過undolog（回滾日志） 實現。undolog 記錄事務中每一步操作的 “逆運算”（例如，插入操作的逆是刪除，更新操作的逆是恢復原值）。當事務需要回滾時，數據庫會 “回放” 這些逆運算，將數據恢復到事務開始前的狀態。
一致性（Consistency）

事務的執行必須使數據庫從一個一致性狀態轉變為另一個一致性狀態，且始終滿足數據庫的完整性約束（如主鍵唯一、外鍵關聯、字段非空等）

例如：

  • 若表中 “用戶名” 字段設為唯一鍵，事務中修改用戶名時，提交后必須保證新用戶名不重復；若修改后出現重復，事務必須回滾，否則破壞一致性。

  • 轉賬場景中，A 和 B 的總余額在事務前后必須相等（A 減少 100，B 增加 100，總余額不變），這就是一種一致性約束。

隔離性（Isolation）

當多個事務同時操作數據庫時，每個事務的操作應與其他事務 “隔離”，互不干擾。隔離性通過定義不同的隔離級別，控制并發事務對同一數據的訪問行為，解決臟讀、不可重復讀、幻讀等問題

  1. 并發問題：

    • 臟讀：事務 A 讀取到事務 B 未提交的修改（若 B 回滾，A 讀取的數據是 “無效” 的）。
    • 不可重復讀：事務 A 多次讀取同一數據時，事務 B 修改并提交了該數據，導致 A 兩次讀取結果不一致。
    • 幻讀：事務 A 按條件查詢數據時，事務 B 插入了符合條件的新數據，導致 A 再次查詢時多了 “不存在” 的記錄。

  2. 隔離級別（MySQL 默認是 “可重復讀”）：

    • 讀未提交（Read Uncommitted）：最低級別，允許讀取未提交的事務數據，可能出現臟讀、不可重復讀、幻讀。
    • 讀已提交（Read Committed）：只能讀取已提交的事務數據，避免臟讀，但仍可能出現不可重復讀、幻讀。
    • 可重復讀（Repeatable Read）：保證同一事務內多次讀取數據結果一致，避免臟讀、不可重復讀，MySQL 通過 MVCC 避免幻讀（大部分場景）。
    • 串行化（Serializable）：最高級別，事務串行執行（不并發），完全避免所有并發問題，但性能極低。

  3. 實現機制：

    • MVCC（多版本并發控制）：通過為數據記錄保存多個版本，實現 “非鎖定讀”。每個事務看到的數據版本由其開始時間決定，避免了讀操作對寫操作的阻塞，提升并發性能。
    • 鎖機制：處理并發寫操作（如同時更新同一行）。MySQL 支持多種粒度的鎖：
      • 表鎖：對整個表加鎖（如 ALTER TABLE 操作），粒度大，并發低。
      • 頁鎖：對數據頁（B + 樹的葉子節點）加鎖，粒度中等。
      • 行鎖：對單行記錄加鎖（如 InnoDB 的行級鎖），粒度小，并發高。

  4. 事務控制語句：

    • START TRANSACTION / BEGIN：顯式開啟事務。
    • COMMIT：提交事務，將所有修改持久化。
    • ROLLBACK：回滾事務，撤銷所有未提交的修改。
    • SAVEPOINT <標識>：在事務中創建保存點（中間狀態）。
    • ROLLBACK TO SAVEPOINT <標識>：回滾到指定保存點（而非事務開始），適用于部分回滾場景。
    • RELEASE SAVEPOINT <標識>：刪除保存點。

在這里插入圖片描述

設置和查看隔離級別

設置當前會話的隔離級別（僅對當前連接有效）

-- 語法：設置為指定級別
SET TRANSACTION ISOLATION LEVEL [隔離級別];-- 示例：
-- 設置為讀未提交
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;-- 設置為讀已提交
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;-- 設置為可重復讀（默認）
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;-- 設置為串行化
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

設置全局隔離級別（影響所有新會話，不影響當前已存在的會話）

-- 語法：
SET GLOBAL TRANSACTION ISOLATION LEVEL [隔離級別];-- 示例：設置全局隔離級別為讀已提交
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

查看當前會話的隔離級別

-- MySQL 8.0+ 推薦語法
SELECT @@transaction_isolation;-- 兼容舊版本（MySQL 5.7及以下）
SELECT @@tx_isolation;

查看全局隔離級別（新會話的默認隔離級別）

-- MySQL 8.0+ 推薦語法
SELECT @@global.transaction_isolation;-- 兼容舊版本
SELECT @@global.tx_isolation;

首先，我們準備這樣的表，用于下面的事件隔離測試：

在這里插入圖片描述

讀未提交

其中一個客戶端修改了分數，但未提交事務

-- 開啟事務，修改學生1的課程1成績
BEGIN;
UPDATE score_tbl SET score = 90.00 WHERE student_id=1 AND course_id=1;
-- 不執行COMMIT，保持事務未提交

另一個客戶端使用讀未提交的隔離界別，讀到了臟數據

-- 設置隔離級別
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
-- 查詢學生1的課程1成績
SELECT score FROM score_tbl WHERE student_id=1 AND course_id=1; 
-- 結果：90.00（讀取到事務A未提交的修改，出現【臟讀】）

在這里插入圖片描述

讀已提交

客戶端A開啟事務，查詢成績，輸出結果為90

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
-- 第一次查詢學生1的課程1成績
SELECT score FROM score_tbl WHERE student_id=1 AND course_id=1;

在這里插入圖片描述

另一個客戶端B修改了成績，并且提交了事務

BEGIN;
-- 修改學生1的課程1成績并提交
UPDATE score_tbl SET score = 95.00 WHERE student_id=1 AND course_id=1;
COMMIT; -- 提交事務

此時，回到A客戶端，事務還沒有執行結束，我們再查詢一遍分數，變成了95，出現了不可重復讀

SELECT score FROM score_tbl WHERE student_id=1 AND course_id=1;

在這里插入圖片描述

可重復讀

同樣的方式，這次我們設置可重復讀的隔離界別

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
-- 第一次查詢學生1的課程1成績
SELECT score FROM score_tbl WHERE student_id=1 AND course_id=1; 
-- 結果：90

在這里插入圖片描述

即使另一個事務提交了，也不會影響當前事務的讀操作

BEGIN;
-- 修改學生1的課程1成績并提交
UPDATE score_tbl SET score = 95.00 WHERE student_id=1 AND course_id=1;
COMMIT; -- 提交事務

讀取的依然是修改前的值

SELECT score FROM score_tbl WHERE student_id=1 AND course_id=1; 
-- 結果：90

在這里插入圖片描述

事務提交后，再次查詢，已經查詢到了最新的值

SELECT score FROM score_tbl WHERE student_id=1 AND course_id=1;

在這里插入圖片描述

串行化

一個客戶端設置串行化隔離，查詢記錄，此時上鎖了

在這里插入圖片描述

另一個客戶端嘗試插入數據，但是被阻塞了

BEGIN;
INSERT INTO score_tbl (student_id, course_id, score) VALUES (3, 4, 80.00);

在這里插入圖片描述

提交事務后，鎖被釋放，成功插入

COMMIT;

在這里插入圖片描述

不同隔離界別的對比
隔離級別臟讀不可重復讀幻讀測試場景表現（結合 score 表）
讀未提交可能可能可能看到未提交的成績修改（如 90.00）
讀已提交不可能可能可能僅看到已提交的成績修改（兩次查詢結果不同）
可重復讀（默認）不可能不可能不可能事務內成績查詢結果不變，課程記錄數不新增
串行化不可能不可能不可能插入課程 4 記錄時被阻塞，需等待前事務結束
持久性（Durability）

事務一旦提交，其對數據的修改將永久保存，即使發生數據庫宕機、斷電等故障，修改也不會丟失

實現機制：

  • 通過redolog（重做日志） 實現。redolog 是一種物理日志，記錄 “某個數據頁的偏移量上修改了什么數據”（而非具體操作）

  • 事務提交時，數據庫會將修改記錄寫入 redolog 并刷盤（確保寫入磁盤）。即使此時數據尚未寫入數據文件（MySQL 為提升性能會先放內存），若發生宕機，重啟后數據庫可通過 redolog"重放" 操作，恢復提交的修改

1.2 鎖

鎖機制用于管理對共享資源的并發訪問，用來實現事務的隔離級別

在這里插入圖片描述

1.2.1 鎖的粒度

MySQL InnoDB 支持多粒度鎖，即可以對不同層級的資源加鎖，從大到小分為：

  • 表級鎖：對整個表加鎖（如意向鎖、AUTO-INC 鎖）；
  • 頁級鎖：對 B+ 樹的葉子節點頁加鎖（較少直接使用，由引擎自動管理）；
  • 行級鎖：對表中某一行記錄加鎖（如共享鎖、排他鎖）。

粒度越小，并發度越高，但鎖管理開銷越大；粒度越大，并發度越低，開銷越小。

1.2.2 鎖類型

根據作用和范圍，MySQL 鎖可分為以下幾類：

1. 共享鎖（S 鎖，行級）

  • 定義：事務對記錄執行讀操作時加的鎖，允許其他事務加 S 鎖，但阻塞 X 鎖。

  • 觸發時機：

    • SERIALIZABLE 隔離級別下，普通 SELECT 自動加 S 鎖；
    • 其他級別需手動加鎖：SELECT ... LOCK IN SHARE MODE;

  • 釋放時機：事務提交或回滾后釋放。

-- 事務A加S鎖
BEGIN;
SELECT score FROM score_tbl WHERE student_id=1 AND course_id=1 LOCK IN SHARE MODE; 
-- 此時事務A持有該記錄的S鎖-- 事務B嘗試加X鎖（更新操作）會被阻塞
BEGIN;
UPDATE score_tbl SET score=95 WHERE student_id=1 AND course_id=1; -- 阻塞，直到事務A提交
不同隔離級別下的讀操作鎖機制
SERIALIZABLE

  • 讀操作鎖行為：所有讀操作（SELECT）會默認自動加 S 鎖（無需手動指定）。

  • 設計目標：提供最高隔離性，避免臟讀、不可重復讀、幻讀，但犧牲并發性能（本質是 “串行執行事務”）。

  • 底層邏輯：通過 S 鎖強制阻塞修改操作 —— 只要事務 A 對某行加了 S 鎖，事務 B 想修改該行（加 X 鎖）就必須等待事務 A 提交 / 回滾，直到 S 鎖釋放。

  • 事務 A：

BEGIN;
-- 自動加S鎖（SERIALIZABLE級別默認）
SELECT * FROM users WHERE id = 1;
  • 事務 B：
BEGIN;
-- 嘗試加X鎖修改，會被事務A的S鎖阻塞（需等待）
UPDATE users SET name = 'Bob' WHERE id = 1;
  • 結果：事務 B 的 UPDATE 會被阻塞，直到事務 A 執行COMMIT或ROLLBACK釋放 S 鎖后，事務 B 才能繼續
REPEATABLE READ
  • 讀操作鎖行為：默認不加 S 鎖，而是通過MVCC（多版本并發控制） 保證 “可重復讀”（同一事務內多次讀同一行，結果一致）；若需解決幻讀，需手動加 S 鎖（SELECT ... LOCK IN SHARE MODE）或依賴間隙鎖（Gap Lock）。
  • 設計目標：在保證可重復讀的前提下，盡可能提升并發性能（避免默認加鎖導致的阻塞）

（1）默認不加 S 鎖：依賴 MVCC 實現可重復讀
MVCC 的核心是 “讀快照”：事務啟動時生成一個read view（快照），后續讀操作均基于該快照，不受其他事務提交的修改影響。

  • 事務 A：
BEGIN;
-- 不加S鎖，讀快照（此時id=1的name是Alice）
SELECT * FROM users WHERE id = 1; -- 結果：Alice
  • 事務 B：
BEGIN;
UPDATE users SET name = 'Bob' WHERE id = 1;
COMMIT; -- 提交修改
  • 事務 A 再次讀：
SELECT * FROM users WHERE id = 1; -- 結果仍為Alice（MVCC保證可重復讀）
  • 原因：事務 A 的read view在啟動時生成，只可見啟動前已提交的版本，事務 B 的新修改不可見，因此無需加鎖也能保證 “可重復讀”。

（2）手動加 S 鎖：解決幻讀
幻讀指 “同一事務內，兩次讀同一范圍，第二次讀到新插入的行”。RR 級別默認通過間隙鎖（Gap Lock） 防止幻讀，但如果是 “讀操作需要嚴格禁止其他事務插入新行”，可手動加 S 鎖（結合間隙鎖生效）。

  • 事務 A：
BEGIN;
-- 手動加S鎖（同時會對范圍加間隙鎖）
SELECT * FROM users WHERE id BETWEEN 1 AND 10 LOCK IN SHARE MODE;
-- 此時讀到id=1（Alice）
  • 事務 B：
BEGIN;
-- 嘗試插入新行（id=5），會被事務A的間隙鎖+S鎖阻塞
INSERT INTO users (id, name) VALUES (5, 'Charlie');
  • 結果：事務 B 的 INSERT 被阻塞，直到事務 A 提交釋放 S 鎖和間隙鎖，避免了事務 A 再次讀時出現 “幻讀”（讀到 id=5 的新行）。
READ COMMITTED

  • 讀操作鎖行為：不加 S 鎖，完全依賴 MVCC，但與 RR 級別的 MVCC 不同：RC 級別每次讀操作都會生成新的read view（只可見已提交的最新版本）。

  • 設計目標：保證 “讀已提交”（避免臟讀），但不保證可重復讀，通過犧牲部分隔離性換取更高并發（無需加鎖，修改操作阻塞少）。

  • 事務 A：

BEGIN;
-- 不加S鎖，讀當前已提交的快照（id=1的name是Alice）
SELECT * FROM users WHERE id = 1; -- 結果：Alice
  • 事務 B：
BEGIN;
UPDATE users SET name = 'Bob' WHERE id = 1;
COMMIT; -- 提交修改
  • 事務 A 再次讀：
-- 生成新的read view，可見事務B的提交，結果為Bob
SELECT * FROM users WHERE id = 1; -- 結果：Bob
  • 為何無需 S 鎖：RC 級別允許 “不可重復讀”（兩次讀結果可不同），因此不需要通過 S 鎖阻塞修改操作；MVCC 僅保證 “不讀未提交的數據”（避免臟讀），足夠滿足需求。
READ UNCOMMITTED

  • 讀操作鎖行為：既不加 S 鎖，也不使用 MVCC，讀操作直接讀取當前數據頁的最新版本（無論其他事務是否提交）。

  • 設計目標：最低隔離級別，追求極致性能，允許臟讀（讀未提交的數據），幾乎不用于生產環境。

  • 事務 A：

BEGIN;
UPDATE users SET name = 'Bob' WHERE id = 1; -- 未提交，加X鎖
  • 事務 B：
BEGIN;
-- 無鎖無MVCC，直接讀事務A未提交的修改
SELECT * FROM users WHERE id = 1; -- 結果：Bob（臟讀）
  • 風險：若事務 A 執行ROLLBACK，事務 B 讀到的 “Bob” 就是無效數據，可能導致業務邏輯錯誤。
2. 排他鎖（X 鎖，行級）
  • 定義：事務對記錄執行更新 / 刪除操作時加的鎖，完全阻塞其他事務的 S 鎖和 X 鎖。
  • 觸發時機：所有隔離級別下，UPDATE/DELETE 操作自動加 X 鎖；手動加鎖：SELECT ... FOR UPDATE;
  • 釋放時機：事務提交或回滾后釋放。
-- 事務A加X鎖
BEGIN;
UPDATE score_tbl SET score=95 WHERE student_id=1 AND course_id=1; 
-- 此時事務A持有該記錄的X鎖-- 事務B嘗試加S鎖會被阻塞
BEGIN;
SELECT score FROM score_tbl WHERE student_id=1 AND course_id=1 LOCK IN SHARE MODE; -- 阻塞
3. 意向鎖（表級）

意向鎖是表級鎖，用于標識 “某事務計劃對表中某些行加 S 鎖或 X 鎖”，避免表級鎖與行級鎖的沖突。分為：

  • 意向共享鎖（IS）：事務計劃對某些行加 S 鎖前，先對表加 IS 鎖；
  • 意向排他鎖（IX）：事務計劃對某些行加 X 鎖前，先對表加 IX 鎖。

作用：快速判斷表中是否有行級鎖（例如，若表有 IX 鎖，則全表掃描加表鎖的操作會被阻塞）。

事務 A 更新某行時，先對表加 IX 鎖，再對行加 X 鎖：

BEGIN;
-- 自動加IX鎖（表級），再對行加X鎖（行級）
UPDATE score_tbl SET score=95 WHERE student_id=1 AND course_id=1;
4. AUTO-INC 鎖（表級）
  • 定義：針對 AUTO_INCREMENT 自增列的特殊表鎖，保證插入時自增值唯一。
  • 觸發時機：INSERT 操作插入自增列時自動加鎖，插入完成后立即釋放（MySQL 5.1.22+ 優化為輕量級鎖，并發插入性能提升）。
-- 表結構：id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(10)
BEGIN;
INSERT INTO students (name) VALUES ('Alice'); -- 加AUTO-INC鎖，獲取id=1，立即釋放
COMMIT;
不同隔離級別鎖的對比
隔離級別讀操作是否加 S 鎖依賴機制解決的問題未解決的問題性能
SERIALIZABLE是（默認）共享鎖（S 鎖）臟讀、不可重復讀、幻讀無（但串行化）最差
REPEATABLE READ否（默認）MVCC + 間隙鎖臟讀、不可重復讀、幻讀無（InnoDB 中）中等
READ COMMITTED否MVCC（實時快照）臟讀不可重復讀、幻讀較好
READ UNCOMMITTED否無（直接讀最新）無臟讀、不可重復讀、幻讀最好（不推薦）
鎖的兼容性
1. 意向鎖

  • 意向鎖是表級鎖，用于標識 “某事務計劃對表中部分行加 S 鎖或 X 鎖”，是行級鎖的 “預告”。

  • 目的：快速判斷表中是否存在行級鎖，避免全表掃描時的鎖沖突（例如，若表有意向鎖，全表鎖操作需等待意向鎖釋放）。

  • 意向鎖之間互相兼容：IS（意向共享鎖）與 IS、IX（意向排他鎖）之間無沖突；IX 與 IS、IX 之間也無沖突。

  • 意向鎖與行級鎖兼容：意向鎖（表級）和行級鎖（S/X 鎖）互不干擾，僅行級鎖之間會沖突（如 S 鎖與 X 鎖沖突）。

  • 意向鎖與表級鎖的沖突：

    • IS 鎖會阻塞表級 X 鎖（全表寫鎖），但允許表級 S 鎖（全表讀鎖）；
    • IX 鎖會阻塞表級 S 鎖和 X 鎖（全表讀寫鎖均被阻塞）；
    • 意向鎖不阻塞非全表掃描的請求（如通過索引訪問行）。
2. 行級鎖
  • 加 S 鎖（行級讀鎖） 前：先自動為表和所在頁加 IS 鎖，再為目標行加 S 鎖。
  • 加 X 鎖（行級寫鎖） 前：先自動為表和所在頁加 IX 鎖，再為目標行加 X 鎖。
已持有鎖 \ 請求鎖S（共享鎖）X（排他鎖）IS（意向共享鎖）IX（意向排他鎖）AI（AUTO-INC 鎖）
S（共享鎖）兼容沖突兼容沖突沖突
X（排他鎖）沖突沖突沖突沖突沖突
IS（意向共享鎖）兼容沖突兼容兼容兼容
IX（意向排他鎖）沖突沖突兼容兼容兼容
AI（AUTO-INC 鎖）沖突沖突兼容兼容沖突
鎖算法
1. Record Lock（記錄鎖）
  • 定義：鎖定單個行記錄（僅鎖定索引對應的行，不包含范圍）。
  • 適用場景：通過唯一索引（主鍵 / 唯一鍵）精準命中某一行時。

表 score_tbl 主鍵為 (student_id, course_id)，事務 A 更新精準命中的行：

-- 事務A
BEGIN;
UPDATE score_tbl SET score=95 WHERE student_id=1 AND course_id=1; -- 加Record Lock（行級X鎖）
2. Gap Lock（間隙鎖）
  • 定義：鎖定索引范圍內的間隙（不包含記錄本身），防止其他事務在間隙中插入數據（解決幻讀）。
  • 適用場景：REPEATABLE READ 及以上級別，通過非唯一索引或范圍查詢時觸發。
  • 范圍示例：若索引存在值 4、7，則間隙鎖可能鎖定 (4,7) 區間。

表 students 有索引 age，值為 22、24、25。事務 A 鎖定 age=24 的間隙：

-- 事務A（REPEATABLE READ級別）
BEGIN;
-- 查詢age=24，未命中（假設無此記錄），加Gap Lock鎖定(22,25)間隙
SELECT * FROM students WHERE age=24 FOR UPDATE;-- 事務B嘗試在間隙中插入數據會被阻塞
BEGIN;
INSERT INTO students (age) VALUES (23); -- 阻塞，因(22,25)被Gap Lock鎖定
3. Next-Key Lock（記錄鎖 + 間隙鎖）
  • 定義：鎖定索引范圍 + 記錄本身（左開右閉區間），是 InnoDB 默認的行鎖算法。
  • 適用場景：非唯一索引的精準查詢或范圍查詢（REPEATABLE READ 級別）。

表 students 索引 age 有值 22、24、25。事務 A 查詢 age=24（命中）：

-- 事務A
BEGIN;
SELECT * FROM students WHERE age=24 FOR UPDATE; 
-- 加Next-Key Lock，鎖定(22,24]區間（包含24本身的Record Lock + (22,24)的Gap Lock）-- 事務B插入age=23（在(22,24)間隙）會被阻塞；更新age=24也會被阻塞
4. Insert Intention Lock（插入意向鎖）
  • 定義：INSERT 操作時產生的特殊間隙鎖，標識 “事務想在某間隙插入數據”，多個事務插入不沖突的行時不互斥。
  • 適用場景：多事務同時向同一間隙插入不同數據時。

索引 age 有值 4、7，事務 A 插入 5，事務 B 插入 6：

-- 事務A
BEGIN;
INSERT INTO students (age) VALUES (5); -- 加Insert Intention Lock鎖定(4,7)間隙-- 事務B
BEGIN;
INSERT INTO students (age) VALUES (6); -- 同樣加Insert Intention Lock，與A兼容，不阻塞
鎖兼容

鎖之間的兼容性決定了 “一個事務持有某鎖時，其他事務能否請求另一鎖”。橫向為已持有鎖，縱向為請求鎖：

  • 間隙鎖（GAP）與插入意向鎖（Insert Intention）沖突（防止插入間隙數據）；
  • 記錄鎖（Record）與任何請求的 Record/Next-Key 鎖沖突（同一行互斥）。
持有鎖 \ 請求鎖GAPInsert IntentionRecordNext-Key
GAP兼容沖突兼容兼容
Insert Intention兼容兼容兼容兼容
Record兼容兼容沖突沖突
Next-Key兼容沖突沖突沖突
鎖的對象
  • 行級鎖是針對表的索引加鎖
  • 表級鎖是針對頁或表進行加鎖
  • 索引包括聚集索引和輔助索引，重點考慮 InnoDB 在 read committed 和 repeatable read 級別下鎖的情況

假設我們有如下的表，其中 id 為主鍵，no（學號），輔助唯一索引，name（姓名）和 age（年齡）為輔助普通索引，score（學分）無索引

idnonameagescore
15S0001Bob2534
18S0002Alice2477
20S0003Jim245
30S0004Eric2391
37S0005Tom2222
49S0006Tom2583
50S0007Rose2389

聚集索引，查詢命中：

UPDATE students SET score = 100 WHERE id = 15;

在這里插入圖片描述

聚集索引，查詢未命中：

UPDATE students SET score = 100 WHERE id = 16;

在這里插入圖片描述

輔助唯一索引，查詢命中：

UPDATE students SET score = 100 WHERE no = 'S0003';

在這里插入圖片描述

輔助唯一索引，查詢未命中：

UPDATE students SET score = 100 WHERE no = 'S0008';

在這里插入圖片描述

輔助非唯一索引，查詢命中：

UPDATE students SET score = 100 WHERE name = 'Tom';

在這里插入圖片描述

輔助非唯一索引，查詢未命中：

UPDATE students SET score = 100 WHERE name = 'John';

在這里插入圖片描述

無索引：

UPDATE students SET score = 100 WHERE score = 22;

在這里插入圖片描述

聚集索引，范圍查詢：

UPDATE students SET score = 100 WHERE id <= 20;

在這里插入圖片描述

輔助索引，范圍查詢：

UPDATE students SET score = 100 WHERE age <= 23;

在這里插入圖片描述

修改索引值：

UPDATE students SET name = 'John' WHERE id = 15;

在這里插入圖片描述

MVCC

  • MVCC（Multi-Version Concurrency Control，多版本并發控制）是 InnoDB 存儲引擎實現隔離級別的核心機制，它通過保存數據的多個版本，允許讀寫操作不相互阻塞，從而提高數據庫的并發性能。

  • 簡單來說，當多個事務同時讀寫數據庫時，MVCC 會為每個事務提供一個獨立的 “數據快照”，事務操作的是自己快照中的數據，而不是直接修改或讀取最新的物理數據，以此避免鎖競爭并實現不同的隔離級別。

  • 在 read committed 和 repeatable read 下，innodb 使用MVCC

  • 然后對于快照數據的定義不同，在 read committed 隔離級別下，對于快照數據總是讀取被鎖定行的最新一份快照數據；

  • 而在 repeatable read 隔離級別下，對于快照數據總是讀取事務開始時的行數據版本；

在這里插入圖片描述

首先我們要了解，聚集索引當中是存在隱藏列的，他們分別是：

  • trx_id：事務修改記錄時，trx_id 記錄該事務修改 id；
  • roll_pointer：事務修改記錄時，將舊記錄寫入到 undolog 中，roll_pointer 指向舊版本的記錄。

比如說當前有一個學生表，包含年齡跟姓名，我們在表中插入一條記錄，作為事務1，他的字段就應該是這樣的：

在這里插入圖片描述

此時我們需要去執行事務 2，修改張三的名字為李四，那么現在當前事務的 trx_id = 2，roll_pointer 就會指向原來事務 1 的那張表的記錄

在這里插入圖片描述

然后又有一個事務 3 ，此時要修改李四的 age 為 38，此時版本鏈就應該如下：

在這里插入圖片描述

我們所說的回滾，其實就是根據這個事務的操作，然后根據版本鏈，去尋找之前的一些記錄，將對應的數據進行回恢復即可。

Read View

Read View（讀視圖）是 MVCC 在READ COMMITTED（RC） 和REPEATABLE READ（RR） 隔離級別中實現數據可見性的機制

隔離級別Read View 創建時機數據可見性特點
READ COMMITTED事務中每次執行 SELECT 時生成新的 Read View同一事務中多次讀取可能不一致（可看到其他事務已提交的修改），避免臟讀，但允許不可重復讀。
REPEATABLE READ事務首次執行 SELECT 時生成 Read View，后續復用同一事務中多次讀取結果一致（僅可見事務啟動前已提交的數據），避免不可重復讀。
1. Read View 的構成
  • m_ids：創建 Read View 時，當前活躍（已啟動未提交）的事務 ID 列表。
  • min_trx_id：m_ids中最小的事務 ID。
  • max_trx_id：當前系統即將分配的下一個事務 ID（非m_ids中的最大值）。
  • creator_trx_id：創建該 Read View 的事務自身 ID。
2. 數據版本可見性判斷流程（基于聚集索引隱藏列trx_id）

聚集索引每行數據包含隱藏列：

  • trx_id：最后修改該行的事務 ID；
  • roll_pointer：指向 undo 日志中該行的上一版本（形成版本鏈）。

判斷規則：

  1. 若trx_id < min_trx_id：修改該行的事務在 Read View 創建前已提交，可見。
  2. 若trx_id >= max_trx_id：修改該行的事務在 Read View 創建后啟動，不可見。
  3. 若min_trx_id <= trx_id < max_trx_id：
    • 若trx_id在m_ids中（事務仍活躍）：不可見；
    • 若trx_id不在m_ids中（事務已提交）：可見；
Redo 日志
  • 作用：確保事務提交后，修改不會因宕機丟失（實現 ACID 中的 “持久性”）。
  • 存儲：分為內存中的redo log buffer和磁盤中的redo log file（物理文件）。
  • 特點：
    • 順序寫入，性能高；
    • 記錄對數據頁的物理修改（頁地址、偏移量、修改內容）；
    • 僅在數據庫宕機后恢復時使用（重演修改，恢復數據）。
  • 機制：事務提交時，必須先將 redo 日志持久化到redo log file，才算提交完成。
Undo日志
  • 作用：
    1. 事務回滾：記錄操作的逆邏輯（如 INSERT 的逆操作為 DELETE，UPDATE 的逆操作為反向 UPDATE），使事務可回滾到修改前狀態。
    2. MVCC 支持：通過roll_pointer串聯行的多個版本（版本鏈），為 Read View 提供歷史數據版本。
  • 存儲：位于共享表空間，是邏輯日志（記錄 “做了什么”，而非物理修改）。

1.2.3 死鎖

MySQL 死鎖機制

死鎖是指兩個或多個事務在執行過程中，因爭奪鎖資源而陷入互相等待的狀態：每個事務都持有對方需要的鎖，且都無法繼續執行，形成循環等待，導致所有事務都被阻塞。

MySQL（InnoDB 存儲引擎）通過 wait-for graph（等待圖） 實現死鎖檢測：

  • 等待圖中，每個節點代表一個事務，每條邊代表一個事務對另一個事務持有的鎖的等待關系。
  • 采用 非遞歸深度優先算法 檢測圖中是否存在循環（循環即表示死鎖）。
  • 當檢測到死鎖時，MySQL 會選擇 代價較小的事務（如修改行數少的事務）進行回滾，釋放其持有的鎖，解除死鎖。
MySQL常見死鎖
1. 相反加鎖順序死鎖

核心原因：多個事務對同一批資源（表、行）按 相反順序加鎖，導致互相等待對方釋放鎖。

  • 場景 1：不同表加鎖順序相反
    例：事務 1 先鎖表 A 再鎖表 B，事務 2 先鎖表 B 再鎖表 A。

    • 事務 1：UPDATE tableA ...（持有表 A 的鎖）→ 等待鎖表 B；
    • 事務 2：UPDATE tableB ...（持有表 B 的鎖）→ 等待鎖表 A；
      此時形成循環等待，觸發死鎖。

  • 場景 2：同表不同行加鎖順序相反（隱含索引鎖關聯）
    InnoDB 加鎖時，輔助索引加鎖會同時鎖定對應的聚集索引（主鍵索引），可能導致隱藏的加鎖順序問題。

    以students表為例（id為主鍵，no為輔助唯一索引）：

    • 事務 1：先通過no='S0001'（輔助索引）加鎖→ 同時鎖定id=15（聚集索引）→ 再通過id=18加鎖；
    • 事務 2：先通過id=18加鎖→ 再通過no='S0001'加鎖→ 需等待事務 1 釋放id=15的鎖；
      事務 1 等待事務 2 釋放id=18的鎖，事務 2 等待事務 1 釋放id=15的鎖，形成死鎖。

  • 場景 3：外鍵 / 觸發器隱含加鎖
    外鍵約束檢查時會鎖定父表行，觸發器執行時可能隱含對其他表的加鎖，若加鎖順序相反，也會導致死鎖。

2. 鎖沖突死鎖（插入意向鎖與 gap 鎖沖突）

InnoDB 在 Repeatable Read（RR）隔離級別 下，為保證幻讀，會使用gap鎖（間隙鎖）和next-key鎖（記錄鎖 + gap 鎖）。

  • 插入意向鎖：一種特殊的間隙鎖，用于插入操作，需在插入位置的間隙獲取，僅與其他間隙鎖沖突。

  • 沖突場景：兩個事務在同一間隙持有 gap 鎖，且都等待對方釋放鎖以獲取插入意向鎖。

    • 例：students表id為 15、18、20…，事務 1 先執行SELECT * FROM students WHERE id BETWEEN 16 AND 17 FOR UPDATE（加 gap 鎖：(15,18)）；事務 2 也執行相同語句（同樣持有 (15,18) 的 gap 鎖）。此時兩事務都想插入id=16的行，需獲取插入意向鎖，但都被對方的 gap 鎖阻塞，互相等待形成死鎖。
死鎖的預防和處理

  1. 預防措施

    • 統一加鎖順序：所有事務按固定順序（如表名、主鍵升序）加鎖，避免相反順序。
    • 減少鎖持有時間：事務中盡量晚加鎖、早提交，縮短鎖占用時長。
    • 降低隔離級別：使用Read Committed（RC），RC 下僅外鍵和唯一索引沖突時用 gap 鎖，減少鎖范圍。
    • 避免范圍鎖：盡量用精確查詢（WHERE id=?）代替范圍查詢（WHERE id < ?），減少 gap 鎖使用。

  2. 處理方式

    • MySQL 自動檢測死鎖后，回滾代價較小的事務，報錯：deadlock found when trying to get lock。
    • 應用程序需捕獲該錯誤，重試事務（需避免無限重試）

更多資料：https://github.com/0voice

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/diannao/92901.shtml
繁體地址，請注明出處：http://hk.pswp.cn/diannao/92901.shtml
英文地址，請注明出處：http://en.pswp.cn/diannao/92901.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

虛擬直線閾值告警人員計數算法暑期應用

虛擬直線閾值告警人員計數算法暑期應用

智慧人員計數助力暑期&#xff1a;技術賦能安全管理的創新實踐一、背景&#xff1a;暑期人流激增下的安全管理挑戰暑期是旅游、商業、交通等場景的客流高峰期&#xff0c;人員密集區域易引發踩踏事故、管理混亂等安全隱患。傳統人工計數方式效率低、誤差大&#xff0c;難以滿足…
閱讀更多...
SQL164 2021年11月每天新用戶的次日留存率

SQL164 2021年11月每天新用戶的次日留存率

SQL164 2021年11月每天新用戶的次日留存率 思路 ?找出新用戶?&#xff1a;確定每個用戶首次活躍的日期&#xff08;即新用戶&#xff09; 例如101用戶在11月1日首次出現 ?處理跨天活躍?&#xff1a;考慮用戶可能跨天活躍的情況&#xff08;in_time和out_time不在同一天&a…
閱讀更多...
基于單片機的數字電壓表設計

基于單片機的數字電壓表設計

2 系統原理及基本框圖 如圖2.1所示&#xff0c;模擬電壓經過檔位切換到不同的分壓電路衰減后&#xff0c;經隔離干擾送到A/D轉換器進行A/D轉換&#xff0c;然后送到單片機中進行數據處理。處理后的數據送到LCD中顯示&#xff0c;同時通過串行通訊與上位機通信。圖2.1系統基本方…
閱讀更多...
[NLP]UPF基本語法及其在 native low power verification中的典型流程

[NLP]UPF基本語法及其在 native low power verification中的典型流程

UPF基本語法及其在 native low power verification中的典型流程 摘要：本文首先簡要介紹 UPF（Unified Power Format），然后解釋其在 native low power verification（原生低功耗驗證）中的典型流程。最后，我將使用50個具體例子來完整展示 UPF 的關鍵語法。這些例子基…
閱讀更多...
fish-speech 在50系列顯卡使用 --compile加速兼容

fish-speech 在50系列顯卡使用 --compile加速兼容

#環境說明 GPU: NVIDIA GeForce RTX 5080 Laptop GPU (sm_120) win11家庭版 24H2 #問題匯總 baize.exceptions.HTTPException: (500, "RuntimeError: ptxas failed with error code 4294967295: \\n\\n") 問題匯總 1 baize.exceptions.HTTPException: (500, "…
閱讀更多...
UI自動化測試實戰

UI自動化測試實戰

Python接口自動化測試零基礎入門到精通&#xff08;2025最新版&#xff09;一、設計背景 隨著IT行業的發展&#xff0c;產品愈漸復雜&#xff0c;web端業務及流程更加繁瑣&#xff0c;目前UI測試僅是針對單一頁面&#xff0c;操作量大。為了滿足多頁面功能及流程的需求及節省工…
閱讀更多...
面試實戰，問題六，被問數據庫索引，怎么回答

面試實戰，問題六，被問數據庫索引，怎么回答

Java開發面試&#xff1a;數據庫索引的原理及常見問題解答 在Java開發面試中&#xff0c;數據庫索引是核心知識點&#xff0c;涉及數據庫優化和性能調優。索引通過高效的數據結構加速數據檢索&#xff0c;降低磁盤IO成本&#xff0c;并支持排序操作。下面我將逐步解釋索引的原理…
閱讀更多...
ARM-I2C硬實現

ARM-I2C硬實現

硬件I2C-GD32F4系列的實現初始化操作在初始化函數里執行以下代碼uint32_t i2cx_scl_port_rcu RCU_GPIOB; uint32_t i2cx_scl_port GPIOB; uint32_t i2cx_scl_pin GPIO_PIN_6; uint32_t i2cx_scl_af GPIO_AF_4;uint32_t i2cx_sda_port_rcu RCU_GPIOB; uint32_t i2cx_sda_po…
閱讀更多...
WinUI3開發_過渡動畫

WinUI3開發_過渡動畫

簡介 過渡動畫是當發生事件時控件UI狀態發生改變時以一種動畫形式來演變到另外一種狀態&#xff0c;而非瞬間改變&#xff0c;使用一種更加平滑的方式來進行切換&#xff0c;例如下圖是文字切換的交叉柵欄效果&#xff1a;還有頁面切換動畫&#xff1a;在或者是圖標動畫&#x…
閱讀更多...
Linux下提權root權限

Linux下提權root權限

現在AI工具這么豐富&#xff0c;稍微搜一下就有一個差不多的總結輸出。但是&#xff0c;可能還不夠詳細&#xff0c;或者給得太多~~~今天時間關系&#xff0c;今天只總結了在Linux如何提權到root&#xff0c;并沒有寫如何進行防護。后面有時間&#xff0c;我再總結一下。命令實…
閱讀更多...
焊接機器人節能先鋒

焊接機器人節能先鋒

汽車制造業中&#xff0c;機器人技術的應用已成為推動工業自動化和生產效率提升的重要力量。機器人在焊接、組裝、涂裝等關鍵制造環節中扮演著不可或缺的角色。隨著工業生產規模的不斷擴大&#xff0c;能源消耗和成本控制成為了企業必須面對的重大挑戰。尤其是工業焊接用氣的大…
閱讀更多...
MinIO：云原生對象存儲的終極指南

MinIO：云原生對象存儲的終極指南

MinIO 是什么？ MinIO 是一款高性能、云原生的對象存儲服務，具有以下優勢： 輕量級部署：采用 Go 語言編寫，資源占用低，支持快速部署 兼容性強：完全兼容 Amazon S3 API，輕松對接現有應用 高可用架構：支持分布式部署，確保數據持久性和服務可用性 高性能表現：專為云環境…
閱讀更多...
Spring AOP `MethodInvocation` 工作原理

Spring AOP `MethodInvocation` 工作原理

?? 一、通知到 MethodInterceptor 的轉換機制 Spring AOP 通過適配器模式將開發者定義的注解型通知&#xff08;如 Before&#xff09;統一轉換為 MethodInterceptor 接口實現&#xff0c;確保所有通知類型能接入同一調用鏈。以下是轉換細節&#xff1a; 1. 適配器實現原理 核…
閱讀更多...
PPO原論文閱讀

PPO原論文閱讀

一、Introduction1.目前存在的問題&#xff1a;(deep)Q-learning:在一些簡單問題上表現不佳&#xff0c;可理解性差基礎的policy gradient算法&#xff1a;&#xff08;如REINFORCE&#xff09;魯棒性差&#xff0c;需要大量數據TRPO&#xff1a;復雜&#xff0c;在包含噪音&am…
閱讀更多...
零基礎也能創作專屬歌曲：文心一言+蘑兔AI協同教程

零基礎也能創作專屬歌曲：文心一言+蘑兔AI協同教程

在AI技術飛速發展的今天&#xff0c;音樂創作已不再是專業音樂人的專屬領域。通過文心一言與蘑兔AI的協同使用&#xff0c;即使沒有音樂基礎&#xff0c;也能輕松完成從歌詞創作到作曲編曲的全流程。本文將詳細拆解操作步驟&#xff0c;助你快速上手&#xff0c;實現音樂創作夢…
閱讀更多...
圖論:搜索問題

圖論:搜索問題

提到圖論中的搜索問題&#xff0c;首先想到的也就是DFS和BFS了&#xff0c;而提到這兩種搜索&#xff0c;那么最典型的題目就是島嶼問題了&#xff0c;下面就練習幾道相關的題目&#xff0c;為之后的更深奧的圖論學習打下基礎&#xff01; 孤島的總面積 題目鏈接&#xff1a;…
閱讀更多...
AI驅動攻防升級，API安全走到關鍵檔口

AI驅動攻防升級，API安全走到關鍵檔口

在數字化轉型與AI技術快速發展的雙重驅動下&#xff0c;API已成為企業業務與外部世界連接的神經中樞。然而&#xff0c;隨著API的深度應用&#xff0c;針對API的攻擊規模與復雜性也在持續升級。 API為何頻頻成為黑客重點盯防的突破口&#xff1f;企業常見的API防護手段是否還能…
閱讀更多...
網絡基礎DAY18-動態路由協議基礎

網絡基礎DAY18-動態路由協議基礎

動態路由協議基礎知識回顧&#xff1a;1.什么是路由&#xff1f; 答&#xff1a;是三層設備轉發IP報文的路徑信息。 2.路由有哪些來源&#xff1f; 答&#xff1a;1.直連路由2.靜態路由3.動態路由 3.有直連路由的條件&#xff1f; 答&#xff1a;1.二層和三層物理接口狀態為UP …
閱讀更多...
axios統一封裝規范管理

axios統一封裝規范管理

新建/api/ 1.新建統一處理文件/api/axios.ts import axios from "axios"const http axios.create({baseURL: import.meta.env.VITE_API_BASE_URL, // 從環境變量讀取timeout: 10000, });// 請求攔截器&#xff08;如添加 Token&#xff09; http.interceptors.reque…
閱讀更多...
Java學習第七十四部分——Elasticsearch（ES）

Java學習第七十四部分——Elasticsearch（ES）

目錄 一、前言提要 二、核心特性 三、應用場景 四、主要優勢 五、集成方式 六、基礎操作 七、高級特性 八、概念類比——與關系型數據庫 九、簡單示例——實現存儲與搜索 十、生態集成——基于Spring Data Elasticsearch 十一、性能優化建議 十二、總結歸納概述 一…
閱讀更多...
最新文章

Copyright @ 2022~2023