beautifulsoups.dev
Published on

MySQL·PostgreSQL 데드락 분석과 트랜잭션·인덱스 튜닝

Authors

서버가 느려졌는데 CPU는 낮고, 커넥션은 늘고, 애플리케이션은 타임아웃이 터진다. 이런 상황에서 DB를 보면 deadlock detected 또는 Deadlock found when trying to get lock이 찍혀 있는 경우가 많다. 데드락은 “두 트랜잭션이 서로가 가진 락을 기다리며 영원히 진행하지 못하는 상태”지만, 실제 운영에서는 락 범위를 키우는 쿼리/인덱스/트랜잭션 습관이 원인인 경우가 대부분이다.

이 글에서는 MySQL(InnoDB)과 PostgreSQL을 나눠서 (1) 데드락이 어떻게 만들어지는지, (2) 어떤 로그/뷰로 원인을 좁히는지, (3) 트랜잭션·인덱스 튜닝으로 어떻게 줄이는지를 실전 관점으로 정리한다.

> 참고로, 장애 분석은 “증상 → 원인 후보 → 재현 → 관측 지표로 검증”의 흐름이 중요하다. 인프라/배포 쪽에서 비슷한 접근이 필요하다면 GitHub Actions OIDC assume-role 실패 원인별 해결 같은 글의 진단 흐름도 참고가 된다.

데드락의 본질: 순서 불일치 + 락 범위 확장

데드락을 만드는 핵심 조건은 단순하다.

  1. 트랜잭션 A가 리소스 1을 잠근다.
  2. 트랜잭션 B가 리소스 2를 잠근다.
  3. A가 리소스 2를 기다린다.
  4. B가 리소스 1을 기다린다.

여기서 실무적으로 중요한 포인트는 두 가지다.

  • 잠그는 순서가 일관되지 않다(예: 어떤 코드는 orders → users 순서로 갱신, 다른 코드는 users → orders 순서로 갱신).
  • 필요 이상으로 많은 행/인덱스 레코드를 잠근다(예: 인덱스 미스/범위 조건으로 인해 락이 넓게 걸림, MySQL의 next-key lock, PostgreSQL의 FK/트리거/업데이트 확장 등).

MySQL(InnoDB) 데드락 분석: 로그와 next-key lock 이해

MySQL에서 데드락이 자주 생기는 패턴

  • 서로 다른 순서로 여러 테이블/행을 UPDATE/SELECT ... FOR UPDATE 하는 경우
  • 인덱스가 없어 UPDATE ... WHERE가 많은 행을 스캔하며 락 범위를 넓히는 경우
  • REPEATABLE READ(기본)에서 next-key lock(레코드 락 + 갭 락) 때문에 범위 조건이 커지는 경우
  • 유니크 키 경쟁(동일 유니크 키로 insert/update) + 보조 인덱스 경합

재현 예제: 잠금 순서 불일치로 데드락 만들기

테이블:

CREATE TABLE accounts (
  id BIGINT PRIMARY KEY,
  balance INT NOT NULL
) ENGINE=InnoDB;

INSERT INTO accounts VALUES (1, 1000), (2, 1000);

세션 1:

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 아직 COMMIT 하지 않음
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- 여기서 대기 가능

세션 2:

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
UPDATE accounts SET balance = balance + 100 WHERE id = 1; -- 서로 교착

두 트랜잭션이 서로 반대 순서로 락을 잡으면 데드락이 발생하기 쉽다.

데드락 로그 확인: SHOW ENGINE INNODB STATUS

MySQL은 데드락이 발생하면 한 트랜잭션을 희생(rollback) 시키고, 그 직전의 데드락 정보를 InnoDB 상태에 남긴다.

SHOW ENGINE INNODB STATUS\G

LATEST DETECTED DEADLOCK 섹션에서 확인할 것:

  • 어떤 트랜잭션들이 관련되었는지(트랜잭션 ID)
  • 어떤 인덱스/레코드를 잡고 있었는지
  • 대기 중인 락이 무엇인지(LOCK WAIT)
  • 실제 실행된 SQL(가능하면)

운영 팁:

  • MySQL 8.0에서는 performance_schema의 데이터 락 테이블도 활용 가능
  • 데드락은 발생 빈도가 낮아도 영향이 크므로, 애플리케이션 로그에 SQL + 바인딩 값 + 트랜잭션 경계를 남기면 재현이 쉬워진다.

MySQL 인덱스 튜닝 포인트: “락 범위를 줄이는 인덱스”

InnoDB는 “찾는 과정” 자체가 락 범위를 좌우한다. 인덱스가 없으면 많은 레코드를 스캔하며 더 많은 레코드/갭을 잠글 수 있다.

다음 쿼리를 보자.

UPDATE orders
SET status = 'PAID'
WHERE user_id = 123 AND status = 'PENDING';

권장 인덱스:

CREATE INDEX idx_orders_user_status ON orders(user_id, status);

이 인덱스가 없으면 user_id로 빠르게 좁히지 못해 스캔이 커지고, 그만큼 잠금 경합/데드락 가능성이 올라간다.

또한 MySQL에서는 범위 조건이 섞이면 next-key lock이 걸려 갭까지 잠그는 상황이 생길 수 있다. 예:

SELECT * FROM orders
WHERE user_id = 123 AND created_at >= '2026-01-01'
FOR UPDATE;

이때는 INDEX(user_id, created_at) 같은 복합 인덱스로 범위 스캔을 최소화하는 것이 도움이 된다.

MySQL 트랜잭션 튜닝 포인트

  • 항상 동일한 순서로 락을 잡기: 예를 들어 “부모 테이블 → 자식 테이블” 순서로 업데이트 규칙을 정한다.
  • 트랜잭션을 짧게: 외부 API 호출/파일 IO/락이 필요 없는 계산을 트랜잭션 밖으로 빼라.
  • SELECT ... FOR UPDATE 남발 금지: 정말로 “갱신할 행”만 잠그는지 점검.
  • 격리수준 검토: InnoDB 기본 REPEATABLE READ에서의 next-key lock이 문제라면, 워크로드에 따라 READ COMMITTED로 완화가 가능한지 검토(단, 애플리케이션 정합성 요구와 함께 판단).

PostgreSQL 데드락 분석: lock graph와 wait 이벤트

PostgreSQL은 MVCC로 읽기-쓰기 충돌이 상대적으로 적지만, 다음 상황에서 데드락이 흔하다.

  • 서로 다른 순서로 UPDATE/SELECT ... FOR UPDATE 수행
  • FK(외래키)로 인해 부모/자식 테이블 간 잠금이 엮임
  • UPDATE가 인덱스/핫 업데이트 실패 등으로 더 많은 작업을 유발
  • SERIALIZABLE 또는 명시적 락(LOCK TABLE) 사용

데드락 로그 활성화 및 확인

PostgreSQL은 데드락을 감지하면 로그에 상세 정보를 남길 수 있다.

postgresql.conf(또는 ALTER SYSTEM)에서:

deadlock_timeout = '1s'
log_lock_waits = on
  • deadlock_timeout: 이 시간 이상 락 대기가 지속되면 데드락 탐지/락 대기 로그를 남길 후보가 된다.
  • log_lock_waits: 락 대기 상황을 로그로 남긴다.

로그에는 보통 다음이 포함된다.

  • 어떤 프로세스(pid)들이 어떤 락을 기다리는지
  • 어떤 relation(테이블) 또는 tuple(행)인지
  • 어떤 쿼리가 관련되는지

실시간 관측: pg_stat_activity + pg_locks

대기 중인 세션과 락을 묶어서 보는 기본 쿼리 패턴:

SELECT
  a.pid,
  a.usename,
  a.state,
  a.wait_event_type,
  a.wait_event,
  a.query,
  now() - a.query_start AS query_age,
  l.locktype,
  l.mode,
  l.granted,
  l.relation::regclass AS relation
FROM pg_stat_activity a
LEFT JOIN pg_locks l ON l.pid = a.pid
WHERE a.state <> 'idle'
ORDER BY query_age DESC;

여기서 확인할 것:

  • wait_event_type='Lock' 인지
  • 어떤 relation에서 대기하는지
  • mode(RowExclusiveLock, ShareLock 등)와 granted 여부
  • 서로가 서로의 락을 기다리는 “고리”가 있는지

PostgreSQL에서 인덱스가 데드락에 미치는 영향

PostgreSQL은 InnoDB의 next-key lock 같은 갭 락은 없지만, 업데이트 대상 행을 찾는 과정이 길어지면 그만큼 락을 오래 쥐게 된다. 즉, 인덱스는 “락 범위”보다는 “락 보유 시간”을 줄여 데드락 가능성을 낮추는 쪽으로 기여하는 경우가 많다.

예:

UPDATE orders
SET status = 'PAID'
WHERE user_id = 123 AND status = 'PENDING';

권장 인덱스:

CREATE INDEX CONCURRENTLY idx_orders_user_status
ON orders(user_id, status);
  • CONCURRENTLY는 운영 중 락 영향을 줄이기 위한 옵션(빌드가 오래 걸 수 있음)

추가로, 특정 상태값만 자주 갱신한다면 부분 인덱스도 고려할 수 있다.

CREATE INDEX CONCURRENTLY idx_orders_pending_by_user
ON orders(user_id)
WHERE status = 'PENDING';

이렇게 하면 PENDING만 빠르게 찾고 갱신해 락 점유 시간을 줄일 수 있다.

PostgreSQL 트랜잭션/쿼리 튜닝 포인트

  • 업데이트 순서 고정: 동일한 리소스 집합을 갱신한다면 항상 같은 정렬 기준으로 잠가라.

예를 들어 여러 행을 갱신해야 한다면:

-- 잠금 순서를 강제(일관된 order)
SELECT id
FROM accounts
WHERE id IN (1, 2, 3)
ORDER BY id
FOR UPDATE;

-- 이후 update 수행
UPDATE accounts SET balance = balance + 10 WHERE id IN (1, 2, 3);
  • FK가 있는 경우 부모/자식 갱신 순서를 통일하고, 필요한 인덱스(특히 FK 컬럼 인덱스)를 점검한다.
  • 트랜잭션 내부에서 오래 걸리는 작업(대량 처리, 네트워크 호출)을 제거한다.
  • NOWAIT, SKIP LOCKED로 경쟁을 회피하는 설계도 가능(큐/잡 처리에 유용).
SELECT *
FROM jobs
WHERE status = 'READY'
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 10;

공통 체크리스트: “재발 방지”에 바로 쓰는 10가지

1) 데드락은 ‘버그’가 아니라 ‘설계 부채’로 본다

재시도 로직만 넣고 끝내면, 트래픽이 오를수록 더 자주 터진다. 왜 락 순서가 꼬였는지, 왜 락을 오래 쥐는지까지 해결해야 한다.

2) 락 순서를 코드 규칙으로 만든다

  • 예: users를 먼저 잠그고, 그 다음 orders를 잠근다.
  • 예: 다건 처리 시 ORDER BY pk로 잠금 순서를 고정한다.

3) “찾는 조건”에 맞는 인덱스를 만든다

  • MySQL: 인덱스 미스는 락 범위 확장(스캔 증가 + next-key lock 확대)로 이어질 수 있음
  • PostgreSQL: 인덱스 미스는 락 보유 시간 증가로 이어져 교착 확률 증가

4) 트랜잭션을 짧게 유지한다

  • 트랜잭션 안에서 로그 포맷팅, 외부 API 호출, 대용량 JSON 직렬화 등을 하지 않는다.

5) 불필요한 SELECT ... FOR UPDATE를 제거한다

“읽고 나중에 업데이트할지도 몰라서” 잠그는 습관은 경합을 폭발시킨다. 정말로 업데이트가 필요할 때만 잠근다.

6) 배치/온라인 트래픽의 갱신 대상이 겹치지 않게 한다

대량 배치가 동일한 키 범위를 건드리면 데드락이 아니라도 락 대기 지옥이 된다. 파티셔닝/샤딩/시간대 분리 등 운영 설계를 고려한다.

7) 유니크 키 경쟁을 줄인다

동일 키에 대한 동시 upsert는 데드락/경합의 단골이다. 애플리케이션 레벨에서 키 생성 전략, 큐잉, 분산락(필요 시)을 검토한다.

8) 관측 가능성(Observability)을 올린다

  • MySQL: 데드락 로그 수집, performance_schema 락 테이블, 슬로우 쿼리
  • PostgreSQL: log_lock_waits, deadlock_timeout, pg_stat_activity/pg_locks

9) 재시도는 “최후의 안전장치”로만 둔다

데드락은 DB가 한 트랜잭션을 롤백시키므로 애플리케이션은 실패를 받는다. 이때 재시도는 필요하지만,

  • 지수 백오프
  • 멱등성 보장
  • 재시도 횟수 제한 을 함께 가져가야 한다.

10) 변경 전후로 “락 대기 시간”을 측정한다

튜닝은 감으로 하지 말고, 변경 전후로 락 대기/데드락 빈도를 비교해야 한다. 운영 환경에서 빠르게 진단하는 습관은 쿠버네티스에서도 동일하게 중요하다(예: EKS Pod Pending(Insufficient cpu) 원인과 해결).

결론: 데드락은 ‘인덱스’와 ‘트랜잭션 경계’가 함께 만든다

MySQL과 PostgreSQL은 락 구현이 다르지만, 데드락을 줄이는 방향은 놀랄 만큼 비슷하다.

  • 일관된 잠금 순서로 교착 고리를 끊고
  • 적절한 인덱스로 스캔/락 보유 시간을 줄이며
  • 짧은 트랜잭션으로 경합 창(window)을 최소화한다

운영에서 데드락을 잡는 가장 빠른 방법은 “데드락 로그 1건을 끝까지 읽고, 그 쿼리를 동일한 조건으로 재현한 다음, 인덱스/쿼리/순서를 바꿔 다시 측정”하는 것이다. 이 루프를 몇 번만 반복해도, 대부분의 데드락은 구조적으로 줄일 수 있다.

Discuss on Twitter • View on GitHub