生產(chǎn)環(huán)境 MySQL 死鎖：定位思路與根治方案

MySQL死鎖是數(shù)據(jù)庫運維和后端開發(fā)中最棘手的問題之一。與普通查詢超時不同，死鎖意味著兩個或多個事務(wù)相互持有對方需要的鎖，形成循環(huán)依賴，導(dǎo)致涉及的表或行無法被任何事務(wù)繼續(xù)修改。業(yè)務(wù)系統(tǒng)一旦出現(xiàn)死鎖，輕則部分請求報錯，重則整個業(yè)務(wù)鏈路的寫操作集體阻塞。

本文從死鎖的形成原理出發(fā)，系統(tǒng)講解如何排查、分析和解決MySQL死鎖問題。內(nèi)容適用于MySQL 5.7/8.0及兼容版本（MySQL 8.0在鎖機制上有部分改進）。

1. 死鎖的形成原理

1.1 事務(wù)與鎖的基本概念

MySQL的InnoDB引擎采用行級鎖（Row Lock）實現(xiàn)并發(fā)控制。事務(wù)在對某行數(shù)據(jù)進行修改時，會對該行加鎖，直到事務(wù)提交（COMMIT）或回滾（ROLLBACK）時才釋放鎖。

-- 事務(wù)A：先鎖定id=1的行
BEGIN;
SELECT*FROMaccountsWHEREid=1FORUPDATE; -- 對id=1加排他鎖
-- 此時事務(wù)A持有id=1的鎖，等待事務(wù)B釋放id=2的鎖

-- 事務(wù)B：先鎖定id=2的行
BEGIN;
SELECT*FROMaccountsWHEREid=2FORUPDATE; -- 對id=2加排他鎖
-- 此時事務(wù)B持有id=2的鎖，等待事務(wù)A釋放id=1的鎖
-- 循環(huán)等待形成：事務(wù)A等事務(wù)B，事務(wù)B等事務(wù)A → 死鎖

1.2 死鎖的必要條件

數(shù)據(jù)庫理論中，死鎖的形成必須滿足以下四個條件（Carl-RoadConditions）：

MySQL的InnoDB引擎通過死鎖檢測（Deadlock Detection）來打破循環(huán)等待：當(dāng)檢測到死鎖后，會主動回滾代價最小的事務(wù)（通常是持有最少行鎖的事務(wù)），讓其他事務(wù)繼續(xù)執(zhí)行。

1.3 鎖的類型與兼容性

InnoDB的鎖類型遠(yuǎn)比表面上復(fù)雜：

Next-Key Lock是死鎖的高發(fā)區(qū)：當(dāng)執(zhí)行范圍查詢（如WHERE id > 10 AND id < 20）時，Next-Key Lock會鎖定(10, 20)這個間隙，如果另一個事務(wù)試圖插入這個范圍內(nèi)的記錄，會被阻塞，長期積累可能導(dǎo)致死鎖。

-- 事務(wù)A：鎖定id > 10的所有行（實際鎖定區(qū)間10到正無窮）
BEGIN;
SELECT*FROMordersWHEREuser_id >100FORUPDATE;
-- Next-Key Lock鎖定區(qū)間 (100, +∞)

-- 事務(wù)B：插入id=101的新訂單（嘗試獲取插入意向鎖）
BEGIN;
INSERTINTOorders (id, user_id, amount)VALUES(NULL,101,100);
-- 被事務(wù)A的Next-Key Lock阻塞：Gap Lock沖突

-- 事務(wù)A再執(zhí)行：插入id=102的新訂單
INSERTINTOorders (id, user_id, amount)VALUES(NULL,102,200);
-- 嘗試獲取插入意向鎖，但事務(wù)B已經(jīng)持有id=102的Gap鎖
-- 死鎖形成

2. 死鎖的排查方法

2.1 開啟死鎖日志

MySQL默認(rèn)將死鎖信息記錄到錯誤日志，但不會記錄每次死鎖的完整鎖等待圖。可以通過以下方式增強日志：

-- 查看當(dāng)前死鎖日志配置
SHOWVARIABLESLIKE'innodb_print_all_deadlocks'; -- 默認(rèn)OFF

-- 開啟所有死鎖信息輸出到錯誤日志（需要SUPER權(quán)限）
SETGLOBALinnodb_print_all_deadlocks =ON;

-- 查看死鎖日志（MySQL錯誤日志文件）
-- Linux: /var/log/mysql/error.log
-- macOS Homebrew: /usr/local/var/mysql/{hostname}.err
-- Windows: {數(shù)據(jù)目錄}mysql*.err

innodb_print_all_deadlocks = ON會將每次死鎖的完整信息輸出到錯誤日志，包括涉及的事務(wù)、SQL語句、持有的鎖和等待的鎖。

2.2 使用information_schema獲取鎖信息

-- 查看當(dāng)前所有事務(wù)持有的鎖
SELECT
  t.trx_id,
  t.trx_state,
  t.trx_started,
  t.trx_rows_locked,
  t.trx_query,
  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
FROMinformation_schema.INNODB_TRX t
JOINinformation_schema.INNODB_LOCKS lONt.trx_id = l.lock_trx_id
ORDERBYt.trx_started;

-- 查看鎖等待關(guān)系
SELECT
  requesting_trx.trx_idASrequesting_trx_id,
  requesting_trx.trx_queryASrequesting_query,
  blocking_trx.trx_idASblocking_trx_id,
  blocking_trx.trx_queryASblocking_query,
  blocking_locks.lock_idASblocking_lock_id,
  blocking_locks.lock_modeASblocking_lock_mode,
  blocking_locks.lock_typeASblocking_lock_type,
  blocking_locks.lock_tableASblocking_lock_table
FROMinformation_schema.INNODB_LOCK_WAITS lw
JOINinformation_schema.INNODB_TRX requesting_trxONlw.requesting_trx_id = requesting_trx.trx_id
JOINinformation_schema.INNODB_TRX blocking_trxONlw.blocking_trx_id = blocking_trx.trx_id
JOINinformation_schema.INNODB_LOCKS blocking_locksONlw.blocking_lock_id = blocking_locks.lock_id;

2.3 使用performance_schema監(jiān)控鎖事件

MySQL 8.0引入了更強大的performance_schema鎖監(jiān)控：

-- 開啟鎖監(jiān)控（需要重啟或重新配置）
UPDATEperformance_schema.setup_instruments
SETENABLED ='YES', TIMED ='YES'
WHERENAMELIKE'wait/lock%';

UPDATEperformance_schema.setup_consumers
SETENABLED ='YES'
WHERENAMELIKE'%events_transactions%';

-- 查看最近的鎖等待事件
SELECT*FROMperformance_schema.events_waits_history_long
WHEREevent_nameLIKE'%lock%'
ORDERBYTIMER_ENDDESC
LIMIT20;

2.4 解讀死鎖日志

開啟innodb_print_all_deadlocks后，錯誤日志會輸出類似以下內(nèi)容的死鎖報告：

2025-04-27 1045 0x7f8c9a4c8700 INNODB MONITOR OUTPUT
========================
LATEST DETECTED DEADLOCK
------------------------
2025-04-27 1042 0x7f8c9a4c8700
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 5 sec inserting
mysql tablesinuse 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 99, OS thread handle 0x7f8c9a4c8700, query id 10001 localhost root updating

-- 事務(wù)1正在執(zhí)行的SQL
UPDATE orders SET status ='shipped'WHERE user_id > 100

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 123 page no 5 n bits 200 index idx_user_id of table `shop`.`orders`
trx id 12345 lock_mode X locks rec but not gap
-- 事務(wù)1持有orders表中idx_user_id索引上的記錄鎖

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 5 n bits 200 index idx_user_id of table `shop`.`orders`
trx id 12345 lock_mode X locks rec but not gap waiting
-- 事務(wù)1正在等待另一個記錄鎖（可能是Gap鎖沖突）

*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 3 sec inserting
mysql tablesinuse 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 100, OS thread handle 0x7f8c9a4c8800, query id 10002 localhost root updating

-- 事務(wù)2正在執(zhí)行的SQL
INSERT INTO orders (id, user_id, amount) VALUES (NULL, 105, 299.00)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 123 page no 5 n bits 200 index idx_user_id of table `shop`.`orders`
trx id 12346 lock_mode X locks gap before rec
-- 事務(wù)2持有Gap鎖（鎖定區(qū)間）

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 5 n bits 200 index idx_user_id of table `shop`.`orders`
trx id 12346 lock_mode X locks rec but not gap waiting
-- 事務(wù)2正在等待記錄鎖

-- MySQL決定回滾事務(wù)12346（較晚開始，持有鎖較少）
*** WE ROLL BACK TRANSACTION 12346

關(guān)鍵解讀點：

LOCK WAIT表示當(dāng)前正在等待鎖

HOLDS THE LOCK(S)表示事務(wù)已持有的鎖

WE ROLL BACK TRANSACTION后面是MySQL決定回滾的事務(wù)ID

lock_mode X locks rec but not gap是記錄鎖，不鎖定間隙

lock_mode X locks gap before rec是間隙鎖，鎖定記錄前的區(qū)間

3. 常見死鎖場景與解決方案

3.1 場景一：不同事務(wù)以不同順序訪問多行

問題：事務(wù)A先鎖定行1再鎖定行2，事務(wù)B先鎖定行2再鎖定行1，形成循環(huán)等待。

解決：確保所有事務(wù)以相同順序訪問資源。

# 錯誤的并發(fā)寫入（死鎖高發(fā)）
deftransfer_funds_wrong(from_id, to_id, amount):
 withconnection.cursor()ascursor:
   # 事務(wù)1: A->B, 事務(wù)2: B->A → 死鎖
    cursor.execute("SELECT balance FROM accounts WHERE id = %s FOR UPDATE", (from_id,))
    cursor.execute("SELECT balance FROM accounts WHERE id = %s FOR UPDATE", (to_id,))

# 正確的并發(fā)寫入（順序加鎖）
deftransfer_funds_correct(from_id, to_id, amount):
 withconnection.cursor()ascursor:
   # 按ID順序加鎖，避免循環(huán)等待
    first_id, second_id = (from_id, to_id)iffrom_id < to_id?else?(to_id, from_id)
? ? ? ? cursor.execute("SELECT balance FROM accounts WHERE id = %s FOR UPDATE", (first_id,))
? ? ? ? cursor.execute("SELECT balance FROM accounts WHERE id = %s FOR UPDATE", (second_id,))

3.2 場景二：索引導(dǎo)致的間隙鎖沖突

問題：范圍查詢或使用索引范圍掃描時，Next-Key Lock鎖定較寬區(qū)間，導(dǎo)致插入操作被阻塞。

解決：

使用覆蓋索引減少鎖范圍：覆蓋索引（Covering Index）可以讓查詢只需掃描索引，不必回表，減少鎖定的記錄數(shù)。

-- 創(chuàng)建覆蓋索引：查詢只需掃描idx_user_id，無需回表鎖定主鍵
CREATEINDEXidx_user_id_coveringONorders(user_id,status, amount);

-- 改寫查詢使用覆蓋索引
SELECTstatus, amountFROMordersWHEREuser_id =100;

調(diào)整隔離級別：將隔離級別從REPEATABLE READ降為READ COMMITTED，可以減少Gap Lock的使用。

-- 方法1：會話級別調(diào)整
SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;

-- 方法2：配置文件永久調(diào)整（my.cnf / my.ini）
-- [mysqld]
-- transaction-isolation = READ-COMMITTED

3.3 場景三：主從延遲導(dǎo)致的鎖等待升級

問題：主從架構(gòu)中，從庫應(yīng)用事件存在延遲，主庫上的長事務(wù)持有鎖時間延長，增加死鎖概率。

解決：

-- 檢查從庫延遲
SHOWSLAVESTATUSG
-- 關(guān)注 Seconds_Behind_Master 字段

-- 優(yōu)化從庫應(yīng)用速率
STOPSLAVE;
CHANGEMASTERTOMASTER_RETRY_COUNT =3;
STARTSLAVE;

3.4 場景四：大事務(wù)拆分

問題：單個事務(wù)中處理過多數(shù)據(jù)，持有鎖的時間過長，死鎖窗口擴大。

解決：將大事務(wù)拆分為小批量事務(wù)，減少單次持有的鎖數(shù)量。

# 錯誤：單事務(wù)處理10萬條記錄
defbatch_update_wrong(ids):
 withconnection.cursor()ascursor:
    cursor.execute("BEGIN")
   foridinids: # 10萬次循環(huán)，鎖持有時間長
      cursor.execute(
       "UPDATE orders SET status = 'processed' WHERE id = %s",
        (id,)
      )
    cursor.execute("COMMIT")

# 正確：分批處理，每批500條
defbatch_update_correct(ids, batch_size=500):
 withconnection.cursor()ascursor:
   foriinrange(0, len(ids), batch_size):
      batch = ids[i:i + batch_size]
      cursor.execute("BEGIN")
      cursor.execute(
       "UPDATE orders SET status = 'processed' WHERE id IN (%s)"%
       ",".join(["%s"] * len(batch)),
        batch
      )
      cursor.execute("COMMIT")
      connection.commit() # 每批后立即釋放鎖

4. 代碼層面的防死鎖設(shè)計

4.1 應(yīng)用層鎖順序控制

在應(yīng)用層維護一個全局鎖順序規(guī)則：

importthreading

# 定義全局鎖順序：按資源ID排序
# 所有需要同時鎖定多個資源的代碼，必須按此順序獲取鎖
LOCK_ORDER = {}

classAccountService:
 def__init__(self, db_connection):
    self.conn = db_connection

 deftransfer(self, from_id: int, to_id: int, amount: decimal.Decimal):
   # 按ID順序確定加鎖順序
    first_id, second_id = sorted([from_id, to_id])

   # 獲取應(yīng)用層邏輯鎖（防止代碼層面的并發(fā)問題）
   withself._get_lock(first_id):
     withself._get_lock(second_id):
        self._do_transfer(first_id, second_id, amount)

 def_get_lock(self, account_id: int):
   """獲取指定賬戶的應(yīng)用層鎖"""
   ifaccount_idnotinLOCK_ORDER:
      LOCK_ORDER[account_id] = threading.Lock()
   returnLOCK_ORDER[account_id]

4.2 鎖超時機制

設(shè)置合理的鎖等待超時時間，避免無限等待：

-- 查看當(dāng)前鎖等待超時（默認(rèn)50秒）
SHOWVARIABLESLIKE'innodb_lock_wait_timeout'; -- 默認(rèn)50

-- 設(shè)置鎖等待超時為10秒
SETGLOBALinnodb_lock_wait_timeout =10;

-- 在應(yīng)用程序中捕獲鎖等待超時異常

importpymysql
frompymysqlimportOperationalError

try:
 withconnection.cursor()ascursor:
    cursor.execute("SELECT ... FOR UPDATE")
exceptOperationalErrorase:
 ife.args[0] ==1205: # Lock wait timeout error
    logger.error(f"Lock wait timeout exceeded for transaction")
   raiseRetryableError("Lock timeout, should retry")frome
 raise

4.3 重試機制

鎖等待超時不等同于死鎖，被超時的其他事務(wù)可能已經(jīng)完成。應(yīng)用層應(yīng)實現(xiàn)有限重試：

importtime
frompymysqlimportOperationalError

MAX_RETRIES =3
RETRY_DELAY =0.5# 秒

deftransfer_with_retry(from_id, to_id, amount):
 forattemptinrange(MAX_RETRIES):
   try:
     withconnection.cursor()ascursor:
        cursor.execute("BEGIN")
       # 鎖定邏輯...
        cursor.execute("COMMIT")
     returnTrue
   exceptOperationalErrorase:
     ife.args[0] ==1205: # Lock wait timeout
        connection.rollback()
        logger.warning(f"Attempt{attempt +1}failed, retrying...")
        time.sleep(RETRY_DELAY * (attempt +1))
       continue
     raise
  logger.error(f"Transfer failed after{MAX_RETRIES}attempts")
 returnFalse

5. 監(jiān)控與預(yù)防

5.1 持續(xù)監(jiān)控指標(biāo)

建議在數(shù)據(jù)庫監(jiān)控系統(tǒng)中追蹤以下指標(biāo)：

-- 查看InnoDB行鎖統(tǒng)計
SHOWSTATUSLIKE'Innodb_row_lock%';
-- +-------------------------------+-------+
-- | Variable_name         | Value |
-- +-------------------------------+-------+
-- | Innodb_row_lock_current_waits | 0   |
-- | Innodb_row_lock_time     | 12345 |
-- | Innodb_row_lock_time_avg   | 123  |
-- | Innodb_row_lock_time_max   | 5000 |
-- | Innodb_row_lock_waits     | 100  |
-- +-------------------------------+-------+

5.2 慢查詢與死鎖的關(guān)聯(lián)分析

長時間運行的查詢是死鎖的主要誘因。定期分析慢查詢?nèi)罩荆?/p>

# 查看慢查詢配置
mysql -e"SHOW VARIABLES LIKE 'slow_query%';"
mysql -e"SHOW VARIABLES LIKE 'long_query_time';"

# 常用分析命令
mysqldumpslow -s t -t 20 /var/log/mysql/slow.log # 按時間排序top 20
mysqldumpslow -s c -t 20 /var/log/mysql/slow.log  # 按次數(shù)排序top 20

6. 排障清單

死鎖排查的核心能力在于正確解讀INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS三個系統(tǒng)表聯(lián)合查詢出的鎖等待關(guān)系圖。運維和開發(fā)人員應(yīng)建立肌肉記憶：在生產(chǎn)環(huán)境出現(xiàn)死鎖時，第一時間導(dǎo)出這三個表的數(shù)據(jù)快照，同時開啟innodb_print_all_deadlocks抓取完整的死鎖上下文。事后的日志分析比現(xiàn)場排查更有價值，因為死鎖發(fā)生時相關(guān)事務(wù)可能已經(jīng)回滾。