필사 모드: 데드락 진단과 예방 — 로그에서 두 쿼리를 특정하는 법

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;