MySQLのデッドロックを調査する手順｜SHOW ENGINE INNODB STATUSで原因を特定してロック競合を解消する

この記事のポイント

・SHOW ENGINE INNODB STATUSのLATEST DETECTED DEADLOCKでデッドロックの詳細を確認できる
・innodb_print_all_deadlocks=ONでエラーログに全件を記録して調査履歴を残せる
・異なる順序でのロック取得・ギャップロック・外部キーがデッドロックの3大原因
・トランザクション内のロック順序を統一し短く保つことが根本的な再発防止策になる

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

mysql -u root -p -e "SHOW VARIABLES LIKE 'log_error';"
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| log_error     | /var/log/mysql/mysqld.log    |
+---------------+------------------------------+

grep -i deadlock /var/log/mysql/mysqld.log
2026-09-10T03:21:15.123456Z 42 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.

# 現在の値を確認
mysql -u root -p -e "SHOW GLOBAL VARIABLES LIKE 'innodb_print_all_deadlocks';"
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_print_all_deadlocks  | OFF   |
+-----------------------------+-------+

# 動的に有効化（再起動不要）
mysql -u root -p -e "SET GLOBAL innodb_print_all_deadlocks = ON;"

# /etc/my.cnf または /etc/mysql/conf.d/custom.cnf
[mysqld]
innodb_print_all_deadlocks = ON

mysql -u root -p -e "SHOW ENGINE INNODB STATUS\G" | less

------------------------
LATEST DETECTED DEADLOCK
------------------------
2026-09-10 12:21:15 0x7f3abc123456
*** (1) TRANSACTION:
TRANSACTION 421938, ACTIVE 0 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 42, OS thread handle 140145678901234, query id 8812 192.168.1.101 appuser updating
UPDATE orders SET status='shipped' WHERE id=100
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table shop.inventory
trx id 421938 lock_mode X locks rec but not gap
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 57 page no 3 n bits 72 index PRIMARY of table shop.orders
trx id 421938 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 421937, ACTIVE 0 sec updating
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 41, OS thread handle 140145678902345, query id 8811 192.168.1.102 appuser updating
UPDATE inventory SET qty=qty-1 WHERE product_id=500
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 57 page no 3 n bits 72 index PRIMARY of table shop.orders
trx id 421937 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table shop.inventory
trx id 421937 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)

# id=5が存在しない状況でこのSQLを実行するとギャップロックが発生する
# 3～7の範囲のギャップがロックされ、他セッションのINSERTをブロックする
SELECT * FROM orders WHERE id BETWEEN 3 AND 7 FOR UPDATE;

# セッション単位で変更（動作確認用）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

# グローバルに変更する場合はmy.cnfに追記して再起動
# [mysqld]
# transaction_isolation = READ-COMMITTED

-- ロック順序を統一した例（ordersを先にロックしてからinventoryをロック）
BEGIN;
SELECT id FROM orders WHERE id=100 FOR UPDATE;
SELECT product_id FROM inventory WHERE product_id=500 FOR UPDATE;
UPDATE orders SET status='shipped' WHERE id=100;
UPDATE inventory SET qty=qty-1 WHERE product_id=500;
COMMIT;

# 現在値の確認（デフォルト50秒）
mysql -u root -p -e "SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';"
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+

# 動的に変更（再起動不要）
mysql -u root -p -e "SET GLOBAL innodb_lock_wait_timeout = 30;"

-- ロックを保持・待機しているセッションを確認する
SELECT
  dl.ENGINE_TRANSACTION_ID AS trx_id,
  dl.LOCK_STATUS,
  dl.OBJECT_SCHEMA,
  dl.OBJECT_NAME,
  dl.LOCK_TYPE,
  dl.LOCK_MODE,
  esc.PROCESSLIST_ID AS thread_id,
  esc.PROCESSLIST_INFO AS query
FROM performance_schema.data_locks AS dl
JOIN performance_schema.threads AS esc
  ON dl.THREAD_ID = esc.THREAD_ID
ORDER BY trx_id, LOCK_STATUS
LIMIT 20;