FOR UPDATE로 MAX + 1 번호 발급을 막을 수 없는 이유와 테이블 락의 한계

MAX + 1과 동시 요청

이전 포스팅에서 쿠폰 코드를 '지금까지 발급된 코드 중 가장 큰 값 + 1'로 만들면, 두 요청이 동시에 들어왔을 때 같은 번호를 받을 수 있다는 것을 살펴보았다. 두 요청 모두 저장하기 전에 같은 최댓값 00041을 읽고, 둘 다 00042를 저장하려 하기 때문이다.

sql

-- 지금까지 발급된 쿠폰 코드 중 숫자가 가장 큰 값을 가져옴; 1을 더해 INSERT
SELECT MAX(code) FROM coupons;

이번 포스팅에서는 이 문제를 락으로 막으려 하면 실제로 어떤 일이 생기는지, PostgreSQL 17과 MySQL 8.4에서 직접 실험한 결과로 살펴보도록 하자.

SELECT ... FOR UPDATE를 통한 해결과 한계

가장 먼저 떠오르는 방법은 한 요청이 최댓값을 읽는 동안 다른 요청은 읽지 못하게 잠그는 것이다. 이때 쓰는 구문이 SELECT ... FOR UPDATE이다.

FOR UPDATE의 특징

FOR UPDATE는 SELECT로 읽은 행에 쓰기 잠금을 거는 구문이다. 잠금은 트랜잭션이 끝날 때까지 유지되며, 다음과 같은 특징을 갖는다.

다른 트랜잭션이 같은 행을 UPDATE, DELETE 하거나 다시 FOR UPDATE로 읽으려 하면, 앞 트랜잭션이 끝날 때까지 기다린다.
잠금 없는 일반 SELECT는 막지 않는다. PostgreSQL과 MySQL(InnoDB) 모두 MVCC(다중 버전 동시성 제어, 행의 이전 버전을 보여 주어 읽기와 쓰기가 서로를 막지 않게 하는 방식)를 쓰기 때문이다.
잠그는 대상은 SELECT가 실제로 찾은 행이다. 즉, 이미 테이블에 존재하는 행만 잠글 수 있다.

PostgreSQL 문서, Explicit Locking

마지막 특징이 중요하다. MAX + 1에서 지켜야 하는 것은 이미 있는 00041이 아니라, 아직 존재하지 않는 다음 번호 00042이기 때문이다. 이 차이가 실제로 어떤 결과를 만드는지 PostgreSQL 17과 MySQL 8.4에서 직접 확인해 보자. 두 DB 모두 code를 기본 키로 하는 테이블에 00039, 00040, 00041 세 행을 넣고 시작했다.

PostgreSQL의 경우

PostgreSQL에서는 집계 결과에 FOR UPDATE를 아예 걸 수 없다.

sql

SELECT MAX(code) FROM coupons FOR UPDATE;
-- ERROR:  FOR UPDATE is not allowed with aggregate functions

집계 결과는 테이블의 특정 행 하나와 대응하지 않기 때문에, 무엇을 잠가야 할지 정할 수 없어서다.

"The locking clauses cannot be used in contexts where returned rows cannot be clearly identified with individual table rows; for example they cannot be used with aggregation."

PostgreSQL 문서, SELECT

그렇다면 집계 대신 가장 큰 코드를 가진 행을 직접 찾아서 잠그면 어떨까? 두 세션에서 아래 쿼리를 거의 동시에 실행해 보았다. A는 잠금을 잡은 채 2초 쉬었다가 00042를 INSERT 하고 커밋한다.

sql

BEGIN;
-- 가장 큰 코드를 가진 행 하나를 찾아 잠금
SELECT code FROM coupons ORDER BY code DESC LIMIT 1 FOR UPDATE;
INSERT INTO coupons VALUES ('00042');
COMMIT;

실행 결과는 다음과 같다.

A read      | 00041
B start     | 04:00:51.703
B read      | 00041            ← A가 커밋한 뒤에 읽었는데도 00041
B got lock  | 04:00:53.004
ERROR:  duplicate key value violates unique constraint "coupons_pkey"
DETAIL:  Key (code)=(00042) already exists.

B는 A가 커밋할 때까지 약 1.3초를 기다렸다. 여기까지는 의도대로다. 그런데 기다린 끝에 읽은 값은 A가 넣은 00042가 아니라 여전히 00041이었고, 결국 같은 00042를 넣으려다 실패했다.

이는 PostgreSQL의 기본 격리 수준인 READ COMMITTED의 동작 방식 때문이다. B는 쿼리를 시작한 시점에 00041 행을 대상으로 찾았고, 그 행이 잠겨 있어 기다렸다. A가 커밋한 뒤에는 대상을 다시 찾는 것이 아니라, 처음 찾은 00041 행의 최신 버전에 대해서만 작업을 이어 간다. A가 새로 넣은 00042는 B가 처음에 찾은 행이 아니므로 고려되지 않는다.

"If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row."

PostgreSQL 문서, Transaction Isolation

두 세션에서 일어난 일을 순서대로 놓으면 다음과 같다.

즉, FOR UPDATE는 00041을 지켰을 뿐 00042가 생기는 것은 막지 못했다.

MySQL의 경우

MySQL(InnoDB)은 집계에도 FOR UPDATE를 허용한다. 두 DB가 잠그는 대상을 다르게 정하기 때문이다. PostgreSQL은 쿼리가 돌려준 결과 행을 잠그므로, 특정 행과 대응하지 않는 집계 결과는 잠글 수 없었다. 반면 InnoDB는 쿼리를 처리하면서 읽은 인덱스 레코드를 잠근다. MAX(code)를 구하려면 인덱스의 마지막 레코드를 읽어야 하니, 그 레코드가 잠금 대상이 된다.

"A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of an SQL statement."

MySQL 문서, Locks Set by Different SQL Statements in InnoDB

같은 실험을 이번에는 MAX 그대로 해 보았다.

sql

BEGIN;
-- MySQL은 집계 쿼리에도 FOR UPDATE 허용
SELECT MAX(code) FROM coupons FOR UPDATE;
INSERT INTO coupons VALUES ('00042');
COMMIT;

A read      | 00041
B start     | 04:01:07.723
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
B read      | 00041
B got lock  | 04:01:09.089

이번에는 중복 대신 데드락이 났다. A의 INSERT가 데드락으로 롤백되었고, 그제서야 B가 잠금을 얻어 00041을 읽었다. 무슨 잠금이 잡혔는지 performance_schema.data_locks로 확인해 보면 다음과 같다.

thread_id  lock_type  lock_mode  lock_status  lock_data
301        RECORD     X          GRANTED      supremum pseudo-record
301        RECORD     X          GRANTED      '00041'
302        RECORD     X          GRANTED      supremum pseudo-record
302        RECORD     X          WAITING      '00041'

301이 A, 302가 B이다. A는 마지막 행인 '00041'과 함께 supremum pseudo-record에도 잠금을 잡았다. supremum은 인덱스의 마지막 값 뒤를 나타내는 가상의 레코드로, 여기에 걸린 잠금은 '00041 뒤의 빈 공간'에 새 행이 들어오는 것을 막는다. 이렇게 행 사이의 빈 공간에 거는 잠금을 갭 락(gap lock)이라고 한다. MySQL은 기본 격리 수준인 REPEATABLE READ에서 아직 없는 행이 끼어드는 것을 막기 위해 갭 락을 사용한다.

문제는 갭 락끼리는 서로 충돌하지 않는다는 점이다.

"Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap."

MySQL 문서, InnoDB Locking

그래서 다음과 같은 순서로 데드락이 생긴다.

MySQL에서는 같은 번호가 저장되지는 않았지만, 두 요청 중 하나는 실패했다.

두 DB의 공통 결론

두 DB 모두 잠금 자체는 걸렸다. 하지만 잠금이 걸린다고 해서 겹치지 않는 번호가 보장되지는 않았다.

PostgreSQL은 이미 있는 행만 잠근다. 그래서 A가 새로 넣을 00042는 잠금 범위 밖에 있었고, 기다린 B는 지나간 값 00041을 읽어 중복 키 에러가 났다.
MySQL은 갭 락으로 00041 뒤의 빈 공간까지 잠가 새 행이 들어오는 것을 막았다. 하지만 갭 락은 여러 트랜잭션이 함께 잡을 수 있어서, A와 B가 서로의 INSERT를 막다가 데드락이 났다.

실패하는 모양은 달랐지만 결과는 같다. 동시에 들어온 두 요청 중 하나는 실패하고, 처음의 MAX + 1과 마찬가지로 재시도 로직이 필요하다. 즉, FOR UPDATE는 이미 읽은 값을 지키는 도구이지, 아직 발급되지 않은 다음 번호를 한 요청에게만 나눠 주는 도구는 아니다.

테이블 락을 통한 해결과 한계

행을 잠그는 것으로 부족하다면, 남은 방법으로 테이블 전체를 잠그는 것을 시도해 볼 수 있다.

sql

BEGIN;
-- 같은 모드로 잠그려는 다른 트랜잭션은 이 트랜잭션이 끝날 때까지 여기서 대기
LOCK TABLE coupons IN SHARE ROW EXCLUSIVE MODE;
-- 가장 큰 코드에 1을 더해 5자리 문자열로 저장
INSERT INTO coupons (code)
SELECT lpad((MAX(code)::int + 1)::text, 5, '0') FROM coupons;
COMMIT;

SHARE ROW EXCLUSIVE 모드는 같은 모드끼리 부딪히기 때문에, A가 잠금을 쥐고 있는 동안 B는 기다린다. 앞의 실험과 같은 조건으로 두 세션을 동시에 실행해 보면 다음과 같다.

A locked    | 04:18:11.568
A next 00042
B start     | 04:18:12.308
B locked    | 04:18:13.584   ← A가 커밋할 때까지 약 1.3초 대기
A committed | 04:18:13.584
B next 00043                 ← A가 넣은 00042를 보고 다음 번호를 계산

B는 A가 커밋할 때까지 기다렸다가 00043을 받았고, 테이블에는 00042와 00043이 모두 저장되었다. FOR UPDATE 실험과 결과가 다른 이유는 기다리는 위치에 있다. FOR UPDATE에서 B는 00041 행을 이미 찾은 상태로 기다렸지만, 여기서는 MAX를 읽기 전에 기다린다. 그래서 B는 A가 커밋한 뒤의 테이블을 처음부터 읽는다.

이제 번호는 겹치지 않는다. 그런데 이 잠금은 쿠폰 생성과 상관없는 요청까지 함께 막는다. SHARE ROW EXCLUSIVE는 INSERT, UPDATE, DELETE가 잡는 ROW EXCLUSIVE 잠금과도 충돌하기 때문이다. A가 테이블 락을 잡고 있는 동안, 다른 세션에서 일반 SELECT와 다른 쿠폰의 사용 횟수를 올리는 UPDATE를 실행해 보았다.

A locked       | 04:38:05.692
C select start | 04:38:06.427
C select done  | 04:38:06.427   ← 일반 SELECT는 기다리지 않는다
D update start | 04:38:06.427
A committed    | 04:38:07.701
D update done  | 04:38:07.723   ← 다른 쿠폰(00039)의 UPDATE가 약 1.3초 대기

PostgreSQL 문서, Explicit Locking

다른 쿠폰을 만드는 B와 결제처럼 다른 쿠폰을 수정하는 D가 각각 어디서 기다리는지 함께 놓으면 다음과 같다.

D는 A가 만들던 쿠폰과 전혀 다른 쿠폰의 사용 횟수를 올렸을 뿐인데, A가 커밋할 때까지 기다렸다. 쿠폰 생성은 관리자만 하니 생성 요청끼리 줄을 서는 것은 큰 문제가 아니다. 하지만 쿠폰을 만드는 동안 그 쿠폰 테이블을 건드리는 결제까지 멈춘다면 이야기가 다르다.

쓰기를 막지 않는 잠금 모드

그렇다면 생성 요청끼리만 부딪히고, INSERT, UPDATE, DELETE와는 부딪히지 않는 모드를 고르면 된다. SHARE UPDATE EXCLUSIVE 모드에서는 같은 모드끼리 부딪히지만, ROW EXCLUSIVE와는 충돌하지 않는다.

A가 잡은 모드	쿠폰 생성 B (같은 모드)	다른 쿠폰 UPDATE D (`ROW EXCLUSIVE`)	일반 SELECT C (`ACCESS SHARE`)
`SHARE ROW EXCLUSIVE`	대기	대기	통과
`SHARE UPDATE EXCLUSIVE`	대기	통과	통과

참고로 이 잠금 모드들은 SQL 표준이 아니라 DB마다 다르다. SHARE UPDATE EXCLUSIVE는 PostgreSQL에만 있는 모드이고, MySQL의 LOCK TABLES는 크게 READ와 WRITE 두 가지라서 생성 요청끼리만 막는 중간 모드가 없다. 그래서 이 섹션의 내용은 PostgreSQL 기준이다.

PostgreSQL 문서, LOCK, MySQL 문서, LOCK TABLES

잠금 모드만 바꿔서 같은 실험을 다시 해 보았다.

A locked       | 08:31:05.488
B start        | 08:31:06.027
D update start | 08:31:06.027
D update done  | 08:31:06.029   ← 다른 쿠폰의 UPDATE는 기다리지 않는다
A committed    | 08:31:07.499
B locked       | 08:31:07.499   ← 생성 요청끼리는 여전히 줄을 선다
B next 00043

앞의 다이어그램과 같은 순서로 놓으면, 이번에는 D만 기다리지 않는다.

이번에는 번호도 겹치지 않았고, 다른 쿠폰의 UPDATE도 막히지 않았다. 하지만 이 방법에도 한계가 남는다.

생성 요청끼리는 A가 커밋할 때까지 기다린다. 번호 하나를 받는 순간만 막는 것이 아니라, 트랜잭션 전체가 끝날 때까지 막는다.
SHARE UPDATE EXCLUSIVE는 원래 VACUUM(지워진 행이 차지하던 공간을 정리하는 작업)이나 ANALYZE 같은 관리 작업이 잡는 잠금이다. 그래서 A가 잠금을 쥐고 있는 동안에는 그 테이블의 VACUUM도 기다린다. 실제로 실행해 보니 VACUUM이 A가 커밋할 때까지 멈춰 있었다.
이 잠금은 쿠폰을 만드는 모든 코드가 먼저 LOCK TABLE을 실행한다는 약속 위에서만 동작한다. 잠금 없이 INSERT 하는 코드가 하나라도 생기면, 그 요청은 줄을 서지 않고 그대로 끼어든다.

즉, 테이블 락은 모드를 잘 고르면 다른 쓰기를 막지 않고도 번호를 지킬 수 있다. 하지만 번호 하나를 위해 트랜잭션 전체를 줄 세우고, 그 약속을 모든 코드가 지켜야 한다. DB가 번호를 직접 나눠 주면서, 그 순간만 짧게 막는 장치가 필요하다.

정리

FOR UPDATE는 이미 있는 행을 트랜잭션이 끝날 때까지 잠그고, 일반 SELECT는 막지 않는다.
PostgreSQL은 결과 행을 잠그므로 집계에는 걸 수 없고, 최댓값 행을 잠가도 새로 들어올 행은 막지 못해 중복 키 에러가 났다.
MySQL은 읽은 인덱스 레코드와 그 뒤의 빈 공간(갭 락)을 잠그지만, 갭 락끼리 공존하기 때문에 데드락이 났다.
테이블 락은 번호가 겹치지 않게 해 준다. 다만 SHARE ROW EXCLUSIVE 모드는 같은 테이블의 다른 쓰기까지 막고, SHARE UPDATE EXCLUSIVE 모드는 다른 쓰기는 통과시키지만 생성 요청끼리 커밋까지 줄을 서고 VACUUM과도 부딪힌다.
그래서 PostgreSQL에서는 번호를 나눠 주는 순간만 짧게 막는 시퀀스를 쓴다. 시퀀스의 동작 방식은 이전 포스팅에서 다루었다.