· 개발

MySQL InnoDB 갭 락: 없는 행을 잠그면 무엇이 잠기는가

InnoDB가 REPEATABLE READ에서 인덱스 레코드 사이의 간격까지 잠그는 이유, 없는 행을 FOR SHARE로 확인한 뒤 INSERT하면 데드락이 날 수 있는 원리, READ COMMITTED로 바꿀 때의 대가를 MySQL 8.4 문서 기준으로 정리합니다.

mysqlinnodbdatabaseconcurrencylocking

MySQL에서 SELECT ... FOR UPDATE가 0행을 돌려줬는데도 다른 세션의 INSERT가 멈춰 있는 경우가 있습니다. 잠근 행이 하나도 없는데 무엇을 기다리는 걸까요. 답은 InnoDB가 행만이 아니라 인덱스 레코드 사이의 간격(gap) 도 잠근다는 데 있습니다.

이 글은 MySQL 8.4 레퍼런스 매뉴얼을 기준으로 합니다. 매뉴얼에는 다른 버전 페이지도 있고, 세부 동작은 버전에 따라 다를 수 있습니다.

잠금의 대상은 행이 아니라 인덱스다

InnoDB의 레코드 락은 인덱스 레코드에 걸립니다. 테이블에 인덱스를 정의하지 않았더라도 InnoDB는 숨겨진 클러스터드 인덱스를 만들어 그 레코드를 잠급니다.

매뉴얼이 구분하는 잠금은 다음과 같습니다.

  • 레코드 락: 인덱스 레코드 하나를 잠급니다.
  • 갭 락: 인덱스 레코드 사이의 간격, 또는 첫 레코드 앞이나 마지막 레코드 뒤의 간격을 잠급니다.
  • 넥스트 키 락: 레코드 락과 그 레코드 앞 간격의 갭 락을 합친 것입니다.
  • 삽입 의도 락(insert intention lock): INSERT가 행을 넣기 전에 거는 갭 락의 한 종류입니다.

InnoDB의 기본 격리 수준은 REPEATABLE READ입니다. 이 수준에서 평범한 SELECT는 스냅샷을 읽는 비잠금 일관된 읽기(consistent read)라서 잠금을 걸지 않습니다. 잠금 읽기(SELECT ... FOR UPDATE나 FOR SHARE), UPDATE, DELETE는 다릅니다. 스캔한 인덱스 범위를 잠그고, 넥스트 키 락이나 갭 락으로 그 범위에 대한 삽입을 막습니다. 예외는 고유 인덱스를 고유한 조건으로 검색하는 경우로, 이때는 찾은 레코드만 잠급니다. 범위를 잠그는 목적은 팬텀 행 방지입니다. 팬텀은 같은 트랜잭션에서 같은 쿼리를 두 번 실행했을 때 두 번째에만 나타나는 행을 말합니다.

구간으로 보면 쉽다

매뉴얼의 예시처럼 인덱스에 10, 11, 13, 20이 있다면 넥스트 키 락이 덮을 수 있는 구간은 이렇습니다.

(음의 무한대, 10]
(10, 11]
(11, 13]
(13, 20]
(20, 양의 무한대)

마지막 구간은 실제 레코드가 아니라 supremum 의사 레코드에 걸립니다. supremum은 인덱스의 어떤 값보다도 큰 가상의 레코드라서, 이 넥스트 키 락은 사실상 가장 큰 값 뒤의 간격만 잠급니다.

매뉴얼의 예시를 그대로 따라가 보겠습니다. child 테이블의 id에 90과 102가 있습니다.

-- 세션 A
START TRANSACTION;
SELECT * FROM child WHERE id > 100 FOR UPDATE;

이 쿼리는 102를 잠그는 것으로 끝나지 않습니다. 102 앞의 간격(90과 102 사이)과 102 뒤의 간격까지 잠그기 때문에, 세션 B가 INSERT INTO child (id) VALUES (101);을 실행하면 삽입 의도 락을 얻지 못하고 기다립니다. 매뉴얼은 이 잠금이 100보다 큰 모든 id의 삽입을 막는다고 설명합니다. 그래서 A가 같은 SELECT를 다시 실행해도 새 행이 나타나지 않습니다.

갭 락끼리는 충돌하지 않는다

여기서 많이 헷갈리는 성질이 나옵니다. 매뉴얼은 갭 락을 “순수하게 억제적(purely inhibitive)“이라고 표현합니다. 갭 락의 유일한 목적은 다른 트랜잭션이 그 간격에 삽입하지 못하게 하는 것입니다.

  • 한 트랜잭션이 어떤 간격에 공유 갭 락을 가진 상태에서 다른 트랜잭션이 같은 간격에 배타 갭 락을 가질 수 있습니다.
  • 공유 갭 락과 배타 갭 락 사이에 차이가 없고, 서로 충돌하지 않습니다.

반면 삽입 의도 락은 다른 트랜잭션의 갭 락과 충돌해 기다립니다. 삽입 의도 락끼리는 같은 간격이라도 위치가 다르면 서로 기다리지 않습니다. 4와 7 사이에 두 트랜잭션이 각각 5와 6을 넣는 경우가 매뉴얼의 예시입니다.

이 두 규칙을 합치면 서로를 기다리는 순환이 생길 재료가 갖춰집니다.

“없으면 넣기”가 데드락이 될 수 있는 과정

매뉴얼은 넥스트 키 락으로 애플리케이션 수준의 중복 검사를 구현할 수 있다고 설명합니다. 공유 모드로 읽어서 중복이 없으면, 읽는 동안 걸린 넥스트 키 락이 그 사이에 다른 트랜잭션이 중복 행을 넣는 것을 막아 준다는 것입니다. 매뉴얼의 표현으로는 “무언가가 존재하지 않음”을 잠그는 방법입니다.

중복은 실제로 막힙니다. 문제는 두 트랜잭션이 동시에 같은 검사를 할 때 생길 수 있습니다. email에 고유하지 않은 인덱스가 있는 테이블을 예로 들어 보겠습니다.

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  KEY idx_email (email)
) ENGINE = InnoDB;

아래 순서는 문서화된 규칙에서 따라가 본 것이고, 실제 서버에서 캡처한 결과는 아닙니다. 격리 수준이 REPEATABLE READ이고, SELECT가 실제로 idx_email을 타며(EXPLAIN으로 확인), 해당 이메일 행이 아직 없고, 기존 인덱스 값의 배치 때문에 두 세션의 잠금이 새 항목이 들어갈 바로 그 간격에 걸린다고 가정합니다.

-- 세션 A
START TRANSACTION;
SELECT id FROM users WHERE email = 'kim@example.com' FOR SHARE;  -- 0행

-- 세션 B
START TRANSACTION;
SELECT id FROM users WHERE email = 'kim@example.com' FOR SHARE;  -- 0행, 갭 락끼리는 호환

-- 세션 A
INSERT INTO users (email) VALUES ('kim@example.com');  -- B의 갭 락 때문에 대기할 수 있음

-- 세션 B
INSERT INTO users (email) VALUES ('kim@example.com');  -- A의 갭 락 때문에 대기할 수 있음 → 순환
  1. 두 SELECT ... FOR SHARE는 같은 간격을 잠급니다. 갭 락끼리는 충돌하지 않으므로 어느 읽기도 기다리지 않습니다.
  2. A의 INSERT는 삽입 의도 락이 필요한데, B가 가진 갭 락과 충돌해 기다립니다.
  3. B의 INSERT도 같은 이유로 A를 기다립니다.

잠금이 이렇게 겹치면 두 트랜잭션은 서로를 기다리게 됩니다. InnoDB는 이 순환을 감지해 한쪽을 롤백하고 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction을 돌려줍니다. 매뉴얼의 데드락 예시에 나오는 것과 같은 형태의 오류입니다. 접근 경로나 주변 값, 잠금 범위가 다르면 대기 양상이 달라지거나 순환이 아예 생기지 않을 수도 있습니다. 실제 상황은 아래의 방법으로 직접 확인해 보세요.

어느 쪽이든 중복 행은 생기지 않습니다. 대신 동시 요청 하나가 데드락 오류로 실패할 수 있습니다. 이 패턴을 쓰는 코드라면 오류 1213을 잡아 트랜잭션이 롤백된 것으로 처리하고, 적절한 경우 트랜잭션 전체를 횟수 제한을 둔 채 다시 실행해야 합니다.

비슷한 데드락은 고유 키에서도 납니다. 매뉴얼의 SQL 문별 잠금 설명에 따르면 INSERT가 중복 키 오류를 내면 그 레코드에 공유 락을 겁니다. 세 세션이 같은 기본 키 값을 넣다가 첫 세션이 롤백하면, 남은 두 세션이 모두 공유 락을 얻은 뒤 서로 배타 락을 기다리며 데드락이 됩니다. INSERT ... ON DUPLICATE KEY UPDATE는 공유 락 대신 배타 락을 겁니다.

갭 락이 생기지 않는 경우

모든 잠금 읽기가 간격을 잠그지는 않습니다.

  • 고유 인덱스로 고유한 행 하나를 찾을 때는 찾은 인덱스 레코드만 잠그고 앞의 간격은 잠그지 않습니다. 예를 들어 id가 고유 인덱스라면 WHERE id = 100은 레코드 락만 사용합니다.
  • 단, 여러 열로 된 고유 인덱스의 일부 열만 검색 조건에 쓰면 갭 락이 걸립니다.
  • 검색 열에 인덱스가 없거나 고유하지 않은 인덱스라면 앞의 간격도 잠급니다.

마지막 항목과 관련해 놓치기 쉬운 점이 있습니다. 잠금 읽기, UPDATE, DELETE는 WHERE 조건과 상관없이 스캔한 모든 인덱스 레코드에 잠금을 겁니다. 적절한 인덱스가 없어 전체 테이블을 스캔하면 테이블의 모든 행이 잠기고, 다른 사용자의 삽입도 전부 막힙니다. 갭 락이 예상보다 넓게 느껴진다면 먼저 EXPLAIN으로 어떤 인덱스를 타는지 확인해 볼 만합니다.

READ COMMITTED로 바꾸면

격리 수준 문서에 따르면 READ COMMITTED에서는 검색과 인덱스 스캔의 갭 락이 꺼집니다. 갭 락은 외래 키 제약 검사와 중복 키 검사에만 쓰입니다. 위의 “없으면 넣기” 순환은 검색 단계의 갭 락에 기대고 있으므로 이 수준에서는 같은 방식으로 생기지 않습니다.

대가도 분명합니다.

  • 다른 세션이 간격에 새 행을 넣을 수 있으므로 팬텀이 생길 수 있습니다. 위의 FOR SHARE 중복 검사는 더 이상 중복을 막지 못합니다.
  • READ COMMITTED에서는 행 기반 바이너리 로깅만 지원됩니다. binlog_format=MIXED라면 서버가 자동으로 행 기반 로깅을 사용합니다.
  • UPDATE와 DELETE는 WHERE 조건을 평가한 뒤 조건에 맞지 않는 행의 레코드 락을 풉니다.

따라서 격리 수준을 바꾸는 것은 데드락 해결책이라기보다 보장의 교환입니다. 중복을 막는 책임을 고유 제약 조건으로 옮긴 다음에 고려하는 것이 순서입니다. email에 UNIQUE 제약이 있다면 조회 없이 바로 INSERT하고 중복 키 오류를 처리하는 방식이 가능합니다.

잠금을 직접 보는 방법

추측보다 관찰이 빠릅니다.

performance_schema.data_locks는 보유 중이거나 요청 중인 잠금을 보여 줍니다.

SELECT ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME,
       LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
FROM performance_schema.data_locks;
  • LOCK_MODE가 X,GAP처럼 GAP을 포함하면 갭 락입니다. 매뉴얼의 데드락 예시에는 S,REC_NOT_GAP처럼 간격을 포함하지 않는 레코드 락도 나옵니다.
  • LOCK_STATUS는 GRANTED 또는 WAITING입니다.
  • LOCK_DATA가 supremum pseudo-record라면 가장 큰 값 뒤의 간격이 잠긴 것입니다.
  • 어떤 요청이 어떤 잠금에 막혀 있는지는 performance_schema.data_lock_waits에서 볼 수 있습니다.

SHOW ENGINE INNODB STATUS에서는 다음과 같은 문구로 나타납니다. lock_mode X locks rec but not gap은 레코드 락, 수식어 없는 lock_mode X는 넥스트 키 락, lock_mode X locks gap before rec insert intention waiting은 갭 락 때문에 기다리는 삽입 의도 락입니다. 이 명령은 가장 최근 데드락의 원인도 보여 줍니다. 모든 데드락을 오류 로그에 남기려면 innodb_print_all_deadlocks를 켜고, 디버깅이 끝나면 끄라고 매뉴얼은 권합니다.

정리하면

  • REPEATABLE READ의 잠금 읽기는 찾은 행뿐 아니라 스캔한 범위의 간격도 잠급니다. 0행을 돌려준 쿼리도 삽입을 막을 수 있습니다.
  • 갭 락끼리는 충돌하지 않고 삽입만 막습니다. 그래서 두 트랜잭션이 같은 간격을 확인한 뒤 둘 다 삽입하면 데드락이 날 수 있습니다.
  • 고유 인덱스로 행 하나를 찾는 경우에는 갭 락이 없습니다. 인덱스가 없으면 반대로 테이블 전체가 잠깁니다.
  • READ COMMITTED는 검색 갭 락을 없애지만 팬텀 방지도 함께 없앱니다.

매뉴얼의 데드락 대응 지침은 데드락으로 실패한 트랜잭션을 다시 실행할 준비를 항상 해 두라고 말합니다. 갭 락을 이해하면 어떤 코드 경로에서 그 재시도가 필요해질 가능성이 큰지 미리 알 수 있습니다.

블로그로