-- 현재 격리 수준 확인
-- PostgreSQL
SHOW transaction_isolation;

-- MySQL
SELECT @@transaction_isolation;

-- 세션 단위로 격리 수준 변경
-- PostgreSQL
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- MySQL
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

실행 완료 또는 조회 결과가 표시됩니다.

BEGIN;
   │
   ① 트랜잭션 시작: 트랜잭션 ID(xid) 부여 + '내가 볼 스냅샷' 결정
   │      스냅샷 = 이 시점에 이미 커밋돼 있는 버전들의 경계선
   │
   ② 변경(UPDATE/DELETE): 기존 행을 그 자리서 덮어쓰지 않음
   │      옛 버전은 그대로 두고 '새 버전'을 추가(내 xid로 표시)
   │      아직 COMMIT 전 → 다른 트랜잭션에는 안 보임
   │
   ③ 읽기(SELECT): 스냅샷 규칙에 따라 '보여야 하는 버전'만 읽음
   │      READ COMMITTED  : 문장마다 '그 순간' 최신 커밋 스냅샷을 새로 잡음
   │      REPEATABLE READ : 트랜잭션 시작 시점 스냅샷을 끝까지 유지
   │
   ④ COMMIT: 내가 만든 새 버전이 '커밋됨'으로 확정 → 이후 시작하는 트랜잭션에 보임
   │      ROLLBACK: 새 버전을 버림 → 옛 버전이 계속 유효(되돌림)
   │
   ⑤ 뒷정리: 아무 스냅샷에도 안 보이게 된 옛(죽은) 버전을 VACUUM/purge가 회수
   ▼

-- [터미널 A] 트랜잭션 시작
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- → 10000 반환

-- [터미널 B] 이 시점에 다른 트랜잭션이 업데이트하고 커밋
UPDATE accounts SET balance = 5000 WHERE id = 1;
COMMIT;

-- [터미널 A] 같은 쿼리를 다시 실행
SELECT balance FROM accounts WHERE id = 1;
-- READ COMMITTED → 5000  (다른 트랜잭션의 COMMIT이 즉시 반영)
-- REPEATABLE READ → 10000  (트랜잭션 시작 시점 스냅샷 유지)

-- 재고 차감 안전 패턴 (SELECT FOR UPDATE)
BEGIN;

-- 행 잠금: 다른 트랜잭션이 이 행을 수정하면 여기서 대기
SELECT stock FROM products WHERE id = 42 FOR UPDATE;

-- 재고 충분할 때만 차감
UPDATE products SET stock = stock - 1 WHERE id = 42 AND stock > 0;

COMMIT;

트랜잭션 A (읽기)                트랜잭션 B (쓰기)
-----
BEGIN;                           BEGIN;
SELECT balance → 10000           UPDATE balance = 5000;
(스냅샷에서 읽음,                 (새 버전 생성,
 B의 변경과 충돌 없음)             A의 읽기를 차단 안 함)
                                 COMMIT;
SELECT balance → 10000           (구 버전은 VACUUM이
(여전히 스냅샷 값)                 나중에 정리)
COMMIT;

트랜잭션 A                          트랜잭션 B
-----
BEGIN;                               BEGIN;
UPDATE orders SET ... WHERE id=1;    UPDATE order_items SET ... WHERE id=10;
-- orders 행 잠금 획득                -- order_items 행 잠금 획득

UPDATE order_items SET ... WHERE id=10;  UPDATE orders SET ... WHERE id=1;
-- order_items 잠금 대기 (B가 보유)      -- orders 잠금 대기 (A가 보유)

-- 서로를 기다리다 DB가 데드락 감지 → 한쪽을 롤백

-- 현재 대기 중인 잠금 확인
SELECT
    blocked.pid          AS blocked_pid,
    blocked.query        AS blocked_query,
    blocking.pid         AS blocking_pid,
    blocking.query       AS blocking_query
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

-- 데드락 발생 시 로그 확인 (postgresql.conf)
-- log_lock_waits = on
-- deadlock_timeout = 1s  (1초 이상 잠금 대기 시 로그)

-- 가장 최근 데드락 상세 정보 확인
SHOW ENGINE INNODB STATUS\G
-- "LATEST DETECTED DEADLOCK" 섹션에서
-- 어떤 트랜잭션이 어떤 행의 잠금을 가지고 있었는지 확인

-- 데드락 로그 자동 기록 활성화
SET GLOBAL innodb_print_all_deadlocks = ON;
-- /var/log/mysql/error.log 에 기록됨

# 나쁜 예: 트랜잭션마다 잠금 순서가 다름
# 트랜잭션 A: account_a 잠금 → account_b 잠금
# 트랜잭션 B: account_b 잠금 → account_a 잠금

# 좋은 예: 항상 id 오름차순으로 잠금
def transfer(from_id, to_id, amount):
    first_id, second_id = sorted([from_id, to_id])
    # first_id 행 잠금 → second_id 행 잠금 (일관된 순서)
    with transaction():
        first = Account.objects.select_for_update().get(id=first_id)
        second = Account.objects.select_for_update().get(id=second_id)

from sqlalchemy.exc import OperationalError
import time

def execute_with_retry(session, func, max_retries=3):
    for attempt in range(max_retries):
        try:
            return func(session)
        except OperationalError as e:
            if 'deadlock' in str(e).lower() and attempt < max_retries - 1:
                session.rollback()
                time.sleep(0.1 * (attempt + 1))  # 점진적 대기
                continue
            raise