問題描述

mysql部署1主3從，昨天發現主庫有大量報警錯誤：

Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

定位根因

MySQLTransactionRollbackException是MySQL 處理并發事務時的典型錯誤。一般有死鎖Deadlock和鎖等待超時innodb_lock_wait_timeout兩種；

當兩個或多個事務相互等待對方持有的鎖資源時，MySQL 會主動終止其中一個事務以打破僵局，從而拋出該異常，這一過程由 InnoDB 的死鎖檢測機制自動完成，無需人工干預。

分析死鎖日志，定位沖突點：MySQL 會記錄死鎖詳情到錯誤日志中，可通過以下命令查看最近一次死鎖信息：

SHOW ENGINE INNODB STATUS;

在輸出的?LATEST DETECTED DEADLOCK?部分，能看到：

• 參與死鎖的事務 ID 和 SQL 語句。
• 事務持有和等待的鎖類型（如?X-lock?排他鎖）。
• 涉及的表和行記錄（通過?space id?和?page no?定位）。

LATEST DETECTED DEADLOCK
------------------------
2025-09-11 17:19:22 0x7ee2dbbdf700
*** (1) TRANSACTION:
TRANSACTION 922499728, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 24349657, OS thread handle 139512913655552, query id 4391620199 10.199.99.99 kms_mpmanage_st_pjab updating
/*id:3b7f6356*//*ip=10.199.99.99*/update srunningset report_time = 1757582355865,is_del = 0,graph_version = null,service_item = ‘aaaaa’,build_id = 9652,report_version = 2,config_version = 'config-202508112023',scene_sdk_version = '1.3.20',table_sdk_version = '2.4.31'where pid = 179529542 and report_time < 1757582355865
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 828 page no 8 n bits 200 index uniq_ip_app_scene_s of table `camp`.`srunning` trx id 922499728 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 922499723, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
27 lock struct(s), heap size 3520, 35 row lock(s), undo log entries 12
MySQL thread id 24348682, OS thread handle 139512814368512, query id 4391620202 10.199.99.99 kms_mpmanage_st_pjab updating
/*id:389d6f54*//*ip=10.199.99.99*/update srunningSET report_time = 1757582353855, is_del = 1 WHERE ( pid = 179529542 and report_time < 1757582353855 )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 828 page no 8 n bits 200 index uniq_ip_app_scene_s of table `camp`.`srunning` trx id 922499723 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 828 page no 8 n bits 200 index uniq_ip_app_scene_s of table `camp`.`srunning` trx id 922499723 lock_mode X waiting
*** WE ROLL BACK TRANSACTION (1)

兩個update更新同一表死鎖是兩個事務在該表中以不同順序獲取行鎖，形成循環等待。

解決和避免

業務涉及事務的4個表都達到了500W+，且事務內同時查詢插入或更新4個表，所以多次死鎖

統一事務操作順序

多個事務更新同一批行時，按相反或不同的順序執行?UPDATE，是單表死鎖的最主要原因。

降低鎖粒度或使用合理的索引

使用非主鍵索引、范圍條件（>,?<,?BETWEEN）等，導致 InnoDB 加鎖范圍擴大（如間隙鎖、臨鍵鎖），增加交叉等待概率。

縮小事務范圍減少鎖持有時間

事務執行時間過長，長時間占用鎖資源，提高了與其他事務形成循環等待的可能性。

數據表量級對性能有顯著影響

• 小型表（10 萬行以內）SELECT、INSERT、UPDATE基本無瓶頸，操作耗時主要受 SQL 語句寫法影響
• 中型表（10 萬～1000 萬行）：SELECT無索引的全表掃描開始變慢，有索引但索引設計不合理（如低基數索引）時，性能下降明顯，復雜查詢（多表關聯、子查詢）耗時增加，可能出現臨時表或文件排序。INSERT：單條插入影響不大，但批量插入可能因索引維護（如 B+ 樹分裂）耗時增加。若表有多個索引，寫入性能下降更明顯（每個索引都需更新）。UPDATE：基于非索引字段的更新需要全表掃描，耗時顯著增加。高頻更新可能導致行鎖 / 表鎖競爭，出現等待延遲。
• 大型表（1000 萬～1 億行）：需要分庫分表、數據歸檔等拆分

代碼層面捕獲異常并重試

死鎖是臨時性異常，重試通常可解決。在代碼中捕獲?MySQLTransactionRollbackException，并設置合理的重試機制

鎖類型

• lock_type：鎖類型，常見值：
  • RECORD：行級鎖（針對具體行記錄）。
  • TABLE：表級鎖（針對整個表）。
• lock_mode：鎖模式，常見值：
  • S：共享鎖（讀鎖，允許其他事務讀，不允許寫）。
  • X：排他鎖（寫鎖，禁止其他事務讀和寫）。
  • GAP：間隙鎖（鎖定索引間隙，防止插入數據）。
  • Next-Key：臨鍵鎖（行鎖 + 間隙鎖的組合，默認的行鎖模式）。

行級排他鎖，UPDATE/DELETE/INSERT?時自動加鎖，阻止其他事務修改該行。

行級共享鎖，SELECT ... FOR SHARE?顯式加鎖，允許其他事務讀，但阻止寫。

排他間隙鎖，鎖定索引間隙，防止其他事務插入數據（如?UPDATE ... WHERE id > 10?可能觸發）。

臨鍵鎖（默認行鎖模式），鎖定行及前面的間隙，防止幻讀（REPEATABLE READ?隔離級別下默認）。