필사 모드: デッドロックの診断と予防 — ログから二つのクエリを特定する方法

ERROR:  deadlock detected
DETAIL:  Process 24188 waits for ShareLock on transaction 98211; blocked by process 24193.
         Process 24193 waits for ShareLock on transaction 98209; blocked by process 24188.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (128,17) in relation "orders"

時間 ──────────────────────────────────────────▶

トランザクションA:  row 1 のロック獲得 ─────── row 2 のロック要求 ─── 待機
トランザクションB:          row 2 のロック獲得 ────── row 1 のロック要求 ─── 待機
                                                       両方とも永遠に待機

# PostgreSQL: 40P01 (deadlock_detected)
# MySQL:      1213 (ER_LOCK_DEADLOCK)
RETRYABLE = {"40001", "40P01"}

-- デッドロックと長引いたロック待ちをログに残す
ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET deadlock_timeout = '1s';
ALTER SYSTEM SET log_min_error_statement = 'error';
ALTER SYSTEM SET log_line_prefix = '%m [%p] %u@%d app=%a ';
SELECT pg_reload_conf();

2026-07-26 14:02:11.442 KST [24188] api@shop app=order-service ERROR:  deadlock detected
2026-07-26 14:02:11.442 KST [24188] api@shop app=order-service DETAIL:  Process 24188 waits for ShareLock on transaction 98211; blocked by process 24193.
	Process 24193 waits for ShareLock on transaction 98209; blocked by process 24188.
	Process 24188: UPDATE inventory SET stock = stock - 1 WHERE sku = 'B-200';
	Process 24193: UPDATE inventory SET stock = stock - 1 WHERE sku = 'A-100';
2026-07-26 14:02:11.442 KST [24188] api@shop app=order-service HINT:  See server log for query details.
2026-07-26 14:02:11.442 KST [24188] api@shop app=order-service CONTEXT:  while updating tuple (128,17) in relation "inventory"
2026-07-26 14:02:11.442 KST [24188] api@shop app=order-service STATEMENT:  UPDATE inventory SET stock = stock - 1 WHERE sku = 'B-200';

-- 関連プロセスがその時刻に実行した別の文をログから逆追跡する
-- (log_line_prefixに%pがあってこそ可能)

grep -E '\[24188\]|\[24193\]' /var/log/postgresql/postgresql.log \
  | awk '$1 " " $2 >= "2026-07-26 14:01:50"' \
  | head -60

SELECT a.pid,
       a.application_name,
       a.state,
       now() - a.xact_start        AS tx_age,
       now() - a.state_change      AS state_age,
       pg_blocking_pids(a.pid)     AS blocked_by,
       left(a.query, 80)           AS query
FROM pg_stat_activity a
WHERE a.backend_type = 'client backend'
  AND (cardinality(pg_blocking_pids(a.pid)) > 0
       OR a.state = 'idle in transaction')
ORDER BY tx_age DESC;

  pid  | application_name |        state        |    tx_age    |  state_age   | blocked_by |              query
-------+------------------+---------------------+--------------+--------------+------------+--------------------------------
 24193 | order-service    | idle in transaction | 00:04:12.881 | 00:04:10.220 | {}         | SELECT ... FOR UPDATE
 24188 | order-service    | active              | 00:00:08.114 | 00:00:08.101 | {24193}    | UPDATE inventory SET stock ...

SHOW ENGINE INNODB STATUS\G

------------------------
LATEST DETECTED DEADLOCK
------------------------
2026-07-26 14:02:11 0x7f2a1c0d5700
*** (1) TRANSACTION:
TRANSACTION 4821094, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 8812, query id 2210934 10.0.3.21 api updating
UPDATE inventory SET stock = stock - 1 WHERE sku = 'B-200'

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 421 page no 5 n bits 80 index PRIMARY of table `shop`.`inventory`
trx id 4821094 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; ...  0: len 5; hex 412d313030; asc A-100;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 421 page no 5 n bits 80 index PRIMARY of table `shop`.`inventory`
trx id 4821094 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; ...  0: len 5; hex 422d323030; asc B-200;

*** (2) TRANSACTION:
TRANSACTION 4821096, ACTIVE 2 sec starting index read
MySQL thread id 8815, query id 2210941 10.0.3.22 api updating
UPDATE inventory SET stock = stock - 1 WHERE sku = 'A-100'

*** (2) HOLDS THE LOCK(S):
... asc B-200;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
... asc A-100;

*** WE ROLL BACK TRANSACTION (2)

SET GLOBAL innodb_print_all_deadlocks = ON;

-- 要求A
BEGIN;
UPDATE inventory SET stock = stock - 1 WHERE sku = 'A-100';
UPDATE inventory SET stock = stock - 1 WHERE sku = 'B-200';
COMMIT;

-- 要求B (同時に)
BEGIN;
UPDATE inventory SET stock = stock - 1 WHERE sku = 'B-200';
UPDATE inventory SET stock = stock - 1 WHERE sku = 'A-100';
COMMIT;

-- 一つの文で処理し、順序を明示する
UPDATE inventory
SET stock = stock - v.qty
FROM (VALUES ('B-200', 1), ('A-100', 2)) AS v(sku, qty)
WHERE inventory.sku = v.sku;

BEGIN;
SELECT sku FROM inventory
WHERE sku IN ('B-200', 'A-100')
ORDER BY sku
FOR UPDATE;
-- 以降の更新は順序に関係なく安全である
UPDATE inventory SET stock = stock - 1 WHERE sku = 'B-200';
UPDATE inventory SET stock = stock - 1 WHERE sku = 'A-100';
COMMIT;

-- order_noにインデックスがないなら
UPDATE orders SET status = 'cancelled' WHERE order_no = 'ORD-20260726-001';
-- 実際にはテーブル全体スキャン + スキャンしたすべての行にロック

CREATE INDEX idx_orders_order_no ON orders (order_no);

CREATE TABLE users (id bigserial PRIMARY KEY, name text);
CREATE TABLE orders (
  id      bigserial PRIMARY KEY,
  user_id bigint REFERENCES users(id),
  total   numeric
);

-- セッションA
BEGIN;
INSERT INTO orders (user_id, total) VALUES (7, 1000);   -- users(7)にKEY SHARE
UPDATE users SET name = 'Kim' WHERE id = 7;             -- 自分のロックなので通過

-- セッションB (同時)
BEGIN;
INSERT INTO orders (user_id, total) VALUES (7, 2000);   -- KEY SHARE獲得 (共有なので可能)
UPDATE users SET name = 'Lee' WHERE id = 7;             -- セッションAのロックを待機

-- 親を先に排他的にロックして昇格の競合をなくす
BEGIN;
SELECT id FROM users WHERE id = 7 FOR UPDATE;
INSERT INTO orders (user_id, total) VALUES (7, 1000);
UPDATE users SET last_ordered_at = now() WHERE id = 7;
COMMIT;

-- コネクションがトランザクションを開いたまま放置するのを強制的に断ち切る
ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';
SELECT pg_reload_conf();

-- 整列された順序で、小さな塊で
WITH batch AS (
  SELECT id FROM jobs
  WHERE status = 'queued'
  ORDER BY id
  LIMIT 1000
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs SET status = 'processing'
WHERE id IN (SELECT id FROM batch);

-- デッドロック検知を開始するまで待つ時間。検知コストが大きいのですぐには行わない。
SHOW deadlock_timeout;              -- デフォルト 1s

-- ロック獲得を諦めるまでの時間。デフォルトは無制限。
SHOW lock_timeout;                  -- デフォルト 0 (無制限)

-- 文全体の実行時間の上限。
SHOW statement_timeout;             -- デフォルト 0 (無制限)

-- ユーザー応答の経路ではロックを長く待たない
SET lock_timeout = '3s';

-- DDLは特に短く。待機中に後続のすべてのクエリが一緒に詰まるからだ。
SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN memo text;

SET SESSION innodb_lock_wait_timeout = 5;

-- ロック待ちの連鎖の根を探す
WITH RECURSIVE chain AS (
  SELECT pid, unnest(pg_blocking_pids(pid)) AS blocker, 1 AS depth
  FROM pg_stat_activity
  WHERE cardinality(pg_blocking_pids(pid)) > 0
  UNION ALL
  SELECT c.blocker, unnest(pg_blocking_pids(c.blocker)), c.depth + 1
  FROM chain c
  WHERE cardinality(pg_blocking_pids(c.blocker)) > 0 AND c.depth < 10
)
SELECT DISTINCT a.pid, a.state, now() - a.xact_start AS tx_age, left(a.query, 60)
FROM chain c
JOIN pg_stat_activity a ON a.pid = c.blocker
WHERE cardinality(pg_blocking_pids(c.blocker)) = 0;