SQL 트랜잭션과 격리수준 - ACID, 격리수준별 이상현상과 데드락 (SQL 중급 11단원)

이 단원에서 배우는 것

7단원부터 10단원까지는 읽기만 했다. 읽기는 아무리 틀려도 데이터가 남아 있지만, 쓰기는 다르다. 게다가 운영 DB 에는 나 혼자 접속하지 않는다. 같은 행을 두 세션이 동시에 고치는 순간 "각각은 맞는데 합쳐 놓으면 틀린" 상황이 생긴다. 이번 단원은 그 경계를 관리하는 트랜잭션과 격리수준을 다룬다. 테이블은 계속 1단원의 emp 를 쓴다. 아래 예제에 나오는 1003 은 강감찬(3,900), 1004 는 을지문덕(4,100)이다.

• ACID 네 글자가 각각 무엇을 보장하는지 실제 장애 상황과 연결해 설명한다
• 네 가지 격리수준과 각 수준에서 허용되는 이상현상을 구분한다
• 데드락이 왜 나는지 재현하고, 애플리케이션에서 어떻게 대처할지 정한다

개념

계좌 이체를 생각해 보자. A 에서 10만원을 빼고 B 에 10만원을 더한다. 두 UPDATE 사이에서 서버가 죽으면 돈이 증발한다. 그래서 DB 는 "여러 문장을 하나의 단위로 묶고, 전부 반영하거나 전부 없던 일로 만든다"는 장치를 제공한다. 그게 트랜잭션이다. ACID 는 그 보장의 네 측면이다.

속성	보장 내용	안 지켜지면
Atomicity 원자성	전부 성공하거나 전부 취소	출금만 되고 입금이 안 된다
Consistency 일관성	제약조건을 깨는 상태로 끝나지 않음	없는 부서를 참조하는 사원이 생긴다
Isolation 격리성	동시 실행이 순차 실행처럼 보임	남의 미완성 작업을 읽고 계산한다
Durability 지속성	커밋된 것은 장애 후에도 남음	커밋 응답을 받았는데 재기동하니 없다

이 중 실무에서 매일 부딪히는 것은 Isolation 하나다. 격리를 완벽하게 하려면 트랜잭션을 한 줄로 세워야 하는데 그러면 처리량이 죽는다. 그래서 표준은 격리를 얼마나 포기하고 성능을 얼마나 얻을지를 네 단계로 나눠 두었다. 그 대가로 나타나는 현상이 이상현상이다.

표준 SQL 문법과 예제

트랜잭션 시작과 끝

START TRANSACTION;                     -- 표준. MySQL 은 BEGIN 도 가능

UPDATE emp SET salary = salary - 500 WHERE emp_id = 1003;
UPDATE emp SET salary = salary + 500 WHERE emp_id = 1004;

COMMIT;                                -- 확정
-- ROLLBACK;                           -- 취소

대부분의 DB 클라이언트는 autocommit 이 켜져 있어 문장 하나가 곧 트랜잭션 하나다. 위처럼 START TRANSACTION 을 쓰면 그 시점부터 명시적 트랜잭션이 시작되고 COMMIT/ROLLBACK 까지 이어진다. 중간 지점을 만들 수도 있다.

START TRANSACTION;
UPDATE emp SET salary = 4300 WHERE emp_id = 1004;
SAVEPOINT sp1;
DELETE FROM emp WHERE dept_id IS NULL;     -- 홍범도가 지워진다. 아차 싶으면
ROLLBACK TO SAVEPOINT sp1;                 -- DELETE 만 취소, UPDATE 는 유지
COMMIT;

격리수준과 이상현상

격리수준	Dirty Read	Non-repeatable Read	Phantom Read
READ UNCOMMITTED	발생	발생	발생
READ COMMITTED	없음	발생	발생
REPEATABLE READ	없음	없음	표준상 발생
SERIALIZABLE	없음	없음	없음

• Dirty Read — 아직 커밋되지 않은 값을 읽는다. 상대가 롤백하면 존재한 적 없는 값으로 계산한 셈이 된다.
• Non-repeatable Read — 같은 행을 두 번 읽었는데 값이 달라진다. 사이에 남이 UPDATE 하고 커밋했기 때문이다.
• Phantom Read — 같은 조건으로 두 번 조회했는데 행 개수가 달라진다. 사이에 남이 INSERT 했기 때문이다.

세션 두 개를 띄우고 직접 확인하는 것이 가장 빠르다. 아래는 Non-repeatable Read 재현이다.

-- 세션 A
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT salary FROM emp WHERE emp_id = 1003;   -- 3900.00

-- 세션 B (여기서 실행)
UPDATE emp SET salary = 4300 WHERE emp_id = 1003;
COMMIT;

-- 세션 A (다시)
SELECT salary FROM emp WHERE emp_id = 1003;   -- 4300.00  같은 트랜잭션인데 값이 바뀐다
COMMIT;

세션 A 의 격리수준을 REPEATABLE READ 로 바꾸고 같은 순서로 실행하면 두 번째 SELECT 도 3900.00 이 나온다. 트랜잭션 시작 시점의 스냅샷을 계속 보기 때문이다.

잠금 읽기

스냅샷을 읽는 것만으로는 막지 못하는 문제가 있다. 읽고, 계산하고, 쓰는 세 단계 사이에 남이 끼어드는 갱신 손실(lost update)이다.

-- 위험: 두 세션이 동시에 실행하면 한쪽 인상분이 사라진다
SELECT salary FROM emp WHERE emp_id = 1003;   -- 3900 을 애플리케이션이 읽고
UPDATE emp SET salary = 4290 WHERE emp_id = 1003;  -- 10% 인상해서 덮어쓴다

-- 방법 1: 잠금을 걸고 읽는다
START TRANSACTION;
SELECT salary FROM emp WHERE emp_id = 1003 FOR UPDATE;   -- 다른 세션은 여기서 대기
UPDATE emp SET salary = 4290 WHERE emp_id = 1003;
COMMIT;

-- 방법 2: 읽지 말고 DB 안에서 계산한다 (가능하면 이쪽)
UPDATE emp SET salary = salary * 1.1 WHERE emp_id = 1003;

방법 2가 가능하면 항상 방법 2다. 잠금을 걸지 않고, 왕복이 한 번이며, 원자적이다. 재고 차감도 SET stock = stock - 1 WHERE id = ? AND stock >= 1 로 쓰고 영향받은 행 수가 0 이면 품절로 처리하는 것이 정석이다.

데드락 재현

-- 세션 A                                 -- 세션 B
START TRANSACTION;                        START TRANSACTION;
UPDATE emp SET salary = salary + 1
  WHERE emp_id = 1003;                    UPDATE emp SET salary = salary + 1
                                            WHERE emp_id = 1004;
UPDATE emp SET salary = salary + 1
  WHERE emp_id = 1004;   -- B 를 기다림    UPDATE emp SET salary = salary + 1
                                            WHERE emp_id = 1003;   -- A 를 기다림

서로가 상대의 잠금을 기다리며 영원히 풀리지 않는 상태다. DB 는 이걸 탐지해서 한쪽을 강제로 실패시킨다. MySQL 이면 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 이 뜬다. 원인은 두 세션이 같은 두 행을 서로 다른 순서로 잡았다는 것 하나다.

-- MySQL/MariaDB: 마지막 데드락 상세 확인
SHOW ENGINE INNODB STATUS\G     -- LATEST DETECTED DEADLOCK 섹션

예방책은 세 가지다. (1) 여러 행을 갱신할 때 항상 같은 순서(예: 기본키 오름차순)로 접근한다. (2) 트랜잭션을 짧게 유지한다. (3) 인덱스 없는 컬럼으로 UPDATE 하지 않는다. 인덱스가 없으면 InnoDB 가 훨씬 넓은 범위에 잠금을 걸어 충돌 확률이 급증한다.

DB별 차이

항목	MySQL · MariaDB	PostgreSQL	Oracle	SQL Server
기본 격리수준	REPEATABLE READ	READ COMMITTED	READ COMMITTED	READ COMMITTED (잠금 기반)
지원 수준	4단계 모두	4단계 문법 모두 (READ UNCOMMITTED 는 READ COMMITTED 로 동작)	READ COMMITTED, SERIALIZABLE	4단계 + SNAPSHOT
팬텀 방지	갭 락으로 REPEATABLE READ 에서도 대부분 차단	REPEATABLE READ 가 스냅샷 격리라 팬텀 없음	SERIALIZABLE 이 스냅샷 격리	RCSI 옵션으로 MVCC 전환 가능
데드락 에러	1213 (SQLSTATE 40001)	SQLSTATE 40P01	ORA-00060	1205
DDL 롤백	불가 (암묵 커밋)	가능	불가 (암묵 커밋)	대부분 가능
기본 autocommit	ON	ON	OFF (직접 COMMIT 필요)	ON

Oracle 의 autocommit OFF 는 SQL*Plus 로 데이터를 고치고 창을 닫았다가 "왜 반영이 안 됐지"로 이어지는 대표적인 함정이다. 반대로 MySQL 에 익숙한 사람이 Oracle 에서 DELETE 를 치고 커밋하지 않은 채 자리를 뜨면, 그 행에 잠금이 걸린 채로 다른 배치가 전부 밀린다.

실무에서 자주 틀리는 것

1. 트랜잭션 안에서 외부 호출을 한다

START TRANSACTION;
UPDATE orders SET status = 'PAID' WHERE order_id = 1001;
-- 여기서 결제사 API 호출 (평균 300ms, 타임아웃 30초)
INSERT INTO payment_log ...;
COMMIT;

API 가 느려지는 순간 잠금이 30초씩 유지되고, 같은 행을 건드리는 요청이 줄줄이 밀리다가 커넥션 풀이 마른다. 장애 보고서에 가장 자주 등장하는 형태다. 외부 호출은 트랜잭션 밖으로 빼고, DB 갱신은 호출 결과를 받은 뒤 짧게 처리한다.

2. DDL 을 롤백할 수 있다고 믿는다

START TRANSACTION;
DELETE FROM emp WHERE dept_id = 30;
ALTER TABLE emp ADD COLUMN memo VARCHAR(100);   -- 여기서 암묵 커밋 발생
ROLLBACK;                                       -- DELETE 는 이미 커밋됐다

MySQL 과 Oracle 은 DDL 을 만나면 진행 중인 트랜잭션을 자동으로 커밋한다. 마이그레이션 스크립트에서 DML 과 DDL 을 섞어 쓰면 실패했을 때 절반만 반영된 상태로 남는다. PostgreSQL 은 DDL 도 롤백되지만, 그 사실에 의존한 스크립트는 MySQL 로 옮기는 순간 깨진다.

3. 데드락을 버그 취급한다

데드락은 동시성이 있는 시스템에서 완전히 없앨 수 없다. DB 가 한쪽을 죽이는 것은 정상 동작이다. 따라서 애플리케이션은 해당 에러코드를 잡아 짧은 지연 후 재시도하는 로직을 갖고 있어야 한다. 재시도 횟수는 2~3회, 지연은 무작위 값을 섞는다(같은 간격으로 재시도하면 다시 충돌한다). 재시도 대상은 데드락과 잠금 타임아웃이지 제약조건 위반이 아니다.

4. 트랜잭션을 열어 놓고 오래 둔다

커밋하지 않은 트랜잭션은 잠금만 잡고 있는 게 아니라, 다른 세션이 볼 옛 버전을 계속 보관하게 만든다. PostgreSQL 에서는 오래된 트랜잭션 하나가 VACUUM 을 막아 테이블이 부풀고, MySQL 에서는 언두 로그가 커진다. 배치가 수십 분짜리 트랜잭션을 하나로 묶고 있다면 일정 건수마다 커밋하도록 쪼개는 편이 낫다.

스스로 확인하기

1. 세션 두 개로 Dirty Read 를 재현하려면 읽는 쪽 세션에 어떤 설정이 필요한가. MySQL 기준으로 문장을 써라.
2. 사원 1003 과 1004 의 급여를 서로 맞바꾸는 작업을 안전하게 수행하는 트랜잭션을 작성하라.
3. 재고 테이블 stock(item_id, qty) 에서 수량을 1 줄이되 재고가 없으면 아무 일도 일어나지 않게 하는 UPDATE 를 써라. 잠금 읽기 없이 해결한다.

-- 1
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT salary FROM emp WHERE emp_id = 1003;   -- 다른 세션이 커밋 전에 바꾼 값이 보인다
COMMIT;

-- 2  (기본키 오름차순으로 잠금을 잡아 데드락 위험을 줄인다)
START TRANSACTION;
SELECT emp_id, salary FROM emp
 WHERE emp_id IN (1003, 1004) ORDER BY emp_id FOR UPDATE;   -- 두 값을 읽고 잠근다
UPDATE emp SET salary = ? WHERE emp_id = 1003;   -- 1004 에서 읽은 값
UPDATE emp SET salary = ? WHERE emp_id = 1004;   -- 1003 에서 읽은 값
COMMIT;

-- 3
UPDATE stock SET qty = qty - 1 WHERE item_id = ? AND qty >= 1;
-- 영향받은 행 수가 0 이면 품절. 애플리케이션에서 그 값을 반드시 확인한다.

2번의 핵심은 FOR UPDATE 로 두 행을 먼저, 기본키 순서로 잠그는 것이다. 순서를 고정하지 않으면 반대 방향으로 맞바꾸는 트랜잭션과 만나 데드락이 난다. SQL 한 문장으로 맞바꾸려고 UPDATE emp SET salary = (SELECT salary FROM emp WHERE ...) 를 쓰면 MySQL 은 대상 테이블을 같은 문장의 서브쿼리에서 조회하지 못하게 막아 에러 1093 을 낸다. 애플리케이션이 두 값을 읽어 두고 UPDATE 두 번을 치는 편이 이식성도 가독성도 낫다.

마지막 12단원에서는 지금까지 "DB별 차이" 표로 흩어 놓았던 방언들을 페이징 · 문자열 · 날짜 · UPSERT · 시퀀스 축으로 한 번에 정리한다.

참고: MySQL InnoDB 격리수준 문서, PostgreSQL 트랜잭션 격리 문서