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

Diagnosing and Preventing Deadlocks — How to Pin Down the Two Queries from the Log

• Introduction — When You Meet a deadlock detected Log for the First Time
• A Deadlock Is Not an Incident but Normal Operation
• Reading the Deadlock Report in the PostgreSQL Log
• Reading SHOW ENGINE INNODB STATUS in MySQL
• Three Patterns That Keep Recurring in Practice
  • Pattern 1 — Inconsistent Update Order
  • Pattern 2 — Lock Scope Widened by a Missing Index
  • Pattern 3 — Parent Row Locking Caused by Foreign Keys
• Prevention Principles — Order, Length, Index
• deadlock_timeout, lock_timeout, and Telling Them Apart from Lock Waits
• Closing — Deadlocks Are Not to Be Eliminated but Made Retryable

Introduction — When You Meet a deadlock detected Log for the First Time

A line like this shows up in the production log.

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"

At first glance it looks like a serious incident, but the fact that this log was written means the database detected the problem and has already resolved it. One transaction was rolled back and the other proceeded normally. The data is consistent.

The real problems are two. First, the possibility that the application is not retrying this error and is exposing it straight to the user. Second, the fact that repeated deadlocks are a signal of a structural flaw in the transaction design.

This article starts with how to pin down which two queries got tangled from the log, then covers the three recurring patterns and the prevention principles, and finally how not to confuse a deadlock with a plain lock wait.

A Deadlock Is Not an Incident but Normal Operation

A deadlock occurs when two transactions wait for locks each other is holding.

time ──────────────────────────────────────────▶

transaction A:  acquires lock on row 1 ─────── requests lock on row 2 ─── waits
transaction B:          acquires lock on row 2 ────── requests lock on row 1 ─── waits
                                                       both sides wait forever

If the database does nothing in this state, the two transactions stop forever. So both PostgreSQL and MySQL detect a cycle in the lock wait graph and pick one side to abort. This is the correct design, and there is no other option.

The key point follows from this. Eliminating deadlocks entirely cannot be the goal. Under any design, as long as there is concurrency you cannot drive the probability of occurrence to zero. The goals are two: lowering the frequency to a practical level, and having the application quietly retry when one does occur.

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

Two things must be honoured when retrying: re-run the entire transaction from the beginning, and mix jitter into the delay. Retrying with a fixed delay makes the two transactions collide again at the same interval.

Reading the Deadlock Report in the PostgreSQL Log

With the default settings all you get is the hint "see the server log," as in the log above. To see the actual queries, logging has to be configured.

-- Record deadlocks and long lock waits in the log
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();

Putting the process ID and the application name into log_line_prefix is decisive. The deadlock report identifies the other side by process number, so you have to find other log lines by that number to reconstruct the queries. If the application sets application_name in the connection string, which service was involved becomes immediately obvious.

After configuring, the log comes out like this.

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';

The reading order goes like this.

1. Process 24188 waits ... blocked by process 24193 — work out the direction of the cycle. Two lines means a two-party cycle; three or more lines means three or more transactions are entangled.
2. The Process 24188: and Process 24193: lines — the statement each process was last waiting on.
3. CONTEXT — tells you which tuple of which table it got stuck on.

Here we should address the most important misunderstanding. The two statements printed in the report are merely the last piece of the deadlock, not the cause. 24188 is waiting on B-200, but the lock it was already holding is A-100. The statement that created that A-100 lock does not appear in the report. In other words, identifying the cause requires all of the statements the two transactions executed before that point.

So in practice you use a combination like this.

-- Trace back in the log for other statements the processes involved ran at that time
-- (only possible if log_line_prefix contains %p)

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

Once a deadlock has passed, the log is the only evidence. But if you want to see the lock waits currently in progress, you can just query the catalog.

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 ...

If there is a process whose pg_blocking_pids is empty but whose state is idle in transaction, that one is the root. It means the application opened a transaction and went off to do something else.

Reading SHOW ENGINE INNODB STATUS in MySQL

MySQL keeps only the most recent deadlock in its status output.

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)

There is more information here than in the PostgreSQL report. In particular, the HOLDS THE LOCK(S) section shows even the locks each transaction was already holding. The cycle is fully reconstructed from this output alone. Transaction 1 holds A-100 and waits for B-200; transaction 2 holds B-200 and waits for A-100.

The items worth paying attention to when reading are these.

• The string after asc — the index key value of the locked record. You can tell immediately which row it is.
• index PRIMARY of table — tells you which index the lock was taken on. If a secondary index name appears, the access went through that index path.
• lock_mode X locks rec but not gap — only the record is locked. If you see locks gap before rec or just lock_mode X, it is a gap lock or a next-key lock. Seeing a gap lock is a signal that the lock scope is wide.
• WE ROLL BACK TRANSACTION (2) — InnoDB sacrifices the side with less work to undo, that is, the side that changed fewer rows.

The problem is that only the most recent one remains in this output. In production you have to log all of them.

SET GLOBAL innodb_print_all_deadlocks = ON;

Turning this setting on records every deadlock in the error log. If deadlocks occur at a rate where the logging load is a concern, that by itself is already a problem you need to fix.

Three Patterns That Keep Recurring in Practice

If you take apart a large number of deadlock cases, they generally converge to three.

Pattern 1 — Inconsistent Update Order

This is the most common. Two requests touch the same two rows in opposite order.

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

-- Request B (at the same time)
BEGIN;
UPDATE inventory SET stock = stock - 1 WHERE sku = 'B-200';
UPDATE inventory SET stock = stock - 1 WHERE sku = 'A-100';
COMMIT;

In application code this order is usually the order items were added to the cart, that is, a different order for every user. The fix is to sort the lock targets up front.

-- Handle it in one statement and make the order explicit
UPDATE inventory
SET stock = stock - v.qty
FROM (VALUES ('B-200', 1), ('A-100', 2)) AS v(sku, qty)
WHERE inventory.sku = v.sku;

There is a caveat. The lock acquisition order of a single UPDATE statement is determined by the query plan, so SQL alone does not fully guarantee it. To be certain, take the locks first in sorted order.

BEGIN;
SELECT sku FROM inventory
WHERE sku IN ('B-200', 'A-100')
ORDER BY sku
FOR UPDATE;
-- after this, the updates are safe in any order
UPDATE inventory SET stock = stock - 1 WHERE sku = 'B-200';
UPDATE inventory SET stock = stock - 1 WHERE sku = 'A-100';
COMMIT;

For batch jobs, the principle is to sort the keys on the application side before passing them along. The sort criterion can be anything; all that matters is using the same criterion in every code path.

Pattern 2 — Lock Scope Widened by a Missing Index

This is especially lethal in MySQL. If the condition column has no index, InnoDB locks every record it scans.

-- If there is no index on order_no
UPDATE orders SET status = 'cancelled' WHERE order_no = 'ORD-20260726-001';
-- in reality: a full table scan plus a lock on every row scanned

The target is one row, but a million rows get locked. In this state even two unrelated requests collide with each other. If the two queries appearing in a deadlock log look logically unrelated, suspect this pattern.

CREATE INDEX idx_orders_order_no ON orders (order_no);

PostgreSQL does not unconditionally lock every row it scans without an index, but it does lock the rows being updated. Even so, without an index it takes longer to find the update targets and the transaction gets longer, so the collision probability rises in the end anyway.

Pattern 3 — Parent Row Locking Caused by Foreign Keys

INSERTing into a child table locks the parent row in order to preserve referential integrity. PostgreSQL takes a FOR KEY SHARE level lock on the parent row, and InnoDB takes a shared lock.

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

-- Session A
BEGIN;
INSERT INTO orders (user_id, total) VALUES (7, 1000);   -- KEY SHARE on users(7)
UPDATE users SET name = 'Kim' WHERE id = 7;             -- its own lock, so it passes

-- Session B (at the same time)
BEGIN;
INSERT INTO orders (user_id, total) VALUES (7, 2000);   -- acquires KEY SHARE (possible, it is shared)
UPDATE users SET name = 'Lee' WHERE id = 7;             -- waits on the lock of session A

When two sessions reference the same parent row and then try to update that parent row, it becomes a deadlock. A shared lock can be held by several transactions at once, so the moment each tries to upgrade to an exclusive lock, they block each other.

In practice this pattern shows up often in code that "places an order while also updating the timestamp of the last order of the user." The remedy is to remove the parent update, split it into a separate table, or force an order so the parent is locked exclusively first.

-- Lock the parent exclusively first to eliminate upgrade contention
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;

In PostgreSQL, an UPDATE that does not modify the referenced column does not conflict with FOR KEY SHARE, so this problem is milder. Even so, if the structure has transactions contending for an exclusive lock on the same parent row, it is the same story.

Prevention Principles — Order, Length, Index

The prevention principles fall directly out of the three patterns.

Always access in the same order. If you lock several rows, sort them by a deterministic criterion such as ascending primary key. If you lock several tables, fix the table order as a coding convention too.

Keep transactions short. Deadlock probability is proportional to how long locks are held. External API calls, file I/O, and waiting for user input must not go inside a transaction. Following this one rule alone makes most deadlocks disappear.

-- Forcibly cut off connections that leave a transaction open and abandon it
ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';
SELECT pg_reload_conf();

Narrow the lock scope with indexes. Update conditions and locking read conditions must be handled by an index. This is not a query performance issue but a concurrency issue.

Sort batches and split them small. Update 100 thousand rows in one transaction and 100 thousand rows stay locked for that entire time.

-- In sorted order, in small chunks
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);

SKIP LOCKED structurally eliminates deadlocks when several workers consume the same queue. Because it skips locked rows instead of waiting on them, no cycle can form in the first place.

deadlock_timeout, lock_timeout, and Telling Them Apart from Lock Waits

There are three timeouts and their roles are completely different.

-- How long to wait before starting deadlock detection. Detection is expensive, so it is not immediate.
SHOW deadlock_timeout;              -- default 1s

-- How long before giving up on acquiring a lock. Unlimited by default.
SHOW lock_timeout;                  -- default 0 (unlimited)

-- An upper bound on the execution time of a whole statement.
SHOW statement_timeout;             -- default 0 (unlimited)

Lowering deadlock_timeout resolves deadlocks faster, but the detection algorithm then runs every time even for ordinary lock waits with no cycle, burning CPU. The default of 1 second is reasonable in most environments. You sometimes see advice to lower this value to something like 100ms, but unless deadlocks are frequent enough that the added response delay is genuinely a problem, the cost outweighs the benefit.

What you really should set is lock_timeout.

-- On the user response path, do not wait long for a lock
SET lock_timeout = '3s';

-- Especially short for DDL, because every query queued behind it gets blocked while it waits.
SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN memo text;

The corresponding setting in MySQL is innodb_lock_wait_timeout, and its default is 50 seconds. That is excessively long for a web request path.

SET SESSION innodb_lock_wait_timeout = 5;

Finally, let us sort out the most common misdiagnosis. A lock wait and a deadlock are different problems, and the responses differ too.

• A deadlock is a cycle. The database detects it within a second and kills one side. The error codes are 40P01 in PostgreSQL and 1213 in MySQL. The symptom is an intermittent error, not latency.
• A lock wait is not a cycle. Somebody holds a lock for a long time and everyone else queues up. Nobody dies; instead response times stretch out until the connection pool is exhausted and the whole service stops. The error code is 55P03, or a timeout.

The diagnosis "the service got slow because of deadlocks" is usually wrong. Deadlocks resolve quickly, so they do not create latency. If things got slow, look at lock waits, and behind them the long transactions or the abandoned idle in transaction connections.

-- Find the root of the lock wait chain
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;

Closing — Deadlocks Are Not to Be Eliminated but Made Retryable

To summarize.

When you look at the log, start by remembering that the two statements printed in the report are not the cause. They are merely the statement each transaction was last waiting on, and the real cause is the lock already taken before that. In MySQL the HOLDS THE LOCK(S) section gives you that information; in PostgreSQL you have to find the earlier log lines with the same process number.

Prevention comes down to three words: order, length, index. Lock in the same order, keep transactions short, and put an index on the locking condition. Add lock_timeout and idle_in_transaction_session_timeout on top and an accident will not spread into a total outage.

And even if you do all of this, deadlocks will still happen. You need code that catches 40P01 and 1213 and retries with backoff and jitter. Without that code, the results of your prevention work never reach the user.