# 데이터베이스 Deadlock 완벽 해결 가이드: PostgreSQL & MySQL 프로덕션 디버깅 실전 전략
Table of Contents
블랙 프라이데이 새벽 3시, 데이터베이스가 멈췄다
블랙 프라이데이 새벽 3시. 평소 트래픽의 3배가 몰리는 상황에서 온콜 담당자의 휴대폰이 울립니다. Slack 알림이 폭주하고, 데이터베이스 커넥션이 쌓이기 시작합니다. 응답 시간은 허용 불가능한 수준으로 증가하고, 트랜잭션은 줄줄이 타임아웃됩니다.
로그를 확인하니 무서운 단어가 보입니다: “Deadlock detected”.
두 개의 트랜잭션이 서로의 락을 기다리며 영원히 진행되지 못하는 상황. Transaction 1은 order_items 테이블의 락을 쥐고 inventory 테이블을 기다리고, Transaction 2는 inventory 테이블의 락을 쥔 채 order_items 테이블을 기다립니다.
이것이 바로 프로덕션 데이터베이스 데드락의 현실입니다.
이 글에서는 PostgreSQL과 MySQL에서 발생하는 데드락을 실시간으로 탐지하고, 디버깅하고, 완벽하게 예방하는 실전 전략을 다룹니다.
데이터베이스 Deadlock이란 무엇인가?
정의
**Deadlock(교착 상태)**은 두 개 이상의 트랜잭션이 서로가 보유한 리소스(락)를 기다리면서 무한정 대기하는 상태입니다.
Transaction A: orders 테이블 락 보유 → inventory 테이블 락 대기
Transaction B: inventory 테이블 락 보유 → orders 테이블 락 대기
결과: 둘 다 영원히 진행 불가 (Circular Dependency)Lock Wait vs Deadlock
Lock Wait (락 대기):
- 한 트랜잭션이 다른 트랜잭션의 락이 해제되기를 기다림
- 대기 중인 트랜잭션이 결국 진행 가능 (락이 풀리면)
- 성능 저하는 있지만 시스템은 정상 동작
Deadlock (데드락):
- 순환 대기 (Circular Wait) 발생
- 외부 개입 없이는 절대 해결 불가
- 데이터베이스가 자동으로 희생자(victim) 트랜잭션을 선택하여 강제 종료(Rollback)
프로덕션에서 Deadlock의 진짜 비용
글로벌 소프트웨어 품질 비용
2025년 연구에 따르면:
- 연간 $3.1 trillion 손실: 불량 소프트웨어 품질로 인한 글로벌 비용
- 40% 기업: 분기마다 최소 1회 이상의 치명적인 소프트웨어 장애 발생
- 100배 비용 증가: 프로덕션에서 발견된 버그는 개발 단계보다 100배 더 비싸게 수정됨
실제 비즈니스 영향
E-commerce 플랫폼 사례 (블랙 프라이데이):
일반 트래픽: 1,000 TPS
블랙 프라이데이: 3,000 TPS (3배 증가)
Deadlock 발생 시:
- 주문 처리 중단: 30분
- 손실된 트랜잭션: 54,000건
- 평균 주문 금액: $150
- 직접적 매출 손실: $8,100,000 (약 108억 원)
간접 비용:
- 고객 불만 및 이탈
- 브랜드 신뢰도 하락
- 긴급 엔지니어링 투입
- 개발팀 다른 작업 중단SaaS 플랫폼 사례:
- 다운타임: 15분
- 영향받은 사용자: 50,000명
- SLA 위반: 99.9% → 99.8%
- 보상 비용: $250,000
- 고객 이탈: 5% (장기적 손실 더 큼)
5가지 치명적인 Deadlock 발생 패턴
1. 역순 락 획득 (Reverse Lock Ordering)
가장 흔한 데드락 원인입니다. 리소스에 접근하는 순서가 서로 다를 때 발생합니다.
-- Transaction A
BEGIN;
UPDATE orders SET status = 'processing' WHERE id = 1001; -- orders 락
UPDATE inventory SET quantity = quantity - 1 WHERE id = 5; -- inventory 대기
-- Transaction B (동시 실행)
BEGIN;
UPDATE inventory SET quantity = quantity - 1 WHERE id = 5; -- inventory 락
UPDATE orders SET status = 'confirmed' WHERE id = 1001; -- orders 대기
-- 결과: DEADLOCK!발생 빈도: 전체 데드락의 60% 영향도: 높음 (트랜잭션 롤백)
2. Bulk Update에서의 정렬 불일치
대량 업데이트 시 행의 순서가 다르면 데드락이 발생합니다.
// Transaction A - Node.js
const orderIds = [101, 102, 103, 104]; // 오름차순
for (const id of orderIds) {
await db.query('UPDATE orders SET processed = true WHERE id = $1', [id]);
}
// Transaction B - 동시 실행
const orderIds = [104, 103, 102, 101]; // 내림차순
for (const id of orderIds) {
await db.query('UPDATE orders SET shipped = true WHERE id = $1', [id]);
}
// Deadlock: 101과 104에서 락 순서 충돌해결책: 항상 동일한 순서(예: ID 오름차순)로 정렬하여 업데이트
3. 외래키 제약조건 (Foreign Key Constraints)
외래키 업데이트 시 자동으로 참조되는 테이블에 락이 걸립니다.
-- PostgreSQL 예시
-- 테이블 구조
CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id),
total DECIMAL
);
-- Transaction A
BEGIN;
UPDATE orders SET total = 150 WHERE id = 1; -- orders 행 락
UPDATE users SET name = 'John' WHERE id = 5; -- users 행 락
-- Transaction B
BEGIN;
UPDATE users SET name = 'Jane' WHERE id = 5; -- users 행 락 대기
UPDATE orders SET total = 200 WHERE user_id = 5; -- orders 행 락 대기
-- Deadlock!발생 조건:
- 자식 테이블(orders) → 부모 테이블(users) 순서
- 부모 테이블 → 자식 테이블 순서가 섞일 때
4. Index Range Lock에서의 겹침
WHERE 조건이 범위(range)일 때, 인덱스 락(Gap Lock, Next-Key Lock)이 겹쳐서 데드락이 발생합니다.
-- MySQL InnoDB
-- Transaction A
BEGIN;
SELECT * FROM products WHERE price BETWEEN 100 AND 200 FOR UPDATE;
-- Gap lock on index range [100, 200]
-- Transaction B
BEGIN;
SELECT * FROM products WHERE price BETWEEN 150 AND 250 FOR UPDATE;
-- Gap lock on index range [150, 250]
-- 두 범위가 겹침 [150, 200] → Deadlock 가능성5. 트랜잭션 내부에서 유저 입력 대기
트랜잭션을 열어둔 채 유저 입력을 기다리면 데드락 확률이 급증합니다.
# 잘못된 예시 - Python
conn = psycopg2.connect(DATABASE_URL)
cursor = conn.cursor()
cursor.execute("BEGIN")
cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
# 치명적 실수: 트랜잭션 내부에서 유저 입력 대기
user_confirmation = input("Confirm transfer? (yes/no): ") # 절대 안 됨!
if user_confirmation == "yes":
cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
conn.commit()
else:
conn.rollback()문제점:
- 트랜잭션이 수 초~분 동안 열려 있음
- 그동안 다른 트랜잭션이 같은 행을 접근하려고 대기
- 데드락 발생 확률 폭증
PostgreSQL Deadlock 디버깅 5단계
1단계: 긴급 대응 - 현재 데드락 확인
-- 현재 블로킹된 쿼리 확인
SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;2단계: log_lock_waits 활성화
# postgresql.conf 수정
log_lock_waits = on # 락 대기 로그 활성화
deadlock_timeout = 1s # 데드락 감지 대기 시간
log_min_duration_statement = 250 # 250ms 이상 쿼리 로그
# 설정 적용
sudo systemctl reload postgresql
# 또는
SELECT pg_reload_conf();성능 영향: 없음! (데드락 감지 로직은 항상 실행됨) 프로덕션 권장: 항상 켜두세요.
3단계: 데드락 로그 분석
# PostgreSQL 로그에서 데드락 찾기
sudo tail -f /var/log/postgresql/postgresql-16-main.log | grep -A 50 "deadlock detected"로그 예시:
ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 67890; blocked by process 12346.
Process 12346 waits for ShareLock on transaction 67891; blocked by process 12345.
HINT: See server log for query details.
CONTEXT: while updating tuple (0,42) in relation "orders"
Process 12345: UPDATE orders SET status = 'confirmed' WHERE id = 1001;
Process 12346: UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 5;핵심 정보:
- 데드락을 일으킨 두 프로세스 ID
- 각 프로세스가 실행 중이던 쿼리
- 어떤 테이블의 어떤 행에서 충돌했는지
4단계: pg_stat_statements로 패턴 파악
-- pg_stat_statements 확장 설치 (한 번만)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- 가장 느린 쿼리 TOP 10
SELECT
query,
calls,
total_exec_time,
mean_exec_time,
max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;5단계: 근본 원인 해결
공통 해결 방법:
- 일관된 락 순서 강제: 모든 코드에서 테이블/행 접근 순서를 통일하세요 (예: 알파벳 순, ID 오름차순).
- 트랜잭션 짧게 유지: 트랜잭션 내부에서는 DB 작업만 수행하고, 외부 API 호출 등은 밖으로 빼세요.
- Batch 업데이트 시 정렬: 여러 행을 업데이트할 때는 반드시 ID 순으로 정렬하세요.
MySQL/InnoDB Deadlock 디버깅
SHOW ENGINE INNODB STATUS 활용
-- 가장 최근 데드락 정보 확인
SHOW ENGINE INNODB STATUS\G출력 예시 (LATEST DETECTED DEADLOCK 섹션):
------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-01-15 03:42:15 0x7f8a2c001700
*** (1) TRANSACTION:
TRANSACTION 421578942, ACTIVE 0.000 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 12345, OS thread handle 140236789012224, query id 987654 localhost root updating
UPDATE orders SET status = 'confirmed' WHERE id = 1001
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `shop`.`inventory`
*** (2) TRANSACTION:
TRANSACTION 421578943, ACTIVE 0.000 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 12346, OS thread handle 140236788948736, query id 987655 localhost root updating
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 5
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `shop`.`inventory`
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3 page no 5 n bits 80 index PRIMARY of table `shop`.`orders`
*** WE ROLL BACK TRANSACTION (1)MySQL 데드락 로깅 설정
-- my.cnf 또는 my.ini 설정
[mysqld]
innodb_print_all_deadlocks = 1 # 모든 데드락을 에러 로그에 기록
log_error = /var/log/mysql/error.log효과: 모든 데드락이 에러 로그에 자동 기록됨 (SHOW ENGINE INNODB STATUS는 최근 1개만 보관하므로 이 설정이 필수적입니다).
Deadlock 예방 전략 5가지
1. 애플리케이션 레벨: 일관된 락 순서
// Java/Spring Boot - 좋은 예
@Transactional
public void processOrder(Long orderId, Long inventoryId) {
// 항상 ID 작은 것부터 락을 획득
Long firstId = Math.min(orderId, inventoryId);
Long secondId = Math.max(orderId, inventoryId);
if (orderId < inventoryId) {
orderRepository.lockById(orderId);
inventoryRepository.lockById(inventoryId);
} else {
inventoryRepository.lockById(inventoryId);
orderRepository.lockById(orderId);
}
}2. 데이터베이스 레벨: 낙관적 락 (Optimistic Locking)
-- version 컬럼 추가
ALTER TABLE orders ADD COLUMN version INT NOT NULL DEFAULT 0;애플리케이션에서 업데이트 시 version을 체크하여 충돌을 감지합니다. 데드락 가능성을 원천 차단할 수 있습니다.
3. 트랜잭션 타임아웃 설정
-- PostgreSQL
SET statement_timeout = '30s'; -- 30초 이상 실행 시 자동 취소
-- MySQL
SET SESSION innodb_lock_wait_timeout = 30; -- 30초 대기 후 에러4. Retry 로직 with Exponential Backoff
데드락은 일시적인 문제일 수 있으므로, 재시도 로직을 구현하면 서비스 안정성이 크게 향상됩니다.
5. 배치 작업은 작은 청크로 분할
한 번에 100만 건을 업데이트하면 락을 너무 오래 잡고 있게 됩니다. 1,000건씩 나누어 처리하세요.
프로덕션 모니터링 설정
Prometheus + Grafana로 데드락 모니터링
Grafana 대시보드 쿼리:
# 데드락 발생 횟수
rate(pg_stat_database_deadlocks{datname="production"}[5m])
# 락 대기 중인 쿼리 수
pg_stat_activity_count{state="active", wait_event_type="Lock"}AlertManager로 알림 설정
# alertmanager/rules.yml
groups:
- name: database_deadlocks
rules:
- alert: HighDeadlockRate
expr: rate(pg_stat_database_deadlocks[5m]) > 0.1
for: 2m
labels:
severity: critical
annotations:
summary: "High deadlock rate detected"결론: Deadlock은 예방 가능하다
데이터베이스 데드락은 무서워 보이지만, 올바른 이해와 체계적인 접근으로 충분히 예방하고 해결할 수 있습니다.
핵심 원칙 3가지:
- 일관된 락 순서: 모든 코드에서 동일한 순서로 리소스 접근
- 짧은 트랜잭션: 트랜잭션 내부에서는 DB 작업만
- 적극적인 모니터링: 로깅과 알림으로 조기 발견
블랙 프라이데이 새벽 3시에 당황하지 않으려면, 지금 당장 로깅을 켜고 모니터링을 설정하세요. 연간 $3.1 trillion 손실의 일부가 되지 마시고, 프로덕션 데이터베이스를 완벽하게 제어하는 엔지니어가 되시길 바랍니다.
