一、概述

1.1 背景介紹

在我擔(dān)任某互聯(lián)網(wǎng)金融平臺(tái)SRE期間，曾遇到過(guò)一次嚴(yán)重的線上事故：凌晨3點(diǎn)，監(jiān)控系統(tǒng)瘋狂告警，數(shù)據(jù)庫(kù)活躍連接數(shù)從平時(shí)的200飆升到2000，大量請(qǐng)求超時(shí)。緊急排查后發(fā)現(xiàn)，一個(gè)批量更新任務(wù)與在線交易產(chǎn)生了死鎖，導(dǎo)致數(shù)據(jù)庫(kù)連接被占滿。

這次事故持續(xù)了40分鐘，影響了上萬(wàn)名用戶的交易。事后復(fù)盤發(fā)現(xiàn)，問(wèn)題根源是開發(fā)團(tuán)隊(duì)對(duì)MySQL事務(wù)和鎖機(jī)制理解不足，寫出了容易產(chǎn)生死鎖的代碼。

從那以后，我花了大量時(shí)間研究MySQL的事務(wù)和鎖機(jī)制，并總結(jié)出一套完整的排查和預(yù)防方法。本文將系統(tǒng)性地講解MySQL事務(wù)的ACID特性、鎖的工作原理，以及死鎖的排查和解決方案。

1.2 技術(shù)特點(diǎn)

MySQL InnoDB存儲(chǔ)引擎的事務(wù)和鎖機(jī)制具有以下特點(diǎn)：

ACID事務(wù)特性

Atomicity（原子性）：事務(wù)是不可分割的工作單位

Consistency（一致性）：事務(wù)執(zhí)行前后數(shù)據(jù)保持一致

Isolation（隔離性）：并發(fā)事務(wù)之間相互隔離

Durability（持久性）：事務(wù)提交后數(shù)據(jù)永久保存

多粒度鎖機(jī)制

行鎖：鎖定單行記錄，并發(fā)度高

間隙鎖：鎖定索引間隙，防止幻讀

表鎖：鎖定整張表，開銷小但并發(fā)度低

意向鎖：表級(jí)鎖，用于協(xié)調(diào)行鎖和表鎖

MVCC多版本并發(fā)控制

讀不阻塞寫，寫不阻塞讀

通過(guò)undo log實(shí)現(xiàn)一致性讀

支持多種隔離級(jí)別

1.3 適用場(chǎng)景

1.4 環(huán)境要求

關(guān)鍵配置要求：

-- 查看InnoDB相關(guān)配置
SHOWVARIABLESLIKE'innodb%';

-- 關(guān)鍵配置
innodb_buffer_pool_size = 8G -- 緩沖池大小
innodb_lock_wait_timeout = 50 -- 鎖等待超時(shí)（秒）
innodb_deadlock_detect = ON -- 開啟死鎖檢測(cè)
innodb_print_all_deadlocks = ON -- 打印所有死鎖信息
transaction_isolation = REPEATABLE-READ -- 默認(rèn)隔離級(jí)別

二、詳細(xì)步驟

2.1 準(zhǔn)備工作

2.1.1 ACID特性深入理解

原子性（Atomicity）

事務(wù)中的所有操作要么全部成功，要么全部失敗回滾。MySQL通過(guò)undo log實(shí)現(xiàn)原子性。

-- 原子性示例：轉(zhuǎn)賬操作
STARTTRANSACTION;

-- 操作1：扣減轉(zhuǎn)出賬戶余額
UPDATEaccountsSETbalance = balance -1000WHEREuser_id =1;

-- 操作2：增加轉(zhuǎn)入賬戶余額
UPDATEaccountsSETbalance = balance +1000WHEREuser_id =2;

-- 如果兩個(gè)操作都成功，提交事務(wù)
COMMIT;

-- 如果任一操作失敗，回滾事務(wù)
-- ROLLBACK;

-- 原子性保證：
-- 1. 要么兩個(gè)賬戶都更新成功
-- 2. 要么兩個(gè)賬戶都保持原狀
-- 不會(huì)出現(xiàn)錢扣了但沒(méi)有到賬的情況

一致性（Consistency）

事務(wù)執(zhí)行前后，數(shù)據(jù)庫(kù)從一個(gè)一致狀態(tài)轉(zhuǎn)換到另一個(gè)一致狀態(tài)。

-- 一致性示例：確?？偨痤~不變
-- 假設(shè)系統(tǒng)中只有兩個(gè)賬戶，總金額應(yīng)該始終為10000

-- 事務(wù)前檢查
SELECTSUM(balance)FROMaccounts; -- 結(jié)果：10000

STARTTRANSACTION;
UPDATEaccountsSETbalance = balance -1000WHEREuser_id =1;
UPDATEaccountsSETbalance = balance +1000WHEREuser_id =2;
COMMIT;

-- 事務(wù)后檢查
SELECTSUM(balance)FROMaccounts; -- 結(jié)果仍然：10000

-- 一致性由應(yīng)用程序和數(shù)據(jù)庫(kù)約束共同保證
-- 比如：CHECK約束、外鍵約束、觸發(fā)器等

隔離性（Isolation）

并發(fā)執(zhí)行的事務(wù)之間相互隔離，一個(gè)事務(wù)的中間狀態(tài)對(duì)其他事務(wù)不可見。

-- 隔離性示例：并發(fā)讀寫

-- 會(huì)話1
STARTTRANSACTION;
UPDATEproductsSETstock = stock -1WHEREid=1;
-- 此時(shí)還未提交

-- 會(huì)話2
SELECTstockFROMproductsWHEREid=1;
-- 根據(jù)隔離級(jí)別，可能看到更新前或更新后的值

-- MySQL默認(rèn)使用REPEATABLE READ隔離級(jí)別
-- 會(huì)話2看到的是事務(wù)開始時(shí)的快照，即更新前的值

持久性（Durability）

事務(wù)一旦提交，其結(jié)果就是永久性的，即使系統(tǒng)崩潰也不會(huì)丟失。

-- 持久性由redo log保證
-- 事務(wù)提交時(shí)，redo log會(huì)刷入磁盤

-- 相關(guān)配置
SHOWVARIABLESLIKE'innodb_flush_log_at_trx_commit';

-- innodb_flush_log_at_trx_commit = 1（默認(rèn)）
-- 每次事務(wù)提交都將redo log刷入磁盤
-- 最安全但性能略低

-- innodb_flush_log_at_trx_commit = 2
-- 每次提交寫入OS緩存，每秒刷盤
-- 性能好，但斷電可能丟失1秒數(shù)據(jù)

-- innodb_flush_log_at_trx_commit = 0
-- 每秒寫入OS緩存并刷盤
-- 性能最好，但可能丟失1秒數(shù)據(jù)

2.1.2 事務(wù)隔離級(jí)別

MySQL支持四種隔離級(jí)別，解決不同的并發(fā)問(wèn)題：

-- 查看當(dāng)前隔離級(jí)別
SELECT@@transaction_isolation;
-- 或
SHOWVARIABLESLIKE'transaction_isolation';

-- 設(shè)置會(huì)話隔離級(jí)別
SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;

-- 設(shè)置全局隔離級(jí)別（需要重連生效）
SETGLOBALTRANSACTIONISOLATIONLEVELREADCOMMITTED;

-- 在配置文件中設(shè)置
-- [mysqld]
-- transaction-isolation = READ-COMMITTED

臟讀演示

-- 會(huì)話1（設(shè)置為READ UNCOMMITTED）
SETSESSIONTRANSACTIONISOLATIONLEVELREADUNCOMMITTED;
STARTTRANSACTION;

-- 會(huì)話2
STARTTRANSACTION;
UPDATEaccountsSETbalance =500WHEREuser_id =1;
-- 未提交

-- 會(huì)話1
SELECTbalanceFROMaccountsWHEREuser_id =1;
-- 結(jié)果：500（讀到了未提交的數(shù)據(jù)，即臟讀）

-- 會(huì)話2
ROLLBACK; -- 回滾

-- 會(huì)話1再次查詢
SELECTbalanceFROMaccountsWHEREuser_id =1;
-- 結(jié)果可能是原來(lái)的值，之前讀到的500是"臟數(shù)據(jù)"

不可重復(fù)讀演示

-- 會(huì)話1（READ COMMITTED級(jí)別）
SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;
STARTTRANSACTION;

SELECTbalanceFROMaccountsWHEREuser_id =1;
-- 結(jié)果：1000

-- 會(huì)話2
UPDATEaccountsSETbalance =500WHEREuser_id =1;
COMMIT;

-- 會(huì)話1再次查詢
SELECTbalanceFROMaccountsWHEREuser_id =1;
-- 結(jié)果：500（同一事務(wù)內(nèi)兩次讀取結(jié)果不同，即不可重復(fù)讀）

COMMIT;

幻讀演示

-- 會(huì)話1（即使REPEATABLE READ也可能有幻讀場(chǎng)景）
STARTTRANSACTION;

SELECTCOUNT(*)FROMordersWHEREuser_id =1;
-- 結(jié)果：10

-- 會(huì)話2
INSERTINTOorders (user_id, amount)VALUES(1,100);
COMMIT;

-- 會(huì)話1使用當(dāng)前讀
SELECTCOUNT(*)FROMordersWHEREuser_id =1FORUPDATE;
-- 結(jié)果：11（看到了新插入的行，即幻讀）

-- 注意：InnoDB的REPEATABLE READ通過(guò)間隙鎖很大程度上防止了幻讀
-- 但在某些邊界情況下仍可能發(fā)生

2.1.3 創(chuàng)建測(cè)試環(huán)境

-- 創(chuàng)建測(cè)試數(shù)據(jù)庫(kù)
CREATEDATABASEIFNOTEXISTSlock_demo;
USElock_demo;

-- 創(chuàng)建賬戶表
CREATETABLEaccounts (
 idBIGINTUNSIGNEDAUTO_INCREMENT PRIMARYKEY,
  user_idBIGINTUNSIGNEDNOTNULL,
  balanceDECIMAL(15,2)NOTNULLDEFAULT0.00,
 versionINTUNSIGNEDNOTNULLDEFAULT0, -- 樂(lè)觀鎖版本號(hào)
  created_at DATETIMEDEFAULTCURRENT_TIMESTAMP,
  updated_at DATETIMEDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
 UNIQUEKEYuk_user_id (user_id)
)ENGINE=InnoDB;

-- 創(chuàng)建訂單表
CREATETABLEorders (
 idBIGINTUNSIGNEDAUTO_INCREMENT PRIMARYKEY,
  order_noVARCHAR(32)NOTNULL,
  user_idBIGINTUNSIGNEDNOTNULL,
  amountDECIMAL(10,2)NOTNULL,
 statusTINYINTDEFAULT0,
  created_at DATETIMEDEFAULTCURRENT_TIMESTAMP,
 UNIQUEKEYuk_order_no (order_no),
 INDEXidx_user_id (user_id),
 INDEXidx_status (status),
 INDEXidx_user_status (user_id,status)
)ENGINE=InnoDB;

-- 創(chuàng)建庫(kù)存表
CREATETABLEinventory (
 idBIGINTUNSIGNEDAUTO_INCREMENT PRIMARYKEY,
  product_idBIGINTUNSIGNEDNOTNULL,
  stockINTUNSIGNEDNOTNULLDEFAULT0,
 versionINTUNSIGNEDNOTNULLDEFAULT0,
 UNIQUEKEYuk_product_id (product_id)
)ENGINE=InnoDB;

-- 插入測(cè)試數(shù)據(jù)
INSERTINTOaccounts (user_id, balance)VALUES
(1,10000.00), (2,5000.00), (3,3000.00);

INSERTINTOinventory (product_id, stock)VALUES
(1001,100), (1002,200), (1003,50);

-- 生成訂單測(cè)試數(shù)據(jù)
INSERTINTOorders (order_no, user_id, amount,status)
SELECT
 CONCAT('ORD',LPAD(seq,10,'0')),
 FLOOR(RAND() *3) +1,
 ROUND(RAND() *1000,2),
 FLOOR(RAND() *5)
FROM(
 SELECT@row:= @row+1asseqFROM
  (SELECT0UNIONSELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4
  UNIONSELECT5UNIONSELECT6UNIONSELECT7UNIONSELECT8UNIONSELECT9) t1,
  (SELECT0UNIONSELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4
  UNIONSELECT5UNIONSELECT6UNIONSELECT7UNIONSELECT8UNIONSELECT9) t2,
  (SELECT0UNIONSELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4
  UNIONSELECT5UNIONSELECT6UNIONSELECT7UNIONSELECT8UNIONSELECT9) t3,
  (SELECT@row:=0) r
) seq_table;

2.2 核心配置

2.2.1 InnoDB鎖類型詳解

1. 共享鎖（S鎖）和排他鎖（X鎖）

-- 共享鎖（S鎖）：允許其他事務(wù)讀，但不允許寫
SELECT*FROMaccountsWHEREuser_id =1LOCKINSHAREMODE;
-- MySQL 8.0 新語(yǔ)法
SELECT*FROMaccountsWHEREuser_id =1FORSHARE;

-- 排他鎖（X鎖）：不允許其他事務(wù)讀寫（當(dāng)前讀除外）
SELECT*FROMaccountsWHEREuser_id =1FORUPDATE;

-- 鎖兼容性矩陣
-- |   | S鎖 | X鎖 |
-- | S鎖 | 兼容 | 沖突 |
-- | X鎖 | 沖突 | 沖突 |

2. 意向鎖（IS/IX鎖）

-- 意向鎖是表級(jí)鎖，用于表明事務(wù)稍后會(huì)在表中的行上加什么類型的鎖
-- 意向共享鎖（IS）：事務(wù)準(zhǔn)備給數(shù)據(jù)行加共享鎖
-- 意向排他鎖（IX）：事務(wù)準(zhǔn)備給數(shù)據(jù)行加排他鎖

-- 查看意向鎖
SELECT*FROMperformance_schema.data_locksWHERELOCK_TYPE ='TABLE';

-- 意向鎖的作用：
-- 加表鎖時(shí)，不需要遍歷每一行來(lái)檢查是否有行鎖
-- 只需檢查意向鎖即可

-- 兼容性矩陣：
-- |   | IS | IX | S | X |
-- | IS | 兼容 | 兼容 | 兼容 | 沖突 |
-- | IX | 兼容 | 兼容 | 沖突 | 沖突 |
-- | S  | 兼容 | 沖突 | 兼容 | 沖突 |
-- | X  | 沖突 | 沖突 | 沖突 | 沖突 |

3. 記錄鎖（Record Lock）

-- 記錄鎖鎖定索引記錄
-- 如果表沒(méi)有索引，InnoDB會(huì)創(chuàng)建隱藏的聚簇索引，并使用該索引進(jìn)行記錄鎖定

STARTTRANSACTION;
-- 鎖定id=1的記錄
SELECT*FROMaccountsWHEREid=1FORUPDATE;
-- 此時(shí)其他事務(wù)無(wú)法修改id=1的行

-- 查看記錄鎖
SELECT*FROMperformance_schema.data_locks
WHERELOCK_TYPE ='RECORD'ANDLOCK_MODE ='X,REC_NOT_GAP';

4. 間隙鎖（Gap Lock）

-- 間隙鎖鎖定索引記錄之間的間隙，防止其他事務(wù)插入
-- 只在REPEATABLE READ及以上隔離級(jí)別生效

-- 假設(shè)accounts表中有id: 1, 5, 10
STARTTRANSACTION;
SELECT*FROMaccountsWHEREidBETWEEN3AND7FORUPDATE;
-- 這會(huì)鎖定(1,5)和(5,10)的間隙

-- 其他事務(wù)無(wú)法在這些間隙中插入新記錄
-- INSERT INTO accounts (id, user_id, balance) VALUES (3, 3, 1000); -- 會(huì)等待

-- 查看間隙鎖
SELECT*FROMperformance_schema.data_locks
WHERELOCK_TYPE ='RECORD'ANDLOCK_MODE ='X,GAP';

5. 臨鍵鎖（Next-Key Lock）

-- 臨鍵鎖 = 記錄鎖 + 間隙鎖
-- 鎖定一個(gè)索引記錄及其前面的間隙

-- 假設(shè)有id: 1, 5, 10
STARTTRANSACTION;
SELECT*FROMaccountsWHEREid=5FORUPDATE;
-- 在REPEATABLE READ級(jí)別，這會(huì)鎖定：
-- 1. 記錄id=5
-- 2. 間隙(1,5)

-- 臨鍵鎖是InnoDB默認(rèn)的鎖類型，用于防止幻讀

6. 插入意向鎖（Insert Intention Lock）

-- 插入意向鎖是一種特殊的間隙鎖
-- 多個(gè)事務(wù)可以同時(shí)獲取同一間隙的插入意向鎖（只要插入位置不同）

-- 會(huì)話1
STARTTRANSACTION;
INSERTINTOaccounts (id, user_id, balance)VALUES(3,3,1000);
-- 獲取(1,5)間隙的插入意向鎖，插入id=3

-- 會(huì)話2
STARTTRANSACTION;
INSERTINTOaccounts (id, user_id, balance)VALUES(4,4,2000);
-- 也可以獲取(1,5)間隙的插入意向鎖，插入id=4
-- 兩個(gè)插入可以并發(fā)執(zhí)行，因?yàn)椴迦胛恢貌粵_突

2.2.2 鎖監(jiān)控配置

-- 開啟鎖監(jiān)控
SETGLOBALinnodb_status_output =ON;
SETGLOBALinnodb_status_output_locks =ON;

-- 查看InnoDB狀態(tài)（包含鎖信息）
SHOWENGINEINNODBSTATUSG

-- 使用performance_schema監(jiān)控鎖
-- data_locks：當(dāng)前持有的鎖
SELECT*FROMperformance_schema.data_locks;

-- data_lock_waits：鎖等待關(guān)系
SELECT*FROMperformance_schema.data_lock_waits;

-- 查看等待鎖的事務(wù)
SELECT
  r.trx_idASwaiting_trx_id,
  r.trx_mysql_thread_idASwaiting_thread,
  r.trx_queryASwaiting_query,
  b.trx_idASblocking_trx_id,
  b.trx_mysql_thread_idASblocking_thread,
  b.trx_queryASblocking_query
FROMperformance_schema.data_lock_waits w
INNERJOINinformation_schema.innodb_trx bONb.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID
INNERJOINinformation_schema.innodb_trx rONr.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID;

2.2.3 死鎖檢測(cè)配置

-- 開啟死鎖檢測(cè)（默認(rèn)開啟）
SETGLOBALinnodb_deadlock_detect =ON;

-- 設(shè)置鎖等待超時(shí)時(shí)間
SETGLOBALinnodb_lock_wait_timeout =50; -- 默認(rèn)50秒

-- 打印所有死鎖信息到錯(cuò)誤日志
SETGLOBALinnodb_print_all_deadlocks =ON;

-- 配置文件設(shè)置
-- [mysqld]
-- innodb_deadlock_detect = ON
-- innodb_lock_wait_timeout = 10
-- innodb_print_all_deadlocks = ON

2.3 啟動(dòng)和驗(yàn)證

2.3.1 驗(yàn)證鎖機(jī)制

-- 測(cè)試記錄鎖
-- 會(huì)話1
STARTTRANSACTION;
SELECT*FROMaccountsWHEREid=1FORUPDATE;
-- 不提交，保持鎖定

-- 會(huì)話2
STARTTRANSACTION;
-- 嘗試更新同一行
UPDATEaccountsSETbalance = balance +100WHEREid=1;
-- 此語(yǔ)句會(huì)等待，因?yàn)閕d=1被會(huì)話1鎖定

-- 會(huì)話1
COMMIT; -- 提交后會(huì)話2的更新才會(huì)執(zhí)行

-- 查看鎖等待情況
SELECT*FROMperformance_schema.data_lock_waits;

2.3.2 驗(yàn)證死鎖檢測(cè)

-- 構(gòu)造死鎖場(chǎng)景
-- 會(huì)話1
STARTTRANSACTION;
UPDATEaccountsSETbalance = balance -100WHEREid=1;

-- 會(huì)話2
STARTTRANSACTION;
UPDATEaccountsSETbalance = balance -100WHEREid=2;

-- 會(huì)話1
UPDATEaccountsSETbalance = balance +100WHEREid=2;
-- 等待會(huì)話2釋放id=2的鎖

-- 會(huì)話2
UPDATEaccountsSETbalance = balance +100WHEREid=1;
-- 等待會(huì)話1釋放id=1的鎖
-- 此時(shí)發(fā)生死鎖！

-- MySQL會(huì)檢測(cè)到死鎖，回滾其中一個(gè)事務(wù)
-- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

-- 查看最近的死鎖信息
SHOWENGINEINNODBSTATUSG
-- 找到"LATEST DETECTED DEADLOCK"部分

三、示例代碼和配置

3.1 完整配置示例

3.1.1 死鎖案例分析

案例1：相反順序更新

-- 最常見的死鎖場(chǎng)景：兩個(gè)事務(wù)以相反順序更新行

-- 事務(wù)1：先更新A，再更新B
STARTTRANSACTION;
UPDATEaccountsSETbalance = balance -100WHEREuser_id =1; -- 鎖定user_id=1
-- 等待...
UPDATEaccountsSETbalance = balance +100WHEREuser_id =2; -- 需要鎖定user_id=2

-- 事務(wù)2：先更新B，再更新A
STARTTRANSACTION;
UPDATEaccountsSETbalance = balance -50WHEREuser_id =2; -- 鎖定user_id=2
-- 等待...
UPDATEaccountsSETbalance = balance +50WHEREuser_id =1; -- 需要鎖定user_id=1

-- 死鎖！事務(wù)1持有A等待B，事務(wù)2持有B等待A

-- 解決方案：固定更新順序
-- 始終按user_id升序或降序更新
STARTTRANSACTION;
-- 方法1：應(yīng)用層排序
UPDATEaccountsSETbalance = balance -100WHEREuser_id =1;
UPDATEaccountsSETbalance = balance +100WHEREuser_id =2;
COMMIT;

案例2：間隙鎖死鎖

-- 間隙鎖導(dǎo)致的死鎖

-- 表中有id: 1, 10, 20
-- 事務(wù)1
STARTTRANSACTION;
SELECT*FROMaccountsWHEREid=5FORUPDATE; -- 鎖定間隙(1,10)
-- 等待...

-- 事務(wù)2
STARTTRANSACTION;
SELECT*FROMaccountsWHEREid=15FORUPDATE; -- 鎖定間隙(10,20)
INSERTINTOaccounts (id, user_id, balance)VALUES(7,7,1000); -- 等待事務(wù)1

-- 事務(wù)1
INSERTINTOaccounts (id, user_id, balance)VALUES(12,12,2000); -- 等待事務(wù)2
-- 死鎖！

-- 解決方案：
-- 1. 降低隔離級(jí)別到READ COMMITTED（不使用間隙鎖）
-- 2. 使用唯一索引精確匹配，避免間隙鎖
-- 3. 減少鎖定范圍

案例3：唯一鍵沖突死鎖

-- 唯一鍵沖突可能導(dǎo)致死鎖

-- 表中已有 order_no = 'ORD001'
-- 事務(wù)1
STARTTRANSACTION;
INSERTINTOorders (order_no, user_id, amount)VALUES('ORD002',1,100);

-- 事務(wù)2
STARTTRANSACTION;
INSERTINTOorders (order_no, user_id, amount)VALUES('ORD002',2,200);
-- 唯一鍵沖突，等待事務(wù)1

-- 事務(wù)3
STARTTRANSACTION;
INSERTINTOorders (order_no, user_id, amount)VALUES('ORD002',3,300);
-- 也等待

-- 事務(wù)1回滾
ROLLBACK;
-- 事務(wù)2和事務(wù)3可能死鎖，因?yàn)樗鼈兌荚诘却i

-- 解決方案：
-- 1. 使用INSERT ... ON DUPLICATE KEY UPDATE
-- 2. 使用INSERT IGNORE
-- 3. 先查詢?cè)俨迦耄ㄔ趹?yīng)用層處理）

3.1.2 悲觀鎖實(shí)現(xiàn)

/**
* 悲觀鎖實(shí)現(xiàn)轉(zhuǎn)賬功能
* 使用SELECT FOR UPDATE鎖定記錄
*/
@Service
@Transactional
publicclassTransferService{

 @Autowired
 privateJdbcTemplate jdbcTemplate;

 /**
  * 轉(zhuǎn)賬 - 悲觀鎖實(shí)現(xiàn)
  * 關(guān)鍵：按固定順序獲取鎖，避免死鎖
  */
 publicvoidtransfer(Long fromUserId, Long toUserId, BigDecimal amount){
   // 按user_id排序，確保獲取鎖的順序一致
    Long firstUserId = Math.min(fromUserId, toUserId);
    Long secondUserId = Math.max(fromUserId, toUserId);

   try{
     // 按順序鎖定賬戶
      BigDecimal firstBalance = lockAndGetBalance(firstUserId);
      BigDecimal secondBalance = lockAndGetBalance(secondUserId);

     // 確定轉(zhuǎn)出和轉(zhuǎn)入賬戶的余額
      BigDecimal fromBalance = fromUserId.equals(firstUserId) ? firstBalance : secondBalance;
      BigDecimal toBalance = fromUserId.equals(firstUserId) ? secondBalance : firstBalance;

     // 檢查余額
     if(fromBalance.compareTo(amount) 

	

	3.1.3 樂(lè)觀鎖實(shí)現(xiàn)

	
/**
* 樂(lè)觀鎖實(shí)現(xiàn)庫(kù)存扣減
* 使用版本號(hào)或CAS機(jī)制
*/
@Service
publicclassInventoryService{

 @Autowired
 privateJdbcTemplate jdbcTemplate;

 /**
  * 扣減庫(kù)存 - 樂(lè)觀鎖實(shí)現(xiàn)
  *@returntrue 成功，false 失?。◣?kù)存不足或版本沖突）
  */
 publicbooleandecreaseStock(Long productId,intquantity){
   intmaxRetries =3;

   for(inti =0; i < maxRetries; i++) {
? ? ? ? ? ??// 查詢當(dāng)前庫(kù)存和版本號(hào)
? ? ? ? ? ? String selectSql =?"SELECT stock, version FROM inventory WHERE product_id = ?";
? ? ? ? ? ? Map result = jdbcTemplate.queryForMap(selectSql, productId);

     intcurrentStock = (Integer) result.get("stock");
     intcurrentVersion = (Integer) result.get("version");

     // 檢查庫(kù)存
     if(currentStock < quantity) {
? ? ? ? ? ? ? ??returnfalse; ?// 庫(kù)存不足
? ? ? ? ? ? }

? ? ? ? ? ??// 使用版本號(hào)進(jìn)行CAS更新
? ? ? ? ? ? String updateSql =?"""
? ? ? ? ? ? ? ? UPDATE inventory
? ? ? ? ? ? ? ? SET stock = stock - ?, version = version + 1
? ? ? ? ? ? ? ? WHERE product_id = ? AND version = ?
? ? ? ? ? ? """;

? ? ? ? ? ??int?affected = jdbcTemplate.update(updateSql, quantity, productId, currentVersion);

? ? ? ? ? ??if?(affected >0) {
       returntrue; // 更新成功
      }

     // 版本沖突，重試
     try{
        Thread.sleep(10+ (long)(Math.random() *50)); // 隨機(jī)延遲
      }catch(InterruptedException e) {
        Thread.currentThread().interrupt();
      }
    }

   returnfalse; // 重試次數(shù)用盡
  }

 /**
  * 扣減庫(kù)存 - 使用行級(jí)條件更新（更簡(jiǎn)潔的樂(lè)觀鎖）
  */
 publicbooleandecreaseStockSimple(Long productId,intquantity){
    String sql ="""
      UPDATE inventory
      SET stock = stock - ?
      WHERE product_id = ? AND stock >= ?
    """;

   intaffected = jdbcTemplate.update(sql, quantity, productId, quantity);
   returnaffected >0;
  }
}


	

	3.1.4 分布式鎖實(shí)現(xiàn)

	
/**
* 基于Redis的分布式鎖實(shí)現(xiàn)
* 解決跨實(shí)例的并發(fā)問(wèn)題
*/
@Component
publicclassDistributedLock{

 @Autowired
 privateStringRedisTemplate redisTemplate;

 privatestaticfinallongDEFAULT_EXPIRE_TIME =30000; // 30秒

 /**
  * 獲取鎖
  *@paramlockKey 鎖的key
  *@paramrequestId 請(qǐng)求標(biāo)識(shí)（用于釋放鎖時(shí)驗(yàn)證）
  *@paramexpireTime 過(guò)期時(shí)間（毫秒）
  */
 publicbooleantryLock(String lockKey, String requestId,longexpireTime){
    Boolean success = redisTemplate.opsForValue().setIfAbsent(
      lockKey,
      requestId,
      expireTime,
      TimeUnit.MILLISECONDS
    );
   returnBoolean.TRUE.equals(success);
  }

 /**
  * 釋放鎖
  * 使用Lua腳本保證原子性
  */
 publicbooleanunlock(String lockKey, String requestId){
    String script ="""
      if redis.call('get', KEYS[1]) == ARGV[1] then
        return redis.call('del', KEYS[1])
      else
        return 0
      end
    """;

    Long result = redisTemplate.execute(
     newDefaultRedisScript<>(script, Long.class),
     Collections.singletonList(lockKey),
     requestId
    );

   returnLong.valueOf(1).equals(result);
  }

 /**
  * 帶自動(dòng)續(xù)期的鎖
  * 使用watchdog機(jī)制
  */
 publicbooleantryLockWithWatchdog(String lockKey, String requestId){
   booleanlocked = tryLock(lockKey, requestId, DEFAULT_EXPIRE_TIME);

   if(locked) {
     // 啟動(dòng)watchdog線程，定期續(xù)期
      startWatchdog(lockKey, requestId);
    }

   returnlocked;
  }

 privatevoidstartWatchdog(String lockKey, String requestId){
    Thread watchdog =newThread(() -> {
     while(!Thread.currentThread().isInterrupted()) {
       try{
          Thread.sleep(DEFAULT_EXPIRE_TIME /3); // 每10秒續(xù)期一次

         // 續(xù)期
          String script ="""
            if redis.call('get', KEYS[1]) == ARGV[1] then
              return redis.call('pexpire', KEYS[1], ARGV[2])
            else
              return 0
            end
          """;

          Long result = redisTemplate.execute(
           newDefaultRedisScript<>(script, Long.class),
           Collections.singletonList(lockKey),
           requestId,
           String.valueOf(DEFAULT_EXPIRE_TIME)
          );

         if(!Long.valueOf(1).equals(result)) {
           break; // 鎖已被釋放或被其他進(jìn)程獲取
          }
        }catch(InterruptedException e) {
          Thread.currentThread().interrupt();
         break;
        }
      }
    });

    watchdog.setDaemon(true);
    watchdog.start();
  }
}

/**
* 使用分布式鎖的示例
*/
@Service
publicclassOrderService{

 @Autowired
 privateDistributedLock distributedLock;

 @Autowired
 privateInventoryService inventoryService;

 /**
  * 創(chuàng)建訂單 - 使用分布式鎖保證冪等性
  */
 publicOrdercreateOrder(String orderNo, Long productId,intquantity){
    String lockKey ="lock"+ orderNo;
    String requestId = UUID.randomUUID().toString();

   try{
     // 獲取分布式鎖
     if(!distributedLock.tryLock(lockKey, requestId,30000)) {
       thrownewRuntimeException("獲取鎖失敗，請(qǐng)稍后重試");
      }

     // 檢查訂單是否已存在（冪等性檢查）
      Order existingOrder = orderMapper.findByOrderNo(orderNo);
     if(existingOrder !=null) {
       returnexistingOrder; // 返回已存在的訂單
      }

     // 扣減庫(kù)存
     if(!inventoryService.decreaseStock(productId, quantity)) {
       thrownewRuntimeException("庫(kù)存不足");
      }

     // 創(chuàng)建訂單
      Order order =newOrder();
      order.setOrderNo(orderNo);
      order.setProductId(productId);
      order.setQuantity(quantity);
      orderMapper.insert(order);

     returnorder;

    }finally{
     // 釋放鎖
      distributedLock.unlock(lockKey, requestId);
    }
  }
}


	

	3.2 實(shí)際應(yīng)用案例

	3.2.1 秒殺系統(tǒng)防超賣

	
/**
* 秒殺系統(tǒng)防超賣方案
*/
@Service
publicclassSeckillService{

 @Autowired
 privateRedisTemplate redisTemplate;

 @Autowired
 privateJdbcTemplate jdbcTemplate;

 /**
  * 方案1：Redis預(yù)扣庫(kù)存 + 異步入庫(kù)
  */
 publicSeckillResultseckillWithRedis(Long userId, Long productId){
    String stockKey ="seckill"+ productId;
    String orderKey ="seckill"+ productId;

   // 1. 檢查是否已購(gòu)買（防止重復(fù)購(gòu)買）
    Boolean isMember = redisTemplate.opsForSet().isMember(orderKey, userId);
   if(Boolean.TRUE.equals(isMember)) {
     returnSeckillResult.fail("您已參與過(guò)此活動(dòng)");
    }

   // 2. 預(yù)扣庫(kù)存（原子操作）
    Long stock = redisTemplate.opsForValue().decrement(stockKey);
   if(stock ==null|| stock = ?
      """;

     intaffected = jdbcTemplate.update(sql, quantity, productId, quantity);

     if(affected >0) {
       // 扣減成功，創(chuàng)建訂單
        String orderNo = createOrder(userId, productId, quantity);
       returnSeckillResult.success(orderNo);
      }

     // 可能是庫(kù)存不足或版本沖突，檢查庫(kù)存
      Integer stock = jdbcTemplate.queryForObject(
       "SELECT stock FROM inventory WHERE product_id = ?",
        Integer.class,productId
      );

     if(stock ==null|| stock < quantity) {
? ? ? ? ? ? ? ??return?SeckillResult.fail("庫(kù)存不足");
? ? ? ? ? ? }

? ? ? ? ? ??// 版本沖突，短暫等待后重試
? ? ? ? ? ??try?{
? ? ? ? ? ? ? ? Thread.sleep(10?+ (long)(Math.random() *?30));
? ? ? ? ? ? }?catch?(InterruptedException e) {
? ? ? ? ? ? ? ? Thread.currentThread().interrupt();
? ? ? ? ? ? ? ??break;
? ? ? ? ? ? }
? ? ? ? }

? ? ? ??return?SeckillResult.fail("系統(tǒng)繁忙，請(qǐng)稍后重試");
? ? }
}


	

	3.2.2 死鎖自動(dòng)檢測(cè)和告警

	
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
MySQL死鎖監(jiān)控和告警腳本
"""

importpymysql
importtime
importjson
importrequests
fromdatetimeimportdatetime


classDeadlockMonitor:
 """死鎖監(jiān)控器"""

 def__init__(self, host, user, password, database, alert_webhook=None):
    self.conn_params = {
     'host': host,
     'user': user,
     'password': password,
     'database': database,
     'charset':'utf8mb4'
    }
    self.alert_webhook = alert_webhook
    self.last_deadlock_info =None

 defget_innodb_status(self):
   """獲取InnoDB狀態(tài)"""
    conn = pymysql.connect(**self.conn_params)
   try:
     withconn.cursor()ascursor:
        cursor.execute("SHOW ENGINE INNODB STATUS")
        result = cursor.fetchone()
       returnresult[2]ifresultelseNone
   finally:
      conn.close()

 defparse_deadlock(self, status):
   """解析死鎖信息"""
   ifnotstatus:
     returnNone

    lines = status.split('
')
    in_deadlock_section =False
    deadlock_info = []
    current_section = []

   forlineinlines:
     if'LATEST DETECTED DEADLOCK'inline:
        in_deadlock_section =True
       continue

     ifin_deadlock_section:
       ifline.startswith('---')and'TRANSACTION'notinline:
         ifcurrent_section:
            deadlock_info.append('
'.join(current_section))
            current_section = []
         continue

       if'WE ROLL BACK'inline:
          current_section.append(line)
          deadlock_info.append('
'.join(current_section))
         break

        current_section.append(line)

   return'

'.join(deadlock_info)ifdeadlock_infoelseNone

 defget_lock_waits(self):
   """獲取當(dāng)前鎖等待情況"""
    conn = pymysql.connect(**self.conn_params)
   try:
     withconn.cursor(pymysql.cursors.DictCursor)ascursor:
        sql ="""
          SELECT
            r.trx_id AS waiting_trx_id,
            r.trx_mysql_thread_id AS waiting_thread,
            TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_seconds,
            r.trx_query AS waiting_query,
            b.trx_id AS blocking_trx_id,
            b.trx_mysql_thread_id AS blocking_thread,
            b.trx_query AS blocking_query
          FROM performance_schema.data_lock_waits w
          INNER JOIN information_schema.innodb_trx b
            ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID
          INNER JOIN information_schema.innodb_trx r
            ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
        """
        cursor.execute(sql)
       returncursor.fetchall()
   finally:
      conn.close()

 defalert(self, title, content):
   """發(fā)送告警"""
    print(f"[ALERT]{title}")
    print(content)

   ifself.alert_webhook:
     try:
        payload = {
         'msgtype':'markdown',
         'markdown': {
           'title': title,
           'text':f"##{title}

{content}"
          }
        }
        requests.post(self.alert_webhook, json=payload, timeout=5)
     exceptExceptionase:
        print(f"發(fā)送告警失敗:{e}")

 defcheck_deadlock(self):
   """檢查死鎖"""
    status = self.get_innodb_status()
    deadlock_info = self.parse_deadlock(status)

   ifdeadlock_infoanddeadlock_info != self.last_deadlock_info:
      self.last_deadlock_info = deadlock_info
      self.alert(
       "MySQL檢測(cè)到死鎖",
       f"**時(shí)間**:{datetime.now()}

**詳情**:
```
{deadlock_info[:2000]}
```"
      )
     returnTrue
   returnFalse

 defcheck_lock_waits(self, threshold_seconds=30):
   """檢查長(zhǎng)時(shí)間鎖等待"""
    lock_waits = self.get_lock_waits()

   forwaitinlock_waits:
     ifwait['wait_seconds']andwait['wait_seconds'] > threshold_seconds:
        self.alert(
         "MySQL鎖等待超時(shí)",
         f"**等待時(shí)間**:{wait['wait_seconds']}秒
"
         f"**等待線程**:{wait['waiting_thread']}
"
         f"**等待SQL**:{wait['waiting_query']}
"
         f"**阻塞線程**:{wait['blocking_thread']}
"
         f"**阻塞SQL**:{wait['blocking_query']}"
        )

 defrun(self, interval=10):
   """運(yùn)行監(jiān)控"""
    print(f"死鎖監(jiān)控已啟動(dòng)，檢查間隔:{interval}秒")

   whileTrue:
     try:
        self.check_deadlock()
        self.check_lock_waits(threshold_seconds=30)
     exceptExceptionase:
        print(f"監(jiān)控異常:{e}")

      time.sleep(interval)


if__name__ =='__main__':
  monitor = DeadlockMonitor(
    host='192.168.1.11',
    user='monitor',
    password='password',
    database='lock_demo',
    alert_webhook='https://your-webhook-url.com'
  )
  monitor.run()


	

	3.2.3 事務(wù)超時(shí)和慢事務(wù)監(jiān)控

	
-- 查詢運(yùn)行時(shí)間超過(guò)指定秒數(shù)的事務(wù)
SELECT
  trx_id,
  trx_mysql_thread_idASthread_id,
  trx_state,
  trx_started,
 TIMESTAMPDIFF(SECOND, trx_started,NOW())ASrunning_seconds,
  trx_rows_locked,
  trx_rows_modified,
  trx_lock_structs,
  trx_query
FROMinformation_schema.innodb_trx
WHERETIMESTAMPDIFF(SECOND, trx_started,NOW()) >60
ORDERBYrunning_secondsDESC;

-- 查詢持有鎖最多的事務(wù)
SELECT
  trx_id,
  trx_mysql_thread_id,
  trx_rows_locked,
  trx_lock_structs,
  trx_tables_locked,
  trx_query
FROMinformation_schema.innodb_trx
ORDERBYtrx_rows_lockedDESC
LIMIT10;

-- 查詢鎖定行數(shù)最多的表
SELECT
  object_schema,
  object_name,
 COUNT(*)aslock_count
FROMperformance_schema.data_locks
WHERElock_type ='RECORD'
GROUPBYobject_schema, object_name
ORDERBYlock_countDESC;

-- 創(chuàng)建慢事務(wù)告警存儲(chǔ)過(guò)程
DELIMITER //

CREATEPROCEDUREcheck_slow_transactions(INthreshold_secondsINT)
BEGIN
 DECLAREdoneINTDEFAULTFALSE;
 DECLAREv_trx_idVARCHAR(100);
 DECLAREv_thread_idBIGINT;
 DECLAREv_running_secondsINT;
 DECLAREv_queryTEXT;

 DECLAREcurCURSORFOR
   SELECT
      trx_id,
      trx_mysql_thread_id,
     TIMESTAMPDIFF(SECOND, trx_started,NOW()),
      trx_query
   FROMinformation_schema.innodb_trx
   WHERETIMESTAMPDIFF(SECOND, trx_started,NOW()) > threshold_seconds;

 DECLARECONTINUEHANDLERFORNOTFOUNDSETdone =TRUE;

 -- 創(chuàng)建告警日志表
 CREATETABLEIFNOTEXISTSslow_transaction_log (
   idBIGINTAUTO_INCREMENT PRIMARYKEY,
    trx_idVARCHAR(100),
    thread_idBIGINT,
    running_secondsINT,
   queryTEXT,
    logged_at DATETIMEDEFAULTCURRENT_TIMESTAMP
  );

  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO v_trx_id, v_thread_id, v_running_seconds, v_query;
    IF done THEN
      LEAVE read_loop;
   ENDIF;

   -- 記錄慢事務(wù)
   INSERTINTOslow_transaction_log (trx_id, thread_id, running_seconds,query)
   VALUES(v_trx_id, v_thread_id, v_running_seconds, v_query);

 ENDLOOP;

  CLOSE cur;
END//

DELIMITER ;

-- 使用Event定期檢查
CREATEEVENTIFNOTEXISTScheck_slow_transactions_event
ONSCHEDULE EVERY1MINUTE
DOCALLcheck_slow_transactions(60);


	

	四、最佳實(shí)踐和注意事項(xiàng)

	4.1 最佳實(shí)踐

	4.1.1 事務(wù)設(shè)計(jì)原則

	
-- 1. 事務(wù)盡量短小
-- 差：大事務(wù)
STARTTRANSACTION;
-- 處理100萬(wàn)條記錄
UPDATEordersSETstatus=1WHEREcreated_at  0DO
   STARTTRANSACTION;

   UPDATEorders
   SETstatus=1
   WHEREcreated_at 

	

	4.1.2 鎖優(yōu)化策略

	
-- 1. 盡量使用索引訪問(wèn)數(shù)據(jù)
-- 差：無(wú)索引導(dǎo)致鎖表
UPDATEordersSETstatus=1WHEREorder_date ='2024-01-01';
-- 如果order_date沒(méi)有索引，可能鎖定大量行

-- 好：有索引時(shí)鎖定范圍精確
CREATEINDEXidx_order_dateONorders(order_date);
UPDATEordersSETstatus=1WHEREorder_date ='2024-01-01';

-- 2. 減少鎖定范圍
-- 差：鎖定所有匹配的行
SELECT*FROMordersWHEREuser_id =1FORUPDATE;

-- 好：只鎖定需要的行
SELECT*FROMordersWHEREuser_id =1ANDstatus=0FORUPDATE;

-- 3. 合理使用鎖模式
-- 只讀場(chǎng)景使用共享鎖
SELECT*FROMordersWHEREid=1LOCKINSHAREMODE;

-- 需要修改時(shí)才使用排他鎖
SELECT*FROMordersWHEREid=1FORUPDATE;

-- 4. 避免鎖升級(jí)
-- 差：從共享鎖升級(jí)到排他鎖可能導(dǎo)致死鎖
SELECT*FROMordersWHEREid=1LOCKINSHAREMODE;
-- 后續(xù)需要更新...
UPDATEordersSETstatus=1WHEREid=1; -- 可能死鎖

-- 好：直接使用排他鎖
SELECT*FROMordersWHEREid=1FORUPDATE;
UPDATEordersSETstatus=1WHEREid=1;


	

	4.1.3 隔離級(jí)別選擇

	
-- 不同場(chǎng)景的隔離級(jí)別推薦

-- 1. 高并發(fā)讀寫場(chǎng)景：READ COMMITTED
-- 優(yōu)點(diǎn)：鎖范圍小，不使用間隙鎖
-- 缺點(diǎn)：可能出現(xiàn)不可重復(fù)讀
SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;

-- 2. 金融交易場(chǎng)景：REPEATABLE READ（默認(rèn)）
-- 優(yōu)點(diǎn)：一致性讀，防止幻讀
-- 缺點(diǎn)：間隙鎖可能導(dǎo)致更多死鎖
SETSESSIONTRANSACTIONISOLATIONLEVELREPEATABLEREAD;

-- 3. 報(bào)表查詢場(chǎng)景：使用一致性快照
STARTTRANSACTIONWITHCONSISTENTSNAPSHOT;
SELECT*FROMordersWHERE...;
-- 讀取的是事務(wù)開始時(shí)的快照，不會(huì)被其他事務(wù)影響
COMMIT;

-- 4. 批量導(dǎo)入場(chǎng)景：可以臨時(shí)使用READ UNCOMMITTED
SETSESSIONTRANSACTIONISOLATIONLEVELREADUNCOMMITTED;
-- 導(dǎo)入完成后恢復(fù)
SETSESSIONTRANSACTIONISOLATIONLEVELREPEATABLEREAD;


	

	4.2 注意事項(xiàng)

	4.2.1 配置注意

				配置項(xiàng)
			

				建議值
			

				說(shuō)明
		

				innodb_lock_wait_timeout
			

				10-50
			

				鎖等待超時(shí)，根據(jù)業(yè)務(wù)調(diào)整
		

				innodb_deadlock_detect
			

				ON
			

				開啟死鎖檢測(cè)
		

				innodb_print_all_deadlocks
			

				ON
			

				記錄所有死鎖到錯(cuò)誤日志
		

				transaction_isolation
			

				READ-COMMITTED / REPEATABLE-READ
			

				根據(jù)場(chǎng)景選擇
		

				innodb_rollback_on_timeout
			

				OFF
			

				超時(shí)時(shí)只回滾當(dāng)前語(yǔ)句，不回滾整個(gè)事務(wù)
		

				autocommit
			

				ON
			

				默認(rèn)開啟自動(dòng)提交
		

	4.2.2 常見錯(cuò)誤

				錯(cuò)誤類型
			

				錯(cuò)誤信息
			

				原因分析
			

				解決方案
		

				死鎖
			

				Deadlock found
			

				循環(huán)等待
			

				固定訪問(wèn)順序
		

				鎖超時(shí)
			

				Lock wait timeout exceeded
			

				持鎖時(shí)間過(guò)長(zhǎng)
			

				減小事務(wù)，增加超時(shí)
		

				事務(wù)太大
			

				Transaction too large
			

				修改行數(shù)過(guò)多
			

				分批處理
		

				表鎖
			

				Table lock wait
			

				無(wú)索引導(dǎo)致表鎖
			

				添加適當(dāng)索引
		

				間隙鎖沖突
			

				Conflict on gap lock
			

				并發(fā)插入同一間隙
			

				降低隔離級(jí)別
		

	4.2.3 死鎖預(yù)防清單

	
開發(fā)階段:
-固定訪問(wèn)順序:多表操作按表名或主鍵排序
-減小事務(wù)范圍:只在必要時(shí)開啟事務(wù)
-使用低隔離級(jí)別:非必要不用REPEATABLEREAD
-添加必要索引:避免全表掃描鎖定

部署階段:
-開啟死鎖檢測(cè):innodb_deadlock_detect=ON
-設(shè)置合理超時(shí):innodb_lock_wait_timeout=10
-記錄死鎖日志:innodb_print_all_deadlocks=ON
-配置監(jiān)控告警:死鎖次數(shù)、鎖等待時(shí)間

運(yùn)維階段:
-定期分析死鎖:查看SHOWENGINEINNODBSTATUS
-監(jiān)控長(zhǎng)事務(wù):超過(guò)60秒的事務(wù)告警
-監(jiān)控鎖等待:等待超過(guò)10秒告警
-分析慢查詢:優(yōu)化持鎖時(shí)間長(zhǎng)的SQL


	

	五、故障排查和監(jiān)控

	5.1 故障排查

	5.1.1 死鎖分析

	
-- 查看最近的死鎖信息
SHOWENGINEINNODBSTATUSG

-- 輸出中的關(guān)鍵部分：
-- LATEST DETECTED DEADLOCK
-- ------------------------
-- 2024-01-01 1000 0x7f...
-- *** (1) TRANSACTION:
-- TRANSACTION 12345, ACTIVE 1 sec starting index read
-- mysql tables in use 1, locked 1
-- LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
-- MySQL thread id 100, OS thread handle 123, query id 456 192.168.1.10 app_user updating
-- UPDATE accounts SET balance = balance - 100 WHERE user_id = 1
--
-- *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
-- RECORD LOCKS space id 123 page no 3 n bits 72 index uk_user_id of table `db`.`accounts`
-- trx id 12345 lock_mode X locks rec but not gap waiting
--
-- *** (2) TRANSACTION:
-- TRANSACTION 12346, ACTIVE 1 sec starting index read
-- ...
--
-- *** (2) HOLDS THE LOCK(S):
-- RECORD LOCKS space id 123 page no 3 n bits 72 index uk_user_id of table `db`.`accounts`
-- ...
--
-- *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
-- ...
--
-- *** WE ROLL BACK TRANSACTION (1)

-- 分析步驟：
-- 1. 找到兩個(gè)事務(wù)的SQL
-- 2. 分析鎖等待關(guān)系
-- 3. 確定死鎖原因
-- 4. 制定解決方案


	

	死鎖日志解讀

	
# 從錯(cuò)誤日志中提取死鎖信息
grep -A 100"LATEST DETECTED DEADLOCK"/var/log/mysql/error.log | head -100

# 使用pt-deadlock-logger記錄死鎖
pt-deadlock-logger --host=localhost --user=root --password=xxx 
  --dest h=localhost,D=monitor,t=deadlocks 
  --run-time=1h


	

	5.1.2 鎖等待分析

	
-- 查看當(dāng)前鎖等待
SELECT
  waiting.trx_idASwaiting_trx_id,
  waiting.trx_mysql_thread_idASwaiting_thread,
  waiting.trx_queryASwaiting_query,
 TIMESTAMPDIFF(SECOND, waiting.trx_wait_started,NOW())ASwaiting_seconds,
  blocking.trx_idASblocking_trx_id,
  blocking.trx_mysql_thread_idASblocking_thread,
  blocking.trx_queryASblocking_query,
 TIMESTAMPDIFF(SECOND, blocking.trx_started,NOW())ASblocking_duration
FROMinformation_schema.innodb_trx waiting
INNERJOINperformance_schema.data_lock_waits dlw
 ONwaiting.trx_id = dlw.REQUESTING_ENGINE_TRANSACTION_ID
INNERJOINinformation_schema.innodb_trx blocking
 ONblocking.trx_id = dlw.BLOCKING_ENGINE_TRANSACTION_ID;

-- 查看鎖的詳細(xì)信息
SELECT
  dl.ENGINE_LOCK_ID,
  dl.ENGINE_TRANSACTION_ID,
  dl.OBJECT_SCHEMA,
  dl.OBJECT_NAME,
  dl.INDEX_NAME,
  dl.LOCK_TYPE,
  dl.LOCK_MODE,
  dl.LOCK_STATUS,
  dl.LOCK_DATA
FROMperformance_schema.data_locks dl;

-- 終止阻塞事務(wù)（謹(jǐn)慎使用）
-- 先確認(rèn)阻塞線程ID
KILL12345;


	

	5.1.3 長(zhǎng)事務(wù)分析

	
-- 查找運(yùn)行時(shí)間最長(zhǎng)的事務(wù)
SELECT
  trx_id,
  trx_mysql_thread_idASthread_id,
  trx_state,
  trx_started,
 NOW() - trx_startedASrunning_time,
  trx_rows_locked,
  trx_rows_modified,
  trx_tables_in_use,
  trx_tables_locked,
  trx_query
FROMinformation_schema.innodb_trx
ORDERBYtrx_startedASC;

-- 查看事務(wù)對(duì)應(yīng)的連接信息
SELECT
  t.trx_id,
  t.trx_mysql_thread_id,
  p.user,
  p.host,
  p.db,
  p.command,
  p.time,
  p.state,
  t.trx_query
FROMinformation_schema.innodb_trx t
INNERJOINinformation_schema.processlist p
 ONt.trx_mysql_thread_id = p.id;

-- 查看事務(wù)的undo日志量（判斷回滾代價(jià)）
SELECT
  trx_id,
  trx_undo_record_size,
  trx_undo_record_size /1024/1024ASundo_mb
FROMinformation_schema.innodb_trx
WHEREtrx_undo_record_size >0;


	

	5.2 性能監(jiān)控

	5.2.1 關(guān)鍵指標(biāo)

	
-- InnoDB鎖相關(guān)指標(biāo)
SHOWGLOBALSTATUSLIKE'Innodb_row_lock%';
-- Innodb_row_lock_current_waits: 當(dāng)前等待鎖的數(shù)量
-- Innodb_row_lock_time: 總鎖等待時(shí)間（毫秒）
-- Innodb_row_lock_time_avg: 平均鎖等待時(shí)間
-- Innodb_row_lock_time_max: 最大鎖等待時(shí)間
-- Innodb_row_lock_waits: 總鎖等待次數(shù)

-- 死鎖次數(shù)
SHOWGLOBALSTATUSLIKE'Innodb_deadlocks';

-- 鎖內(nèi)存使用
SHOWGLOBALSTATUSLIKE'Innodb_row_lock_memory';

-- 計(jì)算鎖爭(zhēng)用率
SELECT
  (SELECTVARIABLE_VALUEFROMperformance_schema.global_statusWHEREVARIABLE_NAME ='Innodb_row_lock_waits') /
  (SELECTVARIABLE_VALUEFROMperformance_schema.global_statusWHEREVARIABLE_NAME ='Questions') *100
ASlock_contention_percent;


	

	5.2.2 監(jiān)控指標(biāo)表

				指標(biāo)類別
			

				指標(biāo)名稱
			

				含義
			

				告警閾值
		

				死鎖
			

				Innodb_deadlocks
			

				死鎖累計(jì)次數(shù)
			

				增長(zhǎng)率 > 1/min
		

				鎖等待
			

				Innodb_row_lock_waits
			

				鎖等待累計(jì)次數(shù)
			

				增長(zhǎng)率 > 10/sec
		

				鎖時(shí)間
			

				Innodb_row_lock_time_avg
			

				平均鎖等待時(shí)間(ms)
			

				> 1000
		

				當(dāng)前等待
			

				Innodb_row_lock_current_waits
			

				當(dāng)前等待鎖數(shù)量
			

				> 10
		

				長(zhǎng)事務(wù)
			

				運(yùn)行超過(guò)60秒的事務(wù)
			

				長(zhǎng)事務(wù)數(shù)量
			

				> 0
		

				鎖表
			

				Tables_locks_waited
			

				表鎖等待次數(shù)
			

				> 0
		

	5.2.3 Prometheus告警規(guī)則

	
groups:
-name:mysql-lock-alerts
 rules:
  -alert:MySQLDeadlocks
   expr:increase(mysql_global_status_innodb_deadlocks[5m])>0
   for:1m
   labels:
    severity:warning
   annotations:
    summary:"MySQL發(fā)生死鎖"
    description:"{{ $labels.instance }}在過(guò)去5分鐘內(nèi)發(fā)生{{ $value }}次死鎖"

  -alert:MySQLHighLockWaits
   expr:rate(mysql_global_status_innodb_row_lock_waits[5m])>10
   for:5m
   labels:
    severity:warning
   annotations:
    summary:"MySQL鎖等待頻繁"
    description:"{{ $labels.instance }}鎖等待率為{{ $value }}/秒"

  -alert:MySQLLongLockWait
   expr:mysql_global_status_innodb_row_lock_time_avg>1000
   for:5m
   labels:
    severity:warning
   annotations:
    summary:"MySQL鎖等待時(shí)間過(guò)長(zhǎng)"
    description:"{{ $labels.instance }}平均鎖等待時(shí)間{{ $value }}ms"

  -alert:MySQLLongTransaction
   expr:mysql_info_schema_innodb_trx_running_seconds>60
   for:1m
   labels:
    severity:critical
   annotations:
    summary:"MySQL存在長(zhǎng)事務(wù)"
    description:"{{ $labels.instance }}存在運(yùn)行超過(guò)60秒的事務(wù)"


	

	5.3 備份與恢復(fù)

	5.3.1 事務(wù)日志備份

	
#!/bin/bash
# MySQL binlog備份腳本

BACKUP_DIR="/data/backup/binlog"
MYSQL_USER="backup"
MYSQL_PASS="password"
MYSQL_HOST="localhost"
RETENTION_DAYS=7

mkdir -p$BACKUP_DIR

# 獲取當(dāng)前binlog文件列表
mysql -u$MYSQL_USER-p$MYSQL_PASS-h$MYSQL_HOST-e"SHOW BINARY LOGS;"| 
  tail -n +2 | awk'{print $1}'|whilereadbinlog;do

 # 復(fù)制binlog到備份目錄
 if[ ! -f"$BACKUP_DIR/$binlog"];then
    mysqlbinlog -u$MYSQL_USER-p$MYSQL_PASS-h$MYSQL_HOST
      --read-from-remote-server$binlog>$BACKUP_DIR/$binlog.sql
    gzip$BACKUP_DIR/$binlog.sql
   echo"備份$binlog完成"
 fi
done

# 清理過(guò)期備份
find$BACKUP_DIR-name"*.sql.gz"-mtime +$RETENTION_DAYS-delete

echo"Binlog備份完成"


	

	5.3.2 死鎖恢復(fù)流程

	
-- 死鎖后的恢復(fù)步驟

-- 1. 確認(rèn)事務(wù)狀態(tài)
SELECT
  trx_id,
  trx_state,
  trx_started,
  trx_mysql_thread_id,
  trx_query
FROMinformation_schema.innodb_trx;

-- 2. 如果事務(wù)被回滾，應(yīng)用程序需要重試
-- 檢查應(yīng)用程序的重試邏輯

-- 3. 如果需要手動(dòng)回滾
ROLLBACK;

-- 4. 檢查數(shù)據(jù)一致性
-- 根據(jù)業(yè)務(wù)邏輯驗(yàn)證數(shù)據(jù)

-- 5. 分析死鎖原因
SHOWENGINEINNODBSTATUSG
-- 找到LATEST DETECTED DEADLOCK部分

-- 6. 記錄和上報(bào)
-- 將死鎖信息記錄到監(jiān)控系統(tǒng)


	

	六、總結(jié)

	6.1 技術(shù)要點(diǎn)回顧

	MySQL事務(wù)與鎖機(jī)制的核心要點(diǎn)：

	1. ACID特性

	原子性：通過(guò)undo log實(shí)現(xiàn)

	一致性：通過(guò)約束和應(yīng)用邏輯保證

	隔離性：通過(guò)鎖和MVCC實(shí)現(xiàn)

	持久性：通過(guò)redo log保證

	2. 鎖類型

	行鎖：記錄鎖、間隙鎖、臨鍵鎖

	表鎖：意向鎖、MDL鎖

	鎖模式：共享鎖、排他鎖

	3. 死鎖處理

	預(yù)防：固定訪問(wèn)順序、減小事務(wù)

	檢測(cè)：innodb_deadlock_detect

	恢復(fù)：自動(dòng)回滾一個(gè)事務(wù)

	4. 最佳實(shí)踐

	事務(wù)盡量短小

	按固定順序訪問(wèn)資源

	合理選擇隔離級(jí)別

	使用合適的鎖策略

	6.2 進(jìn)階學(xué)習(xí)方向

				方向
			

				內(nèi)容
			

				推薦資源
		

				MVCC原理
			

				版本鏈、ReadView機(jī)制
			

				《MySQL技術(shù)內(nèi)幕：InnoDB存儲(chǔ)引擎》
		

				鎖算法
			

				B+樹鎖定協(xié)議
			

				MySQL源碼
		

				分布式事務(wù)
			

				XA、TCC、SAGA
			

				Seata框架文檔
		

				死鎖檢測(cè)算法
			

				等待圖、超時(shí)檢測(cè)
			

				數(shù)據(jù)庫(kù)系統(tǒng)概論
		

				性能調(diào)優(yōu)
			

				鎖粒度優(yōu)化
			

				Percona博客
		

	6.3 參考資料

	MySQL官方文檔：https://dev.mysql.com/doc/

	《MySQL技術(shù)內(nèi)幕：InnoDB存儲(chǔ)引擎》第2版

	《高性能MySQL》第4版

	Percona Blog：https://www.percona.com/blog/

	附錄

	A. 命令速查表

				命令
			

				說(shuō)明
			

				示例
		

				START TRANSACTION
			

				開始事務(wù)
			

				START TRANSACTION;
		

				COMMIT
			

				提交事務(wù)
			

				COMMIT;
		

				ROLLBACK
			

				回滾事務(wù)
			

				ROLLBACK;
		

				SELECT ... FOR UPDATE
			

				排他鎖查詢
			

				SELECT * FROM t WHERE id=1 FOR UPDATE;
		

				SELECT ... FOR SHARE
			

				共享鎖查詢
			

				SELECT * FROM t WHERE id=1 FOR SHARE;
		

				SHOW ENGINE INNODB STATUS
			

				查看InnoDB狀態(tài)
			

				SHOW ENGINE INNODB STATUSG
		

				KILL
			

				終止連接
			

				KILL 12345;
		

	B. 配置參數(shù)詳解

				參數(shù)
			

				默認(rèn)值
			

				說(shuō)明
			

				建議
		

				transaction_isolation
			

				REPEATABLE-READ
			

				默認(rèn)隔離級(jí)別
			

				根據(jù)場(chǎng)景選擇
		

				innodb_lock_wait_timeout
			

				50
			

				鎖等待超時(shí)(秒)
			

				10-30
		

				innodb_deadlock_detect
			

				ON
			

				死鎖檢測(cè)開關(guān)
			

				ON
		

				innodb_print_all_deadlocks
			

				OFF
			

				記錄所有死鎖
			

				ON
		

				innodb_rollback_on_timeout
			

				OFF
			

				超時(shí)回滾整個(gè)事務(wù)
			

				OFF
		

				autocommit
			

				ON
			

				自動(dòng)提交
			

				ON
		

	C. 術(shù)語(yǔ)表

				術(shù)語(yǔ)
			

				英文
			

				說(shuō)明
		

				臟讀
			

				Dirty Read
			

				讀取未提交的數(shù)據(jù)
		

				不可重復(fù)讀
			

				Non-Repeatable Read
			

				同一事務(wù)兩次讀取結(jié)果不同
		

				幻讀
			

				Phantom Read
			

				查詢結(jié)果集行數(shù)變化
		

				死鎖
			

				Deadlock
			

				循環(huán)等待鎖
		

				間隙鎖
			

				Gap Lock
			

				鎖定索引間隙
		

				臨鍵鎖
			

				Next-Key Lock
			

				記錄鎖+間隙鎖
		

				MVCC
			

				Multi-Version Concurrency Control
			

				多版本并發(fā)控制
		

				Undo Log
			

				-
			

				回滾日志
		

				Redo Log
			

				-
			

				重做日志
		

				兩階段鎖
			

				Two-Phase Locking
			

				加鎖和解鎖分兩階段