트랜잭션 제어

-- 현재 설정 확인
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;

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

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

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

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

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

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

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;    -- 세 쿼리 모두 동일 시점의 데이터 보장

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;