←

Lock contention — writers blocking writers

DB perf

Understand it step by step (한국어로 이해 → 영어로 말하기)

쓰기가 쓰기를 막는 문제입니다. 100명이 같은 row를 동시에 UPDATE하면 Postgres는 한 명씩 줄을 세웁니다. 첫 트랜잭션이 COMMIT할 때까지 나머지 99명은 그냥 기다립니다. CPU는 한가한데 응답만 느려지는, "바쁜 게 아니라 기다리는" 문제입니다. 증상 → 원인 → 보는 법 → 기본 수정 → 스케일 수정 → 트레이드오프 순서로 올라갑니다.
  1. 1

    증상: UPDATE 하나가 평소 5ms인데 피크 시간엔 3,000ms가 걸립니다. 이상한 점은 DB CPU가 20%밖에 안 된다는 것. 디스크도 한가합니다. 쿼리가 느린 게 아니라, 락을 기다리는 중입니다. '느린데 CPU는 한가하다'가 나오면 대기(wait)를 의심하세요.

    🔧 도구:APM p99 latencypg_stat_statements

    UPDATE offers
SET status = 'ACCEPTED', updated_at = now()
WHERE id = 42;
-- usually 5 ms; at peak 3,000 ms+
-- meanwhile: DB CPU 20%, disk idle. Not working — waiting.
    🗣 영어로 말해

    “Latency spiked but CPU stayed low, so I suspected waiting, not slow work.”

    checking microphone…

  2. 2

    원인: Postgres의 row lock은 문장이 끝날 때가 아니라 COMMIT할 때 풀립니다. 트랜잭션 안에서 UPDATE 후 외부 API를 2초 부르면, 그 2초 동안 그 row는 계속 잠겨 있습니다. 같은 row를 노리는 100개 요청은 한 줄로 서고, 한 건당 30ms씩이면 마지막 요청은 3초를 기다립니다. 범인은 보통 '핫 row'(카운터, 인기 매물 한 건) + '긴 트랜잭션' 조합입니다.

    BEGIN;
UPDATE listings SET offer_count = offer_count + 1
WHERE id = 7;                  -- row lock taken HERE
-- ... app calls an external API, ~2,000 ms ...
COMMIT;                        -- lock released only HERE
-- everyone else updating listing 7 waits the full 2 seconds
    🗣 영어로 말해

    “Row locks live until commit, so one slow transaction blocks the whole line.”

    checking microphone…

  3. 3

    보는 법: EXPLAIN ANALYZE는 락 대기를 안 보여줍니다 — 실행계획은 멀쩡하게 나옵니다. 대신 pg_stat_activity에서 wait_event_type='Lock'인 세션을 세고, pg_blocking_pids()로 누가 누구를 막는지 봅니다. 범인은 보통 xact_start가 오래된 트랜잭션 하나입니다. log_lock_waits=on을 켜 두면 1초 넘게 기다린 락이 로그에 남습니다.

    🔧 도구:pg_stat_activitypg_blocking_pids()pg_lockslog_lock_waits

    SELECT pid, wait_event_type, now() - xact_start AS xact_age, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

SELECT pid, pg_blocking_pids(pid) AS blocked_by, query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

-- postgresql.conf: log_lock_waits = on
-- LOG: process 4321 still waiting for ShareLock
--      on transaction 9876 after 1000.123 ms
    🗣 영어로 말해

    “I query pg_stat_activity and ask Postgres directly who is blocking whom.”

    checking microphone…

  4. 4

    기본 수정: 트랜잭션을 짧게. UPDATE → COMMIT을 5ms 안에 끝내고, 이메일 발송이나 외부 API 호출은 전부 commit 뒤로 뺍니다. 락을 잡는 시간이 2,000ms → 5ms로 줄면 뒤에 줄 선 요청들의 대기도 400분의 1로 줄어듭니다. 화려한 기술이 아니라 순서 정리입니다.

    ⚖️ Trade-off: commit 후의 외부 호출이 실패하면 DB는 이미 커밋된 상태라 따로 재시도가 필요합니다(outbox 패턴 등). 그래도 '짧은 트랜잭션 + 사후 보정'이 '긴 트랜잭션 + 락 줄서기'보다 낫습니다.

    ✅ Fix: 트랜잭션 경계를 다시 긋기: (1) 락이 필요한 쓰기만 트랜잭션 안에 둔다, (2) 느린 작업(외부 호출, 메일, 파일)은 전부 commit 뒤로 뺀다, (3) 여러 row를 잠글 때는 항상 같은 순서(id 오름차순)로 잠가 데드락을 예방한다.

    -- before: lock held ~2,000 ms
BEGIN;
UPDATE offers SET status = 'ACCEPTED' WHERE id = 42;
-- external email API call inside the transaction (bad)
COMMIT;

-- after: lock held ~5 ms
BEGIN;
UPDATE offers SET status = 'ACCEPTED' WHERE id = 42;
COMMIT;
-- send the email AFTER commit (retry separately if it fails)
    🗣 영어로 말해

    “My first fix is boring: commit fast, move the slow calls after commit.”

    checking microphone…

  5. 5

    스케일 수정: 핫 row 자체를 없앱니다. 방법 1 — 카운터를 16개 shard row로 쪼개고 읽을 때 SUM. 한 row에 몰리던 쓰기 경합이 1/16로 줄어듭니다. 방법 2 — UPDATE 대신 INSERT(append-only 이벤트)로 바꾸면 쓰기끼리 같은 row를 다툴 일 자체가 없습니다. 방법 3 — 작업 큐라면 FOR UPDATE SKIP LOCKED로, 잠긴 row를 기다리지 않고 건너뜁니다.

    ✅ Fix: 경합의 단위를 바꾸는 게 핵심입니다. row 1개 = 락 1개이므로, row를 16개로 쪼개거나(샤딩), 쓰기를 전부 새 row로 만들거나(append-only), 잠긴 row를 건너뛰게(SKIP LOCKED) 합니다. 락을 '더 잘 잡는' 게 아니라 '안 잡아도 되게' 만드는 방향입니다.

    -- 1) counter sharding: 1 hot row -> 16 rows
UPDATE listing_counters SET cnt = cnt + 1
WHERE listing_id = 7 AND shard = floor(random() * 16)::int;
-- read: SELECT sum(cnt) FROM listing_counters WHERE listing_id = 7;

-- 3) queue workers skip locked rows instead of waiting
SELECT id FROM jobs
WHERE status = 'PENDING'
ORDER BY created_at
LIMIT 10
FOR UPDATE SKIP LOCKED;
    🗣 영어로 말해

    “At scale I remove the hot row itself — shard it or append instead.”

    checking microphone…

  6. 6

    트레이드오프 정리. 샤딩 카운터: 쓰기는 16배 흩어지지만 읽기가 SUM 16 rows가 되고 값이 잠깐 안 맞을 수 있습니다. append-only: 쓰기 충돌은 사라지지만 집계 테이블과 정리 작업이 따로 필요합니다. SKIP LOCKED: 대기는 없어지지만 엄격한 처리 순서는 포기합니다. 경합이 낮으면 락 대신 낙관적 방식(version 컬럼)으로 충분하고, 경합이 높으면 구조를 바꾸는 쪽이 이깁니다.

    ⚖️ Trade-off: 정답은 하나가 아닙니다. 정확한 즉시 읽기가 꼭 필요하면 '짧은 트랜잭션'까지만 가고, 쓰기 처리량이 우선이면 약간 늦는 숫자를 받아들이고 구조를 바꿉니다.

    -- low contention: optimistic instead of locking
UPDATE offers
SET status = 'ACCEPTED', version = version + 1
WHERE id = 42 AND version = 7;
-- 0 rows updated = someone else won; reload and retry
    🗣 영어로 말해

    “I'd rather serve slightly stale counts than keep writers waiting in line.”

    checking microphone…

6단계 영어를 다 말하면 → 이 메커니즘 전체를 영어로 설명할 수 있게 된다.