트랜잭션과 Lock - 읽기 일관성과 동시성
이 장에서 배우는 것
주문 1천만 건 규모의 온라인 서점 시스템은 하루에도 수만 건의 주문이 동시에 들어온다. 같은 도서, 같은 주문 행을 여러 트랜잭션이 동시에 건드리는 순간 읽기 일관성과 잠금 문제가 함께 드러난다. 이 장은 옵티마이저가 세운 실행계획이 아무리 좋아도 동시성 제어가 어긋나면 응답이 지연되거나 오류로 끝나는 이유를 다룬다.
- Oracle 이 MVCC(Multi-Version Concurrency Control) 와 Undo 로 읽기 일관성을 구현하는 원리를 설명한다
- 격리수준별로 Dirty Read·Non-repeatable Read·Phantom Read 가 발생하는 조건을 구분한다
- 행 Lock 이 걸리는 시점과 대기가 발생하는 상황을 v$session 으로 확인한다
- 교착 상태(Deadlock)를 직접 재현하고 예방 규칙을 세운다
- Oracle 과 MySQL(InnoDB) 의 잠금·격리 처리 차이를 비교표로 정리한다
문제 상황
주문 처리 배치와 사용자 화면이 같은 주문 테이블을 동시에 수정하면서 특정 시간대에 응답 지연이 몰린다는 신고가 들어왔다. 로그를 보면 일부 트랜잭션이 몇 초씩 멈춰 있다가 ORA-00060 오류로 끝난다. 원인을 찾으려면 먼저 예제에서 쓸 테이블과 인덱스를 정리해 둔다.
CREATE TABLE 도서 (
BOOK_ID NUMBER PRIMARY KEY,
TITLE VARCHAR2(200) NOT NULL,
STOCK_QTY NUMBER NOT NULL
);
CREATE TABLE 주문 (
ORDER_ID NUMBER PRIMARY KEY,
MEMBER_ID NUMBER NOT NULL,
ORDER_STATUS VARCHAR2(10) NOT NULL,
ORDER_DATE DATE NOT NULL
);
CREATE TABLE 주문상세 (
ORDER_ID NUMBER NOT NULL,
BOOK_ID NUMBER NOT NULL,
QTY NUMBER NOT NULL,
CONSTRAINT PK_주문상세 PRIMARY KEY (ORDER_ID, BOOK_ID),
CONSTRAINT FK_주문상세_BOOK FOREIGN KEY (BOOK_ID) REFERENCES 도서(BOOK_ID)
);
CREATE INDEX IX_주문상세_BOOK ON 주문상세(BOOK_ID);
INSERT INTO 도서 VALUES (501, 'SQL 실행계획 가이드', 40);
INSERT INTO 주문 VALUES (1001, 9001, '결제완료', SYSDATE);
INSERT INTO 주문 VALUES (1002, 9002, '결제완료', SYSDATE);
COMMIT;
실제로 벌어진 일은 두 가지로 나뉜다. 하나는 아직 커밋되지 않은 값을 다른 세션이 읽으려다 기다린 것처럼 보이는 상황(실제로는 그렇지 않다는 점이 이 장의 핵심이다), 다른 하나는 두 트랜잭션이 서로 다른 순서로 같은 주문 두 건을 잠그려다 맞물린 진짜 교착 상태다. 두 상황을 구분하려면 Oracle 이 읽기와 쓰기를 어떻게 다르게 처리하는지부터 봐야 한다.
MVCC와 Undo — 읽기 일관성의 원리
Oracle 은 읽기 작업에 잠금을 걸지 않는다. 대신 각 트랜잭션이 시작된 시점(정확히는 각 SQL 문이 시작된 시점)의 SCN(System Change Number)을 기준으로, 그 이후에 바뀐 데이터는 Undo 세그먼트에 보관된 이전 값으로 되돌려 읽는다. 이것이 읽기 일관성(read consistency)이며, 표준 용어로는 MVCC 라고 부른다.
세션 B가 주문 1001번의 상태를 '배송준비'로 바꾸고 아직 커밋하지 않았다고 하자. 이 시점에 세션 A가 같은 행을 SELECT 하면 버퍼 캐시에는 미커밋 값이 있지만, Oracle 은 그 값을 그대로 보여주지 않는다. 해당 블록에 아직 커밋되지 않은 변경이 걸려 있다는 것을 블록 헤더로 확인하고, Undo 세그먼트에서 변경 전 값을 가져와 조합한 읽기 일관성 이미지(CR, Consistent Read)를 돌려준다. 그 결과 세션 A는 '결제완료'를 그대로 읽으며, 이는 대기하지 않고도 이루어진다.
이 구조 덕분에 Oracle 에서는 어떤 격리수준을 쓰더라도 Dirty Read 가 발생하지 않는다. 반대로 Undo 보관 기간이 너무 짧으면, 오래 실행되는 SELECT 가 필요한 이전 값을 Undo 세그먼트에서 찾지 못해 ORA-01555 snapshot too old 오류가 날 수 있다. 배치 조회가 긴 시스템이라면 UNDO_RETENTION 과 Undo 테이블스페이스 크기를 함께 점검해야 하는 이유다.
격리수준과 이상현상
표준 SQL은 네 가지 격리수준을 정의하지만, Oracle 은 그중 READ COMMITTED 와 SERIALIZABLE 만 제공한다(READ ONLY 도 있지만 갱신이 없는 세션 전용이다). READ UNCOMMITTED 와 REPEATABLE READ 라는 이름의 격리수준 자체가 Oracle 에는 없다. MVCC 구조상 Dirty Read 를 허용할 필요가 없고, 행 단위 잠금과 Undo 조합만으로 REPEATABLE READ 가 요구하는 수준 이상을 SERIALIZABLE 이 대신하기 때문이다.
| 격리수준 | Dirty Read | Non-repeatable Read | Phantom Read |
|---|---|---|---|
| READ UNCOMMITTED | 발생 가능 | 발생 가능 | 발생 가능 |
| READ COMMITTED (Oracle 기본) | 방지 | 발생 가능 | 발생 가능 |
| REPEATABLE READ | 방지 | 방지 | 발생 가능 |
| SERIALIZABLE (Oracle 지원) | 방지 | 방지 | 방지 |
Oracle 의 READ COMMITTED 에서는 SQL 문마다 새로운 SCN 스냅숏을 잡으므로, 같은 트랜잭션 안에서도 같은 행을 두 번 조회하면 그사이 다른 세션이 커밋한 값이 보일 수 있다(Non-repeatable Read). SERIALIZABLE 로 올리면 트랜잭션 시작 시점의 스냅숏을 트랜잭션이 끝날 때까지 유지하므로 이런 변화가 보이지 않는다. 대신 그 스냅숏 이후 다른 트랜잭션이 같은 행을 먼저 커밋해 버리면, 내 트랜잭션이 그 행을 갱신하려는 순간 ORA-08177 can't serialize access 오류로 실패한다. 즉 Oracle 의 SERIALIZABLE 은 이상현상을 감추는 대신 충돌을 오류로 드러내는 방식이다.
행 Lock과 대기
Oracle 은 DML 을 실행하는 순간 행 단위로 TX(트랜잭션) 잠금을 건다. 테이블 잠금 확대(lock escalation) 같은 동작은 없으며, 백만 건을 갱신해도 잠금은 여전히 행 단위로만 걸린다. 문제는 다른 세션이 같은 행을 잠그려 할 때인데, 이때는 읽기와 달리 반드시 앞선 트랜잭션이 커밋하거나 롤백할 때까지 대기해야 한다.
대기 상태는 v$session 뷰의 blocking_session 컬럼으로 즉시 확인할 수 있다. 이 값이 채워진 세션은 다른 세션이 쥔 잠금을 기다리는 중이며, event 컬럼에는 보통 enq: TX - row lock contention이 찍힌다.
명시적으로 잠금을 선점하고 싶다면 SELECT ... FOR UPDATE를 쓴다. NOWAIT 을 붙이면 즉시 실패시켜 대기를 없애고, SKIP LOCKED 를 붙이면 이미 잠긴 행을 건너뛴다. 재고 차감처럼 경쟁이 잦은 구간에서는 대기 시간을 무한정 두기보다 어느 쪽을 쓸지 미리 정해 두는 편이 낫다.
교착 상태 재현과 예방
교착 상태는 두 트랜잭션이 서로 다른 순서로 같은 자원을 잠그려 할 때 생기는 순환 대기다. 세션 A가 주문 1001번을 잠근 뒤 1002번을 요청하고, 세션 B가 반대로 1002번을 잠근 뒤 1001번을 요청하면 서로가 서로를 기다리는 사이클이 만들어진다.
Oracle 은 이런 순환 대기를 백그라운드에서 주기적으로 감지하고, 사이클을 끊기 위해 그중 한 세션의 마지막 요청 문장 하나만 강제로 롤백시키며 ORA-00060 오류를 돌려준다. 여기서 놓치기 쉬운 점은, 롤백되는 대상이 그 세션의 트랜잭션 전체가 아니라 실패한 그 SQL 문 하나뿐이라는 것이다. 앞서 그 세션이 이미 실행해 둔 다른 갱신은 커밋도 롤백도 되지 않은 채 잠금을 계속 쥐고 있다. 그래서 애플리케이션이 ORA-00060 을 잡고 그 문장만 재시도하면, 이미 어긋난 트랜잭션 상태 위에서 같은 대기가 다시 반복될 수 있다. 올바른 처리는 오류를 받은 즉시 트랜잭션 전체를 ROLLBACK 하고 처음부터 다시 시작하는 것이다.
예방은 감지보다 싸다. 여러 세션이 같은 종류의 행을 여러 건 잠글 때는 항상 같은 순서(예: 기본 키 오름차순)로 잠그도록 코드를 맞추면 순환 자체가 생기지 않는다. 트랜잭션을 짧게 유지하고, 잠금을 쥔 채로 외부 호출이나 사용자 입력을 기다리지 않는 것도 같은 맥락의 예방책이다.
완성 코드
session_a.sql
-- 세션 A: 주문 1001 -> 1002 순서로 잠근다
UPDATE 주문 SET ORDER_STATUS = '배송준비' WHERE ORDER_ID = 1001;
-- 세션 B가 1002를 먼저 잠글 시간을 준다
EXEC DBMS_SESSION.SLEEP(5);
UPDATE 주문 SET ORDER_STATUS = '배송준비' WHERE ORDER_ID = 1002;
COMMIT;
session_b.sql
-- 세션 B: 주문 1002 -> 1001 순서로 잠근다 (A와 반대 순서)
UPDATE 주문 SET ORDER_STATUS = '배송준비' WHERE ORDER_ID = 1002;
UPDATE 주문 SET ORDER_STATUS = '배송준비' WHERE ORDER_ID = 1001;
COMMIT;
lock_monitor.sql
SELECT sid, serial#, username, blocking_session, event, seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL
OR sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL)
ORDER BY blocking_session NULLS FIRST;
줄별 해설
session_a.sql 은 먼저 주문 1001번을 잠근다. 이 시점에 커밋을 하지 않으므로 잠금이 계속 유지된다. DBMS_SESSION.SLEEP(5)는 별도의 권한 부여 없이 18c 이상에서 바로 쓸 수 있는 대기 함수로, 이 5초 사이에 세션 B를 실행해 1002번을 먼저 잠그게 만들기 위한 장치다. 대기가 끝나면 A는 1002번을 요청하는데, 이미 B가 쥐고 있으므로 여기서 A는 대기 상태로 들어간다.
session_b.sql 은 1002번을 먼저 잠그고, 곧바로 1001번을 요청한다. 그런데 1001번은 A가 이미 쥐고 있으므로 B도 대기 상태가 된다. 이 시점에 A는 B를, B는 A를 기다리는 순환이 완성되고, Oracle 의 잠금 관리자가 이를 감지해 둘 중 한 세션의 마지막 요청 문장에 ORA-00060 을 돌려준다. 이 예제 구성에서는 A의 두 번째 UPDATE(1002번 요청)가 사이클을 완성시키는 마지막 요청이므로 A가 오류를 받는다.
A가 오류를 받아도 A는 첫 번째 UPDATE(1001번)로 얻은 잠금을 그대로 쥐고 있다. 그래서 B는 여전히 대기 상태다. A가 예외를 잡고 ROLLBACK을 실행해야 비로소 1001번 잠금이 풀리고 B의 두 번째 UPDATE 가 완료된다. lock_monitor.sql 은 두 세션이 서로 대기하는 구간에서 v$session 만으로 어떤 세션이 누구를 막고 있는지 한 번에 보여준다.
실행 결과
-- 터미널 1
$ sqlplus book/book@orcl @session_a.sql
SQL> UPDATE 주문 SET ORDER_STATUS = '배송준비' WHERE ORDER_ID = 1001;
1 row updated.
SQL> EXEC DBMS_SESSION.SLEEP(5);
PL/SQL procedure successfully completed.
SQL> UPDATE 주문 SET ORDER_STATUS = '배송준비' WHERE ORDER_ID = 1002;
UPDATE 주문 SET ORDER_STATUS = '배송준비' WHERE ORDER_ID = 1002
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
SQL> ROLLBACK;
Rollback complete.
-- 터미널 2
$ sqlplus book/book@orcl @session_b.sql
SQL> UPDATE 주문 SET ORDER_STATUS = '배송준비' WHERE ORDER_ID = 1002;
1 row updated.
SQL> UPDATE 주문 SET ORDER_STATUS = '배송준비' WHERE ORDER_ID = 1001;
1 row updated.
SQL> COMMIT;
Commit complete.
-- 터미널 3 (대기 구간에 실행)
SQL> @lock_monitor.sql
SID SERIAL# USERNAME BLOCKING_SESSION EVENT SECONDS_IN_WAIT
------ ------- --------- ---------------- ------------------------------ ---------------
137 214 BOOK - 0
142 501 BOOK 137 enq: TX - row lock contention 4
실무에서 자주 틀리는 것
FK 컬럼에 인덱스를 만들지 않는다
자식 테이블의 외래키 컬럼에 인덱스가 없으면, 부모 행을 갱신하거나 삭제할 때 Oracle 이 자식 행 존재 여부를 확인하기 위해 자식 테이블 전체에 넓은 범위의 잠금을 건다. 도서 재고를 갱신하는 짧은 트랜잭션 하나가 주문상세 테이블 전체의 DML 을 막는 결과로 이어질 수 있다.
-- 틀린 코드: FK만 걸고 인덱스는 생략
CONSTRAINT FK_주문상세_BOOK FOREIGN KEY (BOOK_ID) REFERENCES 도서(BOOK_ID)
-- (별도 인덱스 없음)
-- 고친 코드: FK 컬럼에 인덱스를 반드시 함께 만든다
CREATE INDEX IX_주문상세_BOOK ON 주문상세(BOOK_ID);
조회 후 갱신하는 패턴으로 재고를 차감한다
재고를 SELECT 로 읽고 애플리케이션에서 1을 뺀 값을 다시 UPDATE 하면, 두 세션이 같은 값을 동시에 읽었을 때 한쪽의 차감이 사라지는 Lost Update 가 생긴다.
-- 틀린 코드
SELECT STOCK_QTY INTO :qty FROM 도서 WHERE BOOK_ID = 501;
-- 애플리케이션에서 :qty - 1 계산
UPDATE 도서 SET STOCK_QTY = :qty - 1 WHERE BOOK_ID = 501;
-- 고친 코드: 갱신 문 안에서 직접 계산해 원자적으로 처리
UPDATE 도서
SET STOCK_QTY = STOCK_QTY - 1
WHERE BOOK_ID = 501
AND STOCK_QTY > 0;
잠금 순서를 정하지 않고 여러 행을 갱신한다
배치가 조회된 순서 그대로 여러 주문을 잠그면, 세션마다 스캔 순서가 달라져 서로 다른 순서로 잠그다가 교착 상태에 빠질 수 있다.
-- 틀린 코드: 정렬 없이 조회된 순서대로 잠금
SELECT ORDER_ID FROM 주문 WHERE ORDER_STATUS = '결제완료';
-- 반환 순서대로 UPDATE 반복
-- 고친 코드: 기본 키 오름차순으로 정렬해 모든 세션이 같은 순서로 잠그게 한다
SELECT ORDER_ID FROM 주문
WHERE ORDER_STATUS = '결제완료'
ORDER BY ORDER_ID;
트랜잭션을 쥔 채로 외부 입력을 기다린다
주문 상태를 갱신한 뒤 커밋하지 않고 사용자의 다음 클릭이나 외부 API 응답을 기다리면, 그 사이 다른 세션은 같은 행에서 계속 대기한다.
-- 틀린 코드
UPDATE 주문 SET ORDER_STATUS = '배송준비' WHERE ORDER_ID = 1001;
-- 결제 승인 API 응답을 기다린 뒤 COMMIT (수 초~수십 초 소요)
-- 고친 코드: 외부 호출을 트랜잭션 밖으로 옮기고, 검증이 끝난 뒤 짧게 갱신한다
-- 1) 결제 승인 API 호출 (트랜잭션 밖)
-- 2) 승인 결과 확인 후
UPDATE 주문 SET ORDER_STATUS = '배송준비' WHERE ORDER_ID = 1001;
COMMIT;
한눈에 보기
| 개념 | 핵심 정리 |
|---|---|
| MVCC / Undo | 읽기는 잠금 없이 Undo의 이전 값으로 재구성되므로 Dirty Read가 원천적으로 없다 |
| 격리수준 | Oracle은 READ COMMITTED(기본)와 SERIALIZABLE만 제공하며, 후자는 충돌 시 ORA-08177로 실패시킨다 |
| 행 Lock | DML은 항상 행 단위로 잠기며 v$session.blocking_session으로 대기 관계를 바로 확인한다 |
| 교착 상태 | Oracle은 순환 대기를 감지해 한 문장만 ORA-00060으로 롤백시키므로, 받은 즉시 트랜잭션 전체를 ROLLBACK해야 한다 |
| 항목 | Oracle 19c | MySQL 8 (InnoDB) |
|---|---|---|
| 기본 격리수준 | READ COMMITTED | REPEATABLE READ |
| 지원 격리수준 | READ COMMITTED, SERIALIZABLE | READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE |
| 교착 상태 처리 | 사이클을 만든 문장 하나만 롤백, 트랜잭션은 열린 채 유지 | 피해 트랜잭션 전체를 자동 롤백 |
| 잠금 확인 위치 | v$session, v$lock, v$locked_object | performance_schema.data_locks, data_lock_waits |
연습 문제
- Oracle 에서 READ COMMITTED 상태로도 Dirty Read 가 발생하지 않는 이유를 MVCC 구조를 근거로 설명하라.
- 세션 A가 주문 2001번, 2002번 순서로 잠그고 세션 B가 2002번, 2001번 순서로 잠근다면, 어느 시점에 어떤 오류가 나며 남은 세션은 왜 곧바로 진행되지 않는지 설명하라.
- 주문상세 테이블의 BOOK_ID 컬럼에 인덱스가 없을 때 도서 마스터를 삭제하면 어떤 잠금 문제가 생기는지, 해결 방법과 함께 설명하라.
- Oracle 과 MySQL(InnoDB)이 교착 상태를 처리하는 방식이 다르다는 점이 애플리케이션의 재시도 로직 설계에 어떤 차이를 만드는지 설명하라.
정답과 해설
1. Oracle 은 SQL 문이 시작될 때의 SCN 을 기준으로 스냅숏을 구성한다. 다른 세션이 아직 커밋하지 않은 변경이 블록에 있으면, 그 블록 헤더의 정보를 보고 Undo 세그먼트에 보관된 변경 전 값을 가져와 조합한다. 따라서 읽는 세션은 항상 커밋된 값만 보게 되며, 이는 잠금이 아니라 버전 관리로 이루어지므로 대기도 발생하지 않는다.
2. A가 2001번을 잠근 뒤 2002번을 요청하는 시점에 B가 이미 2002번을 쥐고 2001번을 요청 중이라면 순환 대기가 만들어진다. Oracle 이 이를 감지해 사이클을 완성시킨 쪽(예제 구성상 A)의 마지막 문장에 ORA-00060 을 돌려준다. 하지만 A가 먼저 실행해 둔 2001번 잠금은 여전히 유효하므로, A가 ROLLBACK 을 실행하기 전까지 B의 2001번 요청은 계속 대기한다.
3. 인덱스가 없으면 Oracle 은 도서 행 삭제 시 주문상세에 참조하는 행이 있는지 확인하기 위해 주문상세 테이블에 넓은 범위의 잠금(TM)을 건다. 그동안 주문상세에 대한 다른 세션의 삽입·수정이 함께 막힌다. 해결책은 FK 컬럼인 BOOK_ID 에 인덱스를 만들어 참조 확인을 인덱스 스캔으로 좁히는 것이다.
4. Oracle 은 교착 상태가 나도 실패한 세션의 트랜잭션이 열린 채 남으므로, 애플리케이션은 오류를 받은 즉시 전체 트랜잭션을 ROLLBACK 한 뒤 처음부터 재시도해야 한다. 문장 하나만 재시도하면 이전에 걸어둔 잠금이 그대로 남아 상대 세션을 계속 막는다. 반면 MySQL(InnoDB)은 교착 상태 발생 시 피해 트랜잭션 전체를 자동으로 롤백하므로, 애플리케이션은 트랜잭션 시작부터 다시 실행하기만 하면 된다.