동시성 제어와 회복 - 여럿이 동시에 쓸 때

이 장에서 배우는 것

앞 장에서 여러 변경을 하나의 트랜잭션으로 묶는 이유를 살펴보았다. 그러나 주문 하나가 올바르게 처리된다고 해서 주문 두 개가 동시에 실행될 때도 결과가 올바르다고 보장되지는 않는다. 두 주문이 같은 재고를 읽고 각각 값을 바꾸면, 각자의 계산은 맞아도 함께 남긴 결과는 틀릴 수 있다. 실행 도중 서버가 멈추면 어느 변경까지 저장된 것으로 인정할지도 판단해야 한다.

이 장에서는 실행이 겹치는 문제와 실행이 중단되는 문제를 구분해서 다룬다. 동시성 제어는 함께 실행되는 작업 사이의 질서를 정하는 일이다. 회복은 중단 뒤에도 데이터베이스를 약속된 상태로 되돌리는 일이다. 온라인 서점의 inventory와 orders를 중심으로 두 원리가 만나는 지점을 살펴본다.

• 갱신 손실, 더티 리드, 반복 불가능 읽기, 팬텀을 실행 순서로 설명한다.
• 격리 수준의 보장 범위를 읽고 SQLite에 그대로 대입하면 안 되는 부분을 구분한다.
• 잠금, 2단계 잠금, 다중 버전 동시성 제어의 역할을 비교한다.
• 로그, REDO, UNDO, 체크포인트가 회복에 필요한 이유를 설명한다.
• 조건부 재고 변경과 되돌리기를 SQLite에서 검증한다.

문제 상황

온라인 서점에서 어떤 책의 재고가 5권이라고 하자. 주문 처리 작업 가와 나는 각각 1권을 판매하려고 한다. 두 작업은 모두 inventory.stock을 읽어 5를 얻는다. 가가 프로그램에서 5에서 1을 빼고 4를 저장한다. 나도 자신이 읽었던 5에서 1을 빼고 4를 저장한다. 판매는 두 번 일어났는데 재고는 한 번만 줄었다.

이 문제는 UPDATE 문 하나의 문법 오류가 아니다. 값을 읽은 시점과 그 값으로 계산한 결과를 저장한 시점 사이에 다른 작업이 끼어든 것이 원인이다. 특히 조회를 끝낸 뒤 트랜잭션 밖에서 계산하고, 나중에 별도의 트랜잭션으로 저장하는 프로그램에서 이런 틈이 생기기 쉽다.

운영 화면에서는 다른 문제가 나타난다. 담당자가 PAID 상태인 orders를 조회했을 때는 주문이 20개였는데, 같은 업무를 처리하는 도중 다시 조회하니 21개가 된다. 한편 결제 완료 응답 직후 서버가 재시작되었는데, 해당 변경이 데이터 파일에 아직 반영되지 않았을 수도 있다. 앞의 문제에는 읽기와 쓰기의 격리가 필요하고, 뒤의 문제에는 확정된 변경을 복구할 근거가 필요하다.

실행이 겹칠 때 달라지는 읽기와 쓰기

갱신 손실과 세 가지 읽기 현상

갱신 손실(lost update)은 한 작업의 변경 효과가 다른 작업의 저장으로 사라지는 현상이다. 앞의 재고 예에서는 나의 저장이 가의 판매 효과를 덮어썼다. 여기서 중요한 것은 두 작업이 모두 오래된 값으로 새 값을 계산했다는 점이다. 재고가 5였다는 사실은 읽었던 순간의 사실이지, 나중에 저장할 때도 유효한 약속이 아니다.

그림은 오래된 조회값을 나중에 저장하는 논리적 실행 순서를 보여 준다. SQLite의 한 트랜잭션 안에서 이 순서를 그대로 재현할 수 있다는 뜻은 아니다. SQLite는 쓰기 충돌을 대기나 오류로 막을 수 있다. 하지만 프로그램이 조회와 저장을 서로 다른 트랜잭션으로 나누면, 데이터베이스는 나중의 4가 오래된 계산 결과인지 알기 어렵다.

더티 리드(dirty read)는 다른 트랜잭션이 아직 확정하지 않은 변경을 읽는 현상이다. 가가 orders.status를 CANCELLED로 바꾸었지만 아직 확정하지 않았다고 하자. 나가 이 값을 읽고 취소 안내 대상을 만들었는데 가가 변경을 취소하면, 나는 최종적으로 존재하지 않게 된 상태를 근거로 판단한 셈이다.

반복 불가능 읽기(non-repeatable read)는 같은 트랜잭션에서 같은 행을 다시 읽었을 때 값이 달라지는 현상이다. 가가 book.price를 읽은 뒤, 나가 해당 가격을 바꾸고 확정한다. 가가 다시 읽었을 때 새 가격이 보이면 이 현상이 발생한다. 다른 작업의 미확정 값을 읽은 것은 아니므로 더티 리드와 구분된다.

팬텀(phantom)은 같은 검색 조건으로 다시 조회했을 때 그 조건에 맞는 행의 집합이 달라지는 현상이다. 가가 status = 'PAID'인 orders를 조회하는 사이 나가 새로운 해당 주문을 추가하고 확정하면, 가의 두 번째 조회에 새 행이 나타날 수 있다. 기존 주문의 상태가 바뀌어 조건에 들어오거나 빠지는 경우도 행 집합을 바꾼다. 핵심은 특정 행의 값이 아니라 검색 조건에 해당하는 구성원의 변화다.

격리 수준 표를 읽는 방법

격리 수준(isolation level)은 동시에 실행되는 트랜잭션 사이에서 어떤 관찰을 허용할지 정한 약속이다. 다음 표는 표준 SQL의 전통적인 세 가지 읽기 현상을 기준으로 정리한 것이다. ‘허용 가능’은 반드시 발생한다는 뜻이 아니다. 제품이 표의 최소 보장보다 강하게 동작할 수 있다.

표준 SQL 격리 수준이 금지하는 읽기 현상

격리 수준	더티 리드	반복 불가능 읽기	팬텀
READ UNCOMMITTED	허용 가능	허용 가능	허용 가능
READ COMMITTED	금지	허용 가능	허용 가능
REPEATABLE READ	금지	금지	허용 가능
SERIALIZABLE	금지	금지	금지

가장 강한 수준인 SERIALIZABLE은 성공적으로 완료된 트랜잭션들의 결과가 어떤 순서로 하나씩 실행한 결과와 같도록 보장한다. 실제로 모든 문장을 한 줄씩 번갈아 실행하지 못하게 만든다는 뜻은 아니다. 내부적으로 실행을 겹치게 하면서 충돌하는 작업을 기다리게 하거나 취소할 수도 있다.

이 표만으로 모든 동시성 문제를 판단해서는 안 된다. 갱신 손실의 처리 방식과 여러 행에 걸친 업무 규칙은 별도로 확인해야 한다. 또한 격리 보장은 트랜잭션의 경계 안에서 해석해야 한다. 조회와 저장을 따로 확정해 버린 프로그램을 강한 격리 수준 하나로 고칠 수는 없다.

SQLite에는 이 네 수준을 SET TRANSACTION 문으로 선택하는 공통 인터페이스가 없다. 일반적인 별도 연결 사이에서는 미확정 변경이 보이지 않으며, 같은 데이터베이스 파일에 대한 쓰기 트랜잭션은 한 번에 하나만 진행된다. 읽기와 쓰기가 서로 기다리는 방식은 저널 모드에 따라 달라진다.

SQLite의 PRAGMA read_uncommitted를 켠다고 해서 일반적인 별도 연결이 곧바로 다른 연결의 미확정 변경을 읽는 것도 아니다. 이러한 읽기가 가능하려면 공유 캐시 사용이라는 추가 조건이 필요하다. SQL 연구소에서는 연결 방식이나 저널 모드를 임의로 가정하지 않는다. SQLite의 동작 조건은 격리 설명에서 확인할 수 있다.

잠금과 여러 버전으로 실행을 조정한다

잠금과 2단계 잠금

잠금(lock)은 특정 자원에 대해 다른 작업이 할 수 있는 행동을 제한하는 장치다. 전형적인 설명에서는 읽기를 위한 공유 잠금과 쓰기를 위한 배타 잠금을 구분한다. 여러 작업이 공유 잠금을 함께 가질 수 있지만, 배타 잠금은 충돌하는 다른 접근과 양립하지 않는다. 실제 잠금의 단위는 행, 페이지, 테이블 등 구현에 따라 다르다.

2단계 잠금(two-phase locking)은 잠금을 얻고 놓는 순서에 관한 규칙이다. 첫 단계에서는 필요한 잠금을 얻으며 잠금을 해제하지 않는다. 두 번째 단계에서는 잠금을 해제하며 새 잠금을 얻지 않는다. 첫 잠금을 해제한 뒤 다른 자원의 잠금을 추가로 얻으면 이 규칙에서 벗어난다. 트랜잭션이 두 개라는 뜻도, 확정을 두 번 한다는 뜻도 아니다.

이 규칙은 잠금 충돌을 기준으로 직렬 실행과 같은 순서를 만들 수 있게 한다. 조건에 맞는 행 전체를 보호하려면 이미 존재하는 행뿐 아니라 검색 범위도 적절히 보호해야 한다. 현재 조회된 행만 잠갔다고 해서 새 행이 그 조건에 들어오는 것까지 막았다고 판단해서는 안 된다.

엄격한 2단계 잠금은 쓰기에 사용한 배타 잠금을 트랜잭션 종료까지 유지한다. 다른 작업이 미확정 변경에 기대어 실행하는 일을 막아 회복도 단순하게 만든다. 다만 기다림이 생길 수 있다. 가가 자원 하나를 잡고 나의 자원을 기다리는 동안 나도 가의 자원을 기다리면, 서로 진행하지 못하는 교착 상태가 된다. DBMS는 이런 상황에서 한 작업을 취소하는 등의 처리가 필요하다.

이 설명은 일반적인 잠금 기반 제어 모델이다. SQLite가 행마다 공유 잠금과 배타 잠금을 두고 이 모델을 그대로 구현한다고 이해하면 안 된다. SQLite의 BEGIN IMMEDIATE는 쓰기 트랜잭션을 시작할 때 쓰기 자리를 먼저 확보하려는 명령이다. 특정 inventory 행만 잠그는 명령이 아니며, 다른 쓰기 작업이 이미 진행 중이면 대기 후 실패할 수 있다.

다중 버전 동시성 제어

다중 버전 동시성 제어(MVCC)는 변경 전후의 여러 상태를 관리하고, 읽는 작업에 알맞은 상태를 보여 주는 접근이다. 읽는 쪽은 자신에게 허용된 시점의 일관된 모습을 보고, 쓰는 쪽은 새 변경을 만든다. 이때 읽기에 제공되는 특정 시점의 모습을 스냅숏이라고 한다.

버전을 여러 개 관리한다고 해서 모든 충돌이 없어지는 것은 아니다. 같은 데이터를 바꾸려는 쓰기끼리는 여전히 조정이 필요하다. 또한 스냅숏의 수명이 문장 하나인지 트랜잭션 전체인지에 따라 반복 조회의 결과가 달라질 수 있다. MVCC는 구현 접근이고, 격리 수준은 외부에 제공하는 보장이라는 차이를 기억해야 한다.

SQLite의 WAL 모드는 이러한 스냅숏 읽기를 이해하기 좋은 사례다. 읽기 트랜잭션은 읽기를 시작할 때 정해진 범위의 데이터를 계속 본다. 다른 연결이 변경을 확정해도 진행 중인 읽기가 그 변경을 곧바로 섞어 보지는 않는다. 읽기와 쓰기를 함께 진행할 수 있지만, 쓰기는 여전히 한 번에 하나다.

오래된 스냅숏을 읽던 연결이 쓰기로 전환하려고 할 때, 그 사이 다른 연결이 쓰기를 확정했다면 SQLITE_BUSY_SNAPSHOT 오류가 날 수 있다. 이 경우 오래 기다린다고 기존 스냅숏이 최신 상태가 되지는 않는다. 현재 트랜잭션을 끝내고, 필요한 데이터를 다시 읽는 새 트랜잭션으로 업무를 재시도해야 한다.

잠금과 버전 관리에서 각각 확인할 질문

장치	주요 역할	함께 고려할 점
잠금	충돌하는 접근의 진행 순서를 조정한다.	대기, 교착 상태, 잠금 범위를 확인한다.
버전 관리	읽기에 일관된 과거 상태를 제공한다.	버전 보관 비용과 쓰기 충돌을 확인한다.
조건부 UPDATE	변경 순간에 업무 조건을 함께 검사한다.	변경된 행 수를 확인한다.

로그로 중단 이전의 약속을 복원한다

데이터 파일과 확정 시점 사이의 간격

DBMS는 변경된 데이터를 메모리에 모아 두었다가 데이터 파일에 기록할 수 있다. 따라서 트랜잭션이 확정되었다는 사실과 모든 변경 페이지가 데이터 파일에 기록되었다는 사실은 같지 않을 수 있다. 반대로 어떤 저장 방식에서는 아직 확정하지 않은 변경이 데이터 파일에 먼저 반영되기도 한다. 회복은 이 차이를 처리해야 한다.

로그(log)는 변경 내용과 트랜잭션의 진행 상태를 기록한 자료다. 대표적인 로그 기반 회복에서는 데이터 페이지를 저장하기 전에 그 변경을 복구하는 데 필요한 로그를 먼저 안전한 저장 장치에 기록한다. 이를 로그 선행 기록(write-ahead logging)이라고 한다. 내구성을 보장하는 설정에서는 확정 응답에 필요한 로그도 응답 전에 안전하게 기록되어야 한다.

재실행(REDO)은 로그를 이용해 필요한 변경을 다시 반영하는 일이다. 예를 들어 재고 5를 4로 바꾼 트랜잭션의 확정 기록은 남았지만 데이터 페이지에는 아직 5가 남았다면, 회복 과정에서 확정된 변경을 살려야 한다. 이때 단순히 ‘재고에서 1 빼기’를 아무 확인 없이 반복하면 안 된다. 회복 알고리즘은 이미 반영된 변경인지 판별하는 정보 등을 사용해 중복 적용을 제어한다.

취소(UNDO)는 확정되지 않은 변경의 영향을 제거하는 일이다. 미확정 변경이 데이터 파일에 기록되는 저장 방식이라면 중단 뒤 그 흔적을 되돌릴 근거가 필요하다. 다만 모든 DBMS가 같은 방식으로 REDO와 UNDO를 수행하지는 않는다. 어떤 알고리즘은 중단 전의 변경 흐름을 먼저 재현하고, 이후 미완료 트랜잭션을 취소한다.

그림은 회복이 달성해야 하는 두 목표를 분리한 개념도다. SQLite가 행마다 REDO 기록과 UNDO 기록을 작성한다는 뜻은 아니다. SQLite의 롤백 저널 모드는 변경 전 페이지를 보관하는 방식으로 되돌리기를 지원한다. WAL 모드는 변경된 페이지를 별도 파일에 기록하고, 확정된 범위의 변경을 읽기에 반영한다. 미확정 부분을 유효한 확정 상태에 포함하지 않는 방식이므로 일반적인 행 단위 UNDO 설명과 구분해야 한다.

체크포인트는 회복을 위한 기준점을 만든다

체크포인트(checkpoint)는 로그와 데이터 파일의 진행 상태를 정리해, 회복할 때 참고할 기준을 만드는 작업이다. 로그가 처음 만들어진 순간부터 모든 변경을 매번 다시 살펴보면 회복 비용이 커진다. 어떤 페이지가 저장되었고 어떤 트랜잭션이 진행 중인지 등의 정보를 정리하면 필요한 처리 범위를 줄일 수 있다.

체크포인트가 모든 트랜잭션의 종료를 기다리거나, 그 시점의 모든 변경을 한꺼번에 저장한다는 뜻은 아니다. 운영을 계속하면서 수행하는 방식도 있다. 따라서 체크포인트 이전의 로그를 아무 조건 없이 삭제해도 된다는 결론은 나오지 않는다. 아직 진행 중인 작업이나 다른 복구 목적에 필요한 기록이 있을 수 있다.

SQLite의 WAL 체크포인트는 확정된 WAL 내용을 데이터베이스 파일로 옮기는 작업이다. 오래 실행되는 읽기 트랜잭션은 자신이 보고 있는 상태를 유지해야 하므로 체크포인트의 진행이나 WAL 재사용을 제한할 수 있다. 확정은 이미 성립했더라도 WAL 파일이 계속 커질 수 있는 이유다. 체크포인트는 백업을 대신하지 않으며, 디스크 자체가 손실되었을 때의 복구 자료를 별도로 보장하지 않는다.

제품별 저장 동작을 확인하려면 SQLite의 원자적 확정 설명과 WAL 및 체크포인트 설명을 참고한다. 이 장의 회복 개념은 이러한 구현을 읽기 위한 틀이며, 특정 제품의 내부 처리 순서를 그대로 나타내지는 않는다.

완성 코드

다음은 온라인 서점 데이터베이스에서 실행하는 완전한 SQLite SQL 프로그램이다. SQL은 별도의 컴파일 단계 없이 SQLite가 해석하여 실행한다. 재고가 1 이상인 책 중 book_id가 가장 작은 하나를 선택하고, 조건부로 1을 차감한 뒤 검증한다. 이어서 저장점으로 돌아가 재고가 복원되었는지 확인한다. 마지막에는 전체 트랜잭션을 취소한다.

임시 테이블 lab_inventory_before는 검증할 변경 전 값을 보관한다. 온라인 서점의 테이블이나 열 이름은 바꾸지 않는다. 저장점은 트랜잭션 내부에서 부분적으로 되돌아갈 위치이며, 저장점 해제가 트랜잭션 확정을 뜻하지는 않는다. 실행은 다른 트랜잭션이 열려 있지 않은 연결에서 시작한다.

이 코드는 조건부 변경과 명시적인 되돌리기를 관찰하는 실습이다. 연결 하나에서 실행하므로 두 연결의 경쟁을 재현하지는 않는다. ROLLBACK의 결과가 맞는다고 해서 장애 회복 알고리즘까지 검증한 것도 아니다.

concurrency_recovery.sql

CREATE TEMP TABLE lab_inventory_before (
    book_id INTEGER PRIMARY KEY,
    stock INTEGER NOT NULL
);

BEGIN IMMEDIATE;

INSERT INTO lab_inventory_before (book_id, stock)
SELECT book_id, stock
FROM inventory
WHERE stock >= 1
ORDER BY book_id
LIMIT 1;

SELECT '대상 행 수: ' || COUNT(*)
FROM lab_inventory_before;

SAVEPOINT lab_change;

UPDATE inventory
SET stock = stock - 1
WHERE book_id = (
    SELECT book_id FROM lab_inventory_before
)
AND stock >= 1;

SELECT '조건부 차감: ' ||
    CASE
        WHEN NOT EXISTS (
            SELECT 1
            FROM lab_inventory_before AS b
            WHERE NOT EXISTS (
                SELECT 1
                FROM inventory AS i
                WHERE i.book_id = b.book_id
                  AND i.stock = b.stock - 1
            )
        ) THEN '통과'
        ELSE '실패'
    END;

ROLLBACK TO lab_change;
RELEASE lab_change;

SELECT '되돌리기: ' ||
    CASE
        WHEN NOT EXISTS (
            SELECT 1
            FROM lab_inventory_before AS b
            WHERE NOT EXISTS (
                SELECT 1
                FROM inventory AS i
                WHERE i.book_id = b.book_id
                  AND i.stock = b.stock
            )
        ) THEN '통과'
        ELSE '실패'
    END;

ROLLBACK;

DROP TABLE lab_inventory_before;

대상 행이 없으면 UPDATE는 아무 행도 바꾸지 않는다. 이 경우 두 검증의 ‘통과’는 검사 대상에서 위반을 발견하지 않았다는 뜻이다. 실제 차감과 복원을 관찰했다는 뜻으로 해석하지 않도록 대상 행 수도 함께 출력한다. 실습 도중 오류가 나면 뒤의 문장을 계속 실행하지 말고, 연결에 남은 트랜잭션을 ROLLBACK으로 정리한 뒤 원인을 확인한다.

줄별 해설

CREATE TEMP TABLE로 시작하는 부분은 현재 연결에서만 사용할 비교 자료를 만든다. book_id는 대상 책을 식별하고 stock은 변경 전 재고를 담는다. 임시 테이블 생성은 BEGIN보다 앞에 있으므로 마지막 ROLLBACK 이후에도 테이블 자체는 남아 있다. 끝의 DROP TABLE이 이를 정리한다.

BEGIN IMMEDIATE는 비교 자료를 읽기 전에 쓰기 트랜잭션을 시작한다. 쓰기가 가능한지 뒤늦게 확인하는 일을 줄여 주지만, 다른 연결이 쓰기 중이면 이 문장 자체가 실패할 수 있다. 실패한 상태에서 이후의 변경 문장만 실행해서는 안 된다.

INSERT부터 LIMIT 1까지는 재고가 남아 있는 첫 책의 원래 값을 보관한다. 이 자료를 저장점보다 먼저 넣었기 때문에 저장점으로 되돌아가도 비교 자료는 유지된다. 첫 SELECT는 실제로 선택된 행이 0개인지 1개인지 알려 준다.

SAVEPOINT lab_change는 재고 변경 직전의 위치를 표시한다. UPDATE는 프로그램이 계산한 숫자를 덮어쓰지 않고, 변경 대상 행의 stock을 기준으로 1을 뺀다. stock >= 1 조건을 같은 문장에 넣어 재고가 남아 있을 때만 차감한다. 스칼라 서브쿼리가 행을 반환하지 않으면 비교값은 NULL이 되어 대상 행이 선택되지 않는다.

첫 검증은 선택된 책마다 ‘원래 재고에서 1을 뺀 행’이 존재하는지 확인한다. 바깥 NOT EXISTS는 그러한 행을 찾지 못한 비교 자료가 하나도 없는지를 묻는다. 단순히 재고가 음수가 아닌지만 검사하는 것보다, 의도한 변화가 일어났는지를 직접 확인한다.

ROLLBACK TO lab_change는 저장점 이후의 차감을 되돌린다. 이 명령만으로 저장점 자체가 없어지지는 않으므로 RELEASE로 해제한다. 이후 검증은 현재 재고가 비교 자료의 원래 값과 같은지 확인한다. 마지막 ROLLBACK은 바깥 트랜잭션을 끝내며, 비교 자료를 넣었던 작업도 취소한다.

실행 결과

macOS 또는 Linux에서 sqlite3 명령이 설치되어 있고, 온라인 서점 테이블이 들어 있는 bookstore.db와 SQL 파일이 현재 디렉터리에 있다고 가정한다. 빈 데이터베이스 파일을 지정하면 inventory가 없으므로 실행할 수 없다. 다음 명령은 오류가 발생하면 실행을 중단하고, 열 이름 없이 결과를 출력한다.

sqlite3 -batch -bail -noheader -list bookstore.db < concurrency_recovery.sql

예시 결과는 다음과 같다. 재고가 1 이상인 행이 하나 이상 있고 다른 쓰기 작업과 충돌하지 않으면 출력은 아래와 일치한다. 책 번호나 재고의 실제 값은 데이터마다 다르므로 출력하지 않는다.

대상 행 수: 1
조건부 차감: 통과
되돌리기: 통과

재고가 1 이상인 행이 하나도 없으면 출력은 다음과 같다. 이 경우에는 실제 재고 변경을 시험하지 못했다.

대상 행 수: 0
조건부 차감: 통과
되돌리기: 통과

SQL 연구소에서는 같은 SQL을 같은 연결에서 순서대로 실행한다. 브라우저 화면은 문자열을 결과 열로 표시할 수 있으므로 터미널과 표시 모양은 다를 수 있다. 환경이 마지막 조회 결과만 보여 준다면 문장 묶음을 나누어 실행하되 연결을 유지한다. 브라우저 탭을 두 개 열었다는 사실만으로 두 탭이 같은 데이터베이스 파일을 공유한다고 가정해서는 안 된다.

실무에서 자주 틀리는 것

조회한 재고를 나중에도 현재 값으로 취급한다

다음 코드는 book_id가 1인 책에서 앞서 읽은 재고가 5였다고 가정하고 4를 저장한다. 조회와 저장 사이에 다른 판매가 있었다면 그 효과를 덮어쓸 수 있다. 숫자 1은 실행 가능한 예시 식별자이며, 실제 데이터에 없으면 아무 행도 바뀌지 않는다.

UPDATE inventory
SET stock = 4
WHERE book_id = 1;

고친 코드는 변경 순간의 값으로 계산하고 재고 조건을 함께 검사한다. 바로 다음 changes() 조회가 1이면 한 행을 차감했고, 0이면 해당 책이 없거나 재고 조건을 만족하지 못한 것이다. 실제 주문에서는 이 결과를 확인한 뒤 같은 트랜잭션 안에서 후속 처리를 결정한다. 아래 예시는 실습을 위해 취소한다.

BEGIN IMMEDIATE;

UPDATE inventory
SET stock = stock - 1
WHERE book_id = 1
  AND stock >= 1;

SELECT changes() AS changed_rows;

ROLLBACK;

격리를 낮추면 쓰기 대기도 사라진다고 생각한다

다음 코드는 쓰기 충돌을 줄이려는 목적으로 read_uncommitted를 켠 경우다. 이 설정은 쓰기 트랜잭션을 여러 개 동시에 진행하도록 바꾸지 않는다. 일반적인 별도 연결에서는 미확정 읽기를 허용하는 효과도 기대할 수 없다.

PRAGMA read_uncommitted = 1;
BEGIN IMMEDIATE;
UPDATE inventory
SET stock = stock - 1
WHERE book_id = 1 AND stock >= 1;
ROLLBACK;

고친 코드는 기본 읽기 설정을 유지하고 짧은 대기 시간을 지정한다. busy_timeout은 기다릴 여지를 주는 설정이며 성공을 보장하지 않는다. 시간이 지나도 쓰기 자리를 확보하지 못하면 BEGIN이 실패할 수 있다. 그때는 업무 단위로 재시도할지 결정해야 한다.

PRAGMA read_uncommitted = 0;
PRAGMA busy_timeout = 2000;

BEGIN IMMEDIATE;
UPDATE inventory
SET stock = stock - 1
WHERE book_id = 1 AND stock >= 1;
ROLLBACK;

모든 잠금 오류가 설정 시간만큼 기다린 뒤 발생하는 것은 아니다. 특히 오래된 스냅숏의 쓰기 전환 실패는 트랜잭션을 새로 시작해야 하는 문제다. 사용자 입력이나 외부 통신을 기다리는 동안 쓰기 트랜잭션을 붙잡아 두지 않는 것이 대기 시간을 줄이는 데 도움이 된다.

확정한 변경도 ROLLBACK으로 되돌릴 수 있다고 생각한다

다음 코드에서 COMMIT은 변경을 확정한다. 뒤의 ROLLBACK은 이미 끝난 트랜잭션을 취소하지 못하며, SQLite에서는 활성 트랜잭션이 없다는 오류가 발생한다. 잘못된 예이므로 실습 데이터에 그대로 실행하지 않는다.

BEGIN IMMEDIATE;
UPDATE inventory
SET stock = stock - 1
WHERE book_id = 1 AND stock >= 1;
COMMIT;
ROLLBACK;

검증 후 원래 상태로 돌아가려는 실습에서는 확정 전에 되돌려야 한다. 이미 확정된 업무를 취소하려면 별도의 보상 작업이 필요하며, 이는 장애 회복의 UNDO와 구분된다.

BEGIN IMMEDIATE;
SAVEPOINT trial;

UPDATE inventory
SET stock = stock - 1
WHERE book_id = 1 AND stock >= 1;

ROLLBACK TO trial;
RELEASE trial;
ROLLBACK;

한눈에 보기

동시 실행과 장애 회복에서 기억할 판단 기준

개념	판단할 질문	핵심 대응
갱신 손실	오래된 계산 결과가 다른 변경을 덮었는가?	조건부 변경과 트랜잭션 경계를 점검한다.
더티 리드	다른 작업의 미확정 변경을 읽었는가?	격리 보장과 연결 설정을 확인한다.
반복 불가능 읽기	같은 행을 다시 읽어 값이 달라졌는가?	읽기 상태가 유지되는 범위를 확인한다.
팬텀	같은 조건에 맞는 행 집합이 달라졌는가?	범위 보호와 스냅숏의 범위를 확인한다.
2단계 잠금	잠금을 놓은 뒤 새 잠금을 얻는가?	획득 단계와 해제 단계를 구분한다.
MVCC	이 읽기는 어느 시점의 상태를 보는가?	읽기 일관성과 쓰기 충돌을 나누어 본다.
REDO와 UNDO	살려야 할 변경과 제거할 흔적은 무엇인가?	로그와 확정 상태를 근거로 회복한다.
체크포인트	회복과 로그 정리에 어떤 기준이 필요한가?	진행 중인 작업과 저장 상태를 함께 고려한다.

연습 문제

1. 한 트랜잭션이 같은 book 행의 price를 두 번 조회했다. 두 조회 사이 다른 트랜잭션이 가격을 바꾸고 확정했고, 두 번째 조회에는 새 가격이 보였다. 어떤 읽기 현상이며 더티 리드와는 무엇이 다른지 설명하라.
2. 두 프로그램이 트랜잭션 밖에서 재고 8을 읽고, 각각 새 트랜잭션을 열어 7을 저장했다. 저장 트랜잭션만 강하게 격리해도 판매 두 번이 올바르게 반영되는지 설명하라.
3. 로그에는 확정 기록이 있지만 데이터 페이지에는 변경이 빠져 있다. 다른 미완료 트랜잭션의 변경은 이미 데이터 페이지에 남았다. 일반적인 로그 기반 회복에서 각각 필요한 목표와 체크포인트의 역할을 설명하라.

정답과 해설

1. 반복 불가능 읽기다. 같은 트랜잭션이 같은 행을 다시 읽었는데 값이 바뀌었다. 새 가격은 다른 트랜잭션이 확정한 값이므로 미확정 변경을 읽는 더티 리드와 다르다.
2. 올바르게 반영된다고 보장할 수 없다. 저장 작업이 순서대로 실행되어도 7을 두 번 저장하면 최종 재고는 7이다. 조회와 판단을 포함한 업무 경계를 다시 정하거나, 현재 재고에서 차감하는 조건부 UPDATE를 사용해야 한다.
3. 빠진 확정 변경을 살리는 것이 REDO의 목표이며, 미확정 변경의 흔적을 제거하는 것이 UNDO의 목표다. 실제 적용 순서는 회복 알고리즘마다 다르다. 체크포인트는 저장 및 진행 상태를 정리해 필요한 회복 범위를 줄이는 데 도움을 준다.

SQL 연구소 실습 A 해설

재고가 2 이상인 책 하나를 선택하는 조건을 UPDATE 안에 넣을 수 있다. 변경된 행 수를 바로 확인하고 취소한다. 대상이 없다면 changed_rows는 0이므로 차감이 수행되지 않은 것이다.

BEGIN IMMEDIATE;

UPDATE inventory
SET stock = stock - 2
WHERE book_id = (
    SELECT book_id
    FROM inventory
    WHERE stock >= 2
    ORDER BY book_id
    LIMIT 1
)
AND stock >= 2;

SELECT changes() AS changed_rows;

ROLLBACK;

SQL 연구소 실습 B 해설

같은 연결에서 저장점 전후의 PAID 주문 수를 비교한다. 저장점 이후에는 자신의 변경이 보이므로 PAID 주문이 있었다면 수가 0이 된다. 되돌린 뒤에는 처음 수로 복원된다. 처음부터 해당 주문이 없다면 세 번 모두 0이다. 이는 자기 변경의 가시성과 되돌리기 실습이며 팬텀 재현은 아니다.

BEGIN IMMEDIATE;

SELECT COUNT(*) AS paid_before
FROM orders
WHERE status = 'PAID';

SAVEPOINT status_trial;

UPDATE orders
SET status = 'CANCELLED'
WHERE status = 'PAID';

SELECT COUNT(*) AS paid_during
FROM orders
WHERE status = 'PAID';

ROLLBACK TO status_trial;
RELEASE status_trial;

SELECT COUNT(*) AS paid_after
FROM orders
WHERE status = 'PAID';

ROLLBACK;

SQL 연구소에서 실습하기

다음 과제는 온라인 서점 데이터에서 실행한다. 변경은 모두 ROLLBACK으로 취소한다. 정답은 앞의 정답과 해설 절에서 확인할 수 있다.

• 실습 A: 재고가 2 이상인 책 중 book_id가 가장 작은 하나의 재고를 2만큼 줄이는 조건부 UPDATE를 작성하라. 변경된 행 수를 조회하고 전체 작업을 취소하라.
• 실습 B: PAID 상태의 주문 수를 조회하고 저장점을 만든 뒤, 해당 주문을 CANCELLED로 바꾸어 다시 수를 조회하라. 저장점으로 되돌린 뒤 최초 주문 수가 복원되는지 확인하고 트랜잭션을 끝내라.