락과 2단계 잠금

T1: SELECT * FROM orders WHERE id = 1;  → S-Lock(id=1)
T2: SELECT * FROM orders WHERE id = 1;  → S-Lock(id=1)  ✓ 허용
T3: UPDATE orders SET status = 'X' WHERE id = 1; → X-Lock 요청
    → T1, T2가 S-Lock을 해제할 때까지 대기!

참고: MVCC 환경에서는 일반 SELECT가 S-Lock을 걸지 않음
  * Oracle: SELECT는 항상 무잠금 (Undo 버전 읽기)
  * MySQL InnoDB: 일반 SELECT는 무잠금 (Snapshot Read)
  * 락이 걸리는 경우: SELECT ... FOR SHARE / FOR UPDATE

  테이블 수준 잠금        행 수준 잠금
  IS (Intent Shared)    → 하위에 S-Lock이 있음을 표시
  IX (Intent Exclusive) → 하위에 X-Lock이 있음을 표시

목적
  다른 트랜잭션이 테이블 수준 잠금(LOCK TABLE)을 걸려고 할 때
  모든 행을 확인하지 않고 테이블 의도 잠금만 확인하면 됨

예시
  T1: UPDATE orders SET status='X' WHERE id=1;
      → 행: X-Lock(id=1), 테이블: IX-Lock(orders)

  T2: LOCK TABLE orders IN EXCLUSIVE MODE;
      → orders에 IX가 있으므로 바로 대기! (모든 행 검사 불필요)

세밀 ←───────────────────────────────────────→ 거침
  행(Row)   페이지(Page)   테이블(Table)   데이터베이스

행 수준 잠금
  * Oracle, MySQL InnoDB, PostgreSQL 기본
  * 동시성 높음, 잠금 관리 오버헤드 큼
  * 대부분의 OLTP에 적합

페이지 수준 잠금
  * SQL Server가 사용 (행 또는 페이지 선택)
  * 행보다 거칠지만 테이블보다 세밀

테이블 수준 잠금
  * MySQL MyISAM 엔진 기본
  * 동시성 매우 낮음, 관리 오버헤드 작음
  * LOCK TABLE 명령으로 명시적 사용

잠금 에스컬레이션 (Lock Escalation)
  * SQL Server에서 발생
  * 한 트랜잭션이 너무 많은 행 잠금을 보유하면
  * 자동으로 테이블 잠금으로 승격
  * 잠금 관리 메모리 절약, but 동시성 저하

확장 단계 (Growing Phase)
  → 락을 획득만 할 수 있고, 해제할 수 없음

축소 단계 (Shrinking Phase)
  → 락을 해제만 할 수 있고, 새로 획득할 수 없음

시간 →      
락 수 ↑    / \
     │    /   \
     │   /     \
     │  / 확장  \ 축소
     │ /  단계   \ 단계
     │/           \
     └─────────────→ 시간

Basic 2PL
  * 축소 단계에서 락 해제 → 다른 트랜잭션이 읽음
  * 그 후 원래 트랜잭션이 ROLLBACK → 연쇄 복귀(Cascading Rollback)!

Strict 2PL (S2PL)
  * X-Lock은 트랜잭션 종료(COMMIT/ROLLBACK) 시까지 유지
  * 연쇄 복귀 방지
  * 대부분의 DBMS가 사용하는 방식

Rigorous 2PL
  * S-Lock, X-Lock 모두 트랜잭션 종료 시까지 유지
  * 가장 엄격, 가장 안전

실제 DBMS
  Oracle: S2PL (읽기는 MVCC이므로 S-Lock 자체를 잘 안 씀)
  MySQL InnoDB: S2PL + MVCC 결합
  PostgreSQL: S2PL + MVCC + SSI(Serializable)

Basic 2PL에서
  T1: WRITE(A)  →  A에 X-Lock
  T1: UNLOCK(A) →  A 잠금 해제 (축소 단계 진입)
  T2: READ(A)   →  T1이 쓴 값을 읽음
  T1: ROLLBACK  →  T1의 변경 취소
  T2: ???       →  T2가 읽은 값이 무효화됨!
                →  T2도 ROLLBACK해야 함 (연쇄 복귀)

Strict 2PL에서
  T1: WRITE(A)  →  X-Lock, COMMIT까지 유지
  T2: READ(A)   →  대기... (T1이 끝날 때까지)
  T1: ROLLBACK  →  X-Lock 해제
  T2: READ(A)   →  원래 값을 읽음 (문제 없음!)

T1                           T2
─────────────                ─────────────
LOCK(A) ✓
                             LOCK(B) ✓
LOCK(B) → 대기 (T2가 B를 잡고 있음)
                             LOCK(A) → 대기 (T1이 A를 잡고 있음)

→ T1은 B를 기다리고, T2는 A를 기다림
→ 영원히 끝나지 않음 = 데드락!

T1 → T2 → T1  (사이클 발견!)
→ T1 또는 T2 중 하나를 의 ROLLBACK (희생자 선택)

희생자 선택 기준 (DBMS별 차이)
  * Oracle: 마지막에 데드락을 발생시킨 트랜잭션
  * MySQL InnoDB: Undo 레코드가 적은 (변경이 적은) 트랜잭션
  * PostgreSQL: 마지막에 데드락을 발생시킨 트랜잭션
  * SQL Server: 비용이 가장 적은 트랜잭션 (DEADLOCK_PRIORITY 설정 가능)

인덱스에 존재하는 값: 10, 20, 30

SELECT * FROM t WHERE id BETWEEN 15 AND 25 FOR UPDATE;

잠긴 범위
  (10, 20] → Next-Key Lock (20 레코드 + 10~20 갭)
  (20, 30] → Next-Key Lock (30 레코드 + 20~30 갭)

결과
  INSERT INTO t (id) VALUES (12); → Gap Lock에 의해 차단!
  INSERT INTO t (id) VALUES (22); → Gap Lock에 의해 차단!
  INSERT INTO t (id) VALUES (35); → 허용 (범위 밖)
  → Phantom Read 방지!

갭 락으로 인한 의외의 대기
  T1: DELETE FROM t WHERE id = 15;
  → id=15가 존재하지 않으면 (10, 20) 갭에 Gap Lock!
  T2: INSERT INTO t (id) VALUES (12);
  → 갭 락에 의해 대기!

  → "없는 행 삭제"가 갭 락을 유발할 수 있음

갭 락 비활성화 (주의해서 사용)
  * 격리 수준을 READ COMMITTED로 변경
    → 갭 락 사용 안 함
  * innodb_locks_unsafe_for_binlog = 1 (deprecated)

-- 행 수준 락 (SELECT FOR UPDATE)
SELECT * FROM products WHERE id = 101 FOR UPDATE;
-- → 해당 행에 X-Lock, 다른 트랜잭션은 수정 대기

-- NOWAIT 옵션 (락 획득 실패 시 즉시 에러)
SELECT * FROM products WHERE id = 101 FOR UPDATE NOWAIT;

-- WAIT 옵션 (최대 5초 대기)
SELECT * FROM products WHERE id = 101 FOR UPDATE WAIT 5;

-- SKIP LOCKED (잠긴 행 건너뛰기 — 큐 패턴)
SELECT * FROM jobs WHERE status = 'PENDING'
FOR UPDATE SKIP LOCKED FETCH FIRST 1 ROW ONLY;

테이블: users (id, name, status)
인덱스: PK(id)만 존재, status에는 인덱스 없음

쿼리: UPDATE users SET name='X' WHERE status='ACTIVE';

인덱스 없음 (status)
  * Full Table Scan 발생
  * InnoDB: 스캔한 모든 행에 X-Lock! (ACTIVE가 아닌 행도!)
  * Oracle: ACTIVE인 행에만 X-Lock (MVCC 덕분)

인덱스 있음 (status)
  * Index Range Scan
  * ACTIVE인 행에만 정확하게 X-Lock
  * 불필요한 잠금 없음!

결론: 인덱스가 없으면 잠금 범위가 불필요하게 넓어질 수 있음
  → 잠금 경합 증가 → 데드락 확률 증가
  → WHERE 조건 컬럼에 적절한 인덱스 필수!

-- 여러 워커가 동시에 작업을 처리하는 큐 패턴

-- 워커 1:
SELECT id, payload FROM job_queue
WHERE status = 'PENDING'
ORDER BY created_at
FOR UPDATE SKIP LOCKED
FETCH FIRST 1 ROW ONLY;
-- → 잠긴 행(다른 워커가 처리 중)은 건너뛰고
-- → 잠금 가능한 첫 번째 행을 가져옴

UPDATE job_queue SET status = 'PROCESSING' WHERE id = ?;
COMMIT;

-- 워커 2:
-- 같은 쿼리 실행 → 워커1이 잠근 행을 건너뛰고 다음 행 처리
-- → 대기 없이 병렬 처리 가능!

1. 트랜잭션은 가능한 짧게 유지
   → 잠금 보유 시간 최소화 → 동시성 향상

2. WHERE 절 컬럼에 인덱스 확인
   → 인덱스 없으면 불필요한 행까지 잠금

3. 잠금 순서 통일
   → 모든 트랜잭션에서 동일한 순서로 테이블/행 접근
   → 데드락 원천 차단

4. 적절한 격리 수준 선택
   → 불필요하게 높은 격리 수준 = 불필요한 잠금

5. FOR UPDATE NOWAIT 또는 WAIT n 사용
   → 무한 대기 방지, 빠른 실패 후 재시도

6. 대량 DML은 배치 처리
   → 한 번에 100만 행 UPDATE → 잠금 폭발
   → 1000행씩 나눠서 COMMIT → 안전

7. 잠금 모니터링 쿼리 준비
   → 운영 환경에서 잠금 경합 즉시 감지

8. 데드락 로그 분석 습관화
   → 반복되는 데드락 패턴을 찾아 코드 수정