Record lock, gap lock, next-key lock의 차이와 잠금 범위

InnoDB 잠금 장애를 분석할 때 “몇 행을 조회했는가”만 보면 원인을 놓치기 쉽다. SELECT ... FOR UPDATE가 0행을 반환했는데도 다른 세션의 INSERT가 기다리거나, 한 행을 변경했을 뿐인데 인접한 key 구간까지 쓰기가 막히는 일이 있기 때문이다. InnoDB는 테이블의 논리적 행 집합이 아니라 선택된 인덱스의 검색 구간에 잠금을 설정한다.

이 동작을 설명하는 핵심 개념이 record lock, gap lock, next-key lock이다. 세 잠금은 독립적인 기능 목록이라기보다 B+Tree에서 기존 key와 key 사이의 빈 공간을 조합해 보호하는 방식이다. 특히 기본 격리 수준인 REPEATABLE READ에서는 locking read와 DML이 검색 범위의 phantom을 막기 위해 next-key locking을 사용할 수 있다.

이 글은 MySQL 8.0 이상을 기준으로 세 잠금의 경계, unique exact match의 예외, secondary index와 clustered index의 관계, READ COMMITTED에서의 변화, insert intention과 deadlock, Performance Schema 진단법을 운영 관점에서 정리한다.

1. 먼저 기억할 한 문장

세 잠금의 차이는 다음처럼 요약할 수 있다.

구분	보호 대상	개념적 구간	주된 효과
Record lock	실제 index record	key 자체	기존 record의 변경·삭제와 충돌한다.
Gap lock	index record 사이의 빈 공간	`(이전 key, 다음 key)`	그 공간에 새 index entry가 삽입되는 것을 억제한다.
Next-key lock	record와 그 앞 gap	`(이전 key, 현재 key]`	기존 record와 앞쪽 삽입 구간을 함께 보호한다.

여기서 “행”이 아니라 index record라고 표현한 점이 중요하다. Secondary index로 검색한 행을 변경 목적으로 잠그면 secondary index entry뿐 아니라 실제 행이 저장된 clustered index record에도 잠금이 나타날 수 있다. 반대로 적절한 index 없이 넓게 scan하면 반환되지 않은 후보와 검색 경계까지 잠금 영향이 확대될 수 있다.

flowchart LR
    M[minus infinity] --- G1((gap)) --- K10[10] --- G2((gap)) --- K20[20] --- G3((gap)) --- K30[30] --- G4((gap)) --- P[plus infinity]

    R[record lock on 20] -. key만 보호 .-> K20
    G[gap lock before 20] -. 10과 20 사이 삽입 억제 .-> G2
    N[next-key lock on 20] -. 구간 10, 20 보호 .-> G2
    N -. record 포함 .-> K20

그림의 20에 대한 next-key lock은 개념적으로 (10, 20]을 보호한다. 가장 작은 record 앞에는 -∞ 방향의 gap이 있고, 마지막 record 뒤에는 실제 사용자 record가 아닌 supremum pseudo-record를 이용해 (마지막 key, +∞) 구간을 표현한다.

2. InnoDB는 왜 빈 공간까지 잠그는가

REPEATABLE READ 트랜잭션에서 다음 locking read를 실행했다고 가정하자.

SELECT order_id, amount
FROM orders
WHERE amount BETWEEN 100 AND 200
FOR UPDATE;

기존 amount 값이 100, 150, 200인 record만 잠그고 빈 공간은 열어 두면 다른 트랜잭션이 amount = 175인 행을 삽입할 수 있다. 같은 트랜잭션이 같은 범위를 current read로 다시 읽었을 때 새로운 행이 나타나는 phantom이 발생한다.

InnoDB는 검색에 사용한 index의 record와 gap을 next-key lock으로 보호해 이 삽입을 직렬화한다. 중요한 목적은 “모든 읽기를 느리게 만드는 것”이 아니라 다음 두 성질을 함께 제공하는 데 있다.

검색 당시 존재한 대상 record를 다른 트랜잭션이 충돌하는 방식으로 바꾸지 못하게 한다.
검색 범위 안에 새로운 index entry가 삽입되어 결과 집합이 바뀌는 것을 막는다.

MVCC의 Read View만으로도 일반 consistent read는 반복 가능한 snapshot을 볼 수 있다. 하지만 UPDATE, DELETE, SELECT ... FOR UPDATE/SHARE 같은 current read는 최신 상태에서 조건을 판정하고 실제 쓰기를 직렬화해야 한다. 이때 검색 범위의 안정성을 만드는 수단이 record·gap·next-key locking이다.

3. Record lock: 존재하는 index record를 보호한다

Record lock은 특정 index record 자체에 설정된다. Primary key 또는 unique index의 모든 컬럼을 상수로 지정해 존재하는 단일 record를 정확히 찾는 검색은 대표적인 record-only locking 경로다.

START TRANSACTION;
SELECT account_id, balance
FROM account
WHERE account_id = 42
FOR UPDATE;
-- 같은 트랜잭션 안에서 필요한 변경 수행
COMMIT;

Primary key 42가 존재하고 Optimizer가 PRIMARY를 exact match로 사용했다면, Performance Schema에서 X,REC_NOT_GAP 형태가 관찰될 수 있다. REC_NOT_GAP은 해당 record를 잠그되 그 앞 gap은 포함하지 않는다는 뜻이다.

그러나 다음 조건을 함께 확인해야 한다.

조건이 unique index의 모든 key part를 정확히 지정했는가?
실제 실행 계획이 그 unique index를 사용했는가?
조건에 range, prefix, 함수, 묵시적 형 변환이 섞이지 않았는가?
검색한 record가 실제로 존재하는가?
Secondary unique index가 nullable이라면 NULL의 uniqueness 의미가 예상과 같은가?

예를 들어 unique composite index (tenant_id, external_id)에서 tenant_id만 조건으로 사용하면 unique exact match가 아니다. 결과가 한 건이어도 검색은 범위가 되며 next-key 또는 gap 성격의 잠금이 필요할 수 있다.

또한 “record lock”은 SQL의 한 행에 잠금 객체가 정확히 하나라는 뜻이 아니다. Secondary index entry를 통해 행을 찾아 변경하면 secondary entry와 clustered primary record 양쪽에 관련 잠금이 나타날 수 있다.

4. Gap lock: 존재하지 않는 key가 들어올 공간을 보호한다

Gap lock은 두 index record 사이, 첫 record 앞, 마지막 record 뒤의 빈 구간에 설정된다. Gap 안에 실제 행이 없어도 잠금은 의미가 있다. 목적은 기존 record 변경을 막는 것이 아니라 그 gap에 새 index entry가 삽입되는 것을 억제하는 것이다.

예를 들어 index key가 10, 20, 30이고 15를 exact condition으로 찾았지만 행이 없다면, InnoDB는 10과 20 사이를 탐색한 뒤 그 gap을 보호할 수 있다. 이때 결과가 Empty set이어도 다른 트랜잭션의 15 삽입은 기다릴 수 있다.

Gap lock에는 일반적인 row lock과 다른 성질이 있다.

Pure gap lock은 기존 record 자체를 보호하지 않는다.
Gap의 shared/exclusive 표시는 일반 record의 S/X 호환성처럼 해석하면 안 된다.
서로 다른 트랜잭션의 gap lock은 같은 gap에서 공존할 수 있다.
실제 충돌의 중심은 그 gap에 들어오려는 insert intention이다.
Record가 purge될 때 여러 트랜잭션의 gap 보호 정보를 합칠 수 있어야 하므로 이런 호환성이 필요하다.

따라서 LOCK_MODE = X,GAP을 보고 “다른 모든 X gap lock과 충돌한다”고 단정하면 안 된다. Gap lock은 inhibitive lock, 즉 삽입을 억제하는 잠금으로 이해하는 편이 정확하다.

5. Next-key lock: 앞 gap과 현재 record를 하나의 구간으로 보호한다

Next-key lock은 record lock과 그 record 바로 앞 gap lock의 결합이다. 정렬된 index key가 10, 20, 30이라면 개념적인 next-key 구간은 다음과 같다.

(-∞, 10]
(10, 20]
(20, 30]
(30, +∞) — supremum을 이용해 표현

범위 검색 10 <= key < 30이 어떤 경계 record까지 방문하고 잠그는지는 실제 access path, 격리 수준, 조건 평가와 MySQL minor version에 따라 세부 표현이 달라질 수 있다. 그래서 SQL 문장의 수학적 범위만 보고 잠금 목록을 고정적으로 예측하지 말고, EXPLAIN의 선택 index와 performance_schema.data_locks를 함께 확인해야 한다.

다음 상황에서 next-key locking이 흔하다.

Non-unique index의 equality search
<, <=, >, >=, BETWEEN 범위 검색
Unique composite index의 일부 key part만 사용한 검색
ORDER BY ... LIMIT로 후보를 선점하지만 앞선 후보들을 scan하는 검색
적절한 index가 없어 광범위하게 scan하는 locking read 또는 DML

Next-key lock은 phantom 방지에 유용하지만, 검색 범위가 넓으면 삽입 동시성을 크게 줄인다. 따라서 잠금 문제를 timeout 설정만으로 다루지 말고 index와 predicate를 먼저 점검해야 한다.

6. 실행 재현: exact record, range, 빈 gap 비교

다음 예제는 동일한 InnoDB 테이블에서 세 검색 형태의 lock을 관찰한다. 첫 번째는 primary key exact match, 두 번째는 non-unique secondary index range, 세 번째는 존재하지 않는 secondary key 검색이다. 예제는 MySQL 8.0 전용 검증 인스턴스에서 실행하며, LOCK_DATA와 행 순서는 버전과 내부 표현에 따라 달라질 수 있다.

DROP TABLE IF EXISTS lock_shape_demo;
CREATE TABLE lock_shape_demo (
    item_id INT PRIMARY KEY,
    score INT NOT NULL,
    payload VARCHAR(40) NOT NULL,
    INDEX ix_score (score, item_id)
) ENGINE = InnoDB;
INSERT INTO lock_shape_demo VALUES
    (1, 10, 'ten'),
    (2, 20, 'twenty'),
    (3, 30, 'thirty');

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT item_id, score
FROM lock_shape_demo
WHERE item_id = 1
FOR UPDATE;
SELECT INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
FROM performance_schema.data_locks
WHERE OBJECT_SCHEMA = DATABASE()
  AND OBJECT_NAME = 'lock_shape_demo'
  AND LOCK_TYPE = 'RECORD'
  AND THREAD_ID = (
      SELECT THREAD_ID
      FROM performance_schema.threads
      WHERE PROCESSLIST_ID = CONNECTION_ID()
  )
ORDER BY INDEX_NAME, LOCK_DATA;
ROLLBACK;

START TRANSACTION;
SELECT item_id, score
FROM lock_shape_demo FORCE INDEX (ix_score)
WHERE score >= 10 AND score < 30
ORDER BY score, item_id
FOR UPDATE;
SELECT INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
FROM performance_schema.data_locks
WHERE OBJECT_SCHEMA = DATABASE()
  AND OBJECT_NAME = 'lock_shape_demo'
  AND LOCK_TYPE = 'RECORD'
  AND THREAD_ID = (
      SELECT THREAD_ID
      FROM performance_schema.threads
      WHERE PROCESSLIST_ID = CONNECTION_ID()
  )
ORDER BY INDEX_NAME, LOCK_DATA;
ROLLBACK;

START TRANSACTION;
SELECT item_id, score
FROM lock_shape_demo FORCE INDEX (ix_score)
WHERE score = 15
FOR UPDATE;
SELECT INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
FROM performance_schema.data_locks
WHERE OBJECT_SCHEMA = DATABASE()
  AND OBJECT_NAME = 'lock_shape_demo'
  AND LOCK_TYPE = 'RECORD'
  AND THREAD_ID = (
      SELECT THREAD_ID
      FROM performance_schema.threads
      WHERE PROCESSLIST_ID = CONNECTION_ID()
  )
ORDER BY INDEX_NAME, LOCK_DATA;
ROLLBACK;

준비·정리 문장과 반복되는 transaction 제어 출력은 줄이고, 세 검색의 결과와 잠금 차이를 보여 주는 핵심 부분만 발췌했다.

실행 결과(MySQL 8.0.x):

mysql> CREATE TABLE lock_shape_demo (... INDEX ix_score (score, item_id)) ENGINE = InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO lock_shape_demo VALUES
    -> (1, 10, 'ten'), (2, 20, 'twenty'), (3, 30, 'thirty');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT item_id, score
    -> FROM lock_shape_demo WHERE item_id = 1 FOR UPDATE;
+---------+-------+
| item_id | score |
+---------+-------+
|       1 |    10 |
+---------+-------+
1 row in set (0.00 sec)

mysql> SELECT INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
    -> FROM performance_schema.data_locks ...;
+------------+-----------+---------------+-------------+-----------+
| INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+------------+-----------+---------------+-------------+-----------+
| PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 1         |
+------------+-----------+---------------+-------------+-----------+
1 row in set (0.00 sec)

mysql> SELECT item_id, score
    -> FROM lock_shape_demo FORCE INDEX (ix_score)
    -> WHERE score >= 10 AND score < 30
    -> ORDER BY score, item_id FOR UPDATE;
+---------+-------+
| item_id | score |
+---------+-------+
|       1 |    10 |
|       2 |    20 |
+---------+-------+
2 rows in set (0.00 sec)

mysql> SELECT INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
    -> FROM performance_schema.data_locks ...;
+------------+-----------+---------------+-------------+-----------+
| INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+------------+-----------+---------------+-------------+-----------+
| ix_score   | RECORD    | X             | GRANTED     | 10, 1     |
| ix_score   | RECORD    | X             | GRANTED     | 20, 2     |
| ix_score   | RECORD    | X             | GRANTED     | 30, 3     |
| PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 1         |
| PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 2         |
| PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 3         |
+------------+-----------+---------------+-------------+-----------+
6 rows in set (0.00 sec)

mysql> SELECT item_id, score
    -> FROM lock_shape_demo FORCE INDEX (ix_score)
    -> WHERE score = 15 FOR UPDATE;
Empty set (0.00 sec)

mysql> SELECT INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
    -> FROM performance_schema.data_locks ...;
+------------+-----------+-----------+-------------+-----------+
| INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+------------+-----------+-----------+-------------+-----------+
| ix_score   | RECORD    | X,GAP     | GRANTED     | 20, 2     |
+------------+-----------+-----------+-------------+-----------+
1 row in set (0.00 sec)

이 예제에서 해석할 신호는 다음과 같다.

Primary key exact match는 PRIMARY record의 X,REC_NOT_GAP가 중심이다.
Secondary range는 ix_score의 대상 entry와 범위 끝을 보호하는 gap 성격, 그리고 대응하는 PRIMARY record 잠금을 함께 만들 수 있다.
score = 15가 0행이어도 다음 key인 20 앞 gap을 나타내는 X,GAP가 관찰될 수 있다.
FORCE INDEX는 잠금 모양을 비교하기 위한 교육용 통제다. 운영 SQL에는 실제 통계와 비용을 무시한 강제 hint를 무조건 적용하지 않는다.

data_locks는 현재 존재하는 lock의 관찰 결과이지 SQL 의미의 영구 계약이 아니다. LOCK_DATA가 page에서 memory로 올라오지 않은 record에 대해 다르게 보일 수 있고, 선택 plan이나 MySQL minor version이 달라지면 표시 행도 달라질 수 있다. 안정적인 해석 기준은 선택 index, record/gap 성격, granted/waiting 관계다.

7. 실행 재현: range lock이 gap의 INSERT를 기다리게 한다

Lock 목록만 보는 것보다 실제 대기를 연결하면 gap 보호의 의미가 분명해진다. 다음 예제는 Event Scheduler를 별도 서버 세션으로 사용한다. 메인 세션이 score BETWEEN 10 AND 20 범위를 FOR UPDATE로 잠근 뒤, 이벤트 세션이 그 사이인 score = 15를 삽입한다.

이 예제는 전용 임시 검증 인스턴스용이다. 운영 서버에서 재현 목적으로 event_scheduler를 켜거나 lock wait를 의도적으로 만들지 않는다.

DROP EVENT IF EXISTS gap_insert_attempt;
DELETE FROM lock_shape_demo WHERE item_id = 4;
SET GLOBAL event_scheduler = ON;
CREATE EVENT gap_insert_attempt
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 SECOND
    ON COMPLETION PRESERVE
    DO INSERT INTO lock_shape_demo (item_id, score, payload)
       VALUES (4, 15, 'inserted-by-event');

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT item_id, score
FROM lock_shape_demo FORCE INDEX (ix_score)
WHERE score BETWEEN 10 AND 20
ORDER BY score, item_id
FOR UPDATE;
DO SLEEP(4);
SELECT rl.INDEX_NAME,
       rl.LOCK_MODE AS waiting_mode,
       rl.LOCK_STATUS AS waiting_status,
       bl.LOCK_MODE AS blocking_mode,
       bl.LOCK_STATUS AS blocking_status,
       rl.LOCK_DATA
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks rl
  ON rl.ENGINE = w.ENGINE
 AND rl.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks bl
  ON bl.ENGINE = w.ENGINE
 AND bl.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID
WHERE rl.OBJECT_SCHEMA = DATABASE()
  AND rl.OBJECT_NAME = 'lock_shape_demo'
ORDER BY rl.INDEX_NAME, rl.LOCK_DATA;
SELECT COUNT(*) AS inserted_rows_before_commit
FROM lock_shape_demo
WHERE item_id = 4;
COMMIT;
DO SLEEP(2);
SELECT item_id, score, payload
FROM lock_shape_demo
WHERE item_id = 4;
DROP EVENT IF EXISTS gap_insert_attempt;
DROP TABLE lock_shape_demo;

이 결과는 Event 생성과 대기용 DO SLEEP 등 준비 출력은 줄이고, gap 삽입 대기와 잠금 해제 전후 상태를 보여 주는 부분을 발췌한 것이다.

실행 결과(MySQL 8.0.x):

mysql> CREATE EVENT gap_insert_attempt
    -> ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 SECOND
    -> ON COMPLETION PRESERVE
    -> DO INSERT INTO lock_shape_demo (item_id, score, payload)
    ->    VALUES (4, 15, 'inserted-by-event');
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT item_id, score
    -> FROM lock_shape_demo FORCE INDEX (ix_score)
    -> WHERE score BETWEEN 10 AND 20
    -> ORDER BY score, item_id FOR UPDATE;
+---------+-------+
| item_id | score |
+---------+-------+
|       1 |    10 |
|       2 |    20 |
+---------+-------+
2 rows in set (0.00 sec)

mysql> SELECT rl.INDEX_NAME,
    ->        rl.LOCK_MODE AS waiting_mode,
    ->        rl.LOCK_STATUS AS waiting_status,
    ->        bl.LOCK_MODE AS blocking_mode,
    ->        bl.LOCK_STATUS AS blocking_status,
    ->        rl.LOCK_DATA
    -> FROM performance_schema.data_lock_waits w ...;
+------------+------------------------+----------------+---------------+-----------------+-----------+
| INDEX_NAME | waiting_mode           | waiting_status | blocking_mode | blocking_status | LOCK_DATA |
+------------+------------------------+----------------+---------------+-----------------+-----------+
| ix_score   | X,GAP,INSERT_INTENTION | WAITING        | X             | GRANTED         | 20, 2     |
+------------+------------------------+----------------+---------------+-----------------+-----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) AS inserted_rows_before_commit
    -> FROM lock_shape_demo WHERE item_id = 4;
+-----------------------------+
| inserted_rows_before_commit |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT item_id, score, payload
    -> FROM lock_shape_demo WHERE item_id = 4;
+---------+-------+-------------------+
| item_id | score | payload           |
+---------+-------+-------------------+
|       4 |    15 | inserted-by-event |
+---------+-------+-------------------+
1 row in set (0.00 sec)

mysql> DROP TABLE lock_shape_demo;
Query OK, 0 rows affected (0.01 sec)

삽입을 요청한 이벤트 세션의 waiting lock에는 INSERT_INTENTION과 GAP 성격이 나타날 수 있다. 메인 세션이 가진 next-key/gap 보호와 충돌하기 때문에 inserted_rows_before_commit은 0이다. 메인 세션이 COMMIT하여 잠금을 해제한 뒤 이벤트의 statement가 진행되고, 최종 조회에서 item_id = 4, score = 15가 확인된다.

Insert intention lock은 여러 삽입 세션이 같은 넓은 gap 안에서 서로 다른 위치에 들어갈 수 있도록 하는 특수한 gap lock이다. 삽입 위치가 충돌하지 않으면 서로를 불필요하게 직렬화하지 않지만, 기존 gap/next-key lock이 해당 위치를 보호하고 있으면 기다린다.

8. Secondary index 잠금과 clustered index 잠금을 함께 본다

InnoDB 테이블의 실제 행은 primary key B+Tree의 leaf record에 저장된다. Secondary index leaf에는 secondary key와 해당 행의 primary key가 들어 있다. 그러므로 secondary index를 통해 후보를 찾고 행을 변경 목적으로 보호하는 흐름은 다음처럼 진행될 수 있다.

sequenceDiagram
    participant Q as SQL executor
    participant S as Secondary B+Tree
    participant P as Clustered PRIMARY B+Tree
    participant L as Lock subsystem

    Q->>S: score 범위 탐색
    S->>L: secondary record와 gap 보호
    S-->>Q: primary key 반환
    Q->>P: clustered record 조회
    P->>L: primary record 보호
    L-->>Q: granted 또는 wait

이 구조 때문에 SELECT 결과 두 행을 잠갔더라도 data_locks에는 secondary record, 범위 경계 gap, primary record가 합쳐져 더 많은 행이 표시될 수 있다. 반대로 lock table의 행 수를 애플리케이션 row 수로 환산해서도 안 된다.

특히 다음 설계가 잠금 범위를 넓힌다.

조건에 맞는 index가 없어 clustered index 전체를 scan한다.
Low-cardinality non-unique index로 넓은 값 구간을 잠근다.
Composite index의 선두 컬럼을 빠뜨려 효율적인 range를 만들지 못한다.
함수나 형 변환으로 predicate가 sargable하지 않다.
ORDER BY ... LIMIT에 맞는 index가 없어 많은 후보를 읽고 정렬한다.
한 transaction에서 지나치게 큰 batch를 갱신한다.

잠금 최적화는 “lock 설정을 끄는 것”이 아니라 업무 정합성을 유지하면서 탐색해야 하는 index 구간과 transaction 수명을 줄이는 작업이다.

9. Unique exact match가 gap을 생략하는 조건

InnoDB는 unique index로 유일한 record를 정확히 찾을 수 있으면 일반적으로 gap 보호가 필요하지 않다. 같은 unique key를 가진 새 행은 어차피 uniqueness 제약으로 들어올 수 없고, 기존 record 자체를 잠그면 충돌을 직렬화할 수 있기 때문이다.

하지만 다음 예시는 exact unique search가 아니다.

-- UNIQUE KEY uq_tenant_external (tenant_id, external_id)가 있다고 가정

-- 선두 key part만 지정했으므로 범위 검색
SELECT *
FROM object_registry
WHERE tenant_id = ?
FOR UPDATE;

-- prefix/range이므로 exact match가 아님
SELECT *
FROM object_registry
WHERE external_id LIKE 'ORD-%'
FOR UPDATE;

-- 조건은 한 행처럼 보여도 실제 선택 index를 확인해야 함
SELECT *
FROM object_registry
WHERE CAST(external_id AS CHAR) = ?
FOR UPDATE;

존재하지 않는 unique key를 검색하는 경우도 주의해야 한다. 결과 record가 없으므로 “잠글 record”만으로 새 삽입을 막을 수 없다. 업무가 그 key의 부재를 트랜잭션 동안 보호해야 한다면 gap 잠금이나 unique-check 과정의 잠금이 관여할 수 있다. 실제 동작은 index 정의, isolation level과 statement 종류로 검증해야 한다.

10. `READ COMMITTED`에서는 무엇이 달라지는가

READ COMMITTED에서는 일반 검색과 index scan의 gap locking이 크게 줄어든다. Nonmatching record에 대한 잠금도 조건 평가 후 더 일찍 해제될 수 있어 쓰기 동시성이 개선될 수 있다. 대신 같은 트랜잭션의 연속 current read 사이에 다른 트랜잭션이 새 범위 행을 커밋하면 결과 집합이 달라질 수 있다.

그렇다고 READ COMMITTED에서 gap lock이 완전히 사라지는 것은 아니다.

Foreign key 제약 검사
Duplicate-key 검사
내부 정합성 유지에 필요한 일부 경로

이런 상황에서는 gap 성격의 잠금이 계속 사용될 수 있다. 따라서 격리 수준 변경은 단순한 성능 옵션이 아니다. 다음 항목을 함께 검증해야 한다.

애플리케이션이 같은 transaction 안에서 범위의 부재 또는 불변성을 전제로 하는가?
Statement가 아닌 row 기반 replication과의 운영 조건은 적절한가?
Lock wait는 줄지만 업무 race가 늘지 않는가?
모든 connection pool session이 의도한 isolation level을 실제로 사용하는가?
Retry와 unique constraint가 동시성 충돌을 안전하게 수습하는가?

범위의 논리적 단일성을 DB 잠금으로 보장해야 한다면 REPEATABLE READ의 locking read, 명시적인 unique constraint, 업무 key를 대표하는 별도 row 잠금 등 여러 설계를 비교해야 한다.

11. Insert intention과 gap deadlock

Insert intention은 삽입하려는 정확한 위치를 알리는 잠금이다. 같은 gap (10, 20)에 한 세션은 12, 다른 세션은 18을 삽입하려 한다면 위치가 다르므로 가능한 한 동시에 진행할 수 있게 한다. 하지만 다른 transaction이 gap을 보호 중이면 둘 다 기다릴 수 있다.

Gap 관련 deadlock은 다음 패턴에서 자주 복잡해진다.

여러 transaction이 서로 다른 range를 먼저 잠근 뒤 상대 range에 삽입한다.
Unique key 검사와 secondary index 갱신 순서가 교차한다.
Foreign key 부모/자식 검사와 다른 DML의 접근 순서가 반대다.
Batch가 서로 다른 정렬 순서로 같은 key 공간을 처리한다.
SELECT ... FOR UPDATE로 “없는 key를 예약”한 뒤 여러 index에 삽입한다.

Deadlock이 발생하면 잠금 하나만 재시도하지 않는다. InnoDB가 victim transaction을 rollback했으므로 업무 transaction 전체를 처음부터 재실행해야 한다. Retry에는 횟수 제한, exponential backoff와 jitter, idempotency가 필요하다.

innodb_lock_wait_timeout을 늘리는 것은 deadlock cycle을 해결하지 않는다. Deadlock은 detector가 보통 즉시 victim을 선택하며, timeout 증가는 cycle이 아닌 장기 blocker에서 connection과 worker가 더 오래 점유되게 할 수 있다.

12. 운영 진단: 결과 행보다 기다림 관계를 연결한다

MySQL 8.0 이상에서는 performance_schema.data_locks와 data_lock_waits를 사용한다. 제거된 구식 잠금 테이블 대신 다음 관찰점을 연결한다.

요청한 lock: REQUESTING_ENGINE_LOCK_ID
차단한 lock: BLOCKING_ENGINE_LOCK_ID
대상 schema/table/index: OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME
잠금 성격: LOCK_TYPE, LOCK_MODE, LOCK_STATUS
index record 단서: LOCK_DATA
transaction 수명과 변경량: information_schema.INNODB_TRX
session과 현재 statement: performance_schema.threads, events_statements_current

운영 진단 쿼리의 기본 형태는 다음과 같다. 대기가 없는 정상 인스턴스에서는 Empty set이 올바른 결과다.

SELECT r.trx_id AS waiting_trx_id,
       rt.PROCESSLIST_ID AS waiting_thread,
       bs.SQL_TEXT AS waiting_query,
       b.trx_id AS blocking_trx_id,
       bt.PROCESSLIST_ID AS blocking_thread,
       es.SQL_TEXT AS blocking_query,
       rl.OBJECT_SCHEMA,
       rl.OBJECT_NAME,
       rl.INDEX_NAME,
       rl.LOCK_MODE AS waiting_lock_mode,
       bl.LOCK_MODE AS blocking_lock_mode,
       rl.LOCK_DATA
FROM performance_schema.data_lock_waits w
JOIN information_schema.INNODB_TRX r
  ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
JOIN information_schema.INNODB_TRX b
  ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID
JOIN performance_schema.data_locks rl
  ON rl.ENGINE = w.ENGINE
 AND rl.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks bl
  ON bl.ENGINE = w.ENGINE
 AND bl.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID
LEFT JOIN performance_schema.threads rt
  ON rt.THREAD_ID = w.REQUESTING_THREAD_ID
LEFT JOIN performance_schema.threads bt
  ON bt.THREAD_ID = w.BLOCKING_THREAD_ID
LEFT JOIN performance_schema.events_statements_current bs
  ON bs.THREAD_ID = w.REQUESTING_THREAD_ID
LEFT JOIN performance_schema.events_statements_current es
  ON es.THREAD_ID = w.BLOCKING_THREAD_ID
ORDER BY r.trx_started;

실행 결과(MySQL 8.0.x):

mysql> SELECT r.trx_id AS waiting_trx_id,
    ->        rt.PROCESSLIST_ID AS waiting_thread,
    ->        bs.SQL_TEXT AS waiting_query,
    ->        b.trx_id AS blocking_trx_id,
    ->        bt.PROCESSLIST_ID AS blocking_thread,
    ->        es.SQL_TEXT AS blocking_query,
    ->        rl.OBJECT_SCHEMA,
    ->        rl.OBJECT_NAME,
    ->        rl.INDEX_NAME,
    ->        rl.LOCK_MODE AS waiting_lock_mode,
    ->        bl.LOCK_MODE AS blocking_lock_mode,
    ->        rl.LOCK_DATA
    -> FROM performance_schema.data_lock_waits w
    -> JOIN information_schema.INNODB_TRX r
    ->   ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
    -> JOIN information_schema.INNODB_TRX b
    ->   ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID
    -> JOIN performance_schema.data_locks rl
    ->   ON rl.ENGINE = w.ENGINE
    ->  AND rl.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
    -> JOIN performance_schema.data_locks bl
    ->   ON bl.ENGINE = w.ENGINE
    ->  AND bl.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID
    -> LEFT JOIN performance_schema.threads rt
    ->   ON rt.THREAD_ID = w.REQUESTING_THREAD_ID
    -> LEFT JOIN performance_schema.threads bt
    ->   ON bt.THREAD_ID = w.BLOCKING_THREAD_ID
    -> LEFT JOIN performance_schema.events_statements_current bs
    ->   ON bs.THREAD_ID = w.REQUESTING_THREAD_ID
    -> LEFT JOIN performance_schema.events_statements_current es
    ->   ON es.THREAD_ID = w.BLOCKING_THREAD_ID
    -> ORDER BY r.trx_started;

Empty set (0.00 sec)

events_statements_current.SQL_TEXT는 thread가 idle이면 NULL일 수 있다. 차단 세션에 현재 SQL이 없다고 해서 blocker가 아닌 것은 아니다. 이전 statement 뒤에 COMMIT하지 않은 채 connection pool로 반환된 session일 수 있으므로 trx_started, trx_rows_modified, application trace와 connection 상태를 함께 본다.

12.1 진단 순서

data_lock_waits에서 waiting과 blocking lock을 연결한다.
OBJECT_NAME, INDEX_NAME, LOCK_DATA로 충돌한 index 구간을 찾는다.
LOCK_MODE의 REC_NOT_GAP, GAP, INSERT_INTENTION을 구분한다.
Waiting SQL과 blocker의 transaction 시작 시각·최근 SQL·변경 행 수를 연결한다.
EXPLAIN에서 실제 선택 index와 scan 범위를 확인한다.
결과 행 수보다 rows examined와 잠금 보유 시간이 과도하지 않은지 본다.
Blocker 종료 전 rollback 비용과 업무 영향을 평가한다.
해소 뒤 transaction duration, lock wait time, deadlock과 hot key 분포를 추적한다.

LOCK_DATA는 편리한 단서지만 애플리케이션 key를 완전하게 복원하는 감사 로그가 아니다. 내부 표현과 page 상태에 따라 값이 제한될 수 있으므로 SQL predicate, index 정의와 함께 해석한다.

13. 흔한 오해와 실패 패턴

13.1 “0행을 반환했으니 잠금도 없다”

REPEATABLE READ의 locking range search는 일치하는 record가 없어도 탐색한 gap을 잠글 수 있다. 존재하지 않는 key를 예약하는 패턴은 특히 hot gap을 만들기 쉽다.

13.2 “한 행을 갱신했으니 record lock 하나뿐이다”

Secondary index 탐색과 clustered record 변경, 범위 경계 보호가 함께 나타날 수 있다. SQL 결과 행 수와 lock object 수는 일대일이 아니다.

13.3 “모든 X lock은 서로 충돌한다”

Pure gap lock의 S/X 표시는 일반 record lock의 호환성 규칙과 다르다. Gap lock끼리는 공존할 수 있고, 핵심 충돌은 해당 gap의 insert intention이다.

13.4 “Next-key lock은 record 뒤쪽 gap을 잠근다”

하나의 next-key interval은 일반적으로 현재 record와 그 앞 gap, 즉 (이전 key, 현재 key]로 설명한다. 마지막 뒤쪽 무한 구간은 supremum을 이용해 표현한다.

13.5 “Unique index 조건이면 항상 record lock만 생긴다”

Unique composite index의 일부만 지정하거나 range/prefix/function condition을 사용하면 exact unique lookup이 아니다. 선택 plan도 반드시 확인해야 한다.

13.6 “`READ COMMITTED`로 바꾸면 gap lock 문제가 완전히 사라진다”

일반 scan의 gap locking은 줄지만 Foreign key와 duplicate-key 검사 등에는 남을 수 있다. 업무의 phantom 허용 여부도 달라진다.

13.7 “잠금 timeout만 늘리면 안전하다”

긴 timeout은 blocker를 제거하지 않고 대기 connection을 누적시킬 수 있다. Index, transaction 길이, batch, access order와 오류 처리가 우선이다.

13.8 “보이는 `LOCK_MODE` 문자열만으로 원인을 확정할 수 있다”

잠금은 실행 plan이 만든 결과다. EXPLAIN, index DDL, isolation level, statement 종류와 transaction 경계를 함께 보지 않으면 잘못 해석하기 쉽다.

14. 잠금 범위를 줄이는 설계 기준

14.1 Predicate와 index

업무 key를 unique constraint로 표현할 수 있다면 명시한다.
Locking read의 equality/range 조건과 정렬 순서에 맞는 composite index를 설계한다.
함수와 묵시적 형 변환으로 index search가 깨지지 않게 한다.
EXPLAIN에서 예상 index가 실제 선택되었는지 확인한다.
Low-cardinality 선두 컬럼으로 지나치게 넓은 구간을 잠그지 않는지 본다.
“Index를 추가하면 읽기가 빨라진다”를 넘어 잠글 검색 구간도 줄어드는지 동시성 테스트로 확인한다.

14.2 Transaction 경계

Locking read와 후속 DML을 같은 짧은 transaction에 둔다.
잠금 보유 중 외부 API, 파일 I/O, 사용자 입력을 기다리지 않는다.
Batch 크기와 한 transaction의 최대 처리 행 수를 제한한다.
여러 table·row는 모든 코드 경로에서 같은 순서로 접근한다.
오류와 connection pool 반환 시 ROLLBACK을 보장한다.
Deadlock retry는 전체 transaction 단위로 수행한다.

14.3 “없는 key 예약”의 대안

존재하지 않는 key를 SELECT ... FOR UPDATE로 조회해 예약하려는 설계는 gap contention과 isolation-level 의존성을 만든다. 다음 대안을 검토한다.

업무 key에 unique constraint를 두고 INSERT 충돌을 명시적으로 처리한다.
예약 대상을 대표하는 별도 row를 미리 만들고 primary key exact match로 잠근다.
Idempotency key와 상태 전이 column으로 중복 처리를 막는다.
충돌이 드물다면 version column을 이용한 optimistic concurrency control을 사용한다.
Queue라면 durable owner, state, lease와 재처리 정책을 설계한다.

15. Aurora MySQL에서의 운영 해석

Aurora MySQL도 MySQL 호환 InnoDB transaction 계층에서 record, gap, next-key lock의 핵심 의미를 유지한다. 분산 스토리지와 다중 AZ 복제 구조가 row lock contention을 자동으로 없애지는 않는다.

운영에서는 다음 차이를 함께 고려한다.

Locking read와 DML은 writer instance에서 수행해야 한다.
Reader endpoint의 조회는 writer transaction의 lock 보호 범위에 포함되지 않는다.
Writer failover나 connection reset 뒤 기존 session·transaction·row lock이 계속 유지된다고 가정하지 않는다.
애플리케이션은 transaction 전체를 재시도하고 idempotency를 보장해야 한다.
Database Insights 또는 Performance Insights의 DB load/wait를 Performance Schema lock 관계와 같은 시간축으로 연결한다.
Cluster/instance parameter group의 isolation·timeout 변경 범위와 재부팅 필요 여부를 확인한다.
Failover 전후의 connection surge가 원래 lock contention을 증폭하지 않았는지 본다.

Aurora의 빠른 복구 기능은 업무 잠금의 durable 상태를 대신하지 않는다. Queue 선점이나 예약 상태는 row lock만 믿지 말고 state, owner, lease expiration 같은 영속 column으로 남겨야 한다.

16. 설계·운영 점검표

검색과 잠금 범위

SQL 결과 행 수가 아니라 선택된 index 검색 구간을 확인했는가?
Primary/unique exact match와 non-unique/range search를 구분했는가?
Composite unique index의 모든 key part를 조건에 사용했는가?
0행 locking read에서도 gap lock이 생길 가능성을 검증했는가?
Secondary index와 clustered index 양쪽의 잠금을 함께 해석했는가?
EXPLAIN `EXPLAIN`의 access type, key와 rows examined가 의도와 맞는가?

Transaction과 동시성

Locking read와 후속 변경이 같은 transaction에 있는가?
Transaction 안에서 외부 작업을 기다리지 않는가?
여러 row/table의 접근 순서를 통일했는가?
Batch 크기와 잠금 보유 시간에 상한이 있는가?
Unique constraint나 원자적 DML로 잠금 범위를 줄일 수 있는가?
Deadlock/timeout retry가 idempotent하고 횟수 제한을 가지는가?

운영 관측과 대응

data_lock_waits `data_lock_waits`로 waiting과 blocking lock을 연결하는가?
data_locks의 REC_NOT_GAP, GAP, INSERT_INTENTION `data_locks`의 `REC_NOT_GAP`, `GAP`, `INSERT_INTENTION`을 구분하는가?
Blocker의 trx_started Blocker의 `trx_started`, 수정 행 수와 최근 업무 SQL을 확인하는가?
강제 종료 전 rollback 비용과 업무 중요도를 평가하는가?
Lock wait time, deadlock, transaction duration과 hot key를 추적하는가?
Aurora에서는 writer endpoint, failover와 connection 이력을 함께 보는가?

17. 정리

Record lock은 존재하는 index record를 보호하고, gap lock은 record 사이의 삽입 공간을 보호하며, next-key lock은 현재 record와 그 앞 gap을 (이전 key, 현재 key] 구간으로 함께 보호한다. 이 조합 덕분에 InnoDB는 REPEATABLE READ의 current read와 DML에서 검색 범위가 다른 transaction의 삽입으로 바뀌는 일을 제어할 수 있다.

운영에서 가장 중요한 판단 기준은 반환 행 수가 아니라 어떤 index를 따라 어디까지 탐색했는가다. Unique exact match는 대체로 REC_NOT_GAP record lock으로 범위를 줄일 수 있지만, non-unique 조건, range, index 부재와 0행 검색은 gap 또는 next-key lock을 만들 수 있다. Secondary index 검색은 clustered record 잠금까지 동반할 수 있으므로 lock 행 수와 업무 행 수를 일대일로 대응시키면 안 된다.

잠금 장애를 해결할 때 timeout부터 늘리지 말고 data_lock_waits로 대기 관계를 연결한 뒤, EXPLAIN, index 정의, isolation level과 transaction 수명을 함께 확인해야 한다. 다음 글에서는 이런 잠금들이 phantom read 방지와 격리 수준의 의미를 어떻게 구성하는지 더 넓은 transaction isolation 관점에서 연결할 수 있다.

1. 먼저 기억할 한 문장

2. InnoDB는 왜 빈 공간까지 잠그는가

3. Record lock: 존재하는 index record를 보호한다

4. Gap lock: 존재하지 않는 key가 들어올 공간을 보호한다

5. Next-key lock: 앞 gap과 현재 record를 하나의 구간으로 보호한다

6. 실행 재현: exact record, range, 빈 gap 비교

7. 실행 재현: range lock이 gap의 INSERT를 기다리게 한다

8. Secondary index 잠금과 clustered index 잠금을 함께 본다

9. Unique exact match가 gap을 생략하는 조건

10. READ COMMITTED에서는 무엇이 달라지는가

11. Insert intention과 gap deadlock

12. 운영 진단: 결과 행보다 기다림 관계를 연결한다

12.1 진단 순서

13. 흔한 오해와 실패 패턴

13.1 “0행을 반환했으니 잠금도 없다”

13.2 “한 행을 갱신했으니 record lock 하나뿐이다”

13.3 “모든 X lock은 서로 충돌한다”

13.4 “Next-key lock은 record 뒤쪽 gap을 잠근다”

13.5 “Unique index 조건이면 항상 record lock만 생긴다”

13.6 “READ COMMITTED로 바꾸면 gap lock 문제가 완전히 사라진다”

13.7 “잠금 timeout만 늘리면 안전하다”

13.8 “보이는 LOCK_MODE 문자열만으로 원인을 확정할 수 있다”

14. 잠금 범위를 줄이는 설계 기준

14.1 Predicate와 index

14.2 Transaction 경계

14.3 “없는 key 예약”의 대안

15. Aurora MySQL에서의 운영 해석

16. 설계·운영 점검표

검색과 잠금 범위

Transaction과 동시성

운영 관측과 대응

17. 정리

10. `READ COMMITTED`에서는 무엇이 달라지는가

13.6 “`READ COMMITTED`로 바꾸면 gap lock 문제가 완전히 사라진다”

13.8 “보이는 `LOCK_MODE` 문자열만으로 원인을 확정할 수 있다”