갭 락과 넥스트 키 락

없는 행을 조회했는데 왜 INSERT가 막히나

트랜잭션 A가 SELECT ... FOR UPDATE로 행을 잠급니다. MySQL InnoDB는 행만 잠그는 것이 아니라, 조건에 따라 행과 행 사이의 빈 구간까지 잠가서 다른 트랜잭션이 그 사이에 새 행을 끼워 넣지 못하게 합니다.

실행으로 대조 · MySQL 8.4.11에서 열두 가지 조합의 실제 락과 대기를 대조

이 실습에 나오는 개념

레코드 락, 갭 락, 넥스트 키 락: MySQL InnoDB가 인덱스에 거는 잠금의 종류다. 레코드 락은 인덱스의 한 항목을 잠근다. 갭 락은 인덱스 항목 사이의 빈 구간을 잠가 그 사이에 새 행이 삽입되지 못하게 한다. 넥스트 키 락은 레코드 락과 그 앞 구간의 갭 락을 합친 것이다. 갭 락과 넥스트 키 락은 범위 조건으로 잠근 구간에 새 행이 끼어드는 것(팬텀)을 막기 위한 것이다.
공유 락과 배타 락: 공유 락(S)은 읽기를 위한 잠금으로 여러 트랜잭션이 동시에 가질 수 있다. 배타 락(X)은 쓰기를 위한 잠금으로 한 트랜잭션만 가질 수 있으며, 배타 락이 걸린 데이터에는 다른 트랜잭션이 공유 락도 배타 락도 걸 수 없다.
트랜잭션 격리 수준: 동시에 실행되는 트랜잭션들이 서로의 변경을 어디까지 볼 수 있게 할지 정하는 단계다. READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE의 네 단계가 있으며, 뒤로 갈수록 서로의 영향을 더 많이 차단하지만 동시에 처리할 수 있는 양은 줄어든다. MySQL(InnoDB)의 기본값은 REPEATABLE READ, PostgreSQL과 Oracle의 기본값은 READ COMMITTED다.
Phantom Read: 한 트랜잭션 안에서 같은 조건으로 두 번 조회했는데, 그 사이에 다른 트랜잭션이 행을 추가하거나 삭제해서 결과의 행 수가 달라지는 현상이다. 값이 바뀌는 것이 아니라 없던 행이 나타나거나 있던 행이 사라진다는 점에서 Non-repeatable Read와 구분된다.
비관적 락: 충돌이 일어날 것이라고 가정하고 데이터를 읽는 시점에 먼저 잠그는 방식이다. SELECT ... FOR UPDATE로 행을 잠그면 다른 트랜잭션은 그 행을 수정하려 할 때 잠금이 풀릴 때까지 기다린다. 충돌이 잦은 경우에 확실하지만 대기와 교착 상태의 가능성이 있다.
교착 상태(Deadlock): 둘 이상의 스레드(또는 트랜잭션)가 서로 상대가 가진 자원을 기다리느라 영원히 진행하지 못하는 상태다. A가 자원 1을 잡고 2를 기다리는데 B가 자원 2를 잡고 1을 기다리는 경우가 전형적이다. 자원을 항상 같은 순서로 잡도록 하면 예방할 수 있다.

이렇게 바꿔 보세요

처음 설정으로 — 범위 조회. 20과 30 사이에 끼워 넣으려는 INSERT가 기다립니다.
없는 행을 조회했을 때 — 잠글 행이 없는데도 그 자리의 빈 구간이 잠깁니다.
있는 행 하나를 정확히 조회 — 그 행만 잠깁니다. 옆 구간은 자유롭습니다.
READ COMMITTED로 낮추면 — 빈 구간을 잠그지 않습니다. INSERT가 바로 됩니다.
범위 밖에 넣으면 — 잠긴 구간이 아니므로 기다리지 않습니다.

처음 설정으로 실행하면

coupon 테이블에는 id가 10, 20, 30, 40인 행과 그보다 한참 큰 id의 행들이 있습니다. 인덱스에서 보면 행과 행 사이에 (10~20), (20~30), (30~40), (40 이후) 같은 빈 구간이 있습니다.
트랜잭션 A가 id 20부터 30까지를 FOR UPDATE로 조회합니다. 조건에 맞는 행 20과 30에 락을 겁니다. 그리고 그 사이의 빈 구간 (20~30)도 함께 잠급니다. 행에 건 락과 그 앞 구간에 건 락을 합쳐 넥스트 키 락이라고 부릅니다.
트랜잭션 B가 id = 25인 행을 넣으려 합니다. INSERT는 새 행이 들어갈 자리의 빈 구간이 잠겨 있는지 확인합니다.
id 25가 들어갈 자리는 (20~30) 구간이고, A가 이 구간을 잠그고 있습니다. B의 INSERT는 A가 커밋하거나 롤백할 때까지 기다립니다. 너무 오래 기다리면 락 대기 시간 초과 오류가 납니다.
A가 커밋하면 락이 풀리고, 기다리던 B의 INSERT가 그제야 실행됩니다.

실행 결과

REPEATABLE READ에서 InnoDB는 범위 안에 새 행이 끼어드는 것(Phantom)을 막으려고 빈 구간을 잠급니다. 그 대가로 조회에 걸리지 않는 값을 넣으려는 INSERT도 잠긴 빈 구간 안이면 막힐 수 있습니다.

코드

-- 테이블의 id(기본 키): 10, 20, 30, 40, 그리고 멀리 떨어진 행들
-- (조회 범위가 테이블의 일부라서 그 범위만 훑는 경우)

-- 트랜잭션 A
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM coupon WHERE id BETWEEN 20 AND 30 FOR UPDATE;

-- 트랜잭션 B (A가 아직 커밋하지 않은 상태)
INSERT INTO coupon (id) VALUES (25);

이 모형의 전제

MySQL InnoDB에서 기본 키(유니크 인덱스)로 조회하는 경우를 기준으로 했습니다. 유니크 인덱스로 존재하는 한 행을 찾으면 레코드 락만, 없는 행이나 범위를 찾으면 갭 락이 함께 걸린다는 점, READ COMMITTED에서는 조회에 갭 락을 쓰지 않는다는 점은 MySQL 공식 문서(InnoDB Locking)의 설명을 따랐습니다. 범위 조회에서 실제로 잠기는 구간의 경계는 인덱스 종류와 MySQL 버전에 따라 이보다 넓을 수 있습니다(범위 끝 다음 구간까지 잠기기도 합니다). 열두 가지 조합 모두를 실제 MySQL 8.4.11에서 세션 두 개로 실행해, A가 잡은 락(performance_schema.data_locks에 기록된 레코드 락, 갭 락, 넥스트 키 락)과 B의 INSERT가 기다리는지가 화면과 일치하는 것을 확인했습니다. 단, 이 결과는 범위 조회가 인덱스의 그 범위만 훑는 경우의 것입니다. InnoDB는 조건에 맞는 행이 아니라 조회하면서 훑은 인덱스 레코드를 잠급니다. 실제로 행이 10, 20, 30, 40 네 개뿐인 테이블로 시험했을 때는 MySQL이 인덱스 전체를 훑는 계획을 골라 모든 행과 모든 빈 구간이 잠겼고, 범위 밖인 id = 45의 INSERT도 기다렸습니다. 화면과 같은 결과는 멀리 떨어진 행 500개를 더 넣어 조회 범위가 테이블의 일부가 되게 한 뒤에 나왔습니다.

앞 실습: 격리 수준 · 다음 실습: 재고 차감과 락

이 실습에 나오는 개념

이 실습에서 보는 것

설정

실행 전에 결과 예측하기

코드

그림

실행 비교