트랜잭션 제어

COMMIT
  * 리두 로그만 디스크에 쓰면 완료
  * 변경된 데이터 블록은 나중에 비동기 기록 (Checkpoint)

ROLLBACK
  * 변경된 모든 블록에 언두 이미지를 다시 적용해야 함
  * 대량 DML 후 ROLLBACK → 매우 오래 걸릴 수 있음

예: 100만 건 UPDATE 후
  COMMIT → 수 밀리초 (로그 flush만)
  ROLLBACK → 수 분 (100만 건 원복)

-- 현재 설정 확인
SELECT @@autocommit;  -- 1 = 자동 커밋 ON

-- 세션 단위로 자동 커밋 끄기
SET autocommit = 0;
-- → 이후 모든 DML은 명시적 COMMIT/ROLLBACK 필요
-- → Oracle과 유사한 동작

-- 다시 켜기
SET autocommit = 1;

-- START TRANSACTION은 autocommit 설정과 관계없이
-- 무조건 명시적 트랜잭션 시작
START TRANSACTION;
-- autocommit=1이어도 COMMIT/ROLLBACK까지 하나의 트랜잭션

-- 주문 처리 트랜잭션
INSERT INTO orders (id, user_id, total)
VALUES (orders_seq.NEXTVAL, 1, 50000);

SAVEPOINT after_order;

INSERT INTO order_items (id, order_id, product_id, quantity, price)
VALUES (items_seq.NEXTVAL, orders_seq.CURRVAL, 101, 2, 25000);

-- 재고 부족 발견!
ROLLBACK TO after_order;
-- → order_items INSERT만 취소, orders INSERT는 유지

-- 다른 상품으로 대체
INSERT INTO order_items (id, order_id, product_id, quantity, price)
VALUES (items_seq.NEXTVAL, orders_seq.CURRVAL, 102, 1, 50000);

COMMIT;

트랜잭션 시작
  │
  ├─ INSERT orders      ← 유지됨
  │
  ├─ SAVEPOINT sp1      ← 체크포인트
  │
  ├─ INSERT order_items ← 취소됨
  │
  ├─ ROLLBACK TO sp1    ← sp1 이후만 되돌림
  │
  ├─ INSERT order_items ← 새로 실행
  │
  └─ COMMIT             ← 전체 확정

UPDATE accounts SET balance = balance - 100000 WHERE id = 1;
-- 아직 COMMIT 안 함

CREATE TABLE temp_log (msg VARCHAR2(200));
-- DDL 실행 → 암묵적 COMMIT 발생!
-- 위의 UPDATE가 자동으로 확정됨

ROLLBACK;
-- 이미 커밋되었으므로 롤백 불가!

Oracle
  * DDL 실행 (CREATE, ALTER, DROP, TRUNCATE 등)
  * DCL 실행 (GRANT, REVOKE)
  * 정상적인 세션 종료 (exit, quit)
  * DDL은 실행 전후 두 번 커밋
    → DDL 실패해도 이전 DML은 이미 커밋됨!

MySQL InnoDB
  * DDL 실행
  * autocommit=1 상태에서 각 DML 문
  * LOCK TABLES, UNLOCK TABLES
  * START TRANSACTION (이전 트랜잭션 암묵적 커밋)

PostgreSQL
  * DDL이 트랜잭션 내에서 실행 가능 (롤백 가능!)
  * CREATE TABLE, DROP TABLE도 ROLLBACK 가능
  * 이것은 PostgreSQL만의 큰 장점

-- PostgreSQL: DDL도 트랜잭션 안에서 롤백 가능
BEGIN;

CREATE TABLE new_table (id SERIAL, name TEXT);
INSERT INTO new_table (name) VALUES ('test');

-- 확인 후 문제 발견
ROLLBACK;
-- → CREATE TABLE도 취소됨! (Oracle/MySQL에서는 불가)

세션 A
  UPDATE accounts SET balance = 0 WHERE id = 1;
  -- → id=1 행에 배타적 잠금(X Lock) 획득
  -- → COMMIT/ROLLBACK 할 때까지 잠금 유지

세션 B
  UPDATE accounts SET balance = 500 WHERE id = 1;
  -- → id=1 잠금 대기... (블로킹)
  -- → 세션 A가 COMMIT 또는 ROLLBACK 할 때까지 기다림

결론: 긴 트랜잭션 = 긴 잠금 = 동시성 저하

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

2. 사용자 입력을 기다리는 중에 트랜잭션을 열어두지 않기
   → "SELECT ... FOR UPDATE" 후 사용자 확인 대기 → 위험!

3. 대량 DML은 배치로 나누어 중간 COMMIT
   → 100만 건 UPDATE를 한 트랜잭션으로 → 언두/로그 폭증
   → 1000건씩 나누어 COMMIT → 안전

4. COMMIT 빈도와 데이터 정합성의 균형
   → 너무 잦은 COMMIT → 원자성 깨질 수 있음
   → 너무 드문 COMMIT → 잠금 + 리소스 부담

-- Oracle
SET TRANSACTION READ ONLY;
-- → SELECT만 허용, DML 실행 시 에러
-- → 트랜잭션 시작 시점의 일관된 스냅샷 읽기 (Consistent Read)

-- MySQL
START TRANSACTION READ ONLY;
-- → InnoDB 최적화: 트랜잭션 ID 할당 생략 → 성능 향상

-- PostgreSQL
BEGIN TRANSACTION READ ONLY;

-- SQL Server
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
-- → 읽기 전용으로 명시하는 별도 구문은 없으나
-- → SNAPSHOT 격리로 유사한 효과

1. 일관된 스냅샷: 트랜잭션 시작 시점의 데이터를 일관되게 조회
   → 장기 조회 중 다른 트랜잭션이 데이터를 변경해도
     조회 결과는 변하지 않음

2. 성능 최적화: DML 불가능하므로 잠금/언두 오버헤드 감소

3. 보고서 생성 시 유용:
   BEGIN READ ONLY;
   SELECT ... -- 대시보드 데이터 조회 1
   SELECT ... -- 대시보드 데이터 조회 2
   SELECT ... -- 대시보드 데이터 조회 3
   COMMIT;    -- 세 쿼리 모두 동일 시점의 데이터 보장

잘못된 방법
  START TRANSACTION;
  -- 100만 건 INSERT/UPDATE
  COMMIT;
  → 언두 공간 폭발, 롤백 시 수십 분 소요

올바른 방법
  DECLARE @batch_size INT = 1000;
  WHILE (처리할 데이터 존재)
    START TRANSACTION;
    -- 1000건씩 처리
    COMMIT;
  END WHILE
  → 중간에 실패해도 1000건만 롤백
  → 언두 공간 적게 사용
  → 잠금도 1000건 단위로만 유지

잘못된 방법
  START TRANSACTION;
  UPDATE orders SET status = 'PAID';
  -- 외부 결제 API 호출 (5초 소요)
  -- → 5초 동안 행 잠금 유지!
  COMMIT;

올바른 방법
  -- 1단계: 외부 API 먼저 호출 (트랜잭션 밖)
  결제결과 = 결제API.호출();
  
  -- 2단계: 성공 시 짧은 트랜잭션으로 DB 업데이트
  START TRANSACTION;
  UPDATE orders SET status = 'PAID', payment_id = ?;
  INSERT INTO payment_log (...) VALUES (...);
  COMMIT;
  → 트랜잭션 지속 시간 최소화

주의할 점
  * 커넥션 풀에서 커넥션을 받을 때
    이전 사용자가 COMMIT/ROLLBACK 안 했을 수 있음
  
  * 대부분의 풀은 반환 시 자동 ROLLBACK 수행
    but 명시적으로 처리하는 것이 안전

  * autocommit 설정이 커넥션별로 유지될 수 있음
    → 풀에서 가져온 커넥션의 autocommit 상태 확인 필요

┌───────────────────┬──────────┬──────────┬──────────┬───────────┐
│       기능        │ Oracle   │ MySQL    │PostgreSQL│ SQL Server│
├───────────────────┼──────────┼──────────┼──────────┼───────────┤
│ 기본 커밋 모드    │   수동   │   자동   │   자동   │   자동    │
│ 트랜잭션 시작     │  암묵적  │ START TX │ BEGIN    │ BEGIN TRAN│
│ DDL 트랜잭션      │   불가   │   불가   │   가능   │   가능    │
│ SAVEPOINT         │   지원   │   지원   │   지원   │ SAVE TRAN │
│ 읽기 전용 TX      │ SET TX   │ START TX │ BEGIN    │ SNAPSHOT  │
│        2PC        │   지원   │    XA    │ PREPARE  │ MS DTC    │
│ Autonomous TX     │ PRAGMA   │  미지원  │  미지원  │  미지원   │
└───────────────────┴──────────┴──────────┴──────────┴───────────┘

CREATE OR REPLACE PROCEDURE log_event(p_msg VARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO event_log (msg, log_time)
    VALUES (p_msg, SYSDATE);
    COMMIT;  -- 메인 트랜잭션과 독립적으로 커밋
END;

-- 메인 트랜잭션
BEGIN
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    log_event('출금 시도');  -- 별도 트랜잭션으로 로그 기록
    
    -- 에러 발생!
    ROLLBACK;
    -- → accounts UPDATE는 롤백
    -- → event_log INSERT는 이미 커밋되어 유지됨!
END;

 [시작] ─── DML 실행 ──→ [활동(Active)]
                              │
              ┌───────────────┼───────────────┐
              │               │               │
          정상 완료       에러 발생        SAVEPOINT로
              │               │           부분 롤백
              ▼               ▼               │
     [부분 확정(Partially   [실패(Failed)]    │
      Committed)]             │               │
              │               │               │
          COMMIT           ROLLBACK           │
              │               │               │
              ▼               ▼               │
       [확정(Committed)] [철회(Aborted)]      │
              │               │               │
              └───────┬───────┘               │
                      ▼                       │
                   [종료]                     │
                      └────── 재시작 가능 ────┘

┌────────────────────┬─────────────────────────────────┐
│       명령어       │              동작               │
├────────────────────┼─────────────────────────────────┤
│ COMMIT             │ 변경 확정, 잠금 해제            │
│ ROLLBACK           │ 변경 취소, 잠금 해제            │
│ SAVEPOINT name     │ 중간 체크포인트 설정            │
│ ROLLBACK TO name   │ SAVEPOINT까지만 부분 롤백       │
│ SET TRANSACTION    │ 트랜잭션 특성 설정 (격리 수준)  │
│ START TRANSACTION  │ 명시적 트랜잭션 시작 (MySQL)    │
│ BEGIN              │ 트랜잭션 시작 (PostgreSQL)      │
│ BEGIN TRANSACTION  │ 트랜잭션 시작 (SQL Server)      │
└────────────────────┴─────────────────────────────────┘