MySQL - LOCK 살펴보기 (2) — 락의 타입과 종류

1편에서 락이 무엇이고, 어떤 인덱스 구조 위에서 어떤 과정으로 잡히는지 봤다. 이번 편은 InnoDB가 제공하는 개별 락을 타입(how, S/X) 과 종류(what, 레코드·갭·넥스트키…) 두 축으로 하나씩 살펴본다.

Lock 타입 (how)

InnoDB는 기본적으로 행 수준(row-level) 잠금을 구현하며(테이블 수준 잠금도 지원),
락을 어떻게 쥐느냐에 따라 공유(S, Shared Lock) 잠금과 배타(X, Exclusive Lock) 잠금 두 가지 타입이 있다.

“어떻게 쥐느냐”는 곧 남과 같이 쥘 수 있는지 여부다.

• 공유(S) — “같이 읽자.” 보유 트랜잭션은 그 행을 읽을 수 있고, 여러 트랜잭션이 같은 대상에 동시에 S를 쥘 수 있다. 대신 그동안 누구도 그 행을 수정(X)하지 못한다. 획득: SELECT ... FOR SHARE
• 배타(X) — “나만 쓴다.” 보유 트랜잭션은 그 행을 수정·삭제할 수 있고, 그동안 다른 누구도 그 대상을 읽으려(S) 쓰려(X) 하든 쥘 수 없다. 획득: SELECT ... FOR UPDATE, UPDATE, DELETE

요청을 허용할지 말지는 “이미 걸린 락과 지금 요청하는 락을 같이 쥘 수 있는가”로 결정된다.

즉 S는 읽기끼리만 공존하고, X가 하나라도 끼면 무조건 단독이다.
읽기는 서로 방해되지 않지만, 쓰기는 읽기·쓰기 모두를 밀어낸다.

단, 갭 락은 여기서 예외로, S/X가 서로 충돌하지 않는다. 갭 락은 다른 락을 막는 게 아니라 오직 삽입을 막는 억제용이기 때문인데, 자세한 건 Gap Lock 절에서 다룬다.

Lock 종류 (what)

InnoDB의 모든 락은 직교하는 두 축의 조합이다. performance_schema.data_locks의 LOCK_MODE에 X,REC_NOT_GAP·S,GAP처럼 두 값이 콤마로 붙어 나오는 게 이걸 그대로 보여준다.

LOCK_MODE 값은 이 두 축을 한 문자열에 담는다.

• X,REC_NOT_GAP = 배타(X) + 레코드만
• S,GAP = 공유(S) + 앞 갭만
• X(단독 표기 = 기본형이라 접미사 없음) = 배타(X) + 넥스트키(레코드 + 앞 갭)

S/X(타입)는 “어느 데이터를 덮느냐”를 전혀 정하지 않는다. 그건 오로지 두 번째 축(종류)이 정한다. 앞 절이 타입(how) 축을 다뤘고, 이 절은 종류(what) 축을 하나씩 살펴본다.

아래는 InnoDB가 제공하는 잠금 전체를 레벨 · 종류 · 타입과 함께 정리한 것이다.

• 종류(what)와 타입(how)은 서로 독립이라 자유롭게 조합된다. 대부분의 행 락은 같은 종류라도 FOR SHARE면 S, FOR UPDATE면 X로 갈린다.
• 성격이 고정된 예외가 있다.
  • IS/IX(intention)는 record/gap과 같은 층위가 아니라 테이블 레벨이고, 행 락을 걸기 전에 “행 단위로 뭔가 잠글 것”임을 표시하는 용도라 IS/IX 형태로만 존재한다.
  • AUTO-INC는 채번 전용이라 S/X 구분이 없다.
  • Insert Intention은 삽입(쓰기) 직전 락이라 사실상 X로 쓰인다.
• 참고로 MySQL 공식 문서(InnoDB Locking)는 S/X를 “두 유형(types)의 락”이라 부른다. 이 글은 S/X를 “타입(how)”, 잠금 대상의 형태를 “종류(what)”로 구분하는데, data_locks의 컬럼명이 LOCK_MODE인 것과는 용어가 어긋나지만 값 자체는 위 표처럼 두 축을 다 담고 있다.

행 수준 락은 결국 리프 레코드 체인 위에서 레코드(칸) 와 갭(칸과 칸을 잇는 next_record 링크 = 삽입 자리) 을 잠그는 일이다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

 클러스터드 인덱스 리프 — 레코드는 next_record 링크로 키 순서를 이룬다
 (레코드 = 행 전체,  … = DB_TRX_ID·DB_ROLL_PTR)

  [infimum]                          ← 시작 경계(가상 레코드)
      │ next_record
      ▼
  ┌──────┬────┬─────┬────┬───┐
  │ 헤더  │ 10 │ kim │ 25 │ … │   ◀── Record Lock : 레코드 '칸' 하나(점)만
  └──────┴────┴─────┴────┴───┘
      │ next_record  ◀── Gap Lock : 칸과 칸 사이 링크(= 삽입 자리)
      ▼
  ┌──────┬────┬─────┬────┬───┐
  │ 헤더  │ 20 │ lee │ 30 │ … │   ◀── Next-Key Lock : 레코드 20 + 그 앞 갭(10,20)
  └──────┴────┴─────┴────┴───┘        (레코드 락 + 갭 락을 한 덩어리로)
      │ next_record
      ▼
  ┌──────┬────┬──────┬────┬───┐
  │ 헤더  │ 30 │ park │ 25 │ … │
  └──────┴────┴──────┴────┴───┘
      │ next_record
      ▼
  [supremum]                         ← 끝 경계(가상 레코드)

• Record / Gap / Next-Key 셋이 행 락의 핵심이고, 나머지는 이들을 보조·특수화한 것이다.
• Insert Intention은 갭 락의 변형, Intention(IS/IX)·AUTO-INC는 행 락을 관리하기 위한 테이블 수준 장치다.
• 각 락이 무엇을 왜 막는지는 아래에서 락별로 하나씩 본다.

격리 수준이 “어떤 락을 쓰는지”를 바꾼다

이 글의 락별 설명은 별도 언급이 없으면 InnoDB 기본값 REPEATABLE READ 기준이다. 같은 잠금 읽기라도 격리 수준에 따라 갭·넥스트키를 쓰는지가 갈린다.

• RC에서도 외래 키·중복 키 검사엔 갭이 남을 수 있다(→ Gap Lock).
• 아래 각 락 설명에서 격리 수준별로 갈리는 지점을 그때그때 짚는다.

Intention Lock

트랜잭션이 테이블 안의 개별 행에 나중에 어떤 행 락(S 또는 X)을 걸 것인지, 그 의도를 테이블에 미리 표시해 두는 테이블 수준 잠금이다.

• 의도 공유(IS) — 행에 공유(S) 락을 걸 의도. SELECT ... FOR SHARE가 테이블에 IS를 남긴다.
• 의도 배타(IX) — 행에 배타(X) 락을 걸 의도. SELECT ... FOR UPDATE·UPDATE·DELETE가 테이블에 IX를 남긴다.

행 락을 걸기 전에 “이 테이블 어딘가의 행을 이런 식으로 잠글 것”이라고 테이블에 먼저 깃발을 꽂는 셈이다. 그래서 실제 행 락과 짝을 이뤄, FOR UPDATE 하나에 테이블 IX와 해당 행의 X가 함께 걸린다.

왜 필요한가 — 행 락과 테이블 락을 싸게 공존시키려고

• InnoDB는 크기가 다른 잠금(테이블·행)을 동시에 다루는 다중 세분화 잠금(multiple granularity locking)을 쓴다.

• 여기서 문제가 하나 생긴다. 누군가 LOCK TABLES ... WRITE로 테이블 전체를 잠그려면 그 테이블의 어느 행에도 행 락이 없어야하는데, 의도 락이 없으면 이걸 확인하려고 모든 행 락을 일일이 훑어야 한다.

• 의도 락은 이 검사를 테이블 플래그 하나 보는 것으로 줄인다.

1
2
3
4
5
6

-- 트랜잭션 A
START TRANSACTION;
SELECT * FROM child WHERE id = 1 FOR UPDATE;  -- 테이블 child에 IX + 행 id=1에 X

-- 트랜잭션 B
LOCK TABLES child WRITE;                       -- 테이블 전체 X 요청 → child의 IX와 충돌 → 대기

B는 A가 남긴 테이블 IX 플래그 하나만 보고 “누군가 행을 배타로 잠글 의도가 있다”를 알아 곧바로 대기한다. 행 락 목록을 뒤질 필요가 없다.

반대로 의도 락끼리는 서로 막지 않는다. IX는 “행 단위로 잠그겠다”는 표시일 뿐 어느 행인지는 말하지 않기 때문이다.

1
2
3
4
5

-- 트랜잭션 A : 행 id=1에 X  → child에 IX
SELECT * FROM child WHERE id = 1 FOR UPDATE;

-- 트랜잭션 B : 행 id=2에 X  → child에 IX  → A와 대기 없이 진행
SELECT * FROM child WHERE id = 2 FOR UPDATE;

• 실제 행 락(id=1, id=2)이 다른 행이라 충돌하지 않는 것을, 테이블 레벨에서도 IX ↔ IX 호환으로 반영한 것이다.

전체 호환 관계는 이렇다 — 의도 락은 전체 테이블 락 요청(LOCK TABLES의 S/X) 만 막고 나머지는 통과시킨다.

인덱스 구조에서 어디를 잠그나 — 레코드가 아니라 테이블 객체

record/gap/next-key가 리프 레코드 체인의 칸·갭 링크에 얹히는 것과 달리, 의도 락은 그 위층인 테이블 객체에 붙는 플래그라 리프 체인 위에는 아무 흔적도 남기지 않는다. performance_schema.data_locks에서도 층이 다르게 찍힌다.

• 그래서 FOR UPDATE 하나에 보통 두 줄이 뜬다 — 테이블에 TABLE / IX, 실제 잠근 행에 RECORD / X,REC_NOT_GAP.
• 앞줄이 의도 락(테이블 플래그), 뒷줄이 진짜 행 락이다.
• 잠금 읽기(FOR UPDATE 등)를 data_locks로 확인하면 결과 첫 줄에 TABLE / IX가 보이는 게 이것이다.

Record Lock

인덱스 레코드 하나에 걸리는 잠금이다. 그 앞뒤 갭은 건드리지 않고 딱 그 레코드 칸만 잠근다(data_locks에서 X,REC_NOT_GAP).

• 예: SELECT * FROM child WHERE id = 10 FOR UPDATE;는 id = 10인 행 하나를 다른 트랜잭션이 수정·삭제하지 못하게 막는다. 앞뒤 갭은 안 막으므로 9나 11을 삽입하는 건 자유롭다.
• 타입(S/X)은 쿼리가 정한다 — FOR SHARE면 S, FOR UPDATE·UPDATE·DELETE면 X.
• 레코드 락은 격리 수준을 타지 않는다. 등치로 찾은 그 행에 거는 레코드 락은 RR·RC 어디서나 동일하게 걸린다. 격리 수준이 가르는 건 갭·넥스트키와 안 맞는 행의 처리이지, 매칭된 행의 레코드 락 자체가 아니다.

왜 필요한가 — 동시 수정으로 인한 갱신 손실(lost update) 방지

재고 차감을 보자. 두 주문이 동시에 stock = 10을 읽고 각자 10 - 1 = 9를 저장하면, 2개가 팔렸는데 재고는 9로 남는다(8이어야 한다). X 레코드 락이 한쪽을 대기시켜 10 → 9 → 8로 직렬화한다.

1
2
3
4
5

Record Lock 없음                     Record Lock 있음
A: read 10 ┐                         A: read 10, write 9 (락 보유)
B: read 10 ┘ 둘 다 10을 봄           B: ────대기────→ read 9, write 8
A: write 9                           결과: 8 (정확)
B: write 9  → 최종 9 (틀림, 8이어야)

레코드 락이 지키는 건 이미 존재하는 행을 한 번에 한 트랜잭션만 건드리도록 직렬화하는 것이다. 반대로 “아직 없는 행이 생기는 것”은 레코드 락으로 못 막는데, 그건 갭·넥스트키 락의 몫으로 다음 절들에서 다룬다.

인덱스 구조에서 어디를 잠그나 — 리프 레코드 칸 하나(점)

레코드 락은 리프 레코드 체인에서 레코드 ‘칸’ 하나(점) 에 걸린다. 그 앞 갭 링크는 포함하지 않아서 data_locks에 X,REC_NOT_GAP으로 뜬다.

• 인덱스 레코드란 인덱스 리프에 저장된 각 항목인데, 담긴 게 인덱스 종류에 따라 다르다.
  • 클러스터형 인덱스(PK) 의 리프 레코드는 행 데이터 전체다.
  • 세컨더리 인덱스의 리프 레코드는 인덱스 키 + PK 값(클러스터형 인덱스를 가리키는 포인터)이다. 행 데이터 자체가 아니다.
• InnoDB는 어떤 행을 찾고 수정할 때도 항상 인덱스를 통해 위치를 찾고, 잠금도 그 인덱스 레코드에 건다.
• 그래서 세컨더리 인덱스로 행을 찾아 잠그면 세컨더리 인덱스 레코드와 클러스터형 인덱스 레코드 양쪽에 락이 잡힌다. (세컨더리 인덱스로 조회해 잠그면 data_locks에 idx_class_no_name과 PRIMARY 두 곳에 각각 락이 찍힌다)
• 테이블에 인덱스가 없어도 마찬가지다.
  • InnoDB가 숨겨진 클러스터형 인덱스를 자동으로 만들어 그 위에서 레코드 락을 건다.
• gap·next-key 같은 나머지 행 락도 전부 이 인덱스 레코드(칸)와 그 사이 갭(링크)을 기준으로 동작한다.

Gap Lock

인접한 두 인덱스 레코드를 잇는 next_record 링크(= 새 행이 끼어들 삽입 자리) 에 걸리는 잠금이다. 레코드(칸) 자체가 아니라 레코드와 레코드를 잇는 연결 고리를 잠가, 그 사이로 새 행이 들어오는 걸 막는다.

• 예: SELECT * FROM child WHERE id BETWEEN 10 AND 20 FOR UPDATE;는 그 범위에 값이 있든 없든 다른 트랜잭션이 15를 삽입하지 못하게 막는다. 범위 안 레코드들을 잇는 링크마다 갭 락이 걸리기 때문이다.
• 하나의 링크는 인접한 두 레코드 사이 하나를 가리키고, 범위 쿼리는 이런 링크를 여러 개 연달아 잠가 값 구간 전체를 덮는다. 맨 앞 레코드 이전, 맨 뒤 레코드 이후(→ supremum)의 링크도 갭이다.
• 갭 락은 오직 삽입을 막는 억제용이다. 그래서 S/X 구분이 있어도 갭 락끼리는 서로 충돌하지 않는다. (자세한 건 아래 S/X 갭 락은 왜 충돌하지 않나 절)
• 격리 수준을 탄다 — REPEATABLE READ에서만 검색·스캔에 쓰이고 READ COMMITTED에선 꺼진다. (RC에서도 중복 키 검사 등엔 남을 수 있다 — 아래 실측 절 참고)

왜 필요한가 — “아직 없는 행”을 잠가 삽입을 막으려고

레코드 락은 이미 있는 행만 잠근다. 문제는 “조건에 맞는 행이 하나도 없다”를 확인하고 그걸 근거로 쓰는(check-then-act) 경우다. 잠글 행이 없으니 레코드 락으론 그 “없음”을 지킬 방법이 없다.

child에 id = 10, 20만 있고, 애플리케이션이 “11~19 구간엔 행을 하나만 만든다”는 규칙을 코드로 지킨다고 하자 (유니크 제약으로는 표현 못 하는 규칙이라 직접 확인 후 삽입한다).

1
2
3
4
5

-- 트랜잭션 A                                     -- 트랜잭션 B (거의 동시에)
SELECT * FROM child                               SELECT * FROM child
 WHERE id BETWEEN 11 AND 19;    -- 0건             WHERE id BETWEEN 11 AND 19;    -- 0건
-- "비었네, 넣어도 되겠다"                         -- "비었네, 넣어도 되겠다"
INSERT INTO child VALUES (15);                    INSERT INTO child VALUES (16);

두 트랜잭션 다 “0건”을 보고 각자 삽입하면 구간에 행이 둘 생겨 규칙이 깨진다. 15가 들어가는 것 자체가 해로운 게 아니라, A가 “비었다”를 근거로 내린 판단을 B가 뒤집는 것이 문제다. 그래서 막아야 하는 건 특정 값 하나가 아니라 11~19 구간 전체로 새 행이 들어오는 일이고, 그 구간을 덮는 게 곧 (10,20) 링크의 갭 락이다.

SELECT를 잠금 읽기로 바꾸면 A는 0건이어도 그 사이 링크 (10,20)에 갭 락을 건다.

1
2
3
4
5

-- 트랜잭션 A
SELECT * FROM child WHERE id BETWEEN 11 AND 19 FOR UPDATE;  -- 0건 → (10,20) 링크에 X,GAP

-- 트랜잭션 B
INSERT INTO child VALUES (15);   -- (10,20) 링크에 막혀 A가 끝날 때까지 대기

B의 INSERT 15가 인덱스 구조에서 막히는 과정은 이렇다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

INSERT INTO child VALUES (15) 가 (10,20) 링크에서 막히기까지

① 15가 정렬 체인의 어디에 끼는지 탐색  (B+트리 루트 → 리프)

   [infimum] → 10 → 20 → [supremum]
                  └─ 10 < 15 < 20  →  낄 자리 = 10과 20 사이 링크 (10,20)
                                      오른쪽 이웃 = 레코드 20

② 그 링크(삽입 자리)에 insert intention 락을 요청 → A의 갭 락과 충돌 → 대기

   ┌────┐
   │ 10 │
   └────┘
      │ next_record = 갭 (10,20)  ← 잠기는 대상은 이 링크
      │    • A : X,GAP 보유 (FOR UPDATE에서 옴 · 레코드 20의 heap_no에 앵커)
      │    • B : INSERT_INTENTION 요청 → 갭 락과 충돌 → 대기
      ▼
   ┌────┐
   │ 20 │
   └────┘

15라는 값이 실제로 존재하지 않아도, InnoDB는 그 값이 낄 링크를 찾아 그 자리에서 막는다. 갭 락이 레코드가 아니라 링크에 걸리는 이유가 이것이다. 이제 B의 삽입은 A가 커밋/롤백할 때까지 기다린다. 갭 락은 “이 구간엔 행이 없다”는 사실 자체를 잠가, 그 “없음”을 근거로 한 판단이 뒤집히지 않게 하는 유일한 수단이다.

여기서 B가 막힌 건 A가 쥔 게 갭 락(즉 FOR UPDATE에서 온 것)이기 때문이다. 같은 링크를 대상으로 해도 A가 무엇을 쥐고 있느냐에 따라 결과가 갈린다.

• 삽입을 실제로 막는 충돌은 오직 갭 락 ↔ insert intention 한 조합뿐이다.
• 그래서 맨 위 첫 코드 블록처럼 A·B가 잠금 없이 INSERT만 하면(둘 다 insert intention, 값도 다름) 서로 안 막혀 규칙이 깨진 것이다. 이 INSERT끼리의 비대칭은 Insert Intention Locks에서 자세히 다룬다.

실전에서 “없음을 근거로 삽입”하는 규칙은 대부분 이 모양이고, 유니크 제약으로 표현할 수 없어 갭 락에 의존하게 된다. 둘 다 조회가 0건이라 잠글 레코드가 없고, 막아야 하는 건 그 빈 자리에 새 행이 끼는 것이다.

• 회의실 중복 예약 — idx_room_start (room_id, start_at) 인덱스에서 “이 시간대에 겹치는 예약이 있나”를 잠금 조회하고, 0건이면 삽입한다.

  1 2 3 4

  SELECT COUNT(*) FROM reservation WHERE room_id = 1 AND start_at BETWEEN '2026-07-21 10:00' AND '2026-07-21 11:00' FOR UPDATE; -- 0건이면 INSERT INTO reservation (room_id, start_at) VALUES (1, '2026-07-21 10:30');

• 한 사용자당 활성 구독 하나 — 해지 이력이 남아야 해 UNIQUE(user_id)를 못 건다. 조건부 유니크 제약이 없으니 갭 락으로 대신한다.

  1 2	SELECT * FROM subscription WHERE user_id = 42 AND status = 'ACTIVE' FOR UPDATE; -- 0건이면 INSERT INTO subscription (user_id, status) VALUES (42, 'ACTIVE');

RR이면 조회가 0건이어도 그 조건 범위의 갭에 X,GAP이 걸려 다른 트랜잭션의 INSERT를 그 자리에서 막는다. (RC라면 갭이 없어 양쪽 다 통과 → 중복 생성)

다만 A·B가 둘 다 FOR UPDATE를 먼저 돌리면, 서로의 갭 락 때문에 양쪽 INSERT가 맞물려 데드락이 난다. MySQL이 한쪽을 롤백시켜 중복은 여전히 막히지만, 이 패턴에서 데드락 로그가 자주 보이는 이유가 이것이다.

여기까지는 조회가 0건이라 갭만 걸리는 경우다. 조회 범위에 기존 행이 있어 갭뿐 아니라 그 레코드들까지 함께 잠가야 하는 경우(정원 초과 방지 등)는 레코드와 갭을 함께 거는 넥스트키 락에서 다룬다.

인덱스 구조에서 어디를 잠그나 — next_record 링크(= 삽입 자리)

리프 페이지의 레코드는 물리적으로 정렬돼 저장되지 않고, 각 레코드 헤더의 next_record 포인터로만 키 정렬을 표현한다. 갭이란 이 정렬 체인에서 인접한 두 레코드를 잇는 링크 구간(= 삽입 자리) 이다.

• 그래서 갭 (4,5) = “레코드 4의 next_record가 레코드 5를 가리키는 그 링크 구간” 이다. 값의 산술적 빈틈(4.5 같은)이 아니라 정렬 체인 위의 한 링크다.
• infimum의 next_record는 항상 최소 키를, 최대 키의 next_record는 항상 supremum을 가리키므로, 리스트 맨 끝 (마지막, +∞) 갭도 supremum을 오른쪽 경계로 갖는 하나의 링크다.

INSERT는 이 링크에서 막힌다. 새 키 하나를 INSERT하려면 InnoDB는 먼저 그 키가 정렬 체인의 어디에 끼는지(= 어느 갭인지, 오른쪽이 될 레코드가 누구인지) 를 찾는다. 그리고 그 자리에 insert intention 락을 요청한다. 누군가 그 갭에 갭 락을 쥐고 있으면 대기한다.

1
2
3

INSERT id=4.x  →  "4와 5 사이에 끼겠군 (오른쪽 = 레코드 5)"
               →  레코드 5 자리에 insert intention 요청
               →  거기 X,GAP 있으면 → 대기 / 없으면 → 4.next=신규, 신규.next=5 로 재연결

갭 락 자체는 페이지 안이 아니라 인메모리 lock system(락 구조체 해시)에 저장된다. LOCK_GAP 플래그가 켜진 락 구조체가 오른쪽 레코드의 heap_no에 앵커되는 방식이다. 그래서 performance_schema.data_locks에 LOCK_MODE=X,GAP, LOCK_DATA=5로 뜨는 건 “레코드 5 앞의 갭 (4,5)를 막는 락”이라는 뜻이지 레코드 5를 잠근 게 아니다. 리스트 맨 끝 (마지막, +∞) 갭은 오른쪽 실제 레코드가 없어 supremum(heap_no=1)에 앵커된다.

갭 = 리프 힙 위에 포인터로 얹힌 정렬 링크드 리스트에서 인접한 두 레코드를 잇는 next_record 링크. 갭 락은 그 링크를 오른쪽 레코드 heap_no에 앵커해 기록한 것이라, 포인터 재연결(INSERT)만 막고 기존 레코드의 읽기/수정은 안 막는다.

갭은 값 구간을 임의로 못 자른다 — 그래서 범위 조건이 과잠금될 수 있다. 갭의 최소 단위는 인접한 두 실제 레코드 사이의 링크 하나다. 그래서 조건의 상한이 레코드 사이에 걸리면, 그 값을 품는 갭 링크 전체가 잠긴다. 레코드 10, 20만 있을 때 WHERE id BETWEEN 10 AND 15 FOR UPDATE를 보자. 팬텀으로 막아야 할 값은 11~15뿐이지만, 15가 낄 자리는 갭 (10,20) 하나이고 그걸 쪼갤 앵커(레코드)가 15에 없다. 그래서 (10,20) 전체가 잠기고, 팬텀이 아닌 16~19 삽입까지 덤으로 막힌다.

같은 쿼리를 15에 레코드가 있는 경우와 비교하면, 갭이 (10,15)·(15,20)으로 쪼개져 딱 필요한 만큼만 잠긴다. (MySQL 8.0.46, RR 실측)

InnoDB는 값 범위를 추상적으로 잠그는 게 아니라 정렬 체인의 링크(갭) 단위로 잠그기 때문에, 조건 경계가 레코드에 정렬되지 않으면 다음 실제 레코드까지 과잠금이 생긴다. 인덱스 구조상 불가피한 부분이다.

RC에서 FK·중복 키 검사엔 갭이 남을 수 있다 (실측)

• 갭 락은 RC에서 검색·스캔엔 꺼지지만, 외래 키·중복 키 검사에는 남을 수 있다는 게 매뉴얼 문구다.
• 이걸 performance_schema.data_locks로 확인해보면(MySQL 8.0.46), 중복 키 검사 쪽은 갭이 남는 게 명확히 재현되지만, FK 검사 쪽은 이 환경에서 재현되지 않았다.

중복 키 검사 — RC에서도 갭이 남는다. 유니크 인덱스 uk_c에 5,10,20이 있고 두 세션이 RC에서 동시에 c=7을 INSERT하면:

1
2

Tx A: INSERT c=7 (커밋 안 함)   → uk_c (7,4)에 X,REC_NOT_GAP 보유
Tx B: INSERT c=7              → uk_c (7,4)에 S 요청, WAITING

• B가 요청한 락이 S,REC_NOT_GAP가 아니라 S(접미사 없음 = 넥스트키 = 레코드 + 갭) 다.
• RC인데도 중복 키 검사는 갭을 포함한 락을 건다.
• B는 A가 커밋(→ B는 중복 에러)할지 롤백(→ B는 성공)할지 결정될 때까지 그 값 자리 자체를 붙잡고 기다려야 하는데, 순수 레코드 락만으론 “그 자리에 값이 확정되는 것”을 못 지키기 때문이다.

FK 검사 — 이 환경에선 갭이 안 나왔다. 자식 INSERT 시 부모 존재 확인은 부모 행에 S,REC_NOT_GAP(레코드만)이었고, 자식 없는 부모(id=20)를 DELETE할 때 자식 인덱스 스캔도 격리 수준에 따라 갈렸다.

RR은 삭제 중 pid=20을 참조하는 자식이 새로 들어오는 걸 갭으로 막지만, RC는 그 갭을 걸지 않았다. 즉 “FK 검사도 RC에서 갭을 남긴다”는 이 케이스들에선 재현되지 않았다.

정리: 중복 키 검사의 갭은 RC에서도 남는다(실측 확인). FK 검사의 갭은 매뉴얼 문구이나 위 케이스들에선 재현되지 않았으니, 정확한 발생 조건은 쓰는 버전에서 data_locks로 직접 확인하는 편이 좋다.

S/X 갭 락 차이가 없는데 구분하는 이유 ?

정확히는 “구분이 아예 없다”가 아니라 충돌 판정에서만 무의미하다는 뜻이다.

• 핵심은 갭 락이 다른 갭 락이 아니라 INSERT를 막는 잠금이라는 점이다.

갭 락이 참여하는 충돌은 사실상 insert intention ↔ gap 한 줄뿐이라, S인지 X인지가 판정에 끼어들 여지가 없다. 이 충돌은 비대칭이라는 점도 유의한다. insert intention은 기존 갭 락을 기다리지만, 갭 락 요청은 기존 insert intention을 기다리지 않는다.

갭 락은 “이 빈 공간을 내가 점유한다”가 아니라 “이 빈 공간에 아무도 새 행을 넣지 마라” 라는 선언이다. 점유가 아니라 억제라서, 여러 트랜잭션이 같은 갭에 동시에 선언해도 서로 방해되지 않는다.

그럼 왜 S/X 라벨을 붙여두나? 넥스트키 락 = 레코드 락 + 갭 락이고, 이 둘은 하나의 락 객체로 관리된다. S/X는 쿼리가 정하며(FOR UPDATE → X, FOR SHARE → S), 그 라벨은 레코드 부분에선 충돌 판정에 쓰이고 갭 부분에선 무시된다. 같은 라벨이 두 부분에서 다르게 취급되는 걸 실제 쿼리로 보면 이렇다. (child = 10, 20, 30, RR, MySQL 8.0.46 실측)

① 갭 부분 — S/X 무시 : 존재하지 않는 값 구간에 한쪽은 X 갭, 다른 쪽은 S 갭을 걸어도 그냥 공존한다.

1
2
3
4

-- T1
SELECT * FROM child WHERE id BETWEEN 11 AND 19 FOR UPDATE;  -- 0건 → (10,20)에 X,GAP
-- T2 (T1이 쥔 채)
SELECT * FROM child WHERE id BETWEEN 11 AND 19 FOR SHARE;   -- 0건 → (10,20)에 S,GAP · 대기 없이 성공

1
2

data_locks:  PRIMARY  X,GAP  20  (GRANTED)   ← T1
             PRIMARY  S,GAP  20  (GRANTED)   ← T2  · 같은 갭에 X·S가 공존

② 레코드 부분 — S/X 존중 : 같은 두 쿼리를 존재하는 값 20에 걸면 X와 S가 충돌한다.

1
2
3
4

-- T1
SELECT * FROM child WHERE id = 20 FOR UPDATE;  -- 20에 X,REC_NOT_GAP
-- T2 (T1이 쥔 채)
SELECT * FROM child WHERE id = 20 FOR SHARE;   -- 20에 S 요청 → X와 충돌 → 대기(락 타임아웃)

넥스트키 락은 ①의 갭과 ②의 레코드를 한 객체로 묶어 들고 다닌다. ②의 레코드 부분 때문에 S/X 라벨이 반드시 필요하고, ①의 갭 부분은 그 라벨을 달고만 있을 뿐 판정엔 쓰지 않는다. 그래서 data_locks에 X,GAP/S,GAP처럼 S/X가 찍혀도, 갭끼리의 충돌 판정에서는 그 글자가 아무 역할을 하지 않는다.

그럼 둘이 같은 갭을 쥐면 둘 다 새 행을 넣나? — 아니다. 갭 락끼리 안 막는 것과, 그 갭에 삽입이 되는 것은 별개다. 삽입은 insert intention을 요청하는데 이건 기존 갭 락과 충돌한다. 그래서 갭이 몇 개 공존하든, 그 자리로 새 행을 밀어넣는 순간 막힌다. (실측, RR, child = 10, 20, 30)

• T1·T2가 (10,20) 갭에 각각 S,GAP을 동시에 GRANTED로 쥔다 (공존, 서로 대기 안 함).
• 그 상태에서 T2가 INSERT 16 → insert intention이 T1의 갭 락에 막혀 대기한다.
• T1·T2가 둘 다 넣으면 서로의 갭 락을 기다려 데드락 → MySQL이 한쪽을 롤백해 결국 한 행만 들어간다.

1
2
3

T1: SELECT ... BETWEEN 11 AND 19 FOR SHARE   → (10,20) S,GAP  GRANTED
T2: SELECT ... BETWEEN 11 AND 19 FOR SHARE   → (10,20) S,GAP  GRANTED   ← 공존
T2: INSERT 16                                → insert intention, T1의 갭에 막혀 WAITING

즉 “이 구간엔 행이 없다”를 지키는 실제 방어선은 갭↔갭이 아니라 insert intention ↔ 갭 이다. 방어가 삽입 시점에 걸리므로, 갭끼리는 충돌시킬 필요가 없다. (insert intention은 Insert Intention Locks에서 자세히 다룬다.)

“충돌 안 함”이 왜 꼭 필요한가 — 갭 락 병합

갭 락이 서로 충돌하지 않는 건 단순한 성질이 아니라, 레코드가 삭제될 때 갭 락을 병합해야 해서 강제되는 것이다.

전제 하나: InnoDB에서 갭 락 (10,20)은 독립된 객체가 아니라 “레코드 20에 매달린, 그 앞 갭” 으로 저장된다. 갭은 항상 오른쪽 경계 레코드에 붙어 있다.

1
2
3
4

records: 10        20        30
              └(10,20)┘ └(20,30)┘
              레코드20에   레코드30에
               매달린 갭    매달린 갭

레코드 10, 20, 30이 있고 두 트랜잭션이 레코드 20 양옆의 갭을 각각 잡았다고 하자.

1
2
3
4
5
6
7

-- Tx A : (20,30) 갭을 잠금
SELECT * FROM child WHERE id BETWEEN 21 AND 30 FOR UPDATE;
-- 30에 넥스트키 → 갭 (20,30) [= 레코드 30에 매달림] + 레코드 30

-- Tx B : (10,20) 갭을 잠금
SELECT * FROM child WHERE id BETWEEN 11 AND 19 FOR UPDATE;
-- 19<20이라 20에서 멈춤 → 갭 (10,20) [= 레코드 20에 매달림]

이제 다른 트랜잭션이 레코드 20을 DELETE·커밋 하고, 이후 purge가 20을 물리적으로 제거하면 (10,20)과 (20,30)이 하나의 갭 (10,30)으로 합쳐진다. 이때 B의 갭 락은 사라진 레코드 20에 매달려 있었으므로, InnoDB는 그걸 다음 레코드 30으로 상속(inherit) 시킨다.

1
2
3
4
5
6
7
8

[병합 전]  (10,20)      [20]      (20,30)      [30]
  Tx B:     GAP──매달림→ 20
  Tx A:                           GAP──매달림→ 30

[병합 후]        (10,30)                       [30]
  Tx B:  GAP(상속됨)──────────────매달림→ 30
  Tx A:  GAP(원래)  ──────────────매달림→ 30
         → 레코드 30에 A·B의 갭 락이 같이 매달림 = 같은 갭 공존

만약 갭 락이 서로 충돌하는 종류였다면, purge가 20을 지우는 순간 “같은 갭 (10,30)에 A·B의 충돌하는 락 두 개”라는 불법 상태가 만들어진다. 그런데 purge는 백그라운드 정리 작업이라 이런 트랜잭션 간 조율(대기·롤백)을 할 수 없다. 그래서 InnoDB는 아예 갭 락끼리 절대 충돌하지 않게 설계했고, 그러면 몇 개가 같은 갭에 매달리든 상속·병합이 항상 성립한다.

“갭 락은 순수 억제용이라 S/X가 충돌하지 않는다”는 규칙은 이론적 취향이 아니라, 레코드 삭제 시 갭 락을 다음 레코드로 상속·병합하는 동작을 단순하게 유지하려는 구현상의 요구에서 나온 것이다.

필터링 조건과 갭 락

• 유니크 인덱스의 모든 컬럼을 등치 조건으로 지정해 존재하는 행 하나를 찾는 쿼리는 갭 락이 필요 없다.
• 왜냐하면 이 경우 InnoDB는 정확히 하나의 행만 잠그면 되므로, 범위 전체를 잠글 필요가 없음
• 단, 다중 열 유니크 인덱스 중 일부 열만 조건에 포함된 경우에는 예외로, 갭 락이 발생한다.
  • 왜냐하면 조건이 애매하거나 불완전하면 MySQL이 정확히 하나의 행을 찾았는지 확신할 수 없기 때문

1
2
3
4
5
6
7
8
9

CREATE TABLE product (
  category_id INT,
  code VARCHAR(50),
  price INT,
  UNIQUE(category_id, code)
);

-- 쿼리
SELECT * FROM product WHERE category_id = 1 FOR UPDATE;

• (category_id, code)는 복합 유니크 인덱스
• 그런데 검색 조건은 category_id = 1만 포함됨 (불완전 조건)
  • InnoDB는 “정확히 하나의 행만” 찾았는지 보장할 수 없음
  • 따라서, 검색된 범위 전체에 next-key lock (record + gap) 걸게됨

1

SELECT * FROM product WHERE category_id = 1 AND code = 'A-100' FOR UPDATE;

• 유니크 인덱스 (category_id, code)의 모든 컬럼이 등치 조건으로 지정됨
• 해당 행이 실제로 존재한다면 정확히 한 row만 찾으므로 그 row에만 record lock (X) 걸림
• 갭 락 여부를 가르는 건 LIMIT 같은 게 아니라 유니크 인덱스 전체 컬럼의 등치 조건 + 행의 존재 여부다.

인덱스 없는 컬럼을 조건으로 걸면 ?

갭 락의 범위는 WHERE 조건이 아니라 실제로 스캔한 인덱스와 그 스캔 범위가 결정한다.

child에 인덱스 없는 컬럼 random(PK 아님)이 하나 더 있다고 하자. 여기에 다음 잠금 읽기를 한다.

1

SELECT * FROM child WHERE random < 5 FOR UPDATE;

“random < 5 인 구간”이라는 건 존재하지 않는다

random에 인덱스가 없으므로 스캔은 클러스터형 인덱스(PK) 풀 스캔이 된다. 그런데 조건을 만족하는 행들은 PK 순서상 흩어져 있다.

1
2
3
4

PK:      1     2     3     4     5     6     7
random: 100    3    77     1    42    99     2
                ^           ^                 ^
              조건 만족    조건 만족        조건 만족

갭 락은 (2, 3) 처럼 스캔하는 인덱스의 키 순서상 연속된 빈 공간에만 걸 수 있다. random < 5는 PK 순서에서 연속 구간이 아니라 산발적인 집합이므로, “그 영역만” 잠글 수단 자체가 없다.

그래서 모든 갭을 잠근다

막아야 하는 건 팬텀, 즉 다른 트랜잭션이 random = 3 인 행을 INSERT하는 것이다. 그런데 그 행이 PK 순서상 어디에 꽂힐지 알 수 없다. random 값과 PK 값은 아무 상관이 없기 때문이다. 어느 갭이든 조건에 맞는 행이 들어올 수 있으므로 InnoDB는 모든 갭을 다 잠가야 한다.

• 결과적으로 (-∞, 첫 PK] 부터 (마지막 PK, supremum) 까지 전 구간이 잠긴다 → 사실상 테이블 락과 같은 효과
• 특히 PK가 AUTO_INCREMENT라면 새 행은 거의 항상 마지막 갭에 들어오므로, (마지막 PK, supremum) 을 잠그지 않으면 팬텀 방지가 무의미해진다
• 조건에 맞는 행이 한 건뿐이어도 나머지 행이 전부 잠기고, 다른 트랜잭션의 INSERT도 전부 막힌다

인덱스를 만들면

1

CREATE INDEX idx_random ON child(random);

이제 스캔이 idx_random을 타므로 갭 락도 random 순서 위에 걸린다.

1
2
3

idx_random 순서: 1  2  3  42  77  99  100
                 └──────┘ ^
                조건 만족  여기까지만 잠금 → (-∞, 42]

(-∞, 42] 만 잠기고 그 뒤 레코드와 갭은 자유롭다. 잠금 범위가 조건 범위와 일치하게 된다.

격리 수준에 따른 차이

READ COMMITTED에서는 서버가 random < 5를 평가한 뒤 안 맞는 행의 락을 바로 풀어주므로, 최종적으로는 조건에 맞는 행들만 잠긴 채 남는다. 다만 스캔 도중에는 여전히 모든 행에 락을 걸었다 푸는 것이라 경합 자체가 사라지지는 않는다.

Next-Key Lock

인덱스 레코드에 거는 레코드 락과, 그 레코드 바로 앞 갭에 거는 갭 락을 한 덩어리로 묶은 잠금이다.
레코드(점)와 그 앞 링크(삽입 자리)를 함께 잠근다. data_locks에는 접미사 없는 X(또는 S)로 찍힌다.

• 넥스트키 락은 REPEATABLE READ의 기본 잠금 형태다.
• RR에서 검색·스캔이 밟는 레코드를 이 형태로 잠가 팬텀을 막는다.
• READ COMMITTED에선 갭이 빠져 레코드 락만 남고(→ 팬텀 방지 없음), SERIALIZABLE에선 평범한 SELECT에도 S 넥스트키가 걸린다.
• InnoDB는 인덱스를 검색·스캔할 때 밟은 인덱스 레코드마다 이 락을 걸어 행 수준 잠금을 수행한다.
• 따라서 행 락은 실제로는 인덱스 레코드 락이다.

왜 필요한가 — 팬텀(phantom) 방지

팬텀이 문제가 되는 건 읽은 결과를 근거로 쓰기를 하는 경우다.

• 일반 SELECT는 MVCC 스냅샷을 읽어 트랜잭션 내내 같은 결과를 본다.
• 반면 잠금 읽기(FOR UPDATE, FOR SHARE)와 UPDATE/DELETE는 스냅샷이 아니라 최신 커밋 버전을 대상으로 동작한다.

• 여기서 “내가 본 것”과 “내가 쓰는 대상”이 어긋나면 비즈니스 규칙이 깨진다.

• 순수 갭 락(Gap Lock)은 조회가 0건일 때 빈 자리를 잠그는 이야기였다.
• 넥스트키가 더 하는 일은 조회 범위에 이미 있는 행들까지 함께 잠그는 것이다.

정원 제한(초과 예약 방지)

1
2
3
4
5

-- 1) 이 항공편에 배정된 좌석 수를 센다 (잠금 읽기)
SELECT COUNT(*) FROM booking WHERE flight_id = 7 FOR UPDATE;

-- 2) 정원(180) 미만이면 한 자리 추가
INSERT INTO booking (flight_id, seat_no) VALUES (7, '32A');

• flight_id = 7에 이미 179개 행이 있다고 하자. 두 트랜잭션이 동시에 179석을 세면 둘 다 “정원 미달”로 보고 둘 다 INSERT → 181석, 초과 예약이 된다.
• 넥스트키 락은 스캔한 flight_id = 7 레코드마다 넥스트키(레코드 + 앞 갭) 를, 마지막 7 다음 경계에는 갭을 걸어 범위 전체를 잠근다. 새 예약이 이 범위 어디로 끼든 그 자리 갭이 이미 잠겨 있어 삽입이 막힌다.

새 행이 꼭 맨 뒤에 붙는 건 아니고, 삽입 위치는 인덱스 정렬 순서가 정한다. flight_id 단일 인덱스 + AUTO_INCREMENT PK면 새 7행은 가장 큰 PK를 받아 그룹 맨 뒤로 가지만, (flight_id, seat_no) 복합 인덱스면 새 좌석은 seat_no 순으로 정렬돼 그룹 중간에 끼어들 수 있다. 그래서 꼬리 갭 하나만으로는 부족하고, InnoDB는 밟은 레코드마다 앞 갭을 잠근다.

실측(MySQL 8.0.46): 좌석 '10','20','30'을 둔 채 flight_id = 7을 FOR UPDATE로 잡고 (7,'15')를 삽입하면, '15'가 '10'과 '20' 사이로 정렬돼 내부 갭에서 대기한다 — 꼬리 갭이 아니다.

1
2
3

INSERT (7,'15')  →  data_locks (WAITING)
  idx_flight_seat  X,GAP,INSERT_INTENTION  7, '20', 3
  = 레코드 (7,'20') 앞 내부 갭('10'과 '20' 사이)에 끼려다 대기

한편 기존 행에 걸리는 레코드 락은 삽입 차단과 별개로, 세는 동안 그 179행이 수정·삭제되지 않게 고정해 집계를 안정시킨다. 갭만 잠그는 순수 갭 락과 달리 레코드까지 함께 잠기는 게 넥스트키의 몫이다.

여기서 집계가 반드시 잠금 읽기(FOR UPDATE)여야 한다는 점이 핵심이다. 그냥 SELECT COUNT(*)(일반 읽기)는 RR에서 MVCC 스냅샷을 보므로 넥스트키 락을 잡지 않고, 중간에 들어온 예약도 못 막는다.

정리 — 레코드 락으로 충분한가

공통점은 전부 “없다”는 사실에 기대어 쓰기를 하는 check-then-act 패턴이라는 것이다. 없는 것은 레코드 락으로 잠글 수 없으므로, 그 삽입 자리(링크)를 잠그는 갭 락이 필요하다.

가능하면 유니크 제약으로 DB에 맡기고(갭 락보다 비용이 적고 데드락도 덜하다), 제약으로 표현할 수 없는 규칙에서만 갭 락/넥스트키 락에 의존하는 편이 낫다.

인덱스 구조에서 어디를 잠그나 — 레코드(점) + 그 앞 갭(링크)

• 넥스트키 락은 밟은 인덱스 레코드마다 그 레코드 + 바로 앞 갭을 함께 잠근다. 갭을 뒤(오른쪽)가 아니라 앞(왼쪽) 에 붙이는 건, 두 레코드 사이의 갭을 오른쪽 레코드가 소유한다는 뜻이다.
  • 왜 오른쪽인가: B-트리 탐색은 값 K로 찾으면 K 이상 첫 레코드(오른쪽 이웃) 를 돌려준다. 락을 거는 스캔(id >= 15 → 20에 착지)과 확인하는 INSERT(15 삽입 자리 = 20 바로 앞)가 둘 다 오른쪽 레코드 20에 도착하므로, 갭을 그 레코드에 매달면 거는 쪽과 확인하는 쪽이 같은 슬롯을 본다.
• 인덱스에 값 10, 11, 13, 20이 있다면 가능한 넥스트키 구간은 다음과 같다.

1
2
3
4
5

(-무한대, 10]
(10, 11]
(11, 13]
(13, 20]
(20, +무한대)

(소괄호 = 끝점 제외, 대괄호 = 끝점 포함)

• 마지막 구간 (20, +무한대)는 가장 큰 값 위의 갭과 supremum 가상 레코드를 잠근다. supremum은 실제 인덱스 레코드가 아니므로, 결국 가장 큰 값 이후의 갭만 잠그는 것과 같다.

밟은 레코드가 항상 [레코드 + 갭] 풀셋으로 잠기는 건 아니다. 인덱스 종류와 조건에 따라 갭이 빠지거나(등치 hit) 레코드 부분이 빠진다(경계 miss).

등치로 유니크 1건을 찍으면 갭이 필요 없다(그 값이 이미 존재 = 끼어들 팬텀 여지가 없다). 범위·비유니크 스캔은 “조건에 맞는 새 행”이 끼어들 수 있으므로 갭까지 잠근다.

경계 레코드에는 무엇이 걸리나 — 갭만 걸리고 레코드 부분은 빠진다

레코드 10, 20, 30이 있을 때 WHERE id BETWEEN 10 AND 18 FOR UPDATE를 걸면, 15 같은 팬텀 삽입을 막는 데는 (10,20) 갭만 잠그면 충분하다. 그리고 실제로도 딱 그만큼만 걸린다 — 경계 레코드 20에는 갭 락만(X,GAP) 걸리고 레코드 부분은 빠진다. (MySQL 8.0.46, performance_schema.data_locks 실측)

1
2
3

BETWEEN 10 AND 18 FOR UPDATE
  → id=10 : X,REC_NOT_GAP  (레코드만. 매칭된 하한, 앞 갭은 불필요)
  → id=20 : X,GAP          (갭 (10,20)만. 20은 범위 밖이라 레코드 부분 없음)

핵심은 “접근한 객체만 잠근다 + 스캔을 멈추려고 밟은 범위 경계 레코드는 레코드 부분을 뺀다” 이다. 여기서 ‘조건’은 인덱스로 스캔 범위를 정하는 접근 조건(스캔이 어디서 멈추는지)을 말하며, 스캔한 뒤 서버가 거르는 잔여 필터와는 다르다 (그 차이는 아래 스캔했지만 필터에 걸러진 레코드 참고).

• InnoDB는 범위 [10,18]을 추상적 구간으로 잠그지 않고 B+트리를 레코드 하나씩 밟으며 잠근다.
• 범위가 어디서 끝나는지 알려면 18 다음 레코드인 20을 실제로 밟아야 하므로, 20에도 락 엔트리가 생긴다.
• 다만 20은 id <= 18에 매칭되지 않는 경계 레코드라, 레코드 부분은 빠지고 팬텀 방지에 필요한 갭 (10,20)만 남는다(X,GAP).
• 갭은 “오른쪽 레코드에 붙어” 저장되므로 20의 엔트리에 얹힐 뿐, 레코드 20 자체가 잠기는 건 아니다.

갈림길은 “등값 vs 범위”가 아니라 “경계 레코드가 조건에 매칭되느냐”다. 아래 네 케이스를 같은 데이터(10,20,30)에서 실측한 결과:

매칭되는 레코드(등값 hit, BETWEEN 15 AND 20의 20)만 레코드 부분을 갖고, 매칭 안 되는 경계 레코드(등값 miss, BETWEEN 10 AND 18의 20)는 등값이든 범위든 갭만 걸린다.

그래서 BETWEEN 10 AND 18을 잡고 있어도 다른 트랜잭션은 UPDATE ... WHERE id = 20을 할 수 있다(레코드 20은 안 잠겼으므로). 다만 (10,20) 갭에 INSERT(예: 15)하는 건 막힌다.

참고로 옛 MySQL(5.7 등)에서는 범위 조회의 경계 레코드가 넥스트키(레코드까지)로 걸렸다. 위 “매칭 안 되면 갭으로 강등”은 8.0 기준 동작이니, 버전이 다르면 data_locks로 직접 확인하는 편이 정확하다.

스캔했지만 필터에 걸러진 레코드 — RR에선 그대로 잠긴다

• 위 “레코드 부분이 빠진다”는 스캔을 멈추려고 밟은 범위 경계 레코드에만 해당한다.
• 인덱스 범위 안에 있으면서 서버가 거르는 잔여 필터에 안 걸리는 레코드는 얘기가 다르다. 이건 스토리지 엔진과 서버 레이어의 역할 분담에서 나온다.

val이 비인덱스 컬럼일 때 WHERE id BETWEEN 10 AND 30 AND val = 999를 보자 (val = 999에 맞는 행은 없음).

• InnoDB(스토리지 엔진)는 인덱스로 id 10~30을 좁혀 그 레코드를 먼저 잠그며 서버로 올리고, 서버 레이어가 val = 999로 거른다.
• 이미 잠근 뒤라, 걸러진 레코드의 락을:
  • RR은 풀지 않는다. 하나도 매칭 안 돼도 스캔한 20, 30은 레코드 부분까지 넥스트키로 잡힌 채 커밋까지 남는다.
  • RC는 푼다. 매칭 안 된 행의 락을 평가 직후 해제해, 같은 쿼리가 레코드 락을 하나도 남기지 않는다.

1
2
3

id BETWEEN 10 AND 30 AND val = 999 FOR UPDATE   -- val 비인덱스, 매칭 0건 (MySQL 8.0.46)
  RR → PRIMARY  10:X,REC_NOT_GAP  20:X  30:X  supremum:X   (레코드 부분까지 다 잡힘)
  RC → 레코드 락 없음                                        (비매칭 행 락 해제)

• 즉 경계 레코드에서 레코드 부분이 빠지는 건 “범위 밖”이라 그런 것이지, “필터에 안 맞으면 레코드가 안 잠긴다”는 규칙이 아니다.
• 범위 안에서 서버 필터에 걸러진 레코드는 RR에서 그대로 잠긴다.

언제, 누가 푸는가 — 실행 파이프라인으로 보기

쿼리는 한 번에 처리되지 않고, 서버 레이어와 스토리지 엔진(InnoDB)이 행 하나씩 주고받는 루프로 실행된다.

• 파서 / 옵티마이저: SQL을 파싱하고 실행 계획을 세운다 — “인덱스로 id 10~30을 훑고(접근 조건), 올라온 행마다 val = 999를 확인한다(잔여 필터)”. val은 비인덱스라 엔진에 못 밀어넣으니(ICP 불가) 서버가 들고 있는다.
• 실행기(서버 레이어): 핸들러 API(ha_innobase::index_read/index_next)로 InnoDB에 “다음 행” 을 요청하는 루프를 돈다.
• InnoDB: 잠금 읽기라 요청받은 인덱스 레코드에 락을 먼저 걸고 나서 그 행을 서버로 올린다. (SELECT ... FOR UPDATE이니 락을 쥔 채 반환)

1
2
3
4
5
6
7
8
9
10
11

서버 실행기                         InnoDB (스토리지 엔진)
   │  index_next  ─────────────────▶  레코드 R 읽음
   │                                 R에 넥스트키 락 걸고
   │  ◀──────────────── R 반환 ──────  올려줌   (락 이미 잡힘)
   │
   ├─ val = 999 ?
   │     ├─ 매칭  → 클라이언트로 (락 유지)
   │     └─ 불일치 → handler::unlock_row() 호출 ─────▶  격리 수준 확인:
   │                                                     RC  → row_unlock_for_mysql (R 락 해제)
   │                                                     RR  → 아무것도 안 함 (락 유지)
   ▼  (다음 행으로)

핵심은 락을 푸는 결정이 두 손을 거친다는 점이다.

• 누가: 필터에 탈락시키는 건 서버 레이어다. 서버는 행을 버리면서 핸들러의 unlock_row()를 호출해 “방금 그 레코드 락 필요 없다”고 요청한다. 실제로 풀지 말지는 InnoDB가 격리 수준을 보고 정한다 — RC면 row_unlock_for_mysql로 그 레코드 락을 해제하고, RR이면 unlock_row가 사실상 아무 일도 안 한다(반복 읽기 보장을 깨면 안 되므로).
• 언제: 커밋 시점이 아니라 스캔이 그 행을 지나가는 순간이다. 그래서 RC에선 비매칭 레코드의 락 수명이 아주 짧고(스캔이 다음 행으로 넘어가면 이미 풀림), 스캔이 끝나면 매칭된 행 락만 남는다. 매칭이 0건이면 위 실측처럼 레코드 락이 하나도 안 남는다.

갭까지 보면 차이가 하나 더 있다. RC는 애초에 스캔에 갭 락을 안 잡는다. 그래서 RC 결과가 완전히 비는 건 두 가지가 겹친 것이다 — (1) 갭 락을 안 잡음 + (2) 비매칭 레코드 락을 unlock_row로 즉시 해제. 반면 RR은 갭+레코드(넥스트키)를 다 잡고, 걸러진 레코드도 커밋까지 쥔다.

정리: 락은 InnoDB가 읽는 순간 걸고, 서버 필터가 탈락시키면 unlock_row로 “해제 요청”이 내려간다. 그 요청을 실제 해제로 옮기는 건 RC뿐이고, RR은 무시한다. (엔진 내부 동작이지만, 위 실측처럼 남는 락 개수로 관찰된다.)

Insert Intention Locks

INSERT가 행을 실제로 넣기 직전에, 새 키가 낄 갭에 잡는 특수한 갭 락이다. “이 갭 안에 삽입하려 한다”는 의도를 표시하며, data_locks에는 X,GAP,INSERT_INTENTION으로 찍힌다.

• 같은 갭이라도 서로 다른 위치에 넣는 INSERT끼리는 대기하지 않는다. 반면 그 갭에 갭 락이 걸려 있으면 막힌다.
• 삽입을 실제로 막는 충돌은 오직 갭 락 ↔ insert intention 한 조합뿐이다. Gap Lock에서 여러 번 나온 “삽입을 막는 실제 방어선”이 바로 이 락이다.

왜 필요한가 — 같은 갭이라도 위치가 다른 INSERT는 안 막아야 하니까

“갭 락끼리 충돌시키면 되지 왜 별도 락이 필요하냐”는 의문이 들 수 있다. 갭 락 하나로는 표현할 수 없는 동작이 있기 때문이다 — 같은 갭에 서로 다른 위치로 INSERT하는 건 서로 막지 않아야 한다.

INSERT가 “갭 락을 잡고, 갭 락끼리 충돌”하는 방식이라면, 아래처럼 완전히 다른 행을 넣는 INSERT끼리도 같은 갭이라는 이유만으로 직렬화된다.

1
2
3
4

테이블에 id = 10, 20 만 존재 → (10, 20) 은 하나의 큰 갭

T1: INSERT id=12   → (10,20) 갭 락 획득
T2: INSERT id=18   → (10,20) 갭 락 요청 → 충돌 → 대기 (불필요)

넓은 갭 하나에 INSERT가 몰리는 상황(예: AUTO_INCREMENT 끝 구간)에서 처리량이 붕괴한다. 갭 주변에서 실제로 필요한 동작은 셋인데:

락 종류가 하나뿐이면 자기 자신과 “충돌/공존” 둘 중 하나만 정할 수 있어 이 셋을 표현하지 못한다. 그래서 “지키는 락(gap)”과 “넣으려는 락(insert intention)”으로 이름을 나눠 아래 비대칭 호환표를 만든다.

1
2
3
4

          요청 →      Gap Lock        Insert Intention
이미 ↓
Gap Lock             공존             대기 (막힘)      ← 수비가 공격을 막는다
Insert Intention     공존             공존            ← 공격끼리는 위치 다르면 통과

아래 세 쿼리가 각 칸에 대응한다. 모두 REPEATABLE READ, 세팅은 공통이다.

1
2

CREATE TABLE child (id INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO child VALUES (10), (20);   -- (10, 20) 이 하나의 갭

① 수비 ↔ 수비 : 갭 락끼리 공존

1
2
3
4
5
6
7
8
9

-- 트랜잭션 A
START TRANSACTION;
SELECT * FROM child WHERE id BETWEEN 12 AND 18 FOR UPDATE;
-- 0건. 스캔이 20에서 멈추며 (10,20) 갭에 X,GAP

-- 트랜잭션 B (A가 커밋하기 전에)
START TRANSACTION;
SELECT * FROM child WHERE id BETWEEN 12 AND 18 FOR SHARE;
-- 0건. 같은 (10,20) 갭에 S,GAP → 대기 없이 즉시 성공

같은 갭에 X,GAP과 S,GAP이 동시에 찍힌다. 서로 “여긴 넣지 마”라 충돌하지 않는다.

② 공격 ↔ 공격 : insert intention끼리 공존

1
2
3
4
5
6
7

-- 트랜잭션 A
START TRANSACTION;
INSERT INTO child VALUES (12);   -- (10,20) 갭에 insert intention

-- 트랜잭션 B (A가 커밋하기 전에)
START TRANSACTION;
INSERT INTO child VALUES (18);   -- 같은 (10,20) 갭이지만 위치가 다름 → 대기 없이 성공

12와 18은 다른 행이라 서로 막지 않는다. “INSERT = 충돌하는 갭 락”이었다면 B가 A 커밋까지 대기했을 자리다.

③ 수비 ↔ 공격 : 갭 락이 insert intention을 막음

1
2
3
4
5
6
7
8

-- 트랜잭션 A
START TRANSACTION;
SELECT * FROM child WHERE id BETWEEN 12 AND 18 FOR UPDATE;  -- (10,20) 갭 락

-- 트랜잭션 B
START TRANSACTION;
INSERT INTO child VALUES (15);   -- (10,20)에 insert intention 요청 → A의 갭 락에 막혀 대기
-- A가 COMMIT/ROLLBACK 할 때까지 blocked

여기서만 충돌하며, 이게 팬텀을 막는 지점이다. B의 대기는 data_locks에서 INSERT_INTENTION 행의 LOCK_STATUS = WAITING으로 확인된다.

②에서 B가 안 막히고 ③에서 B가 막히는 이 차이가, 갭 락 하나만으로는 못 만들고 락 이름이 둘이어야 하는 이유다. 인용문의 “레코드가 제거되면 여러 트랜잭션의 갭 락을 병합해야 한다”도 결국 수비끼리는 공존해야 한다는 같은 맥락이다.

④ 레코드가 끼면 : 갭은 공존해도 레코드는 충돌

①~③은 모두 갭 얘기였다. 넥스트키 락은 “레코드 + 앞 갭”이므로, 레코드 부분이 겹치면 공존이 성립하지 않는다.

1
2
3
4
5
6
7
8
9
10
11
12

-- 트랜잭션 A
START TRANSACTION;
SELECT * FROM child WHERE id BETWEEN 15 AND 20 FOR UPDATE;
-- 20은 범위 안(20<=20) → 넥스트키 (10,20] : 갭 (10,20) + 레코드 20에 X

-- 트랜잭션 B
START TRANSACTION;
SELECT * FROM child WHERE id BETWEEN 16 AND 18 FOR UPDATE;   -- (10,20) 갭에 X,GAP → 공존 (대기 X)

-- 트랜잭션 C
START TRANSACTION;
SELECT * FROM child WHERE id = 20 FOR UPDATE;                -- 레코드 20에 X 요청 → 대기

A는 갭 (10,20)과 레코드 20을 둘 다 쥔다.

• B는 A와 갭 부분만 겹친다 → X,GAP ↔ X,GAP은 공존 → 통과
• C는 A와 레코드 부분이 겹친다 → X ↔ X(레코드) 충돌 → 대기

“갭 락은 충돌 안 한다”는 말은 넥스트키 락 전체가 아니라 갭 조각에만 해당한다. 레코드 부분은 S/X 규칙대로 정상적으로 충돌한다.

인덱스 구조에서 어디를 잠그나 — 새 키가 낄 자리(= 오른쪽이 될 레코드 앞 갭)

insert intention은 새 키가 정렬 체인의 어디에 끼는지를 찾아, 그 오른쪽이 될 레코드 앞 갭에 요청된다. 갭 락과 마찬가지로 오른쪽 레코드에 앵커되며, data_locks의 LOCK_DATA도 그 레코드 키로 찍힌다.

child = 90, 102 에서 A가 id > 100 FOR UPDATE로 갭 (90, 102]를 쥔 상태에서 B가 INSERT 101을 시도하면, 101은 90과 102 사이에 끼므로 오른쪽 레코드 102 앞 갭에 insert intention을 요청한다. 그 갭엔 A의 갭 락이 있어 대기한다. (실측, RR, MySQL 8.0.46)

1
2
3
4
5
6
7
8

-- A: BEGIN; SELECT * FROM child WHERE id > 100 FOR UPDATE;  → 갭 (90,102] 보유
-- B: BEGIN; INSERT INTO child VALUES (101);                 → 대기

-- 대기 중 data_locks (OBJECT=child)
INDEX_NAME  LOCK_TYPE  LOCK_MODE                 LOCK_STATUS  LOCK_DATA
PRIMARY     RECORD     X                         GRANTED      102          ← A: 넥스트키(레코드 102 + 앞 갭)
PRIMARY     RECORD     X                         GRANTED      supremum ... ← A: (102,∞) 갭
PRIMARY     RECORD     X,GAP,INSERT_INTENTION    WAITING      102          ← B: 102 앞 갭에 삽입 의도, A의 갭에 막힘

• LOCK_DATA가 삽입하려는 키 101이 아니라 오른쪽 레코드 102 로 찍히는 게 핵심이다.
• insert intention은 “값 101 지점”이 아니라 “레코드 102 앞 갭”이라는 자리를 가리킨다.
• 그래서 같은 (90,102) 갭에 95, 101 어떤 값을 넣든 모두 이 갭을 대상으로 하고, 갭 락이 없으면(공격 ↔ 공격) 서로 위치가 달라 공존한다.

DML은 이 잠금들을 어떻게 조합하나

지금까지 본 record/gap/next-key/insert-intention이 실제 쿼리에서 어떻게 조합되는지 정리한다. SELECT ... FOR UPDATE의 “스캔한 범위 + 격리 수준” 모델은 UPDATE·DELETE엔 그대로 적용되고, INSERT만 모델이 다르다.

UPDATE · DELETE — SELECT ... FOR UPDATE와 같은 잠금 스캔

UPDATE/DELETE는 내부적으로 WHERE로 잠금 스캔을 돌려 대상 행을 찾고 고치는 동작이다. 그래서 잠그는 범위가 SELECT ... FOR UPDATE와 동일하게 결정된다. 같은 범위에 같은 락이 찍히는 걸 실측으로 확인할 수 있다. (MySQL 8.0.46, RR)

1
2
3
4
5

t = (10,20,30,40), 둘 다 id BETWEEN 15 AND 25

SELECT ... FOR UPDATE            UPDATE ... SET v=v
  PRIMARY  X       20              PRIMARY  X       20     ← 넥스트키(레코드 20 + 앞 갭)
  PRIMARY  X,GAP   30              PRIMARY  X,GAP   30     ← 경계 레코드: 갭만, 레코드 부분 빠짐

앞 섹션에서 본 요인이 그대로 적용된다.

FOR UPDATE와 다른 점은 둘뿐이다.

• 항상 X로 잡는다 (FOR SHARE 같은 S 선택지가 없다).
• RC 한정: UPDATE는 semi-consistent read를 써서, WHERE에 안 맞는 행은 서버가 보기도 전에 InnoDB가 더 빨리 풀어준다.

공통적으로 UPDATE·DELETE·SELECT ... FOR UPDATE는 스냅샷이 아니라 최신 커밋 버전(current read) 을 대상으로 동작한다. 일반 SELECT만 MVCC 스냅샷을 읽는다.

INSERT — 훑을 범위가 없다 (삽입 지점 + 무결성 검사)

INSERT는 스캔할 WHERE 범위가 없다. 잠그는 지점은 넣을 자리와 무결성 검사 두 갈래다.

• 삽입 자리: 새 키가 낄 갭에 insert intention을 요청한다. 거기 갭 락이 있으면 대기한다. (Insert Intention Locks)
• 새로 넣은 행: 그 레코드에 X 레코드 락을 커밋까지 쥔다 (대개 implicit lock).
• 유니크 인덱스 중복 검사·FK 검사: 충돌 후보 위치를 공유 락으로 잠금 읽기 한다. 이건 RC에서도 갭이 남을 수 있다. (RC에서 FK·중복 키 검사엔 갭이 남을 수 있다)

즉 INSERT도 격리 수준의 영향은 받지만(중복·FK 검사에서 RR이면 갭까지), 잠금 범위가 “스캔한 범위”로 정해지는 게 아니라 키가 떨어지는 위치 + 검사 대상으로 정해진다.

AUTO-INC Lock

AUTO_INCREMENT 컬럼이 있는 테이블에 INSERT할 때, 채번(값 발급)을 위해 InnoDB가 잡는 특수한 테이블 수준 잠금이다.
지금까지의 record/gap/next-key와 달리 인덱스가 아니라 테이블의 채번 카운터를 잠근다.
data_locks에는 LOCK_TYPE=TABLE, LOCK_MODE=AUTO_INC으로 찍힌다.

• 앞의 Lock 종류 (what) 표에서 테이블 레벨 잠금 목록에 AUTO-INC가 들어간 게 이 락이다.
• 단, 잡히느냐/얼마나 오래 쥐느냐는 innodb_autoinc_lock_mode 설정에 따라 다르다.
• MySQL 8.0 기본값(2)에선 아래에서 보듯 아예 안 잡히기도 한다.

왜 필요한가 — 채번 값의 유일성과 (statement 복제) 순서 보장

두 INSERT가 같은 AUTO_INCREMENT 값을 받으면 안 된다. 카운터를 읽고 올리는 짧은 구간을 직렬화해야 값이 겹치지 않는다.

• 가장 단순한(가장 강한) 형태는 INSERT 문장이 끝날 때까지 테이블 락을 쥐는 것이다.
• 그래야 한 문장이 발급한 값이 연속된(consecutive) 구간으로 떨어지고, 특히 statement 기반 복제에서 소스와 레플리카가 같은 값 배정을 재현한다.
• 하지만 이렇게 하면 넓은 갭에 INSERT가 몰릴 때처럼 채번에서 동시성이 붕괴한다.
• 그래서 innodb_autoinc_lock_mode로 채번 값의 예측 가능성 ↔ INSERT 동시성 절충을 고른다.

• MySQL 8.0의 기본값은 2(interleaved) 다. (8.0 이전 기본값은 1) 복제 기본 방식이 statement 기반에서 row 기반으로 바뀌면서 변경됐다.
• bulk insert는 삽입할 행 수를 미리 알 수 없는 INSERT ... SELECT, LOAD DATA 등을, simple insert는 행 수를 미리 아는 단순 INSERT를 가리킨다.
• 값에 간격이 생긴다는 건 곧 AUTO_INCREMENT 값이 반드시 빈틈없이 이어지지는 않는다는 뜻이다.
• 롤백이나 mode 1·2의 과대추정(overestimation) 때문에 중간 값이 버려질 수 있으므로, 연속성을 전제로 로직을 짜면 안 된다.

인덱스 구조에서 어디를 잠그나 — 인덱스가 아니라 테이블 카운터 (mode에 따라 유지 시간이 다름)

AUTO-INC 락은 어떤 인덱스 레코드·갭에도 걸리지 않는다.
잠그는 대상은 테이블의 채번 카운터 하나이며, data_locks에도 TABLE 한 줄로만 나타난다.
얼마나 오래 쥐는지가 mode의 핵심 차이다.

같은 bulk INSERT ... SELECT(약 100만 행)를 돌리며, 실행 중 data_locks의 TABLE 락을 관측한 결과다. (MySQL 8.0.46, 실측)

1
2
3
4
5
6
7
8
9
10

mode 1 (consecutive) — bulk insert가 문장 끝까지 AUTO-INC 락을 쥔다
  LOCK_TYPE  LOCK_MODE
  TABLE      AUTO_INC     ← 채번 카운터, 문장 내내 유지
  TABLE      IX
  TABLE      IS

mode 2 (interleaved, 8.0 기본) — 같은 insert인데 AUTO_INC 락이 없다
  LOCK_TYPE  LOCK_MODE
  TABLE      IX
  TABLE      IS           ← AUTO_INC 없음: 카운터는 즉시 해제되는 mutex로만 보호

• mode 0·1: 문장이 끝날 때까지 TABLE / AUTO_INC 락을 쥔다 → 그 시간 동안 다른 INSERT의 채번이 직렬화된다.
• mode 2: 채번은 카운터 mutex를 아주 짧게 잡았다 풀 뿐이라 data_locks엔 AUTO_INC가 안 잡힌다. 문장 경계와 무관하게 값이 인터리빙될 수 있는 이유다.

즉 AUTO-INC 락은 “행/갭을 지키는 잠금”이 아니라 채번이라는 짧은 임계 구역을 직렬화하는 장치이고, 그 임계 구역을 문장 끝까지 늘릴지(0·1) 최소로 줄일지(2)를 mode가 정한다.

무엇을 막고 무엇을 안 막나. AUTO_INC 락은 같은 테이블에 INSERT하며 채번하려는 다른 트랜잭션하고만 충돌한다. UPDATE/DELETE/SELECT가 잡는 IX·IS와는 호환되므로, mode 0·1이 문장 끝까지 락을 쥐고 있어도 다른 행의 UPDATE/DELETE는 통과한다. (실측, mode 1, 8.0.46 — bulk insert 중 기존 행 UPDATE는 대기 없이 성공)

• 직렬화되는 건 “그 테이블의 채번”뿐이지 테이블 전체 쓰기가 아니다. 채번 순서를 지켜야 하는 범위가 그 테이블 하나라, 충돌도 그 테이블 INSERT끼리로 한정된다.
• 마지막 행(“넣는 중인 행 수정”)이 대기하는 건 AUTO_INC 때문이 아니라 그 행의 레코드 락 때문이다.
• 채번으로 곧 생길 키(예: 아직 안 커밋된 id=20)를 UPDATE하려 하면 여기에 걸린다.

DDL 잠금 (ALTER TABLE) — MDL과 Online DDL

ALTER TABLE에서 실무상 중요한 건 두 층이다. 모든 DDL이 잡는 메타데이터 락(MDL), 그리고 데이터 변경 방식을 정하는 Online DDL 알고리즘(INSTANT / INPLACE / COPY). 테이블이 통째로 멈춘 것처럼 보이는 사고는 대개 데이터 복사가 아니라 MDL 획득에서 난다.

층 1: 메타데이터 락(MDL) — 모든 DDL의 공통 관문

MDL은 “이 테이블의 구조를 지금 바꾸지 마”를 보장하는 테이블 단위 잠금이다. 행 락(record/gap)과 별개 층이며, 격리 수준·innodb_lock_wait_timeout과 무관하게 동작한다.

MDL은 InnoDB(스토리지 엔진) 층이 아니라 그 위 MySQL 서버 층의 잠금이다. record/gap/next-key/insert-intention·AUTO-INC는 전부 InnoDB가 관리하지만(그래서 performance_schema.data_locks에 뜨고 innodb_lock_wait_timeout을 따른다), MDL은 서버가 파서 위 단계에서 관리한다. 그래서 관측 창구도, 대기 타임아웃도 다르다.

DDL이 막히는데 data_locks엔 아무것도 안 보이는 건 이 때문이다 — 원인이 InnoDB 행 락이 아니라 서버 층 MDL이라, metadata_locks를 봐야 한다.

• 모든 DML은 건드리는 테이블에 shared MDL을 잡고 트랜잭션이 끝날 때까지(문장이 아니라 트랜잭션 단위) 유지한다. FOR UPDATE가 아닌 일반 SELECT도 마찬가지다 — 스냅샷 읽기라 행 락(record/gap)은 하나도 안 잡지만, MDL은 잡는다.
• DDL은 exclusive MDL이 필요하다. 그래서 그 테이블에 shared MDL을 쥔 트랜잭션이 하나라도 열려 있으면 DDL은 시작조차 못 하고 대기한다.
• 문제는 대기 중인 exclusive MDL이 뒤따르는 모든 쿼리(읽기 포함)를 막는다는 점이다. MDL 요청은 대체로 FIFO라, 앞에서 배타 락이 대기하면 새 shared 요청도 그 뒤에 줄 선다.

흔한 사고 패턴 중 하나는 — autocommit을 끈 채(또는 BEGIN 후) 일반 SELECT만 하고 커밋을 안 하면, 행 락은 0건인데 shared MDL이 커밋까지 안 풀려 DDL이 막힌다. (실측, 8.0.46)

1
2
3
4
5
6
7
8

SET autocommit=0;
SELECT * FROM t;      -- 일반 스냅샷 읽기, COMMIT 안 함

이 세션이 t에 잡고 있는 것
  performance_schema.data_locks      →  0건            (행 락 없음)
  performance_schema.metadata_locks  →  SHARED_READ    (MDL은 잡힘, 커밋까지 유지)

→ 이 상태에서 ALTER TABLE t ... ALGORITHM=INSTANT 는 lock wait timeout

autocommit이 켜져 있으면 SELECT 직후 트랜잭션이 바로 커밋돼 MDL이 즉시 풀리므로 이 문제가 안 보인다. autocommit을 끄면 트랜잭션이 열린 채 남아 MDL이 계속 살아 있는 게 차이다.

실측 (8.0.46): T1이 트랜잭션을 열어둔 채 SELECT만 해도(shared MDL 보유), 뒤이은 ALGORITHM=INSTANT ALTER조차 대기하고, 그 뒤 평범한 SELECT까지 막힌다.

1
2
3
4
5
6
7
8
9
10
11
12

processlist (같은 테이블 t)
  ID   STATE                             INFO
  109  User sleep                        (T1) BEGIN; SELECT ...; SLEEP  ← 트랜잭션 열려 있음
  110  Waiting for table metadata lock   (T2) ALTER TABLE t ADD COLUMN e INT, ALGORITHM=INSTANT
  111  Waiting for table metadata lock   (T3) SELECT * FROM t           ← INSTANT ALTER 뒤에 막힘

performance_schema.metadata_locks
  LOCK_TYPE          LOCK_STATUS   요청 주체
  SHARED_READ        GRANTED       T1 (열린 트랜잭션의 SELECT)
  SHARED_UPGRADABLE  GRANTED       T2 ALTER
  EXCLUSIVE          PENDING       T2 ALTER  ← 배타 MDL로 승격 시도 중 대기
  SHARED_READ        PENDING       T3 SELECT ← 대기 중인 배타 MDL 뒤에 막힘

DDL의 위험은 대개 데이터 복사가 아니라 이 exclusive MDL을 못 잡고 대기하는 순간이다. ALGORITHM=INSTANT든 online이든, MDL을 못 잡으면 소용없고 그 사이 사이트 전체가 그 테이블에서 멈춘다.

• DDL 전에 긴/방치된 트랜잭션부터 없앤다. (information_schema.innodb_trx, processlist로 확인)
• DDL 세션의 lock_wait_timeout(MDL 대기 타임아웃, 기본 1년)을 짧게 잡아, 못 잡으면 무한 대기 대신 빠르게 실패하게 한다.

층 2: Online DDL 알고리즘 — 데이터를 어떻게 바꾸나

MDL을 잡은 뒤 실제 데이터 변경은 세 알고리즘 중 하나로 처리된다. ALGORITHM= / LOCK= 절로 강제·확인할 수 있다.

각 알고리즘의 능력 경계는 서버가 에러 메시지로 강제한다. (실측, 8.0.46)

1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- ① 세컨더리 인덱스 추가 → INPLACE + 동시 쓰기 허용
ALTER TABLE t ADD INDEX idx_b (b), ALGORITHM=INPLACE, LOCK=NONE;   -- OK

-- ② COPY는 무잠금 불가 (최소 SHARED = 쓰기 차단)
ALTER TABLE t ADD INDEX idx_c (c), ALGORITHM=COPY, LOCK=NONE;
--   ERROR 1846: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.

-- ③ 컬럼 타입 변경은 INSTANT 불가 (테이블 재구성 필요)
ALTER TABLE t MODIFY COLUMN b BIGINT, ALGORITHM=INSTANT;
--   ERROR 1846: Need to rebuild the table to change column type. Try ALGORITHM=COPY/INPLACE.

-- ④ 타입 변경은 INPLACE도 불가 → COPY로만 (= 쓰기 차단)
ALTER TABLE t MODIFY COLUMN b BIGINT, ALGORITHM=INPLACE, LOCK=NONE;
--   ERROR 1846: Cannot change column type INPLACE. Try ALGORITHM=COPY.

운영 테이블 DDL은 ALGORITHM=INPLACE, LOCK=NONE(또는 INSTANT)을 명시하는 게 안전하다. 그 작업이 online으로 안 되면 서버가 위처럼 거부하므로, 아무 절 없이 실행했다가 조용히 COPY로 떨어져 쓰기가 막히는 사고를 미리 막는다.

• 컬럼 추가·삭제는 8.0에서 INSTANT라 데이터 변경 자체는 순식간이다 — 남는 위험은 층 1의 MDL 대기뿐이다.
• 컬럼 타입 변경·PK 변경 등은 COPY로 강등되어 복사 내내 쓰기가 막힌다.
• 큰 테이블이면 pt-online-schema-change·gh-ost 같은 무중단 도구를 검토한다.

Predicate Lock (공간 인덱스)

SPATIAL 인덱스에서 격리 수준을 지원하기 위해 InnoDB가 쓰는 잠금이다. 인덱스 레코드 앞 갭이 아니라 질의의 MBR(최소 경계 사각형) 영역을 잠가, 그 영역에 새 도형이 끼는 팬텀을 막는다. data_locks에는 LOCK_MODE에 PREDICATE가 붙어 찍힌다.

왜 필요한가 — 공간 데이터엔 “다음 키” 순서가 없어 넥스트키를 못 쓴다

넥스트키 락은 “레코드 + 앞 갭”으로 팬텀을 막는데, 이건 값에 하나의 정렬 순서가 있어 “다음 키”를 정할 수 있을 때만 성립한다.

• 2차원 이상 공간 데이터에는 절대적인 순서가 없다. POINT(5 5)의 “다음 점”이 무엇인지 정의할 수 없으므로, 넥스트키의 갭(= 인접한 두 레코드 사이) 개념 자체가 성립하지 않는다.
• 하지만 막아야 할 문제는 넥스트키와 똑같다 — RR/SERIALIZABLE에서 잠금 읽기로 조회한 질의 영역 안에 새 도형이 삽입되어 팬텀이 생기는 것. 순서 대신 영역(MBR) 을 잠그는 방식으로 이 문제를 푼 게 predicate lock이다.

인덱스 구조에서 어디를 잠그나 — 질의의 MBR 영역 (+ 매칭 레코드는 레코드 락)

R-tree 공간 인덱스에서, 쿼리에 쓰인 MBR 영역에 predicate 락을 걸고, 실제 매칭된 행에는 보통의 레코드 락을 건다. 갭 락이 하던 “영역 방어”를 predicate 락이 대신한다.

geo = POINT(1 1), POINT(5 5), POINT(9 9)에서, 사각형 (0 0)~(6 6) 안의 점을 FOR UPDATE로 조회한 결과다. (MySQL 8.0.46, 실측)

1
2
3
4
5
6
7
8
9
10
11

SELECT id FROM geo
  WHERE MBRContains(ST_GeomFromText('POLYGON((0 0,0 6,6 6,6 0,0 0))',0), pt)
  FOR UPDATE;                          -- 매칭: (1 1), (5 5)

data_locks (OBJECT=geo)
  INDEX  LOCK_MODE          LOCK_DATA
  sp     S,PREDICATE        infimum ...      ← 질의 MBR 영역 락 (넥스트키의 갭 대체)
  sp     X,REC_NOT_GAP      ...(POINT 1 1)   ← 매칭 도형의 공간 인덱스 레코드 락
  sp     X,REC_NOT_GAP      ...(POINT 5 5)
  PRIMARY X,REC_NOT_GAP     1               ← 매칭 행의 PK 레코드 락
  PRIMARY X,REC_NOT_GAP     2

predicate 락이 실제로 질의 영역만 방어하는지는 다른 트랜잭션의 INSERT로 확인된다. (실측, RR)

즉 넥스트키가 “레코드 + 앞 갭”으로 막던 팬텀을, 공간 데이터에서는 “질의 MBR과 겹치는 삽입만 막고, 겹치지 않으면 통과” 로 대체한 것이다. 매칭 행 자체의 충돌은 여전히 레코드 락(X,REC_NOT_GAP)이 담당한다.

이 잠금들은 실제로 어떻게 구현돼 있나

“락을 잠근다”는 건 레코드에 무슨 표식을 박거나 포인터를 붙드는 게 아니다. InnoDB는 락 시스템(lock_sys)의 해시 테이블에 락 객체(lock_t)를 매달고, 그 안의 비트맵으로 “이 페이지의 몇 번 레코드”를 가리킨다. 지금까지 본 record/gap/next-key/insert-intention의 차이도 결국 이 비트에 얹는 플래그 차이다.

락 객체는 lock_sys 해시에 사는 lock_t

InnoDB는 전역 락 시스템에 해시 테이블을 하나 두고 락 객체를 여기에 매단다. 해시 키는 레코드가 들어있는 페이지 — (space_id, page_no)다. 그래서 어떤 레코드에 접근할 때 “이 페이지에 걸린 락이 있나?”를 페이지 단위로 찾는다.

락 객체(lock_t) 하나는 대략 이 정보를 담는다.

• 소유 트랜잭션, 대상 인덱스, 대상 페이지 (space_id, page_no)
• 락 모드(S/X)와 타입 플래그(record/gap/next-key/insert-intention)
• 레코드 비트맵 — 그 페이지 안에서 어떤 레코드들을 잠갔는지

레코드를 가리키는 방식: 포인터가 아니라 heap_no 비트맵

락 객체는 레코드의 물리 주소(포인터)도, 키 값도 담지 않는다. 대신 heap_no(그 페이지 안에서 레코드의 슬롯 번호)를 좌표로 쓴다.

InnoDB 16KB 페이지는 이렇게 생겼다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

┌─────────────────────────────────────────────┐
│ File Header (38B)                            │
├─────────────────────────────────────────────┤
│ Page Header / Index Header                   │
├─────────────────────────────────────────────┤
│ Infimum + Supremum (시스템 의사 레코드)       │  heap_no 0, 1
├─────────────────────────────────────────────┤
│ User Records (레코드 힙)                      │  heap_no 2,3,4...
│   - 물리적으로 "삽입된 순서"대로 쌓임          │
│   - 각 레코드는 next-record 포인터로            │
│     "키 오름차순" 논리 체인 연결               │
├─────────────────────────────────────────────┤
│ Free Space                                   │
├─────────────────────────────────────────────┤
│ Page Directory (슬롯 배열, 키 순 정렬)         │  binary search용
├─────────────────────────────────────────────┤
│ File Trailer (8B)                            │
└─────────────────────────────────────────────┘

• heap_no는 레코드 헤더에 박힌 13비트 필드로, 레코드가 페이지 힙에 물리적으로 자리잡은 할당 순번이다. infimum=0, supremum=1, 첫 사용자 레코드=2, 다음=3…
• 키 정렬 순서와는 무관하다. 키 10·20·30을 30→10→20 순으로 넣으면 heap_no는 30이 2, 10이 3, 20이 4가 된다. 물리 저장은 “넣은 순서”, 논리 순서(키 오름차순)는 별도의 next-record 포인터 체인이 유지한다.
• page directory는 별개 구조다. 페이지 끝의 2바이트 슬롯 배열로, 레코드 4~8개마다 대표 하나를 가리키며 키 순으로 정렬돼 있어 페이지 안 탐색(이진 탐색 → 좁힌 그룹 선형 스캔)에 쓰인다. heap_no는 여기 있는 게 아니다.

락 비트맵이 키나 포인터가 아니라 heap_no를 인덱스로 쓰는 이유가 여기 있다. heap_no는 한 페이지 안에서 레코드가 살아있는 동안 안정적인 정수 좌표라, “이 페이지의 heap_no=4 레코드를 잠갔다”를 비트맵의 4번 비트 하나로 표현할 수 있다. 같은 페이지 여러 레코드를 같은 모드로 잠그면 lock_t 하나 + 비트 여러 개로 압축된다(레코드마다 객체를 만들지 않는다).

갭 락엔 “갭 링크” 같은 별도 객체가 없다

갭을 표현하는 별도 자료구조(구간 객체·링크)는 없다. 같은 비트에 타입 플래그를 얹어 구분한다.

그래서 “갭을 잠근다”는 건 결국 그 갭의 오른쪽 레코드 heap_no 비트에 LOCK_GAP 플래그를 켠 lock_t를 만드는 것이다. 앞에서 정리한 “갭은 오른쪽 레코드가 소유한다”가 구현 레벨에서 이렇게 드러난다 — 갭 자체엔 좌표가 없으니 오른쪽 레코드의 heap_no에 기생시킨다. 테이블 끝 갭은 supremum(heap_no 1)의 비트가 담당한다.

충돌 검사와 대기

INSERT·스캔이 어떤 레코드/갭에 락을 걸려 할 때, lock_sys 해시에서 그 페이지의 lock_t들을 뒤져 같은 heap_no 비트에 걸린 기존 락의 모드와 호환성 매트릭스를 비교한다. 충돌하면 대기 lock_t(WAITING)를 큐에 매달고 잠들며, 이 대기가 innodb_lock_wait_timeout(기본 50초)의 대상이다. performance_schema.data_locks가 보여주는 한 행이 대략 이 lock_t의 비트 하나에 해당한다.

정리: 레코드 = (페이지, heap_no) 좌표, 락 = 그 페이지 lock_t의 비트 + 타입/모드 플래그. 포인터도 갭 링크도 아니고 “페이지별 비트맵 + 플래그”다. 테이블 락(AUTO_INC, IS/IX)은 LOCK_TABLE 타입의 별도 lock_t이고, MDL은 아예 InnoDB 밖 서버 층이라 이 구조와 무관하다.

곁다리: 데이터가 아닌 것을 잠그는 락 — Named Lock (GET_LOCK)

지금까지의 락은 전부 실제 데이터 객체(행·갭·테이블)에 걸렸다. GET_LOCK() 계열의 사용자 수준 락(user-level lock, named/advisory lock) 은 성격이 다르다. 어떤 행·테이블과도 연결되지 않은 임의의 문자열 하나를 잠그고, 데이터 접근을 막지 않으며, 오직 같은 이름으로 GET_LOCK을 호출하는 다른 세션만 막는다.

1
2
3
4
5

SELECT GET_LOCK('my_job', 10);   -- 'my_job' 이름을 10초 안에 잡기 시도 (1=성공, 0=타임아웃, NULL=에러)
SELECT IS_USED_LOCK('my_job');   -- 잡고 있는 세션의 connection_id (없으면 NULL)
SELECT IS_FREE_LOCK('my_job');   -- 아무도 안 잡았으면 1
SELECT RELEASE_LOCK('my_job');   -- 풀기
SELECT RELEASE_ALL_LOCKS();      -- 이 세션이 쥔 named lock 전부 풀기

무엇이 다른가 — advisory, 세션 소유, 트랜잭션 무관

record/gap/AUTO_INC/MDL과 나란히 놓으면 성질이 뚜렷하다.

advisory가 핵심이다. GET_LOCK('my_job')을 쥐어도 어떤 테이블의 SELECT/UPDATE도 막지 않는다. 협조하는 클라이언트끼리 같은 이름을 두고 겨루는 순수한 조율 장치다.

실측으로 확인 (mysql:8.0.46)

세션 A가 이름을 쥐고 있는 동안, 다른 세션에서 상태를 보면 행 락은 0건인데 metadata_locks에만 잡힌다.

1
2
3
4
5
6
7
8
9
10
11
12

-- 세션 A: SELECT GET_LOCK('my_job', 0);  → 1 (획득 후 계속 보유)

-- 세션 B에서 관측
SELECT IS_USED_LOCK('my_job');   -- 151  (A의 connection_id)
SELECT IS_FREE_LOCK('my_job');   -- 0

SELECT OBJECT_TYPE, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS
  FROM performance_schema.metadata_locks
  WHERE OBJECT_TYPE='USER LEVEL LOCK';
-- USER LEVEL LOCK | my_job | EXCLUSIVE | GRANTED

SELECT COUNT(*) FROM performance_schema.data_locks;   -- 0  (행 락은 하나도 없다)

세션 간 상호배제와 대기도 그대로 동작한다. A가 my_job을 쥔 채 놓지 않으면, B의 GET_LOCK('my_job', 5)는 5초를 꽉 채워 기다린 뒤 0(타임아웃) 을 반환한다. (호스트 벽시계로 잰 B의 전체 시간 ≈ 5.3초)

한 세션이 여러 이름을 동시에 보유할 수도 있다(5.7+). GET_LOCK('lockA',0), GET_LOCK('lockB',0) 둘 다 1이고 metadata_locks엔 USER LEVEL LOCK이 2건 잡힌다.

트랜잭션과 무관하다는 것도 실측된다. GET_LOCK 후 START TRANSACTION; ROLLBACK; 해도 IS_USED_LOCK은 여전히 그 세션을 가리킨다 — 롤백은 named lock을 풀지 않는다.

어디에 쓰고, 무엇을 조심하나

• 애플리케이션 레벨 상호배제: “이 배치 잡은 동시에 한 워커만” 같은 걸 DB 하나로 보장한다. 여러 인스턴스가 뜬 스케줄러의 중복 실행 방지·리더 선출에 쓴다. 잠글 마땅한 행이 없거나, 트랜잭션 경계와 락 수명을 분리하고 싶을 때 적합하다.
• 반드시 명시적으로 풀어야 한다. 트랜잭션과 무관하므로 롤백해도 안 풀린다. 커넥션 풀에서 락을 쥔 채 커넥션이 반납되면 다음 사용자에게 락이 새어 나간다 — finally에서 RELEASE_LOCK 하거나, 확실하게는 그 커넥션을 폐기한다.
• 노드 로컬이라 복제/클러스터의 여러 노드에 걸친 상호배제엔 못 쓴다. 그래서 분산 락은 보통 Redis/ZooKeeper 등 외부 코디네이터를 쓴다.
• 내부적으로 MDL 서브시스템 위에 구현돼 metadata_locks에 뜨고(그래서 OBJECT_TYPE='USER LEVEL LOCK'), 데드락 감지에도 참여한다(교착이면 NULL 반환). 다만 lock_wait_timeout이 아니라 GET_LOCK의 인자로 준 timeout을 따른다.

참고 자료

• InnoDB Locking (dev.mysql.com/doc/refman/8.4/en/innodb-locking.html)
• InnoDB Locking (dev.mysql.com/doc/refman/8.0/en/innodb-locking.html)
• AUTO_INCREMENT Handling in InnoDB (dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html)
• InnoDB Online DDL Operations (dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html)
• Metadata Locking (dev.mysql.com/doc/refman/8.0/en/metadata-locking.html)
• The physical structure of InnoDB index pages (blog.jcole.us)
• The physical structure of records in InnoDB (blog.jcole.us)
• Locking Functions — GET_LOCK() (dev.mysql.com/doc/refman/8.0/en/locking-functions.html)