트랜잭션과 격리수준

UPDATE orders SET status_cd = 'CANCEL' WHERE order_dt < SYSDATE - 365;
-- 아직 커밋 안 함. 결과를 확인하고 되돌릴 생각이었다

CREATE TABLE tmp_check AS SELECT * FROM orders WHERE ROWNUM <= 10;
-- DDL 실행 -> 위 UPDATE가 함께 커밋됨

ROLLBACK;
-- 아무것도 되돌아가지 않습니다

TRUNCATE 도 DDL입니다. DELETE 는 되돌릴 수 있지만 TRUNCATE 는 되돌릴 수 없다는 차이가 여기서 나옵니다.

-- 09:00:00 에 시작해 5분간 도는 집계 쿼리
SELECT SUM(amount) FROM orders WHERE order_dt >= TRUNC(SYSDATE);

-- 09:02 에 다른 세션이 주문을 넣고 커밋해도
-- 위 쿼리 결과에는 반영되지 않습니다.
-- 쿼리가 시작된 09:00:00 시점의 데이터로 끝까지 계산합니다.

그래서 긴 조회 중에 값이 뒤섞이는 일이 없습니다. 다만 그 시점 이미지를 UNDO에서 계속 찾아야 하므로, 조회가 길고 그동안 변경이 많으면 ORA-01555 위험이 올라갑니다.

-- 기본값. 각 문장이 자기 시작 시점을 봅니다
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 트랜잭션 전체가 하나의 시점을 봅니다
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 조회 전용. 여러 쿼리가 같은 시점을 보게 할 때
SET TRANSACTION READ ONLY;

리포트 배치에서 여러 쿼리의 합계가 서로 맞아야 한다면 READ ONLY 가 간단한 답입니다. 지정 명령은 트랜잭션의 첫 문장이어야 합니다.

-- 읽는 순간 잠급니다. 다른 세션은 대기합니다
SELECT amount FROM orders WHERE order_id = 100 FOR UPDATE;

-- 대기하지 않고 즉시 실패 (ORA-00054)
SELECT amount FROM orders WHERE order_id = 100 FOR UPDATE NOWAIT;

-- 3초만 기다리고 실패
SELECT amount FROM orders WHERE order_id = 100 FOR UPDATE WAIT 3;

-- 잠긴 행은 건너뛰고 가능한 것만 (작업 큐에 유용)
SELECT * FROM job_queue WHERE status_cd = 'READY'
FOR UPDATE SKIP LOCKED;

화면을 열어 둔 채로 FOR UPDATE 를 잡고 있으면 사용자가 커피를 마시러 간 동안 다른 사람이 전부 막힙니다. 사용자 입력이 끼는 구간에는 쓰지 않습니다.

-- 조회 시 버전을 함께 읽어 둡니다
SELECT order_id, amount, version FROM orders WHERE order_id = 100;

-- 저장할 때 읽었던 버전과 같은지 확인합니다
UPDATE orders
SET    amount  = :amount,
       version = version + 1
WHERE  order_id = 100
AND    version  = :read_version;

-- 변경된 행이 0건이면 그사이 누군가 먼저 수정한 것입니다

잠그지 않으므로 동시성이 좋습니다. 대신 충돌 시 사용자에게 다시 안내해야 합니다. 화면 기반 업무에는 대체로 이쪽이 맞습니다. JPA의 @Version 이 이 방식입니다.