MySQL Next-Key Lock과 Gap Lock 분석: 서로 다른 INSERT가 교착 상태에 빠지는 이유
같은 행을 수정하지 않으면 교착 상태도 발생하지 않을 것이라고 생각하기 쉽습니다. 하지만 InnoDB의 REPEATABLE READ에서는 아직 존재하지 않는 값이 들어갈 인덱스 구간도 잠금 대상이 됩니다.
이번 테스트에서는 기본 키가 10, 20, 30, 40인 작은 테이블을 사용했습니다. 두 연결에서 존재하지 않는 값을 조회한 다음, 서로 다른 키인 18과 19를 삽입하여 잠금 대기와 교착 상태를 확인했습니다.
핵심은 조회 결과의 행 수와 실제 잠금 범위가 같지 않다는 점입니다. 조회 결과가 0건이어도 Gap Lock이 남을 수 있으며, 서로 다른 값을 삽입하더라도 동일한 갭을 보호하는 잠금 때문에 순환 대기가 발생할 수 있습니다.
1. 테스트 환경과 확인할 문제입니다
테스트 환경은 로컬 MySQL 8.4.11입니다. 연결 A와 B에서 트랜잭션을 실행하고, 별도 연결 C에서 performance_schema.data_locks와 data_lock_waits를 조회했습니다.
캡처에서 연결 A의 번호는 66, 연결 B의 번호는 68입니다. 이 번호는 접속할 때마다 달라질 수 있습니다.
테스트 테이블의 구성은 다음과 같습니다.
CREATE TABLE gap_lock_demo (
id INT NOT NULL,
memo VARCHAR(50) NOT NULL,
PRIMARY KEY (id)
) ENGINE = InnoDB;
INSERT INTO gap_lock_demo (id, memo)
VALUES
(10, 'base-10'),
(20, 'base-20'),
(30, 'base-30'),
(40, 'base-40');두 작업 연결의 격리 수준을 REPEATABLE READ로 설정하고, 명시적으로 트랜잭션을 시작했습니다.
SET SESSION transaction_isolation = 'REPEATABLE-READ';
START TRANSACTION;이번 테스트는 일반 SELECT가 아닌 SELECT ... FOR UPDATE를 사용하는 잠금 읽기를 대상으로 합니다. REPEATABLE READ의 일반적인 일관 읽기는 MVCC를 이용하므로, 이 실험 결과를 모든 SELECT에 적용하면 안 됩니다.
2. 한 건을 조회했지만 잠금은 인덱스 구간까지 확장됩니다
먼저 연결 A에서 다음 쿼리를 실행했습니다.
SELECT *
FROM gap_lock_demo FORCE INDEX (PRIMARY)
WHERE id >= 15
AND id < 25
FOR UPDATE;조건에 일치하는 데이터는 id = 20 한 건입니다. 이 상태에서 트랜잭션을 종료하지 않고 연결 B에서 18을 삽입했습니다.
START TRANSACTION;
INSERT INTO gap_lock_demo (id, memo)
VALUES (18, 'next-key-test');연결 B의 INSERT는 즉시 완료되지 않고 대기했습니다.
캡처에 표시된 주요 잠금은 다음과 같습니다.
| 연결 | 대상 | 잠금 모드 | 상태 | 키 값 |
|---|---|---|---|---|
| 66 | PRIMARY 레코드 | X | GRANTED | 20 |
| 66 | PRIMARY 레코드 | X,GAP | GRANTED | 30 |
| 68 | PRIMARY 레코드 | X,GAP,INSERT_INTENTION | WAITING | 20 |
이 결과는 LOCK_TYPE = RECORD라는 표시만으로 잠금이 레코드 한 건에 한정된다고 해석하면 안 된다는 것을 보여 줍니다. LOCK_MODE를 함께 확인해야 합니다.
이번 캡처에서 키 20의 X는 레코드와 앞쪽 갭을 함께 보호하는 Next-Key Lock입니다. 초기 데이터에서 20의 직전 키는 10이므로, 해당 잠금의 구간은 **(10, 20]**입니다.
따라서 18은 기존 행과 다른 값이지만 잠금으로 보호되는 구간 안에 들어갑니다. 연결 B는 이 위치에 삽입하기 위한 Insert Intention Lock을 요청하다가 대기합니다.
키 30에 표시된 X,GAP도 주목할 부분입니다. 이 잠금은 30 자체가 아닌 (20, 30) 갭을 보호합니다. 실제 관찰된 잠금 범위는 SQL에 작성한 15 이상, 25 미만이라는 숫자 범위보다 넓습니다.
이는 인덱스의 기존 키와 검색 경계를 기준으로 잠금을 설정하기 때문입니다. 다만 모든 쿼리가 항상 같은 경계 잠금을 설정하는 것은 아니므로, 실제 접근 인덱스와 잠금 결과를 확인해야 합니다. (dev.mysql.com)
화면의 TABLE, IX는 하위 레코드에 배타적 잠금을 설정하려는 의도 잠금입니다. 테이블 전체를 독점하는 X 잠금과 구분해야 합니다.
3. 존재하지 않는 값을 조회해도 Gap Lock은 남습니다
앞선 트랜잭션을 정리한 뒤, 두 연결에서 동일한 쿼리를 실행했습니다.
START TRANSACTION;
SELECT *
FROM gap_lock_demo
WHERE id = 18
FOR UPDATE;테이블에는 18이 없으므로 두 연결 모두 조회 결과는 0건입니다. 두 번째 연결의 SELECT도 대기 없이 완료되었습니다.
그런데 잠금을 조회하면 두 연결 모두 잠금을 보유하고 있습니다.
| 연결 | 인덱스 | 잠금 모드 | 상태 | 키 값 |
|---|---|---|---|---|
| 66 | PRIMARY | X,GAP | GRANTED | 20 |
| 68 | PRIMARY | X,GAP | GRANTED | 20 |
조회 조건은 id = 18인데 잠금 정보에는 20이 표시됩니다. 이 값은 이번 잠금에서 갭의 오른쪽 경계를 나타냅니다. 보호하는 구간은 두 연결 모두 (10, 20)입니다.
여기서 중요한 특성은 Gap Lock끼리는 함께 존재할 수 있다는 점입니다. 두 연결에 X,GAP이 표시되어도 일반적인 동일 레코드의 배타적 잠금처럼 서로를 즉시 차단하지는 않습니다.
Gap Lock은 해당 구간에 다른 트랜잭션이 새 레코드를 삽입하는 것을 억제하는 역할을 합니다. 따라서 두 SELECT가 모두 완료된 사실과, 이후 INSERT가 가능한지는 별개의 문제입니다. (dev.mysql.com)
또한 “PK 동등 조건이면 Gap Lock이 없다”는 설명도 조건을 구분해야 합니다. 존재하는 유일 키를 정확히 찾은 경우와 존재하지 않는 키를 검색한 경우의 잠금은 같지 않습니다.
4. 첫 번째 INSERT에서 잠금 대기 관계를 확인합니다
두 연결이 Gap Lock을 보유한 상태에서 연결 A가 먼저 18을 삽입했습니다.
INSERT INTO gap_lock_demo (id, memo)
VALUES (18, 'insert-from-A');연결 A는 대기 상태에 들어갔습니다. 이때 data_lock_waits와 data_locks를 연결하여 대기 관계를 조회했습니다.
SELECT
rt.PROCESSLIST_ID AS waiting_connection,
bt.PROCESSLIST_ID AS blocking_connection,
r.INDEX_NAME AS index_name,
r.LOCK_MODE AS requested_lock,
r.LOCK_DATA AS requested_key,
b.LOCK_MODE AS blocking_lock,
b.LOCK_DATA AS blocking_key
FROM performance_schema.data_lock_waits AS w
JOIN performance_schema.data_locks AS r
ON r.ENGINE = w.ENGINE
AND r.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks AS b
ON b.ENGINE = w.ENGINE
AND b.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID
LEFT JOIN performance_schema.threads AS rt
ON rt.THREAD_ID = w.REQUESTING_THREAD_ID
LEFT JOIN performance_schema.threads AS bt
ON bt.THREAD_ID = w.BLOCKING_THREAD_ID
WHERE r.OBJECT_SCHEMA = 'nextkey_deadlock_lab'
AND r.OBJECT_NAME = 'gap_lock_demo';
실제 결과는 다음과 같습니다.
| 항목 | 관측 결과 |
|---|---|
| 대기 연결 | 66 |
| 차단 연결 | 68 |
| 요청 잠금 | X,GAP,INSERT_INTENTION |
| 차단 잠금 | X,GAP |
| 양쪽 잠금에 표시된 경계 키 | 20 |
이 시점에는 66번 연결이 68번 연결을 기다리는 단방향 대기가 확인됩니다. 대기 시간이 길다는 이유만으로 교착 상태라고 판단해서는 안 됩니다.
교착 상태가 되려면 상대 트랜잭션도 다시 이 트랜잭션의 자원을 기다리는 순환 관계가 형성되어야 합니다.
5. 서로 다른 키 18과 19를 삽입해도 교착 상태가 발생합니다
연결 A의 INSERT가 대기하는 동안 연결 B에서 다음 쿼리를 실행했습니다.
INSERT INTO gap_lock_demo (id, memo)
VALUES (19, 'insert-from-B');이 쿼리에서 실제로 다음 오류가 발생했습니다.
MySQL Error (1213):
Deadlock found when trying to get lock;
try restarting transaction
실행 순서와 앞서 확인한 잠금을 연결하면 다음과 같이 해석할 수 있습니다.
| 순서 | 연결 A, 66 | 연결 B, 68 |
|---|---|---|
| 1 | 없는 키 18을 조회하고 Gap Lock을 보유합니다. | |
| 2 | 같은 갭의 Gap Lock을 보유합니다. | |
| 3 | 18 삽입을 시도하고 B의 Gap Lock을 기다립니다. | |
| 4 | 19 삽입을 시도하면서 A의 Gap Lock과 충돌합니다. | |
| 5 | 순환 대기가 형성됩니다. | 이번 실행에서는 1213 오류가 발생했습니다. |
두 INSERT가 같은 PK를 사용해서 발생한 중복 키 충돌이 아닙니다. 18과 19가 들어갈 갭에 상대 트랜잭션의 잠금이 먼저 존재했다는 점이 원인입니다.
Insert Intention Lock끼리도 같은 갭에 있다는 이유만으로 항상 충돌하는 것은 아닙니다. 이번 실험에서는 앞선 잠금 읽기가 설정한 Gap Lock이 삽입을 차단했습니다.
1213 오류가 발생하면 InnoDB는 희생 트랜잭션 전체를 롤백합니다. 어느 트랜잭션이 선택되는지는 실행 조건에 따라 달라질 수 있으므로, 항상 두 번째 INSERT가 실패한다고 가정하면 안 됩니다.
이번 자료에는 상세 Deadlock 로그 대신 오류 화면을 기록했습니다. 운영 환경에서는 오류 직후 아래 명령으로 LATEST DETECTED DEADLOCK을 확보하면 보유 잠금과 요청 잠금을 더 구체적으로 대조할 수 있습니다.
SHOW ENGINE INNODB STATUS;6. READ COMMITTED 비교 결과는 어디까지 확인되었는지 구분합니다
회피 방법 중 하나는 해당 업무에 READ COMMITTED를 적용할 수 있는지 검토하는 것입니다.
SET SESSION transaction_isolation = 'READ-COMMITTED';이 설정은 진행 중인 트랜잭션을 정리한 뒤 적용해야 합니다. 일반적인 검색과 인덱스 스캔에서 Gap Lock 사용을 줄일 수 있지만, 외래 키 검사와 중복 키 검사 등에는 예외가 있습니다. (dev.mysql.com)
캡처에서는 다음 결과가 확인됩니다.
| 격리 수준 | id | memo |
|---|---|---|
| READ-COMMITTED | 18 | insert-from-A |
| READ-COMMITTED | 19 | rc-from-B |
다만 이 화면은 조회 시점의 격리 수준과 두 행의 존재를 증명하는 자료입니다. 18행의 메모가 앞선 실험의 insert-from-A로 남아 있으므로, 두 INSERT를 모두 READ COMMITTED에서 처음부터 재실행했다고 단정할 수는 없습니다.
격리 수준 변경 전후의 교착 상태 재현 여부를 엄밀하게 비교하려면 두 행을 제거하고, 두 연결의 새 트랜잭션에서 동일한 실행 순서를 반복해야 합니다.
또한 READ COMMITTED는 동시성에만 영향을 주는 설정이 아닙니다. 일반적인 일관 읽기에서 문장마다 새로운 스냅샷을 사용하므로, 같은 트랜잭션 안에서 재조회 결과가 달라질 수 있습니다. 업무가 요구하는 읽기 일관성을 확인한 뒤 적용해야 합니다.
7. 운영에서는 격리 수준 변경과 함께 트랜잭션 구조를 검토합니다
이번 실험에서 먼저 살펴볼 패턴은 “없는지 잠금 조회한 다음 삽입하는 구조”입니다.
SELECT *
FROM gap_lock_demo
WHERE id = 18
FOR UPDATE;조회 결과가 없더라도 갭을 보호하는 잠금이 생길 수 있습니다. 존재 여부 확인을 위해 이 패턴을 여러 연결이 동시에 사용하면, 이번 테스트와 같은 삽입 충돌을 만들 수 있습니다.
요구사항이 단순한 중복 방지라면, 유일성은 PK 또는 UNIQUE 제약으로 보장하고 바로 INSERT한 뒤 중복 키 오류를 처리하는 구조를 검토할 수 있습니다.
INSERT INTO gap_lock_demo (id, memo)
VALUES (18, 'new-order');다만 이 변경만으로 모든 교착 상태가 사라지지는 않습니다. 여러 행을 변경하거나 다른 테이블까지 함께 갱신한다면 전체 잠금 획득 순서를 확인해야 합니다. 중복 키 오류를 정상적인 업무 응답으로 처리할지도 별도로 정의해야 합니다.
트랜잭션이 잠금을 보유하는 시간도 줄여야 합니다. 잠금 조회 후 외부 API 호출이나 긴 애플리케이션 처리를 수행하면, 다른 트랜잭션이 같은 구간에 접근할 가능성이 커집니다.
기존 행을 여러 개 갱신하는 업무에서는 가능한 한 동일한 순서로 접근하는 것이 도움이 됩니다. 그러나 이번 실험처럼 두 트랜잭션이 같은 갭을 먼저 잠근 경우에는 삽입할 키의 순서를 정렬하는 것만으로 해결된다고 볼 수 없습니다.
8. 1213 오류는 실패한 INSERT 한 문장만 재시도하지 않습니다
교착 상태에 대한 애플리케이션 처리는 트랜잭션 단위로 설계해야 합니다. InnoDB가 희생 트랜잭션 전체를 롤백하므로, 앞서 수행했던 조회와 변경까지 다시 실행해야 할 수 있습니다. (dev.mysql.com)
재시도 처리에서는 다음을 확인합니다.
- 새로운 트랜잭션에서 업무 단위를 다시 시작합니다.
- 재시도 횟수를 제한하고 짧은 지연과 무작위 편차를 적용합니다.
- 결제 요청이나 메시지 발송처럼 DB 밖에서 발생하는 작업은 중복 실행을 방지합니다.
- 재시도 성공 여부와 별도로 Deadlock 발생 빈도를 기록합니다.
innodb_lock_wait_timeout을 늘리는 것은 순환 대기 자체를 제거하지 않습니다. 이번 실험에서도 중요한 것은 대기 시간을 얼마나 허용하는지가 아니라, 어떤 트랜잭션이 어떤 구간을 먼저 잠그고 이후 무엇을 요청했는지입니다.
이번 캡처로 확인한 진단 기준은 명확합니다. 조회 결과가 0건이어도 잠금은 남을 수 있으며, 서로 다른 INSERT도 같은 갭에서 충돌할 수 있습니다. 장애 분석에서는 SQL의 조건값뿐 아니라 LOCK_MODE, LOCK_DATA, 대기 연결과 차단 연결을 함께 기록해야 원인을 좁힐 수 있습니다.
댓글 0
첫 댓글을 남겨보세요.