데이터베이스 데드락 해결 완벽 가이드: 실전 감지와 해결 전략

-- 트랜잭션 A (사용자 주문)
BEGIN;
UPDATE accounts SET balance = balance - 50000 WHERE id = 1; -- 계좌 A 락
-- 여기서 잠깐 대기...
UPDATE inventory SET stock = stock - 1 WHERE product_id = 100; -- 재고 B 락 대기
COMMIT;

-- 트랜잭션 B (재고 조정)
BEGIN;
UPDATE inventory SET stock = stock + 10 WHERE product_id = 100; -- 재고 B 락
-- 여기서 잠깐 대기...
UPDATE accounts SET balance = balance + 10000 WHERE id = 1; -- 계좌 A 락 대기
COMMIT;

-- 현재 락 상태 확인
SELECT
 pid,
 usename,
 application_name,
 state,
 query,
 wait_event_type,
 wait_event
FROM pg_stat_activity
WHERE wait_event IS NOT NULL;

-- 락 대기 중인 쿼리 찾기
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.page IS NOT DISTINCT FROM blocked_locks.page
 AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
 AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
 AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
 AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
 AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
 AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
 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;

ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 67890; blocked by process 54321.
Process 54321 waits for ShareLock on transaction 12345; blocked by process 12345.
HINT: See server log for query details.

-- 최근 데드락 정보 확인
SHOW ENGINE INNODB STATUS\G

-- InnoDB 락 상태 확인
SELECT * FROM information_schema.INNODB_LOCKS;

-- 트랜잭션 대기 상태
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 현재 실행 중인 트랜잭션
SELECT * FROM information_schema.INNODB_TRX;

-- Performance Schema를 사용한 락 모니터링
SELECT
 r.trx_id AS waiting_trx,
 r.trx_mysql_thread_id AS waiting_thread,
 r.trx_query AS waiting_query,
 b.trx_id AS blocking_trx,
 b.trx_mysql_thread_id AS blocking_thread,
 b.trx_query AS blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

# 애플리케이션에서 데드락 재시도 로직 구현
import time
from sqlalchemy.exc import OperationalError

def execute_with_deadlock_retry(session, max_retries=3):
 """데드락 발생 시 자동 재시도"""
 for attempt in range(max_retries):
 try:
 # 트랜잭션 실행
 session.execute(your_query)
 session.commit()
 return True
 except OperationalError as e:
 if "deadlock detected" in str(e).lower():
 if attempt < max_retries - 1:
 # 지수 백오프로 재시도
 wait_time = (2 ** attempt) * 0.1
 time.sleep(wait_time)
 session.rollback()
 continue
 else:
 raise
 else:
 raise
 return False

-- PostgreSQL: 데드락 감지 타임아웃 설정
SET deadlock_timeout = '1s'; -- 기본값

-- MySQL: 락 대기 타임아웃
SET innodb_lock_wait_timeout = 50; -- 초 단위, 기본 50초

# 잘못된 방식 - 순서가 제각각
def transfer_money_bad(from_id, to_id, amount):
 # 순서가 from_id, to_id 순
 db.execute(f"UPDATE accounts SET balance = balance - {amount} WHERE id = {from_id}")
 db.execute(f"UPDATE accounts SET balance = balance + {amount} WHERE id = {to_id}")

# 올바른 방식 - 항상 ID 순서대로
def transfer_money_good(from_id, to_id, amount):
 # 항상 작은 ID부터 락
 first_id = min(from_id, to_id)
 second_id = max(from_id, to_id)

 if from_id < to_id:
 db.execute(f"UPDATE accounts SET balance = balance - {amount} WHERE id = {from_id}")
 db.execute(f"UPDATE accounts SET balance = balance + {amount} WHERE id = {to_id}")
 else:
 db.execute(f"UPDATE accounts SET balance = balance + {amount} WHERE id = {to_id}")
 db.execute(f"UPDATE accounts SET balance = balance - {amount} WHERE id = {from_id}")

# 나쁜 예 - 트랜잭션 안에서 느린 작업
def process_order_bad(order_id):
 with db.begin():
 order = db.query("SELECT * FROM orders WHERE id = %s FOR UPDATE", order_id)

 # 외부 API 호출 (느림!)
 payment_result = payment_api.charge(order['amount'])

 # 이메일 발송 (느림!)
 send_email(order['user_email'], "주문 완료")

 # 재고 감소
 db.execute("UPDATE inventory SET stock = stock - 1 WHERE product_id = %s", order['product_id'])

 db.commit()

# 좋은 예 - 빠른 DB 작업만 트랜잭션 안에
def process_order_good(order_id):
 # 먼저 읽기만
 order = db.query("SELECT * FROM orders WHERE id = %s", order_id)

 # 외부 작업은 트랜잭션 밖에서
 payment_result = payment_api.charge(order['amount'])

 # 짧은 트랜잭션으로 업데이트만
 with db.begin():
 db.execute("UPDATE inventory SET stock = stock - 1 WHERE product_id = %s", order['product_id'])
 db.execute("UPDATE orders SET status = 'completed' WHERE id = %s", order_id)
 db.commit()

 # 이메일은 나중에
 send_email(order['user_email'], "주문 완료")

-- READ COMMITTED: 가장 많이 쓰는 기본 설정 (PostgreSQL 기본값)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- REPEATABLE READ: 더 강한 일관성 필요할 때 (MySQL 기본값)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- SERIALIZABLE: 꼭 필요할 때만 (데드락 위험 최고)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 인덱스 없으면 전체 테이블 락
UPDATE orders SET status = 'completed'
WHERE user_id = 12345 AND created_at > '2025-01-01';

-- 인덱스 있으면 필요한 로우만 락
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);

UPDATE orders SET status = 'completed'
WHERE user_id = 12345 AND created_at > '2025-01-01';

-- 불필요한 FOR UPDATE
BEGIN;
SELECT * FROM products WHERE id = 100 FOR UPDATE; -- 읽기만 하는데 락?
--... 긴 로직...
COMMIT;

-- 꼭 필요할 때만
BEGIN;
SELECT stock FROM products WHERE id = 100; -- 그냥 읽기
-- 재고 확인
UPDATE products SET stock = stock - 1 WHERE id = 100; -- 업데이트할 때만 락
COMMIT;

from prometheus_client import Counter, Histogram

# 데드락 발생 카운터
deadlock_counter = Counter('db_deadlocks_total', 'Total number of deadlocks')

# 재시도 히스토그램
retry_histogram = Histogram('db_deadlock_retries', 'Number of retries on deadlock')

def execute_with_monitoring(session, query):
 retries = 0
 while retries < 3:
 try:
 result = session.execute(query)
 session.commit()
 if retries > 0:
 retry_histogram.observe(retries)
 return result
 except OperationalError as e:
 if "deadlock" in str(e).lower():
 deadlock_counter.inc() # 데드락 카운트
 retries += 1
 if retries >= 3:
 raise
 time.sleep(0.1 * (2 ** retries))
 session.rollback()
 else:
 raise

# 시간당 데드락 발생 횟수
rate(db_deadlocks_total[1h])

# 데드락 재시도 횟수 분포
histogram_quantile(0.95, rate(db_deadlock_retries_bucket[5m]))

# 데드락 발생률
rate(db_deadlocks_total[5m]) / rate(db_queries_total[5m])

import requests

def send_slack_alert(deadlock_count, query):
 """데드락 발생 시 슬랙 알림"""
 if deadlock_count > 10: # 10분에 10번 이상이면
 webhook_url = "https://hooks.slack.com/services/YOUR/WEBHOOK/URL"
 message = {
 "text": f" 데드락 폭증 경고!",
 "attachments": [{
 "color": "danger",
 "fields": [
 {"title": "발생 횟수", "value": str(deadlock_count), "short": True},
 {"title": "시간", "value": "최근 10분", "short": True},
 {"title": "쿼리", "value": f"```{query}```", "short": False}
 ]
 }]
 }
 requests.post(webhook_url, json=message)

# 문제가 된 코드
def create_order(user_id, items):
 with db.begin():
 # 1. 재고 확인 및 차감
 for item in items:
 inventory = db.query(
 "SELECT stock FROM inventory WHERE product_id = %s FOR UPDATE",
 item['product_id']
 )
 if inventory['stock'] < item['quantity']:
 raise OutOfStockError()

 db.execute(
 "UPDATE inventory SET stock = stock - %s WHERE product_id = %s",
 item['quantity'], item['product_id']
 )

 # 2. 주문 생성
 order_id = db.execute("INSERT INTO orders (...) VALUES (...)")

 # 3. 포인트 차감
 db.execute(
 "UPDATE users SET points = points - %s WHERE id = %s",
 used_points, user_id
 )

 db.commit()

# 개선된 코드
def create_order_fixed(user_id, items):
 # 1. 상품 ID 순서로 정렬 (핵심!)
 sorted_items = sorted(items, key=lambda x: x['product_id'])

 with db.begin():
 # 2. 정렬된 순서대로 재고 락
 for item in sorted_items:
 inventory = db.query(
 "SELECT stock FROM inventory WHERE product_id = %s FOR UPDATE",
 item['product_id']
 )
 if inventory['stock'] < item['quantity']:
 raise OutOfStockError()

 # 3. 모든 락을 확보한 후 업데이트 (빠르게!)
 for item in sorted_items:
 db.execute(
 "UPDATE inventory SET stock = stock - %s WHERE product_id = %s",
 item['quantity'], item['product_id']
 )

 # 4. 주문 생성
 order_id = db.execute("INSERT INTO orders (...) VALUES (...)")

 # 5. 포인트 차감
 db.execute(
 "UPDATE users SET points = points - %s WHERE id = %s",
 used_points, user_id
 )

 db.commit()