MySQL事務與鎖機制詳解
一、概述
1.1 背景介紹
在我擔任某互聯網金融平臺SRE期間，曾遇到過一次嚴重的線上事故：凌晨3點，監控系統瘋狂告警，數據庫活躍連接數從平時的200飆升到2000，大量請求超時。緊急排查后發現，一個批量更新任務與在線交易產生了死鎖，導致數據庫連接被占滿。
這次事故持續了40分鐘，影響了上萬名用戶的交易。事后復盤發現，問題根源是開發團隊對MySQL事務和鎖機制理解不足，寫出了容易產生死鎖的代碼。
從那以后，我花了大量時間研究MySQL的事務和鎖機制，并總結出一套完整的排查和預防方法。本文將系統性地講解MySQL事務的ACID特性、鎖的工作原理，以及死鎖的排查和解決方案。
1.2 技術特點
MySQL InnoDB存儲引擎的事務和鎖機制具有以下特點：
ACID事務特性
Atomicity（原子性）：事務是不可分割的工作單位
Consistency（一致性）：事務執行前后數據保持一致
Isolation（隔離性）：并發事務之間相互隔離
Durability（持久性）：事務提交后數據永久保存
多粒度鎖機制
行鎖：鎖定單行記錄，并發度高
間隙鎖：鎖定索引間隙，防止幻讀
表鎖：鎖定整張表，開銷小但并發度低
意向鎖：表級鎖，用于協調行鎖和表鎖
MVCC多版本并發控制
讀不阻塞寫，寫不阻塞讀
通過undo log實現一致性讀
支持多種隔離級別
1.3 適用場景
| 場景類型 | 隔離級別 | 鎖策略 | 典型應用 |
|---|---|---|---|
| 高并發讀寫 | READ COMMITTED | 最小化鎖范圍 | 電商訂單 |
| 金融交易 | REPEATABLE READ | 行鎖+間隙鎖 | 轉賬、支付 |
| 報表統計 | READ COMMITTED | 快照讀 | 數據分析 |
| 庫存扣減 | REPEATABLE READ | SELECT FOR UPDATE | 秒殺系統 |
| 批量更新 | READ COMMITTED | 分批提交 | 數據遷移 |
1.4 環境要求
| 組件 | 版本要求 | 說明 |
|---|---|---|
| MySQL | 8.0.35+ / 8.4 LTS | 本文基于8.0.35版本 |
| 操作系統 | Rocky Linux 9 / Ubuntu 24.04 | 推薦Rocky Linux 9 |
| 存儲引擎 | InnoDB | 必須使用InnoDB |
| 內存 | 16GB+ | 足夠的緩沖池空間 |
關鍵配置要求：
-- 查看InnoDB相關配置 SHOWVARIABLESLIKE'innodb%'; -- 關鍵配置 innodb_buffer_pool_size = 8G -- 緩沖池大小 innodb_lock_wait_timeout = 50 -- 鎖等待超時（秒） innodb_deadlock_detect = ON -- 開啟死鎖檢測 innodb_print_all_deadlocks = ON -- 打印所有死鎖信息 transaction_isolation = REPEATABLE-READ -- 默認隔離級別
二、詳細步驟
2.1 準備工作
2.1.1 ACID特性深入理解
原子性（Atomicity）
事務中的所有操作要么全部成功，要么全部失敗回滾。MySQL通過undo log實現原子性。
-- 原子性示例：轉賬操作 STARTTRANSACTION; -- 操作1：扣減轉出賬戶余額 UPDATEaccountsSETbalance = balance -1000WHEREuser_id =1; -- 操作2：增加轉入賬戶余額 UPDATEaccountsSETbalance = balance +1000WHEREuser_id =2; -- 如果兩個操作都成功，提交事務 COMMIT; -- 如果任一操作失敗，回滾事務 -- ROLLBACK; -- 原子性保證： -- 1. 要么兩個賬戶都更新成功 -- 2. 要么兩個賬戶都保持原狀 -- 不會出現錢扣了但沒有到賬的情況
一致性（Consistency）
事務執行前后，數據庫從一個一致狀態轉換到另一個一致狀態。
-- 一致性示例：確保總金額不變 -- 假設系統中只有兩個賬戶，總金額應該始終為10000 -- 事務前檢查 SELECTSUM(balance)FROMaccounts; -- 結果：10000 STARTTRANSACTION; UPDATEaccountsSETbalance = balance -1000WHEREuser_id =1; UPDATEaccountsSETbalance = balance +1000WHEREuser_id =2; COMMIT; -- 事務后檢查 SELECTSUM(balance)FROMaccounts; -- 結果仍然：10000 -- 一致性由應用程序和數據庫約束共同保證 -- 比如：CHECK約束、外鍵約束、觸發器等
隔離性（Isolation）
并發執行的事務之間相互隔離，一個事務的中間狀態對其他事務不可見。
-- 隔離性示例：并發讀寫 -- 會話1 STARTTRANSACTION; UPDATEproductsSETstock = stock -1WHEREid=1; -- 此時還未提交 -- 會話2 SELECTstockFROMproductsWHEREid=1; -- 根據隔離級別，可能看到更新前或更新后的值 -- MySQL默認使用REPEATABLE READ隔離級別 -- 會話2看到的是事務開始時的快照，即更新前的值
持久性（Durability）
事務一旦提交，其結果就是永久性的，即使系統崩潰也不會丟失。
-- 持久性由redo log保證 -- 事務提交時，redo log會刷入磁盤 -- 相關配置 SHOWVARIABLESLIKE'innodb_flush_log_at_trx_commit'; -- innodb_flush_log_at_trx_commit = 1（默認） -- 每次事務提交都將redo log刷入磁盤 -- 最安全但性能略低 -- innodb_flush_log_at_trx_commit = 2 -- 每次提交寫入OS緩存，每秒刷盤 -- 性能好，但斷電可能丟失1秒數據 -- innodb_flush_log_at_trx_commit = 0 -- 每秒寫入OS緩存并刷盤 -- 性能最好，但可能丟失1秒數據
2.1.2 事務隔離級別
MySQL支持四種隔離級別，解決不同的并發問題：
| 隔離級別 | 臟讀 | 不可重復讀 | 幻讀 | 性能 |
|---|---|---|---|---|
| READ UNCOMMITTED | 可能 | 可能 | 可能 | 最高 |
| READ COMMITTED | 不可能 | 可能 | 可能 | 高 |
| REPEATABLE READ | 不可能 | 不可能 | InnoDB防止 | 中 |
| SERIALIZABLE | 不可能 | 不可能 | 不可能 | 最低 |
-- 查看當前隔離級別 SELECT@@transaction_isolation; -- 或 SHOWVARIABLESLIKE'transaction_isolation'; -- 設置會話隔離級別 SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED; -- 設置全局隔離級別（需要重連生效） SETGLOBALTRANSACTIONISOLATIONLEVELREADCOMMITTED; -- 在配置文件中設置 -- [mysqld] -- transaction-isolation = READ-COMMITTED
臟讀演示
-- 會話1（設置為READ UNCOMMITTED） SETSESSIONTRANSACTIONISOLATIONLEVELREADUNCOMMITTED; STARTTRANSACTION; -- 會話2 STARTTRANSACTION; UPDATEaccountsSETbalance =500WHEREuser_id =1; -- 未提交 -- 會話1 SELECTbalanceFROMaccountsWHEREuser_id =1; -- 結果：500（讀到了未提交的數據，即臟讀） -- 會話2 ROLLBACK; -- 回滾 -- 會話1再次查詢 SELECTbalanceFROMaccountsWHEREuser_id =1; -- 結果可能是原來的值，之前讀到的500是"臟數據"
不可重復讀演示
-- 會話1（READ COMMITTED級別） SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED; STARTTRANSACTION; SELECTbalanceFROMaccountsWHEREuser_id =1; -- 結果：1000 -- 會話2 UPDATEaccountsSETbalance =500WHEREuser_id =1; COMMIT; -- 會話1再次查詢 SELECTbalanceFROMaccountsWHEREuser_id =1; -- 結果：500（同一事務內兩次讀取結果不同，即不可重復讀） COMMIT;
幻讀演示
-- 會話1（即使REPEATABLE READ也可能有幻讀場景） STARTTRANSACTION; SELECTCOUNT(*)FROMordersWHEREuser_id =1; -- 結果：10 -- 會話2 INSERTINTOorders (user_id, amount)VALUES(1,100); COMMIT; -- 會話1使用當前讀 SELECTCOUNT(*)FROMordersWHEREuser_id =1FORUPDATE; -- 結果：11（看到了新插入的行，即幻讀） -- 注意：InnoDB的REPEATABLE READ通過間隙鎖很大程度上防止了幻讀 -- 但在某些邊界情況下仍可能發生
2.1.3 創建測試環境
-- 創建測試數據庫 CREATEDATABASEIFNOTEXISTSlock_demo; USElock_demo; -- 創建賬戶表 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; -- 創建訂單表 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; -- 創建庫存表 CREATETABLEinventory ( idBIGINTUNSIGNEDAUTO_INCREMENT PRIMARYKEY, product_idBIGINTUNSIGNEDNOTNULL, stockINTUNSIGNEDNOTNULLDEFAULT0, versionINTUNSIGNEDNOTNULLDEFAULT0, UNIQUEKEYuk_product_id (product_id) )ENGINE=InnoDB; -- 插入測試數據 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); -- 生成訂單測試數據 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鎖）：允許其他事務讀，但不允許寫 SELECT*FROMaccountsWHEREuser_id =1LOCKINSHAREMODE; -- MySQL 8.0 新語法 SELECT*FROMaccountsWHEREuser_id =1FORSHARE; -- 排他鎖（X鎖）：不允許其他事務讀寫（當前讀除外） SELECT*FROMaccountsWHEREuser_id =1FORUPDATE; -- 鎖兼容性矩陣 -- | | S鎖 | X鎖 | -- | S鎖 | 兼容 | 沖突 | -- | X鎖 | 沖突 | 沖突 |
2. 意向鎖（IS/IX鎖）
-- 意向鎖是表級鎖，用于表明事務稍后會在表中的行上加什么類型的鎖 -- 意向共享鎖（IS）：事務準備給數據行加共享鎖 -- 意向排他鎖（IX）：事務準備給數據行加排他鎖 -- 查看意向鎖 SELECT*FROMperformance_schema.data_locksWHERELOCK_TYPE ='TABLE'; -- 意向鎖的作用： -- 加表鎖時，不需要遍歷每一行來檢查是否有行鎖 -- 只需檢查意向鎖即可 -- 兼容性矩陣： -- | | IS | IX | S | X | -- | IS | 兼容 | 兼容 | 兼容 | 沖突 | -- | IX | 兼容 | 兼容 | 沖突 | 沖突 | -- | S | 兼容 | 沖突 | 兼容 | 沖突 | -- | X | 沖突 | 沖突 | 沖突 | 沖突 |
3. 記錄鎖（Record Lock）
-- 記錄鎖鎖定索引記錄 -- 如果表沒有索引，InnoDB會創建隱藏的聚簇索引，并使用該索引進行記錄鎖定 STARTTRANSACTION; -- 鎖定id=1的記錄 SELECT*FROMaccountsWHEREid=1FORUPDATE; -- 此時其他事務無法修改id=1的行 -- 查看記錄鎖 SELECT*FROMperformance_schema.data_locks WHERELOCK_TYPE ='RECORD'ANDLOCK_MODE ='X,REC_NOT_GAP';
4. 間隙鎖（Gap Lock）
-- 間隙鎖鎖定索引記錄之間的間隙，防止其他事務插入 -- 只在REPEATABLE READ及以上隔離級別生效 -- 假設accounts表中有id: 1, 5, 10 STARTTRANSACTION; SELECT*FROMaccountsWHEREidBETWEEN3AND7FORUPDATE; -- 這會鎖定(1,5)和(5,10)的間隙 -- 其他事務無法在這些間隙中插入新記錄 -- 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）
-- 臨鍵鎖 = 記錄鎖 + 間隙鎖 -- 鎖定一個索引記錄及其前面的間隙 -- 假設有id: 1, 5, 10 STARTTRANSACTION; SELECT*FROMaccountsWHEREid=5FORUPDATE; -- 在REPEATABLE READ級別，這會鎖定： -- 1. 記錄id=5 -- 2. 間隙(1,5) -- 臨鍵鎖是InnoDB默認的鎖類型，用于防止幻讀
6. 插入意向鎖（Insert Intention Lock）
-- 插入意向鎖是一種特殊的間隙鎖 -- 多個事務可以同時獲取同一間隙的插入意向鎖（只要插入位置不同） -- 會話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 -- 兩個插入可以并發執行，因為插入位置不沖突
2.2.2 鎖監控配置
-- 開啟鎖監控 SETGLOBALinnodb_status_output =ON; SETGLOBALinnodb_status_output_locks =ON; -- 查看InnoDB狀態（包含鎖信息） SHOWENGINEINNODBSTATUSG -- 使用performance_schema監控鎖 -- data_locks：當前持有的鎖 SELECT*FROMperformance_schema.data_locks; -- data_lock_waits：鎖等待關系 SELECT*FROMperformance_schema.data_lock_waits; -- 查看等待鎖的事務 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 死鎖檢測配置
-- 開啟死鎖檢測（默認開啟） SETGLOBALinnodb_deadlock_detect =ON; -- 設置鎖等待超時時間 SETGLOBALinnodb_lock_wait_timeout =50; -- 默認50秒 -- 打印所有死鎖信息到錯誤日志 SETGLOBALinnodb_print_all_deadlocks =ON; -- 配置文件設置 -- [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的更新才會執行 -- 查看鎖等待情況 SELECT*FROMperformance_schema.data_lock_waits;
2.3.2 驗證死鎖檢測
-- 構造死鎖場景 -- 會話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的鎖 -- 此時發生死鎖！ -- MySQL會檢測到死鎖，回滾其中一個事務 -- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction -- 查看最近的死鎖信息 SHOWENGINEINNODBSTATUSG -- 找到"LATEST DETECTED DEADLOCK"部分
三、示例代碼和配置
3.1 完整配置示例
3.1.1 死鎖案例分析
案例1：相反順序更新
-- 最常見的死鎖場景：兩個事務以相反順序更新行 -- 事務1：先更新A，再更新B STARTTRANSACTION; UPDATEaccountsSETbalance = balance -100WHEREuser_id =1; -- 鎖定user_id=1 -- 等待... UPDATEaccountsSETbalance = balance +100WHEREuser_id =2; -- 需要鎖定user_id=2 -- 事務2：先更新B，再更新A STARTTRANSACTION; UPDATEaccountsSETbalance = balance -50WHEREuser_id =2; -- 鎖定user_id=2 -- 等待... UPDATEaccountsSETbalance = balance +50WHEREuser_id =1; -- 需要鎖定user_id=1 -- 死鎖！事務1持有A等待B，事務2持有B等待A -- 解決方案：固定更新順序 -- 始終按user_id升序或降序更新 STARTTRANSACTION; -- 方法1：應用層排序 UPDATEaccountsSETbalance = balance -100WHEREuser_id =1; UPDATEaccountsSETbalance = balance +100WHEREuser_id =2; COMMIT;
案例2：間隙鎖死鎖
-- 間隙鎖導致的死鎖 -- 表中有id: 1, 10, 20 -- 事務1 STARTTRANSACTION; SELECT*FROMaccountsWHEREid=5FORUPDATE; -- 鎖定間隙(1,10) -- 等待... -- 事務2 STARTTRANSACTION; SELECT*FROMaccountsWHEREid=15FORUPDATE; -- 鎖定間隙(10,20) INSERTINTOaccounts (id, user_id, balance)VALUES(7,7,1000); -- 等待事務1 -- 事務1 INSERTINTOaccounts (id, user_id, balance)VALUES(12,12,2000); -- 等待事務2 -- 死鎖！ -- 解決方案： -- 1. 降低隔離級別到READ COMMITTED（不使用間隙鎖） -- 2. 使用唯一索引精確匹配，避免間隙鎖 -- 3. 減少鎖定范圍
案例3：唯一鍵沖突死鎖
-- 唯一鍵沖突可能導致死鎖
-- 表中已有 order_no = 'ORD001'
-- 事務1
STARTTRANSACTION;
INSERTINTOorders (order_no, user_id, amount)VALUES('ORD002',1,100);
-- 事務2
STARTTRANSACTION;
INSERTINTOorders (order_no, user_id, amount)VALUES('ORD002',2,200);
-- 唯一鍵沖突，等待事務1
-- 事務3
STARTTRANSACTION;
INSERTINTOorders (order_no, user_id, amount)VALUES('ORD002',3,300);
-- 也等待
-- 事務1回滾
ROLLBACK;
-- 事務2和事務3可能死鎖，因為它們都在等待鎖
-- 解決方案：
-- 1. 使用INSERT ... ON DUPLICATE KEY UPDATE
-- 2. 使用INSERT IGNORE
-- 3. 先查詢再插入（在應用層處理）
3.1.2 悲觀鎖實現
/**
* 悲觀鎖實現轉賬功能
* 使用SELECT FOR UPDATE鎖定記錄
*/
@Service
@Transactional
publicclassTransferService{
@Autowired
privateJdbcTemplate jdbcTemplate;
/**
* 轉賬 - 悲觀鎖實現
* 關鍵：按固定順序獲取鎖，避免死鎖
*/
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);
// 確定轉出和轉入賬戶的余額
BigDecimal fromBalance = fromUserId.equals(firstUserId) ? firstBalance : secondBalance;
BigDecimal toBalance = fromUserId.equals(firstUserId) ? secondBalance : firstBalance;
// 檢查余額
if(fromBalance.compareTo(amount)
3.1.3 樂觀鎖實現
/**
* 樂觀鎖實現庫存扣減
* 使用版本號或CAS機制
*/
@Service
publicclassInventoryService{
@Autowired
privateJdbcTemplate jdbcTemplate;
/**
* 扣減庫存 - 樂觀鎖實現
*@returntrue 成功，false 失?。◣齑娌蛔慊虬姹緵_突）
*/
publicbooleandecreaseStock(Long productId,intquantity){
intmaxRetries =3;
for(inti =0; i < maxRetries; i++) {
? ? ? ? ? ??// 查詢當前庫存和版本號
? ? ? ? ? ? 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; ?// 庫存不足
? ? ? ? ? ? }
? ? ? ? ? ??// 使用版本號進行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; // 重試次數用盡
}
/**
* 扣減庫存 - 使用行級條件更新（更簡潔的樂觀鎖）
*/
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 分布式鎖實現
/**
* 基于Redis的分布式鎖實現
* 解決跨實例的并發問題
*/
@Component
publicclassDistributedLock{
@Autowired
privateStringRedisTemplate redisTemplate;
privatestaticfinallongDEFAULT_EXPIRE_TIME =30000; // 30秒
/**
* 獲取鎖
*@paramlockKey 鎖的key
*@paramrequestId 請求標識（用于釋放鎖時驗證）
*@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);
}
/**
* 帶自動續期的鎖
* 使用watchdog機制
*/
publicbooleantryLockWithWatchdog(String lockKey, String requestId){
booleanlocked = tryLock(lockKey, requestId, DEFAULT_EXPIRE_TIME);
if(locked) {
// 啟動watchdog線程，定期續期
startWatchdog(lockKey, requestId);
}
returnlocked;
}
privatevoidstartWatchdog(String lockKey, String requestId){
Thread watchdog =newThread(() -> {
while(!Thread.currentThread().isInterrupted()) {
try{
Thread.sleep(DEFAULT_EXPIRE_TIME /3); // 每10秒續期一次
// 續期
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; // 鎖已被釋放或被其他進程獲取
}
}catch(InterruptedException e) {
Thread.currentThread().interrupt();
break;
}
}
});
watchdog.setDaemon(true);
watchdog.start();
}
}
/**
* 使用分布式鎖的示例
*/
@Service
publicclassOrderService{
@Autowired
privateDistributedLock distributedLock;
@Autowired
privateInventoryService inventoryService;
/**
* 創建訂單 - 使用分布式鎖保證冪等性
*/
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("庫存不足");
}
// 創建訂單
Order order =newOrder();
order.setOrderNo(orderNo);
order.setProductId(productId);
order.setQuantity(quantity);
orderMapper.insert(order);
returnorder;
}finally{
// 釋放鎖
distributedLock.unlock(lockKey, requestId);
}
}
}
3.2 實際應用案例
3.2.1 秒殺系統防超賣
/**
* 秒殺系統防超賣方案
*/
@Service
publicclassSeckillService{
@Autowired
privateRedisTemplate redisTemplate;
@Autowired
privateJdbcTemplate jdbcTemplate;
/**
* 方案1：Redis預扣庫存 + 異步入庫
*/
publicSeckillResultseckillWithRedis(Long userId, Long productId){
String stockKey ="seckill"+ productId;
String orderKey ="seckill"+ productId;
// 1. 檢查是否已購買（防止重復購買）
Boolean isMember = redisTemplate.opsForSet().isMember(orderKey, userId);
if(Boolean.TRUE.equals(isMember)) {
returnSeckillResult.fail("您已參與過此活動");
}
// 2. 預扣庫存（原子操作）
Long stock = redisTemplate.opsForValue().decrement(stockKey);
if(stock ==null|| stock = ?
""";
intaffected = jdbcTemplate.update(sql, quantity, productId, quantity);
if(affected >0) {
// 扣減成功，創建訂單
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("系統繁忙，請稍后重試");
? ? }
}
3.2.2 死鎖自動檢測和告警
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
MySQL死鎖監控和告警腳本
"""
importpymysql
importtime
importjson
importrequests
fromdatetimeimportdatetime
classDeadlockMonitor:
"""死鎖監控器"""
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狀態"""
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):
"""獲取當前鎖等待情況"""
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):
"""發送告警"""
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"發送告警失敗:{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):
"""運行監控"""
print(f"死鎖監控已啟動，檢查間隔:{interval}秒")
whileTrue:
try:
self.check_deadlock()
self.check_lock_waits(threshold_seconds=30)
exceptExceptionase:
print(f"監控異常:{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 事務超時和慢事務監控
-- 查詢運行時間超過指定秒數的事務
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;
-- 查詢持有鎖最多的事務
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;
-- 查詢鎖定行數最多的表
SELECT
object_schema,
object_name,
COUNT(*)aslock_count
FROMperformance_schema.data_locks
WHERElock_type ='RECORD'
GROUPBYobject_schema, object_name
ORDERBYlock_countDESC;
-- 創建慢事務告警存儲過程
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;
-- 創建告警日志表
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;
-- 記錄慢事務
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 事務設計原則
-- 1. 事務盡量短小
-- 差：大事務
STARTTRANSACTION;
-- 處理100萬條記錄
UPDATEordersSETstatus=1WHEREcreated_at 0DO
STARTTRANSACTION;
UPDATEorders
SETstatus=1
WHEREcreated_at
4.1.2 鎖優化策略
-- 1. 盡量使用索引訪問數據
-- 差：無索引導致鎖表
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. 避免鎖升級
-- 差：從共享鎖升級到排他鎖可能導致死鎖
SELECT*FROMordersWHEREid=1LOCKINSHAREMODE;
-- 后續需要更新...
UPDATEordersSETstatus=1WHEREid=1; -- 可能死鎖
-- 好：直接使用排他鎖
SELECT*FROMordersWHEREid=1FORUPDATE;
UPDATEordersSETstatus=1WHEREid=1;
4.1.3 隔離級別選擇
-- 不同場景的隔離級別推薦
-- 1. 高并發讀寫場景：READ COMMITTED
-- 優點：鎖范圍小，不使用間隙鎖
-- 缺點：可能出現不可重復讀
SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;
-- 2. 金融交易場景：REPEATABLE READ（默認）
-- 優點：一致性讀，防止幻讀
-- 缺點：間隙鎖可能導致更多死鎖
SETSESSIONTRANSACTIONISOLATIONLEVELREPEATABLEREAD;
-- 3. 報表查詢場景：使用一致性快照
STARTTRANSACTIONWITHCONSISTENTSNAPSHOT;
SELECT*FROMordersWHERE...;
-- 讀取的是事務開始時的快照，不會被其他事務影響
COMMIT;
-- 4. 批量導入場景：可以臨時使用READ UNCOMMITTED
SETSESSIONTRANSACTIONISOLATIONLEVELREADUNCOMMITTED;
-- 導入完成后恢復
SETSESSIONTRANSACTIONISOLATIONLEVELREPEATABLEREAD;
4.2 注意事項
4.2.1 配置注意
| 配置項 | 建議值 | 說明 |
|---|---|---|
| innodb_lock_wait_timeout | 10-50 | 鎖等待超時，根據業務調整 |
| innodb_deadlock_detect | ON | 開啟死鎖檢測 |
| innodb_print_all_deadlocks | ON | 記錄所有死鎖到錯誤日志 |
| transaction_isolation | READ-COMMITTED / REPEATABLE-READ | 根據場景選擇 |
| innodb_rollback_on_timeout | OFF | 超時時只回滾當前語句，不回滾整個事務 |
| autocommit | ON | 默認開啟自動提交 |
4.2.2 常見錯誤
| 錯誤類型 | 錯誤信息 | 原因分析 | 解決方案 |
|---|---|---|---|
| 死鎖 | Deadlock found | 循環等待 | 固定訪問順序 |
| 鎖超時 | Lock wait timeout exceeded | 持鎖時間過長 | 減小事務，增加超時 |
| 事務太大 | Transaction too large | 修改行數過多 | 分批處理 |
| 表鎖 | Table lock wait | 無索引導致表鎖 | 添加適當索引 |
| 間隙鎖沖突 | Conflict on gap lock | 并發插入同一間隙 | 降低隔離級別 |
4.2.3 死鎖預防清單
開發階段: -固定訪問順序:多表操作按表名或主鍵排序 -減小事務范圍:只在必要時開啟事務 -使用低隔離級別:非必要不用REPEATABLEREAD -添加必要索引:避免全表掃描鎖定 部署階段: -開啟死鎖檢測:innodb_deadlock_detect=ON -設置合理超時:innodb_lock_wait_timeout=10 -記錄死鎖日志:innodb_print_all_deadlocks=ON -配置監控告警:死鎖次數、鎖等待時間 運維階段: -定期分析死鎖:查看SHOWENGINEINNODBSTATUS -監控長事務:超過60秒的事務告警 -監控鎖等待:等待超過10秒告警 -分析慢查詢:優化持鎖時間長的SQL
五、故障排查和監控
5.1 故障排查
5.1.1 死鎖分析
-- 查看最近的死鎖信息 SHOWENGINEINNODBSTATUSG -- 輸出中的關鍵部分： -- 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. 找到兩個事務的SQL -- 2. 分析鎖等待關系 -- 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 鎖等待分析
-- 查看當前鎖等待 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; -- 查看鎖的詳細信息 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; -- 終止阻塞事務（謹慎使用） -- 先確認阻塞線程ID KILL12345;
5.1.3 長事務分析
-- 查找運行時間最長的事務 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; -- 查看事務對應的連接信息 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; -- 查看事務的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 性能監控
5.2.1 關鍵指標
-- InnoDB鎖相關指標 SHOWGLOBALSTATUSLIKE'Innodb_row_lock%'; -- Innodb_row_lock_current_waits: 當前等待鎖的數量 -- Innodb_row_lock_time: 總鎖等待時間（毫秒） -- Innodb_row_lock_time_avg: 平均鎖等待時間 -- Innodb_row_lock_time_max: 最大鎖等待時間 -- Innodb_row_lock_waits: 總鎖等待次數 -- 死鎖次數 SHOWGLOBALSTATUSLIKE'Innodb_deadlocks'; -- 鎖內存使用 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 監控指標表
| 指標類別 | 指標名稱 | 含義 | 告警閾值 |
|---|---|---|---|
| 死鎖 | Innodb_deadlocks | 死鎖累計次數 | 增長率 > 1/min |
| 鎖等待 | Innodb_row_lock_waits | 鎖等待累計次數 | 增長率 > 10/sec |
| 鎖時間 | Innodb_row_lock_time_avg | 平均鎖等待時間(ms) | > 1000 |
| 當前等待 | Innodb_row_lock_current_waits | 當前等待鎖數量 | > 10 |
| 長事務 | 運行超過60秒的事務 | 長事務數量 | > 0 |
| 鎖表 | Tables_locks_waited | 表鎖等待次數 | > 0 |
5.2.3 Prometheus告警規則
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發生死鎖"
description:"{{ $labels.instance }}在過去5分鐘內發生{{ $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存在長事務"
description:"{{ $labels.instance }}存在運行超過60秒的事務"
5.3 備份與恢復
5.3.1 事務日志備份
#!/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
# 獲取當前binlog文件列表
mysql -u$MYSQL_USER-p$MYSQL_PASS-h$MYSQL_HOST-e"SHOW BINARY LOGS;"|
tail -n +2 | awk'{print $1}'|whilereadbinlog;do
# 復制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 死鎖恢復流程
-- 死鎖后的恢復步驟 -- 1. 確認事務狀態 SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query FROMinformation_schema.innodb_trx; -- 2. 如果事務被回滾，應用程序需要重試 -- 檢查應用程序的重試邏輯 -- 3. 如果需要手動回滾 ROLLBACK; -- 4. 檢查數據一致性 -- 根據業務邏輯驗證數據 -- 5. 分析死鎖原因 SHOWENGINEINNODBSTATUSG -- 找到LATEST DETECTED DEADLOCK部分 -- 6. 記錄和上報 -- 將死鎖信息記錄到監控系統
六、總結
6.1 技術要點回顧
MySQL事務與鎖機制的核心要點：
1. ACID特性
原子性：通過undo log實現
一致性：通過約束和應用邏輯保證
隔離性：通過鎖和MVCC實現
持久性：通過redo log保證
2. 鎖類型
行鎖：記錄鎖、間隙鎖、臨鍵鎖
表鎖：意向鎖、MDL鎖
鎖模式：共享鎖、排他鎖
3. 死鎖處理
預防：固定訪問順序、減小事務
檢測：innodb_deadlock_detect
恢復：自動回滾一個事務
4. 最佳實踐
事務盡量短小
按固定順序訪問資源
合理選擇隔離級別
使用合適的鎖策略
6.2 進階學習方向
| 方向 | 內容 | 推薦資源 |
|---|---|---|
| MVCC原理 | 版本鏈、ReadView機制 | 《MySQL技術內幕：InnoDB存儲引擎》 |
| 鎖算法 | B+樹鎖定協議 | MySQL源碼 |
| 分布式事務 | XA、TCC、SAGA | Seata框架文檔 |
| 死鎖檢測算法 | 等待圖、超時檢測 | 數據庫系統概論 |
| 性能調優 | 鎖粒度優化 | Percona博客 |
6.3 參考資料
MySQL官方文檔：https://dev.mysql.com/doc/
《MySQL技術內幕：InnoDB存儲引擎》第2版
《高性能MySQL》第4版
Percona Blog：https://www.percona.com/blog/
附錄
A. 命令速查表
| 命令 | 說明 | 示例 |
|---|---|---|
| START TRANSACTION | 開始事務 | START TRANSACTION; |
| COMMIT | 提交事務 | COMMIT; |
| ROLLBACK | 回滾事務 | 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狀態 | SHOW ENGINE INNODB STATUSG |
| KILL | 終止連接 | KILL 12345; |
B. 配置參數詳解
| 參數 | 默認值 | 說明 | 建議 |
|---|---|---|---|
| transaction_isolation | REPEATABLE-READ | 默認隔離級別 | 根據場景選擇 |
| innodb_lock_wait_timeout | 50 | 鎖等待超時(秒) | 10-30 |
| innodb_deadlock_detect | ON | 死鎖檢測開關 | ON |
| innodb_print_all_deadlocks | OFF | 記錄所有死鎖 | ON |
| innodb_rollback_on_timeout | OFF | 超時回滾整個事務 | OFF |
| autocommit | ON | 自動提交 | ON |
C. 術語表
| 術語 | 英文 | 說明 |
|---|---|---|
| 臟讀 | Dirty Read | 讀取未提交的數據 |
| 不可重復讀 | Non-Repeatable Read | 同一事務兩次讀取結果不同 |
| 幻讀 | Phantom Read | 查詢結果集行數變化 |
| 死鎖 | Deadlock | 循環等待鎖 |
| 間隙鎖 | Gap Lock | 鎖定索引間隙 |
| 臨鍵鎖 | Next-Key Lock | 記錄鎖+間隙鎖 |
| MVCC | Multi-Version Concurrency Control | 多版本并發控制 |
| Undo Log | - | 回滾日志 |
| Redo Log | - | 重做日志 |
| 兩階段鎖 | Two-Phase Locking | 加鎖和解鎖分兩階段 |
聲明：本文內容及配圖由入駐作者撰寫或者入駐合作網站授權轉載。文章觀點僅代表作者本人，不代表電子發燒友網立場。文章及其配圖僅供工程師學習之用，如有內容侵權或者其他違規問題，請聯系本站處理。
舉報投訴
-
互聯網
+關注
關注
55文章
11336瀏覽量
109886 -
數據庫
+關注
關注
7文章
4019瀏覽量
68333 -
MySQL
+關注
關注
1文章
905瀏覽量
29517
原文標題：MySQL事務與鎖機制詳解：ACID原理與死鎖排查
文章出處：【微信號：magedu-Linux，微信公眾號：馬哥Linux運維】歡迎添加關注！文章轉載請注明出處。
發布評論請先 登錄
相關推薦
熱點推薦
基于MySQL的鎖機制
在數據庫系統中，為了保證數據的一致性和并發控制，鎖機制發揮著至關重要的作用。尤其在關系型數據庫MySQL中，其獨特的鎖機制設計更是贏得了許多
MySQL死鎖原因排查技巧詳解
在查詢相關資料和咨詢jameszhou后，知道了這個實際和innodb 引擎的寫機制有關，innodb執行寫事務操作時，實際是先取得索引中該行的行鎖（即使該表上沒有任何索引，那么innodb會在后臺創建一個隱藏的聚集主鍵索引），
發表于 10-19 16:38
?4940次閱讀
事務深度遍歷過程詳解
）。 在一個事務中定義USER標簽的name作為模式可索引的屬性，然后使用分離的事務實際設置一個真實用戶的值: 許多數據庫管理系統使用鎖機制來管理對同一個數據庫的同時訪問。Neo4j
發表于 12-11 12:34
?1409次閱讀
Oracle核心技術之事務和鎖
是Oracle數據庫引擎用來同步多個用戶，同時對同一個數據塊訪問的一種機制。鎖可以消除多用戶操作同一個資源產生的隱患。本章重點討論有關事務和鎖的概念。
發表于 03-26 10:24
?3次下載
MySQL事務的四大隔離級別詳解
之前分析一個死鎖問題，發現自己對數據庫隔離級別理解還不夠深入，所以趁著這幾天假期，整理一下MySQL事務的四大隔離級別相關知識，希望對大家有幫助~ 事務 什么是事務？
MySQL中的高級內容詳解
MySQL 進階?。?！ 本文思維導圖如下。 事務控制和鎖定語句 我們知道，MyISAM 和 MEMORY 存儲引擎支持表級鎖定(table-level locking)，InnoDB 存儲引擎支持行級鎖定
數據庫的鎖機制真正的原理
在MySQL數據庫中，為了解決并發問題，引入了很多的鎖機制，很多時候，數據庫的鎖是在有數據庫操作的過程中自動添加的。所以，這就導致很多程序員經常會忽略數據庫的
MySQL事務隔離級別要實際解決的問題
MySQL 是支持多事務并發執行的。否則來一個事務處理一個請求，處理一個人請求的時候，其它事務都等著，那估計都沒人敢用MySQL作為數據庫,
一文徹底搞懂MySQL鎖究竟鎖的啥1
MySQL鎖系列文章已經鴿了挺久了，最近趕緊擠了擠時間，和大家聊一聊MySQL的鎖。
只要學計算機，「`鎖`」永遠是一個繞不過的話題。
一文徹底搞懂MySQL鎖究竟鎖的啥2
MySQL鎖系列文章已經鴿了挺久了，最近趕緊擠了擠時間，和大家聊一聊MySQL的鎖。
只要學計算機，「`鎖`」永遠是一個繞不過的話題。
阿里二面：了解MySQL事務底層原理嗎
那 MySQL 是如何來解決臟寫這種問題的？沒錯，就是鎖。MySQL 在開啟一個事務的時候，他會將某條記錄和事務做一個綁定。這個其實和 JV
工商網監
評論