一、概述

1.1 背景介紹

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

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

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

1.2 技術(shù)特點

MySQL InnoDB存儲引擎的事務(wù)和鎖機制具有以下特點：

ACID事務(wù)特性

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

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

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

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

多粒度鎖機制

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

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

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

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

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

讀不阻塞寫，寫不阻塞讀

通過undo log實現(xiàn)一致性讀

支持多種隔離級別

1.3 適用場景

1.4 環(huán)境要求

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

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

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

二、詳細(xì)步驟

2.1 準(zhǔn)備工作

2.1.1 ACID特性深入理解

原子性（Atomicity）

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

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

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

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

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

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

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

一致性（Consistency）

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

-- 一致性示例：確?？偨痤~不變
-- 假設(shè)系統(tǒng)中只有兩個賬戶，總金額應(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ù)庫約束共同保證
-- 比如：CHECK約束、外鍵約束、觸發(fā)器等

隔離性（Isolation）

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

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

-- 會話1
STARTTRANSACTION;
UPDATEproductsSETstock = stock -1WHEREid=1;
-- 此時還未提交

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

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

持久性（Durability）

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

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

-- 相關(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ù)隔離級別

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

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

-- 設(shè)置會話隔離級別
SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;

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

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

臟讀演示

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

-- 會話2
STARTTRANSACTION;
UPDATEaccountsSETbalance =500WHEREuser_id =1;
-- 未提交

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

-- 會話2
ROLLBACK; -- 回滾

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

不可重復(fù)讀演示

-- 會話1（READ COMMITTED級別）
SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;
STARTTRANSACTION;

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

-- 會話2
UPDATEaccountsSETbalance =500WHEREuser_id =1;
COMMIT;

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

COMMIT;

幻讀演示

-- 會話1（即使REPEATABLE READ也可能有幻讀場景）
STARTTRANSACTION;

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

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

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

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

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

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

-- 創(chuàng)建賬戶表
CREATETABLEaccounts (
 idBIGINTUNSIGNEDAUTO_INCREMENT PRIMARYKEY,
  user_idBIGINTUNSIGNEDNOTNULL,
  balanceDECIMAL(15,2)NOTNULLDEFAULT0.00,
 versionINTUNSIGNEDNOTNULLDEFAULT0, -- 樂觀鎖版本號
  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)建庫存表
CREATETABLEinventory (
 idBIGINTUNSIGNEDAUTO_INCREMENT PRIMARYKEY,
  product_idBIGINTUNSIGNEDNOTNULL,
  stockINTUNSIGNEDNOTNULLDEFAULT0,
 versionINTUNSIGNEDNOTNULLDEFAULT0,
 UNIQUEKEYuk_product_id (product_id)
)ENGINE=InnoDB;

-- 插入測試數(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);

-- 生成訂單測試數(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 新語法
SELECT*FROMaccountsWHEREuser_id =1FORSHARE;

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

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

2. 意向鎖（IS/IX鎖）

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

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

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

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

3. 記錄鎖（Record Lock）

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

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

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

4. 間隙鎖（Gap Lock）

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

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

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

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

5. 臨鍵鎖（Next-Key Lock）

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

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

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

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

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

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

-- 會話2
STARTTRANSACTION;
INSERTINTOaccounts (id, user_id, balance)VALUES(4,4,2000);
-- 也可以獲取(1,5)間隙的插入意向鎖，插入id=4
-- 兩個插入可以并發(fā)執(zhí)行，因為插入位置不沖突

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 死鎖檢測配置

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

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

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

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

2.3 啟動和驗證

2.3.1 驗證鎖機制

-- 測試記錄鎖
-- 會話1
STARTTRANSACTION;
SELECT*FROMaccountsWHEREid=1FORUPDATE;
-- 不提交，保持鎖定

-- 會話2
STARTTRANSACTION;
-- 嘗試更新同一行
UPDATEaccountsSETbalance = balance +100WHEREid=1;
-- 此語句會等待，因為id=1被會話1鎖定

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

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

2.3.2 驗證死鎖檢測

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

-- 會話2
STARTTRANSACTION;
UPDATEaccountsSETbalance = balance -100WHEREid=2;

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

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

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

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

三、示例代碼和配置

3.1 完整配置示例

3.1.1 死鎖案例分析

案例1：相反順序更新

-- 最常見的死鎖場景：兩個事務(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. 降低隔離級別到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可能死鎖，因為它們都在等待鎖

-- 解決方案：
-- 1. 使用INSERT ... ON DUPLICATE KEY UPDATE
-- 2. 使用INSERT IGNORE
-- 3. 先查詢再插入（在應(yīng)用層處理）

3.1.2 悲觀鎖實現(xiàn)

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

 @Autowired
 privateJdbcTemplate jdbcTemplate;

 /**
  * 轉(zhuǎn)賬 - 悲觀鎖實現(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 樂觀鎖實現(xiàn)

	
/**
* 樂觀鎖實現(xiàn)庫存扣減
* 使用版本號或CAS機制
*/
@Service
publicclassInventoryService{

 @Autowired
 privateJdbcTemplate jdbcTemplate;

 /**
  * 扣減庫存 - 樂觀鎖實現(xiàn)
  *@returntrue 成功，false 失敗（庫存不足或版本沖突）
  */
 publicbooleandecreaseStock(Long productId,intquantity){
   intmaxRetries =3;

   for(inti =0; i < maxRetries; i++) {
? ? ? ? ? ??// 查詢當(dāng)前庫存和版本號
? ? ? ? ? ? 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");

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

? ? ? ? ? ??// 使用版本號進(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)); // 隨機延遲
      }catch(InterruptedException e) {
        Thread.currentThread().interrupt();
      }
    }

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

 /**
  * 扣減庫存 - 使用行級條件更新（更簡潔的樂觀鎖）
  */
 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 分布式鎖實現(xiàn)

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

 @Autowired
 privateStringRedisTemplate redisTemplate;

 privatestaticfinallongDEFAULT_EXPIRE_TIME =30000; // 30秒

 /**
  * 獲取鎖
  *@paramlockKey 鎖的key
  *@paramrequestId 請求標(biāo)識（用于釋放鎖時驗證）
  *@paramexpireTime 過期時間（毫秒）
  */
 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);
  }

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

   if(locked) {
     // 啟動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("獲取鎖失敗，請稍后重試");
      }

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

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

     // 創(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 實際應(yīng)用案例

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

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

 @Autowired
 privateRedisTemplate redisTemplate;

 @Autowired
 privateJdbcTemplate jdbcTemplate;

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

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

   // 2. 預(yù)扣庫存（原子操作）
    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);
      }

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

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

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

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


	

	3.2.2 死鎖自動檢測和告警

	
#!/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檢測到死鎖",
       f"**時間**:{datetime.now()}

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

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

   forwaitinlock_waits:
     ifwait['wait_seconds']andwait['wait_seconds'] > threshold_seconds:
        self.alert(
         "MySQL鎖等待超時",
         f"**等待時間**:{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):
   """運行監(jiān)控"""
    print(f"死鎖監(jiān)控已啟動，檢查間隔:{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ù)超時和慢事務(wù)監(jiān)控

	
-- 查詢運行時間超過指定秒數(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ù)告警存儲過程
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);


	

	四、最佳實踐和注意事項

	4.1 最佳實踐

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

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

   UPDATEorders
   SETstatus=1
   WHEREcreated_at 

	

	4.1.2 鎖優(yōu)化策略

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

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

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

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

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

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

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

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


	

	4.1.3 隔離級別選擇

	
-- 不同場景的隔離級別推薦

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

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

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

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


	

	4.2 注意事項

	4.2.1 配置注意

				配置項
			

				建議值
			

				說明
		

				innodb_lock_wait_timeout
			

				10-50
			

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

				innodb_deadlock_detect
			

				ON
			

				開啟死鎖檢測
		

				innodb_print_all_deadlocks
			

				ON
			

				記錄所有死鎖到錯誤日志
		

				transaction_isolation
			

				READ-COMMITTED / REPEATABLE-READ
			

				根據(jù)場景選擇
		

				innodb_rollback_on_timeout
			

				OFF
			

				超時時只回滾當(dāng)前語句，不回滾整個事務(wù)
		

				autocommit
			

				ON
			

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

	4.2.2 常見錯誤

				錯誤類型
			

				錯誤信息
			

				原因分析
			

				解決方案
		

				死鎖
			

				Deadlock found
			

				循環(huán)等待
			

				固定訪問順序
		

				鎖超時
			

				Lock wait timeout exceeded
			

				持鎖時間過長
			

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

				事務(wù)太大
			

				Transaction too large
			

				修改行數(shù)過多
			

				分批處理
		

				表鎖
			

				Table lock wait
			

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

				添加適當(dāng)索引
		

				間隙鎖沖突
			

				Conflict on gap lock
			

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

				降低隔離級別
		

	4.2.3 死鎖預(yù)防清單

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

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

運維階段:
-定期分析死鎖:查看SHOWENGINEINNODBSTATUS
-監(jiān)控長事務(wù):超過60秒的事務(wù)告警
-監(jiān)控鎖等待:等待超過10秒告警
-分析慢查詢:優(yōu)化持鎖時間長的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. 找到兩個事務(wù)的SQL
-- 2. 分析鎖等待關(guān)系
-- 3. 確定死鎖原因
-- 4. 制定解決方案


	

	死鎖日志解讀

	
# 從錯誤日志中提取死鎖信息
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 長事務(wù)分析

	
-- 查找運行時間最長的事務(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ù)對應(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日志量（判斷回滾代價）
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: 總鎖等待時間（毫秒）
-- Innodb_row_lock_time_avg: 平均鎖等待時間
-- Innodb_row_lock_time_max: 最大鎖等待時間
-- Innodb_row_lock_waits: 總鎖等待次數(shù)

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

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

-- 計算鎖爭用率
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
			

				死鎖累計次數(shù)
			

				增長率 > 1/min
		

				鎖等待
			

				Innodb_row_lock_waits
			

				鎖等待累計次數(shù)
			

				增長率 > 10/sec
		

				鎖時間
			

				Innodb_row_lock_time_avg
			

				平均鎖等待時間(ms)
			

				> 1000
		

				當(dāng)前等待
			

				Innodb_row_lock_current_waits
			

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

				> 10
		

				長事務(wù)
			

				運行超過60秒的事務(wù)
			

				長事務(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 }}在過去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鎖等待時間過長"
    description:"{{ $labels.instance }}平均鎖等待時間{{ $value }}ms"

  -alert:MySQLLongTransaction
   expr:mysql_info_schema_innodb_trx_running_seconds>60
   for:1m
   labels:
    severity:critical
   annotations:
    summary:"MySQL存在長事務(wù)"
    description:"{{ $labels.instance }}存在運行超過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

# 清理過期備份
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. 如果需要手動回滾
ROLLBACK;

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

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

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


	

	六、總結(jié)

	6.1 技術(shù)要點回顧

	MySQL事務(wù)與鎖機制的核心要點：

	1. ACID特性

	原子性：通過undo log實現(xiàn)

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

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

	持久性：通過redo log保證

	2. 鎖類型

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

	表鎖：意向鎖、MDL鎖

	鎖模式：共享鎖、排他鎖

	3. 死鎖處理

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

	檢測：innodb_deadlock_detect

	恢復(fù)：自動回滾一個事務(wù)

	4. 最佳實踐

	事務(wù)盡量短小

	按固定順序訪問資源

	合理選擇隔離級別

	使用合適的鎖策略

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

				方向
			

				內(nèi)容
			

				推薦資源
		

				MVCC原理
			

				版本鏈、ReadView機制
			

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

				鎖算法
			

				B+樹鎖定協(xié)議
			

				MySQL源碼
		

				分布式事務(wù)
			

				XA、TCC、SAGA
			

				Seata框架文檔
		

				死鎖檢測算法
			

				等待圖、超時檢測
			

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

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

				鎖粒度優(yōu)化
			

				Percona博客
		

	6.3 參考資料

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

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

	《高性能MySQL》第4版

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

	附錄

	A. 命令速查表

				命令
			

				說明
			

				示例
		

				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)值
			

				說明
			

				建議
		

				transaction_isolation
			

				REPEATABLE-READ
			

				默認(rèn)隔離級別
			

				根據(jù)場景選擇
		

				innodb_lock_wait_timeout
			

				50
			

				鎖等待超時(秒)
			

				10-30
		

				innodb_deadlock_detect
			

				ON
			

				死鎖檢測開關(guān)
			

				ON
		

				innodb_print_all_deadlocks
			

				OFF
			

				記錄所有死鎖
			

				ON
		

				innodb_rollback_on_timeout
			

				OFF
			

				超時回滾整個事務(wù)
			

				OFF
		

				autocommit
			

				ON
			

				自動提交
			

				ON
		

	C. 術(shù)語表

				術(shù)語
			

				英文
			

				說明
		

				臟讀
			

				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
			

				加鎖和解鎖分兩階段