問(wèn)題背景

MySQL 死鎖是生產(chǎn)環(huán)境中讓人頭疼的問(wèn)題之一。很多運(yùn)維工程師看到死鎖報(bào)錯(cuò)，第一反應(yīng)是重啟數(shù)據(jù)庫(kù)——重啟確實(shí)能解決當(dāng)下的阻塞，但根因還在，下次還會(huì)發(fā)生，而且可能在更糟糕的時(shí)間點(diǎn)爆發(fā)。

死鎖的本質(zhì)是并發(fā)事務(wù)對(duì)資源加鎖順序不一致，導(dǎo)致循環(huán)等待。在 MySQL 的 InnoDB 引擎里，死鎖并不一定是配置問(wèn)題或 SQL 寫(xiě)得不好——高并發(fā)場(chǎng)景下，即使業(yè)務(wù)邏輯合理，也可能出現(xiàn)死鎖。但死鎖頻繁發(fā)生，一定說(shuō)明業(yè)務(wù)邏輯或索引設(shè)計(jì)存在優(yōu)化空間。

本文面向 MySQL 5.7 和 MySQL 8.0，以一線(xiàn)運(yùn)維工程師的角度，系統(tǒng)講解死鎖的形成機(jī)制、排查方法、解決思路和預(yù)防措施。涉及命令、SQL、配置均基于生產(chǎn)環(huán)境常用版本，MySQL 5.7 和 8.0 的差異會(huì)單獨(dú)說(shuō)明。

適用場(chǎng)景

線(xiàn)上頻繁出現(xiàn)死鎖報(bào)錯(cuò)，業(yè)務(wù)受阻

監(jiān)控發(fā)現(xiàn) InnoDB Lock wait timeout exceeded 錯(cuò)誤增多

需要分析死鎖日志定位根因

優(yōu)化業(yè)務(wù) SQL 減少死鎖發(fā)生概率

死鎖形成機(jī)制

什么是死鎖

死鎖是兩個(gè)或多個(gè)事務(wù)在執(zhí)行過(guò)程中，因互相持有對(duì)方需要的鎖而形成循環(huán)等待，導(dǎo)致都無(wú)法繼續(xù)執(zhí)行下去。

MySQL InnoDB 的死鎖通常發(fā)生在以下場(chǎng)景：

行鎖沖突：事務(wù) A 鎖住了行 X，事務(wù) B 鎖住了行 Y，然后 A 要鎖 Y、B 要鎖 X，形成循環(huán)

間隙鎖沖突：范圍查詢(xún)導(dǎo)致間隙鎖，多個(gè)事務(wù)對(duì)相交的索引范圍加鎖

唯一索引沖突：多個(gè)事務(wù)同時(shí)插入相同的唯一鍵

鎖升級(jí)：行鎖升級(jí)為頁(yè)鎖或表鎖，擴(kuò)大了沖突范圍

InnoDB 鎖的類(lèi)型

理解死鎖要先理解 InnoDB 的鎖機(jī)制：

-- 查看當(dāng)前 InnoDB 鎖信息（MySQL 5.7+）
SELECT*FROMinformation_schema.INNODB_LOCKS;

-- 查看鎖等待關(guān)系
SELECT*FROMinformation_schema.INNODB_LOCK_WAITS;

-- 查看當(dāng)前事務(wù)及持有的鎖
SELECT*FROMinformation_schema.INNODB_TRX;

InnoDB 的鎖分為：

共享鎖（S鎖）：允許事務(wù)讀取一行

排他鎖（X鎖）：允許事務(wù)更新或刪除一行

記錄鎖（Record Lock）：鎖住索引記錄

間隙鎖（Gap Lock）：鎖住索引記錄之間的間隙，防止幻讀

Next-Key Lock：記錄鎖 + 間隙鎖的組合

意向鎖（Intention Lock）：表級(jí)鎖，表示事務(wù)要在某行加鎖

-- 查看事務(wù)隔離級(jí)別
SELECT@@transaction_isolation;
-- REPEATABLE READ 是最容易產(chǎn)生間隙鎖的隔離級(jí)別

-- 修改隔離級(jí)別（僅當(dāng)前會(huì)話(huà)）
SETSESSIONtransaction_isolation ='READ-COMMITTED';

死鎖與鎖等待的區(qū)別

死鎖和普通的鎖等待不是一回事：

鎖等待（Lock wait timeout）：事務(wù) A 等事務(wù) B 釋放鎖，超過(guò)innodb_lock_wait_timeout（默認(rèn) 50 秒）后報(bào)錯(cuò)。這是單向等待，不是死鎖。

死鎖（Deadlock）：事務(wù) A 等事務(wù) B，B 又等事務(wù) A，MySQL 的死鎖檢測(cè)機(jī)制主動(dòng)回滾其中一個(gè)事務(wù)，打破循環(huán)等待。

MySQL 8.0 中，死鎖檢測(cè)默認(rèn)開(kāi)啟，innodb_deadlock_detect默認(rèn) ON。死鎖發(fā)生時(shí)，MySQL 會(huì)自動(dòng)回滾代價(jià)最小的事務(wù)（通常是持有最少行級(jí)鎖的事務(wù)），讓其他事務(wù)繼續(xù)執(zhí)行。

排查工具：如何拿到死鎖信息

第一步：開(kāi)啟死鎖日志

MySQL 的死鎖信息會(huì)記錄在 error log 中。確認(rèn)以下配置：

# my.cnf / my.ini
[mysqld]
# 開(kāi)啟死鎖詳細(xì)信息記錄到 error log
innodb_print_all_deadlocks = ON

# 鎖等待超時(shí)時(shí)間（秒），超時(shí)后報(bào)錯(cuò)并回滾
innodb_lock_wait_timeout = 50

# 死鎖檢測(cè)開(kāi)關(guān)（MySQL 8.0+ 默認(rèn) ON，MySQL 5.7 也默認(rèn) ON）
innodb_deadlock_detect = ON

# 修改后重載配置，不重啟數(shù)據(jù)庫(kù)的方式：
# MySQL 8.0+ 支持動(dòng)態(tài)修改部分參數(shù)
SET GLOBAL innodb_print_all_deadlocks = ON;
SET GLOBAL innodb_lock_wait_timeout = 50;

# 確認(rèn)配置生效
SHOW VARIABLES LIKE'innodb_print_all_deadlocks';
SHOW VARIABLES LIKE'innodb_lock_wait_timeout';
SHOW VARIABLES LIKE'innodb_deadlock_detect';

第二步：查看死鎖日志

# 查看 error log 中的死鎖信息
grep -A 50"TRANSACTION"/var/log/mysql/error.log | grep -A 30"DEADLOCK"

# 如果不知道 error log 位置
SHOW VARIABLES LIKE'log_error';

# 查看最近的死鎖記錄（MySQL 5.7+）
-- 需要先執(zhí)行：SET GLOBAL innodb_print_all_deadlocks = ON;
-- 然后觸發(fā)死鎖，查看 errorlog

第三步：分析 performance_schema 中的鎖信息

MySQL 5.7 開(kāi)始，可以通過(guò) performance_schema 獲取更詳細(xì)的鎖信息：

-- 開(kāi)啟鎖監(jiān)控（需要提前在 my.cnf 中配置）
-- performance_schema 需要在啟動(dòng)時(shí)開(kāi)啟

-- 查看當(dāng)前所有鎖信息
SELECT
  l.lock_id,
  l.lock_mode,
  l.lock_type,
  l.lock_table,
  l.lock_index,
  l.lock_space,
  l.lock_page,
  l.lock_rec,
  l.lock_data,
  t.trx_id,
  t.trx_state,
  t.trx_started,
  t.trx_rows_locked,
  t.trx_rows_modified,
  t.trx_is_read_only,
  p.ps_table_name
FROMinformation_schema.INNODB_LOCKS l
JOINinformation_schema.INNODB_TRX tONl.lock_trx_id = t.trx_id
LEFTJOINperformance_schema.metadata_locks pONp.lock_object = l.lock_table;

-- 查看鎖等待圖（誰(shuí)在等誰(shuí)的鎖）
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,
  b.trx_startedASblocking_trx_started,
  b.trx_rows_lockedASblocking_trx_rows_locked
FROMinformation_schema.INNODB_LOCK_WAITS w
JOINinformation_schema.INNODB_TRX bONw.blocking_trx_id = b.trx_id
JOINinformation_schema.INNODB_TRX rONw.requesting_trx_id = r.trx_id;

MySQL 8.0 中，information_schema.INNODB_LOCKS和INNODB_LOCK_WAITS被移除了，鎖信息需要從performance_schema.data_locks和performance_schema.data_lock_waits中查詢(xún)：

-- MySQL 8.0 鎖信息查詢(xún)
SELECT*FROMperformance_schema.data_locks;

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

-- MySQL 8.0 綜合查看
SELECT
  dl.lock_id,
  dl.lock_mode,
  dl.lock_type,
  dl.lock_status,
  dl.object_name,
  dl.index_name,
  dl.lock_data,
  dt.trx_id,
  dt.trx_state,
  dt.trx_started,
  dt.trx_query
FROMperformance_schema.data_locks dl
JOINperformance_schema.data_locks dtONdl.lock_trx_id = dt.trx_id
WHEREdl.lock_type ='RECORD'ANDdt.trx_state ='RUNNING';

實(shí)戰(zhàn)案例一：行鎖沖突導(dǎo)致的死鎖

現(xiàn)象

某訂單表orders，高并發(fā)下單時(shí)出現(xiàn)死鎖：

ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

業(yè)務(wù)日志中看到部分訂單創(chuàng)建失敗，頻率約為千分之一。

排查過(guò)程

查看 error log 中的死鎖記錄：

tail -n 200 /var/log/mysql/error.log | grep -A 50"DEADLOCK"

死鎖日志格式如下：

*** (1) TRANSACTION:
TRANSACTION 98765, ACTIVE 2 sec inserting
mysql thread id 12345, OS thread handle 0x7f8a2c3d4e0, query id 67890 localhost root update
INSERT INTO orders (user_id, product_id, amount, status) VALUES (1001, 2001, 99.00,'pending')

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 123 page no 4 n bits 200 index PRIMARY of table `shop`.`orders` trx id 98765 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000001; asc  ;; (primary key)
1: len 6; hex 00000001871a; asc   ;; (transaction id)
2: len 6; hex 000000001b5a; asc   ;; (roll ptr)
3: len 4; hex 80000001; asc  ;; (user_id)
4: len 4; hex 800003e8; asc  ;; (amount)

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 4 n bits 200 index PRIMARY of table `shop`.`orders` trx id 98766 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000001; asc  ;; (primary key)

*** (2) TRANSACTION:
TRANSACTION 98766, ACTIVE 2 sec inserting
mysql thread id 12346, OS thread handle 0x7f8a2c3d5f1, query id 67891 localhost root update
INSERT INTO orders (user_id, product_id, amount, status) VALUES (1001, 2002, 149.00,'pending')

分析死鎖日志：

從日志可以看出：

事務(wù) 98765 和 98766 都在插入orders表

兩者都持有某條記錄的排他鎖（X locks rec but not gap），并且在等待對(duì)方的鎖grant

這說(shuō)明兩條 INSERT 語(yǔ)句插入的主鍵值相同或產(chǎn)生了相同的自增 ID 沖突

查看當(dāng)前訂單表狀態(tài)：

-- 查看訂單表結(jié)構(gòu)
SHOWCREATETABLEordersG

-- 查看最近新增的訂單
SELECT*FROMordersORDERBYidDESCLIMIT10;

-- 查看是否有訂單缺失（連續(xù) ID 不連續(xù)說(shuō)明有回滾）
SELECTid, user_id, created_atFROMordersORDERBYidDESCLIMIT20;

查看當(dāng)時(shí)并發(fā)插入的記錄：

-- 查看特定時(shí)間范圍內(nèi)創(chuàng)建的訂單（根據(jù) error log 中的時(shí)間推算）
SELECTid, user_id, product_id,status, created_at
FROMorders
WHEREcreated_atBETWEEN'2024-01-01 1000'AND'2024-01-01 1000'
ORDERBYidDESC;

根因定位

通過(guò)日志分析，發(fā)現(xiàn)根因是：兩個(gè)并發(fā)事務(wù)同時(shí)對(duì)同一用戶(hù)插入訂單時(shí)，由于表上有user_id索引，InnoDB 在 REPEATABLE READ 隔離級(jí)別下對(duì)相同user_id的記錄加了 Next-Key Lock，導(dǎo)致相互等待。

但更直接的根因是：應(yīng)用層的重試邏輯導(dǎo)致相同的 INSERT 被執(zhí)行了兩次，而兩次執(zhí)行在極短時(shí)間內(nèi)同時(shí)到達(dá)。

解決修復(fù)

方案一：應(yīng)用層加分布式鎖（推薦）

importredis
importuuid

defcreate_order(user_id, product_id, amount):
 # 使用 Redis 分布式鎖，防止同一用戶(hù)的并發(fā)下單
  lock_key =f"order{user_id}"
  lock_value = str(uuid.uuid4())
  lock_timeout =5

 ifnotredis.set(lock_key, lock_value, nx=True, ex=lock_timeout):
   raiseException("下單過(guò)于頻繁，請(qǐng)稍后重試")

 try:
   withdb_connection.cursor()ascursor:
      sql ="INSERT INTO orders (user_id, product_id, amount, status) VALUES (%s, %s, %s, 'pending')"
      cursor.execute(sql, (user_id, product_id, amount))
      db_connection.commit()
     returncursor.lastrowid
 finally:
   # 釋放鎖（只釋放自己持有的鎖）
   ifredis.get(lock_key) == lock_value:
      redis.delete(lock_key)

方案二：數(shù)據(jù)庫(kù)層加唯一約束（兜底方案）

-- 給 orders 表加業(yè)務(wù)唯一鍵約束（假設(shè) user_id + product_id 組合唯一）
ALTERTABLEordersADDCONSTRAINTuk_user_product
 UNIQUE(user_id, product_id);

-- 插入時(shí)使用 INSERT ... ON DUPLICATE KEY UPDATE
INSERTINTOorders (user_id, product_id, amount,status)
VALUES(1001,2001,99.00,'pending')
ONDUPLICATEKEYUPDATEamount =VALUES(amount),status='pending';

方案三：降低隔離級(jí)別

如果業(yè)務(wù)允許，可以將隔離級(jí)別從 REPEATABLE READ 降到 READ COMMITTED，減少 Next-Key Lock 的范圍：

-- 當(dāng)前會(huì)話(huà)修改
SETSESSIONtransaction_isolation ='READ-COMMITTED';

-- 全局修改（需要權(quán)限）
SETGLOBALtransaction_isolation ='READ-COMMITTED';

# 寫(xiě)入 my.cnf 永久生效
[mysqld]
transaction-isolation = READ-COMMITTED

方案四：調(diào)整 SQL 寫(xiě)入順序

如果是多個(gè)事務(wù)并發(fā)更新不同行的同一批數(shù)據(jù)產(chǎn)生死鎖，可以讓所有事務(wù)按相同順序操作資源：

-- 原來(lái)：事務(wù) A 先鎖用戶(hù)再鎖訂單，事務(wù) B 先鎖訂單再鎖用戶(hù) -> 死鎖
-- 改為：所有事務(wù)都按 user_id 順序加鎖

-- 錯(cuò)誤寫(xiě)法（不同事務(wù)順序不同）
-- 事務(wù) A: UPDATE orders SET status='paid' WHERE id=1; UPDATE users SET balance=balance-100 WHERE id=100;
-- 事務(wù) B: UPDATE users SET balance=balance-100 WHERE id=100; UPDATE orders SET status='paid' WHERE id=1;

-- 正確寫(xiě)法（所有事務(wù)統(tǒng)一先鎖用戶(hù)再鎖訂單）
-- 事務(wù) A: UPDATE users SET balance=balance-100 WHERE id=100; UPDATE orders SET status='paid' WHERE id=1;
-- 事務(wù) B: UPDATE users SET balance=balance-100 WHERE id=100; UPDATE orders SET status='paid' WHERE id=1;

驗(yàn)證方法

-- 1. 檢查死鎖是否解決：連續(xù)觀(guān)察 error log 中是否還有新死鎖
tail -f /var/log/mysql/error.log | grep DEADLOCK

-- 2. 檢查是否有 INSERT 重復(fù)數(shù)據(jù)（唯一約束生效）
SELECTuser_id, product_id,COUNT(*)ascnt
FROMorders
GROUPBYuser_id, product_id
HAVINGcnt >1;

-- 3. 檢查應(yīng)用層重試次數(shù)是否降低
-- 查看應(yīng)用日志中 "Deadlock found" 錯(cuò)誤的出現(xiàn)頻率

-- 4. 壓力測(cè)試驗(yàn)證
-- 用 mysqlslap 或 sysbench 模擬并發(fā)插入
mysqlslap--concurrency=20 --iterations=100 
 --query="INSERT INTO orders (user_id, product_id, amount, status) VALUES (FLOOR(1+RAND()*1000), FLOOR(1+RAND()*1000), 99.00, 'pending')" 
 --create-schema=shop

風(fēng)險(xiǎn)提醒

加唯一約束前，確認(rèn)業(yè)務(wù)邏輯上user_id + product_id確實(shí)應(yīng)該唯一，否則會(huì)導(dǎo)致正常業(yè)務(wù)報(bào) duplicate key 錯(cuò)誤

降低隔離級(jí)別到 READ COMMITTED 會(huì)降低隔離性，可能出現(xiàn)不可重復(fù)讀，需要評(píng)估業(yè)務(wù)是否接受

應(yīng)用層加分布式鎖引入了對(duì) Redis 的依賴(lài)，如果 Redis 不可用可能導(dǎo)致下單失敗，需要考慮降級(jí)策略

DELETE + INSERT 混合場(chǎng)景的死鎖比純 INSERT 更復(fù)雜，可能涉及記錄鎖和間隙鎖的組合

實(shí)戰(zhàn)案例二：間隙鎖導(dǎo)致的死鎖

現(xiàn)象

一張商品庫(kù)存表products，多個(gè)事務(wù)同時(shí)執(zhí)行以下語(yǔ)句扣減庫(kù)存：

UPDATEproductsSETstock = stock -1WHEREid=1001ANDstock >0;

在商品庫(kù)存只有 1 個(gè)、同時(shí)有 3 個(gè)請(qǐng)求并發(fā)扣減時(shí)，出現(xiàn)了死鎖。只有一個(gè)請(qǐng)求成功，另外兩個(gè)報(bào) deadlock error。

排查過(guò)程

-- 查看 products 表結(jié)構(gòu)和索引
SHOWCREATETABLEproductsG

-- 查看當(dāng)時(shí) products 表的讀操作
SELECT*FROMperformance_schema.events_statements_history
WHERESQL_TEXTLIKE'%products%'
ORDERBYEND_EVENT_IDDESCLIMIT10;

死鎖日志：

*** (1) TRANSACTION:
TRANSACTION 123456, ACTIVE 0 sec updating
mysql thread id 111, OS thread handle 0x7f8a1b2c3d4e, query id 999 localhost root
UPDATE products SET stock = stock - 1 WHERE id = 1001 AND stock > 0
RECORD LOCKS space id 99 page no 3 n bits 100 index PRIMARY of table `shop`.`products` trx id 123456 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICALL RECORD: n_fields 5; info bits 0
0: len 4; hex 800003e9; asc  ;;

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 99 page no 3 n bits 100 index PRIMARY of table `shop`.`products` trx id 123456 lock_mode X locks rec but not gap

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 99 page no 3 n bits 100 index PRIMARY of table `shop`.`products` trx id 123457 lock_mode X locks rec but not gap waiting

*** (2) TRANSACTION:
TRANSACTION 123457, ACTIVE 0 sec updating
mysql thread id 112, OS thread handle 0x7f8a1b2c3d5f, query id 1000 localhost root
UPDATE products SET stock = stock - 1 WHERE id = 1001 AND stock > 0
RECORD LOCKS space id 99 page no 3 n bits 100 index PRIMARY of table `shop`.`products` trx id 123457 lock_mode X locks rec but not gap

根因定位

這個(gè)場(chǎng)景的死鎖原因是：三個(gè)事務(wù)同時(shí)執(zhí)行相同的 UPDATE 語(yǔ)句，在 stock=1 時(shí)，三個(gè)事務(wù)都讀到了 stock>0 的記錄。由于 InnoDB 在當(dāng)前讀（current read）時(shí)會(huì)對(duì)讀取的行加鎖，三個(gè)事務(wù)實(shí)際上在競(jìng)爭(zhēng)同一行記錄的 X 鎖。

更具體的根因在于WHERE stock > 0這個(gè)條件——在 REPEATABLE READ 隔離級(jí)別下，范圍查詢(xún)不僅鎖住存在的記錄，還可能鎖住不存在的"間隙"。如果庫(kù)存為 1 時(shí)有兩個(gè)并發(fā)請(qǐng)求，它們可能分別鎖住了不同的間隙區(qū)間。

解決修復(fù)

方案一：使用樂(lè)觀(guān)鎖代替悲觀(guān)鎖

-- 悲觀(guān)鎖：直接 UPDATE，行鎖，可能死鎖
UPDATEproductsSETstock = stock -1WHEREid=1001ANDstock >0;

-- 樂(lè)觀(guān)鎖：通過(guò)版本號(hào)或 stock 值作為條件UPDATE，不鎖定行
UPDATEproducts
SETstock = stock -1,version=version+1
WHEREid=1001ANDstock >0ANDversion=1;

defdeduct_stock(product_id, quantity=1):
 """使用樂(lè)觀(guān)鎖扣減庫(kù)存"""
 withdb_connection.cursor()ascursor:
   # 先檢查庫(kù)存
    cursor.execute(
     "SELECT stock FROM products WHERE id = %s FOR UPDATE",
      (product_id,)
    )
    result = cursor.fetchone()
   ifnotresultorresult['stock'] < quantity:
? ? ? ? ? ??raise?Exception("庫(kù)存不足")

? ? ? ??# 樂(lè)觀(guān)鎖更新：檢查 stock 是否變化
? ? ? ? affected = cursor.execute(
? ? ? ? ? ??"UPDATE products SET stock = stock - %s WHERE id = %s AND stock >= %s",
      (quantity, product_id, quantity)
    )

   ifaffected ==0:
     raiseException("庫(kù)存已變化，請(qǐng)重試")

    db_connection.commit()

方案二：使用 SELECT ... FOR UPDATE 預(yù)鎖定（避免重試）

STARTTRANSACTION;

-- 先鎖定行，再檢查和更新
SELECTstockINTO@current_stock
FROMproducts
WHEREid=1001
FORUPDATE;

IF @current_stock >= 1 THEN
 UPDATEproductsSETstock = stock -1WHEREid=1001;
 COMMIT;
ELSE
 ROLLBACK;
 -- 拋出庫(kù)存不足異常
ENDIF;

方案三：使用 INSERT ... ON DUPLICATE KEY 代替 UPDATE

如果業(yè)務(wù)允許，可以將扣減庫(kù)存改為"預(yù)占庫(kù)存"的思路：

-- 創(chuàng)建庫(kù)存預(yù)占表
CREATETABLEstock_reserve (
 idBIGINTAUTO_INCREMENT PRIMARYKEY,
  product_idINTNOTNULL,
  quantityINTNOTNULLDEFAULT1,
 statusENUM('pending','confirmed','cancelled')DEFAULT'pending',
  created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP,
 INDEXidx_product_status (product_id,status),
 UNIQUEKEYuk_reserve (product_id,id)
);

-- 預(yù)占庫(kù)存（插入一條記錄）
INSERTINTOstock_reserve (product_id, quantity)
VALUES(1001,1);

-- 確認(rèn)庫(kù)存（更新預(yù)占記錄狀態(tài)）
UPDATEstock_reserve, products
SETstock_reserve.status ='confirmed',
  products.stock = products.stock - stock_reserve.quantity
WHEREstock_reserve.id = ??
ANDstock_reserve.product_id = products.id
ANDstock_reserve.status ='pending';

驗(yàn)證方法

-- 1. 檢查死鎖頻率是否降低
SELECT
 TIME,
 COUNT(*)asdeadlock_count
FROMmysql.general_log
WHEREcommand_type ='Query'
ANDargumentLIKE'%Deadlock%'
GROUPBYTIME
ORDERBYTIMEDESCLIMIT20;

-- 2. 檢查庫(kù)存扣減是否準(zhǔn)確
SELECTid, stock,versionFROMproductsWHEREid=1001;

-- 3. 對(duì)比修改前后的庫(kù)存流水
SELECTCOUNT(*)astotal_reserve,SUM(quantity)astotal_quantity
FROMstock_reserve
WHEREproduct_id =1001ANDstatus='confirmed';

-- 4. 壓測(cè)驗(yàn)證
-- 使用 sysbench 模擬并發(fā)扣庫(kù)存，觀(guān)察死鎖頻率

風(fēng)險(xiǎn)提醒

樂(lè)觀(guān)鎖的 retry 邏輯需要應(yīng)用層配合，如果 retry 次數(shù)過(guò)多會(huì)造成用戶(hù)等待，建議設(shè)置最大重試次數(shù)

SELECT ... FOR UPDATE 在高并發(fā)下會(huì)顯著降低吞吐量，因?yàn)樗鼤?huì)鎖定行直到事務(wù)結(jié)束

預(yù)占庫(kù)存方案增加了數(shù)據(jù)量和復(fù)雜度，需要額外的定時(shí)任務(wù)清理 cancelled 狀態(tài)的預(yù)占記錄

實(shí)戰(zhàn)案例三：唯一索引沖突導(dǎo)致的死鎖

現(xiàn)象

用戶(hù)注冊(cè)場(chǎng)景，多個(gè)線(xiàn)程同時(shí)為新用戶(hù)創(chuàng)建賬戶(hù)，執(zhí)行相同的 INSERT 語(yǔ)句：

INSERTINTOusers(username, email, password_hash, created_at)
VALUES('john_doe','john@example.com','hashed_pw',NOW());

數(shù)據(jù)庫(kù)表有username和email兩個(gè)唯一索引。高并發(fā)注冊(cè)時(shí)，出現(xiàn)死鎖。

排查過(guò)程

死鎖日志分析：

*** (1) TRANSACTION:
TRANSACTION 5555, ACTIVE 0 sec inserting
mysql thread id 88, query id 333 localhost root
INSERT INTO users (username, email, ...) VALUES ('john_doe','john@example.com', ...)
RECORD LOCKS space id 10 page no 3 n bits 100 index username of table `app`.`users` trx id 5555 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: (name='john_doe')

*** (2) TRANSACTION:
TRANSACTION 5556, ACTIVE 0 sec inserting
mysql thread id 89, query id 334 localhost root
INSERT INTO users (username, email, ...) VALUES ('john_doe','john@example.com', ...)
RECORD LOCKS space id 10 page no 3 n bits 100 index username of table `app`.`users` trx id 5556 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: (name='john_doe')

根因定位

兩個(gè)并發(fā) INSERT 插入相同的唯一鍵值（username='john_doe'），在唯一索引上形成沖突：

事務(wù) A 插入 username='john_doe'，在唯一索引上請(qǐng)求排他鎖

事務(wù) B 也插入 username='john_doe'，也在唯一索引上請(qǐng)求排他鎖

唯一索引檢查發(fā)現(xiàn)鍵值重復(fù)，事務(wù) A 或 B 被通知等待對(duì)方釋放鎖

形成循環(huán)等待——死鎖

MySQL 檢測(cè)到死鎖后會(huì)回滾其中一個(gè)事務(wù)，通常是回滾插入較晚的那個(gè)。

解決修復(fù)

方案一：應(yīng)用層做插入前檢查

defregister_user(username, email, password):
 # 先檢查是否已存在
  existing = db.query(
   "SELECT id FROM users WHERE username = %s OR email = %s",
    (username, email)
  ).fetchone()

 ifexisting:
   raiseValueError("用戶(hù)名或郵箱已被注冊(cè)")

 # 插入（理論上可能有并發(fā)重復(fù)，但概率極低）
  db.execute(
   "INSERT INTO users (username, email, password_hash) VALUES (%s, %s, %s)",
    (username, email, hash_password(password))
  )
  db.commit()

方案二：使用 INSERT IGNORE 或 ON DUPLICATE KEY（適合冪等場(chǎng)景）

-- INSERT IGNORE：插入沖突時(shí)忽略，不報(bào)錯(cuò)
INSERTIGNOREINTOusers(username, email, password_hash)
VALUES('john_doe','john@example.com','hashed_pw');

-- 檢查是否插入成功
SELECTROW_COUNT();-- 1 = 插入成功，0 = 被忽略（重復(fù)）

-- ON DUPLICATE KEY UPDATE：插入沖突時(shí)更新
INSERTINTOusers(username, email, password_hash)
VALUES('john_doe','john@example.com','hashed_pw')
ONDUPLICATEKEYUPDATEemail =VALUES(email), password_hash =VALUES(password_hash);

方案三：使用 REPLACE（刪除舊記錄，插入新記錄）

-- REPLACE：如果唯一鍵沖突，先刪除舊記錄，再插入新記錄
-- 注意：這是 DELETE + INSERT，不是 UPDATE
-- 會(huì)觸發(fā) DELETE 和 INSERT 兩條語(yǔ)句，可能導(dǎo)致自增 ID 不連續(xù)
REPLACEINTOusers(username, email, password_hash)
VALUES('john_doe','john@example.com','hashed_pw');

方案四：分布式鎖（最可靠）

defregister_user_with_lock(username, email, password):
  lock_key =f"user{username}"
  lock_val = str(uuid.uuid4())

 ifnotredis.set(lock_key, lock_val, nx=True, ex=5):
   raiseException("注冊(cè)進(jìn)行中，請(qǐng)稍后")

 try:
   # 檢查是否存在
   ifdb.query("SELECT 1 FROM users WHERE username = %s", (username,)).fetchone():
     raiseValueError("用戶(hù)名已存在")

    db.execute(
     "INSERT INTO users (username, email, password_hash) VALUES (%s, %s, %s)",
      (username, email, hash_password(password))
    )
    db.commit()
 finally:
   ifredis.get(lock_key) == lock_val:
      redis.delete(lock_key)

驗(yàn)證方法

-- 1. 檢查是否有重復(fù)用戶(hù)名（說(shuō)明唯一約束沒(méi)有兜底攔住）
SELECTusername,COUNT(*)FROMusersGROUPBYusernameHAVINGCOUNT(*) >1;

-- 2. 檢查表上唯一約束是否生效
SHOWCREATETABLEusersG

-- 3. 模擬并發(fā)注冊(cè)測(cè)試
-- 啟動(dòng)多個(gè) mysql client 同時(shí)執(zhí)行相同 INSERT
-- 查看只有一個(gè)成功，其余報(bào) deadlock 或 duplicate key error

-- 4. 監(jiān)控死鎖頻率
SHOWGLOBALSTATUSLIKE'Innodb_deadlock%';
-- Innodb_deadlock_graph_writes: 死鎖圖更新次數(shù)
-- Innodb_deadlock_recursive_locks: 遞歸鎖檢測(cè)次數(shù)

風(fēng)險(xiǎn)提醒

REPLACE INTO是 DELETE + INSERT，會(huì)觸發(fā) DELETE 的行鎖和 INSERT 的排他鎖，鎖范圍比普通 INSERT 大，不適合高并發(fā)場(chǎng)景

ON DUPLICATE KEY UPDATE在 MySQL 5.7+ 支持，但注意：如果唯一鍵上有多個(gè)列的組合唯一索引，任意一個(gè)沖突都會(huì)觸發(fā) UPDATE

應(yīng)用層檢查 + INSERT 的組合不是原子操作，并發(fā)時(shí)仍可能兩個(gè)事務(wù)都通過(guò)檢查然后同時(shí)插入。需要配合唯一約束作為最后防線(xiàn)

死鎖排查的核心命令速查

-- 1. 查看當(dāng)前所有事務(wù)及持有的鎖（最常用）
SELECT
  trx_id,
  trx_state,
  trx_started,
  trx_requested_lock_id,
  trx_weight,
  trx_mysql_thread_id,
  trx_query,
  trx_rows_locked,
  trx_rows_modified
FROMinformation_schema.INNODB_TRX;

-- MySQL 8.0
SELECT*FROMperformance_schema.data_locks;
SELECT*FROMperformance_schema.data_lock_waits;

-- 2. 查看當(dāng)前鎖信息
SELECT*FROMinformation_schema.INNODB_LOCKS; -- MySQL 5.7
SELECT*FROMinformation_schema.INNODB_LOCK_WAITS; -- MySQL 5.7

-- 3. 查看當(dāng)前阻塞的 SQL
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
FROMinformation_schema.INNODB_LOCK_WAITS w
JOINinformation_schema.INNODB_TRX bONw.blocking_trx_id = b.trx_id
JOINinformation_schema.INNODB_TRX rONw.requesting_trx_id = r.trx_id;

-- 4. 查看死鎖次數(shù)統(tǒng)計(jì)
SHOWGLOBALSTATUSLIKE'Innodb_deadlock%';

-- 5. 查看事務(wù)等待時(shí)間超過(guò)閾值的事件（需要開(kāi)啟 performance_schema）
SELECT*FROMperformance_schema.events_transactions_history
WHERESTATE ='ROLLING BACK'ANDEVENT_NAME ='transaction'
ORDERBYEND_EVENT_IDDESCLIMIT10;

-- 6. 查看當(dāng)前正在執(zhí)行的 SQL（不包括空閑連接）
SELECT
  p.IDASthread_id,
  p.USER,
  p.HOST,
  d.COMMAND,
  t.trx_state,
  t.trx_started,
  t.trx_rows_locked,
 SUBSTRING(SQL.text,1,100)AScurrent_sql
FROMinformation_schema.PROCESSLIST p
LEFTJOINinformation_schema.INNODB_TRX tONp.ID = t.trx_mysql_thread_id
LEFTJOINperformance_schema.events_statements_currentSQLONp.ID = SQL.THREAD_ID
WHEREp.COMMAND !='Sleep'
ORDERBYt.trx_started;

死鎖預(yù)防措施

從 SQL 層面預(yù)防

按固定順序訪(fǎng)問(wèn)數(shù)據(jù)多個(gè)事務(wù)對(duì)多行數(shù)據(jù)進(jìn)行操作時(shí)，按主鍵或唯一鍵的順序訪(fǎng)問(wèn)，避免反向順序?qū)е滤梨i。

減少事務(wù)持有時(shí)間事務(wù)時(shí)間越長(zhǎng)，持有鎖的時(shí)間越長(zhǎng)，死鎖概率越高。盡量減少在事務(wù)內(nèi)的網(wǎng)絡(luò) IO 和復(fù)雜計(jì)算。

-- 好的做法：批量操作在事務(wù)內(nèi)一次完成
STARTTRANSACTION;
INSERTINTOorder_items (order_id, product_id, quantity)
VALUES(1,101,2), (1,102,1), (1,103,3);
UPDATEordersSETstatus='confirmed'WHEREid=1;
COMMIT;

-- 不好的做法：事務(wù)時(shí)間過(guò)長(zhǎng)
STARTTRANSACTION;
-- 讀取數(shù)據(jù)到應(yīng)用層處理（中間有網(wǎng)絡(luò)延遲）
items = db.query("SELECT*FROMorder_itemsWHEREorder_id=1");
for item in items:
  # 調(diào)用外部服務(wù)（很慢）
  result = call_external_service(item)
  if result:
    db.execute("UPDATEorder_itemsSETstatus='done'WHEREid=%s", item.id)
COMMIT;

使用合理的索引缺失索引的查詢(xún)會(huì)導(dǎo)致全表掃描和更大的鎖范圍。

-- 檢查查詢(xún)是否走了索引
EXPLAINSELECT*FROMordersWHEREuser_id =1001ANDstatus='pending';
-- 看 type 列，如果是 ALL 說(shuō)明全表掃描，需要加索引

-- 給常用查詢(xún)條件加索引
CREATEINDEXidx_orders_user_statusONorders(user_id,status);

盡量使用主鍵或唯一索引查詢(xún)基于主鍵或唯一索引的等值查詢(xún)只會(huì)鎖定一行，普通索引可能鎖定多行（ Next-Key Lock）。

從數(shù)據(jù)庫(kù)配置層面預(yù)防

[mysqld]
# 鎖等待超時(shí)時(shí)間（秒），不要設(shè)置過(guò)長(zhǎng)
innodb_lock_wait_timeout = 10

# 死鎖檢測(cè)開(kāi)關(guān)（默認(rèn) ON，關(guān)閉后用鎖等待超時(shí)兜底）
innodb_deadlock_detect = ON

# 開(kāi)啟死鎖日志
innodb_print_all_deadlocks = ON

# 監(jiān)控鎖信息（performance_schema）
performance-schema-instrument = 'lock%=ON'

# 事務(wù)隔離級(jí)別（高并發(fā)場(chǎng)景可考慮 READ-COMMITTED）
transaction-isolation = REPEATABLE-READ

# InnoDB 表鎖（避免鎖升級(jí)為表鎖）
innodb_table_locks = ON

從應(yīng)用架構(gòu)層面預(yù)防

使用消息隊(duì)列削峰庫(kù)存扣減、余額變動(dòng)等高并發(fā)操作，通過(guò)消息隊(duì)列串行化處理，完全避免數(shù)據(jù)庫(kù)層面的并發(fā)沖突。

應(yīng)用層加分布式鎖在數(shù)據(jù)庫(kù)操作前通過(guò) Redis/ZooKeeper 分布式鎖限制并發(fā)度。

讀寫(xiě)分離讀操作走從庫(kù)，寫(xiě)操作走主庫(kù)，減少主庫(kù)的鎖沖突。

分庫(kù)分表將熱點(diǎn)數(shù)據(jù)按用戶(hù) ID 或訂單 ID 拆分到不同庫(kù)表，從物理上減少鎖沖突概率。

常見(jiàn)誤區(qū)與避坑指南

誤區(qū)一：死鎖了就是數(shù)據(jù)庫(kù)的問(wèn)題

死鎖是業(yè)務(wù)邏輯和數(shù)據(jù)庫(kù)交互模式共同造成的。單純重啟數(shù)據(jù)庫(kù)或增加數(shù)據(jù)庫(kù)資源不能解決根本問(wèn)題。需要分析死鎖日志，找出是哪些 SQL 之間的鎖沖突導(dǎo)致的。

誤區(qū)二：加大innodb_lock_wait_timeout就不會(huì)超時(shí)

這個(gè)參數(shù)只是讓鎖等待的錯(cuò)誤晚一點(diǎn)出現(xiàn)，不會(huì)減少死鎖的發(fā)生次數(shù)。相反，設(shè)置過(guò)長(zhǎng)會(huì)讓事務(wù)長(zhǎng)時(shí)間占用連接資源，反而加劇資源緊張。

誤區(qū)三：把 REPEATABLE READ 改成 READ COMMITTED 就不會(huì)死鎖

降低隔離級(jí)別確實(shí)會(huì)減少 Next-Key Lock 的范圍，但不能完全消除死鎖。行鎖沖突在任意隔離級(jí)別下都可能發(fā)生。調(diào)整隔離級(jí)別前要評(píng)估對(duì)業(yè)務(wù)一致性的影響。

誤區(qū)四：大事務(wù)不會(huì)有問(wèn)題

大事務(wù)持有鎖的時(shí)間長(zhǎng)，鎖定范圍大，是死鎖的高發(fā)場(chǎng)景。應(yīng)該拆分為多個(gè)小事務(wù)，避免在單個(gè)事務(wù)中處理過(guò)多數(shù)據(jù)。

誤區(qū)五：加了唯一約束就不會(huì)重復(fù)插入

唯一約束只保證數(shù)據(jù)庫(kù)層面的約束，應(yīng)用層仍可能出現(xiàn)并發(fā)檢查通過(guò)后同時(shí)插入的情況。唯一約束是兜底方案，不是預(yù)防方案。

總結(jié)

MySQL 死鎖的排查與解決，核心在于三點(diǎn)：

第一，讀懂死鎖日志。innodb_print_all_deadlocks = ON開(kāi)啟后，error log 中的死鎖記錄包含了兩個(gè)事務(wù)持有的鎖和等待的鎖，這是定位根因的第一手材料。重點(diǎn)看HOLDS THE LOCK(S)和WAITING FOR THIS LOCK TO BE GRANTED部分，判斷是行鎖沖突、間隙鎖沖突還是唯一索引沖突。

第二，理解 InnoDB 鎖機(jī)制。Next-Key Lock 在 REPEATABLE READ 隔離級(jí)別下會(huì)鎖定索引范圍，而不僅僅是命中的行。理解這一點(diǎn)，就能明白為什么范圍查詢(xún)比等值查詢(xún)更容易引發(fā)死鎖。

第三，從業(yè)務(wù)邏輯和 SQL 兩層共同優(yōu)化。單純改 SQL 不能解決所有問(wèn)題——如果應(yīng)用層并發(fā)模式不合理，即使 SQL 再優(yōu)化也可能產(chǎn)生死鎖。分布式鎖、消息隊(duì)列、樂(lè)觀(guān)鎖等架構(gòu)層面的手段往往比單純調(diào)整 SQL 更有效。

死鎖是 MySQL 高并發(fā)下的正?，F(xiàn)象，完全消除死鎖幾乎不可能。目標(biāo)是將死鎖頻率控制在可接受范圍內(nèi)，并確保死鎖發(fā)生時(shí)業(yè)務(wù)能夠正確處理（重試、冪等）。不要一遇到死鎖就重啟數(shù)據(jù)庫(kù)，那只是在掩蓋問(wèn)題，不是在解決問(wèn)題。