없는 행에 SELECT FOR UPDATE를 걸면 MySQL이 교착되는 이유

한서윤 · 2026년 10월 30일 · 트랜잭션과 잠금

앱에 푸시 알림 설정 화면을 새로 붙인 적이 있다. 회원마다 설정 행이 하나씩 필요했는데, 기존 회원 몫을 미리 만들어 두는 대신 화면을 처음 열 때 만들기로 했다. 트랜잭션을 열고 SELECT ... FOR UPDATE로 그 회원의 행을 찾은 뒤, 없으면 INSERT하는 흐름이었다. 잠금을 걸었으니 요청 두 개가 겹쳐도 하나는 기다릴 거라고 믿었다.

배포 다음 날 아침, 에러 알림 채널에 Deadlock found when trying to get lock이 줄지어 쌓여 있었다. 앱이 화면을 열 때 설정 조회 API를 두 번 부르는 버그가 따로 있었고, 같은 회원 번호로 동시에 들어온 그 두 요청이 서로를 붙잡고 넘어진 것이다. 행 하나 만드는 일에서 교착이 날 줄은 몰라서 꽤 당황했다.

원인은 MySQL InnoDB가 REPEATABLE READ에서 없는 행을 잠그는 방식에 있었다. 재현 순서와 고쳐 보려 시도한 방법 세 가지를 적어 둔다.

없는 행을 잠그면 실제로 잠기는 것

InnoDB는 행이 아니라 인덱스 레코드에 잠금을 건다. 찾는 값이 인덱스에 없으면 잠글 레코드가 없으니, 그 값이 들어갈 앞뒤 레코드 사이의 틈을 잠근다. 이게 갭 락이다. 공식 문서는 갭 락을 “purely inhibitive”라고 설명한다. 다른 트랜잭션이 그 틈에 INSERT하지 못하게 막는 것 말고는 하는 일이 없다는 뜻이다.

문제는 그다음이다. 갭 락끼리는 충돌하지 않는다. 같은 갭에 여러 트랜잭션이 갭 락을 함께 잡을 수 있고, 공유와 배타의 구분도 없다. FOR UPDATE라는 이름을 보고 배타 잠금을 기대했지만, 행이 없을 때 손에 쥐는 것은 남과 나눠 갖는 갭 락이었다.

INSERT는 행을 넣기 전에 삽입 의도 잠금(insert intention lock)부터 요청한다. 이것도 갭 락의 일종인데, 같은 갭에 남의 갭 락이 있으면 기다려야 한다. 삽입 의도 잠금끼리는 위치만 다르면 서로 막지 않으니, 길을 막는 쪽은 앞서 SELECT ... FOR UPDATE가 남긴 갭 락이다.

오래된 문제이기도 하다. 2007년 1월에 올라온 MySQL 버그 25847번이 바로 이 교착을 보고했다. InnoDB에는 술어 잠금(predicate locking)이 없어 존재하지 않는 레코드 하나만 골라 잠글 수 없다는 답이 달렸고, 보고는 지금도 기능 요청으로 남아 있다.

두 세션으로 재현하기

스테이징 DB에 테이블을 작게 만들고 터미널 두 개로 순서를 맞춰 돌렸다. user_id에는 처음 설계 그대로 일반 인덱스만 걸었다.

CREATE TABLE notification_settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  push_enabled TINYINT NOT NULL DEFAULT 1,
  KEY idx_user (user_id)
) ENGINE=InnoDB;

INSERT INTO notification_settings (user_id) VALUES (10), (20);

-- 세션 A
START TRANSACTION;
SELECT * FROM notification_settings WHERE user_id = 15 FOR UPDATE;  -- Empty set

-- 세션 B
START TRANSACTION;
SELECT * FROM notification_settings WHERE user_id = 15 FOR UPDATE;  -- Empty set, 기다리지 않는다

-- 세션 A
INSERT INTO notification_settings (user_id) VALUES (15);  -- 여기서 멈춘다

-- 세션 B
INSERT INTO notification_settings (user_id) VALUES (15);
-- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

세션 B의 SELECT가 기다리지 않고 빈 결과를 돌려준 순간 판은 끝나 있었다. 두 세션 모두 user_id 10과 20 사이 갭에 갭 락을 쥐고 있으니, A의 INSERT는 B의 갭 락에, B의 INSERT는 A의 갭 락에 막힌다. InnoDB가 이 고리를 감지해 B를 롤백하고 나서야 A의 INSERT가 끝난다.

잠금을 눈으로 확인하기

A가 멈춰 있는 동안 세 번째 창에서 잠금 목록을 조회하면 상황이 그대로 보인다.

SELECT engine_transaction_id, index_name, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks
WHERE object_name = 'notification_settings';

두 트랜잭션이 idx_user의 같은 레코드(user_id 20)에 GAP이 붙은 X 잠금을 GRANTED 상태로 들고 있고, A 쪽에는 INSERT_INTENTION이 붙은 잠금 하나가 WAITING으로 걸려 있다. 처음 SHOW ENGINE INNODB STATUS의 교착 기록에서 lock_mode X locks gap before rec insert intention waiting이라는 줄을 봤을 때는 20번 행이 잠긴 줄 알았는데, 실은 20번 레코드 앞의 틈이 잠겼다는 뜻이었다.

유니크 인덱스: 잠금 대신 제약으로 막기

먼저 user_id를 유니크 인덱스로 바꿨다. 회원당 설정 행 하나라는 규칙은 처음부터 스키마가 지켰어야 했다. 인덱스만 바꾸고 코드를 그대로 두었더니 똑같이 1213이 났다. 허탈했다. 유니크 인덱스로 유일한 값을 찾을 때 레코드만 잠근다는 규칙은 행을 찾았을 때 이야기고, 값이 없으면 여전히 갭을 잠근다.

유니크 인덱스가 쓸모 있는 건 다른 데서다. 중복을 DB가 거절해 주니 먼저 잠그고 확인할 필요가 없다. SELECT ... FOR UPDATE를 빼고 곧장 INSERT한 뒤, 1062 중복 키 에러가 나면 이미 행이 있다고 보고 다시 읽으면 된다.

빈틈은 있다. 중복 키 에러가 나면 InnoDB는 중복 레코드에 공유 잠금을 거는데, 세 세션이 같은 키를 INSERT하다 첫 세션이 롤백하면 남은 둘이 서로의 공유 잠금에 걸려 교착에 빠진다는 예가 문서에 나온다. 에러로 흐름을 나누는 코드도 읽기 불편하다.

READ COMMITTED: 갭 락을 끄는 대가

격리 수준을 READ COMMITTED로 내리면 잠금 읽기, UPDATE, DELETE는 인덱스 레코드만 잠그고 갭은 잠그지 않는다. 갭 락은 외래 키 검사와 중복 키 검사에만 쓰인다. 위 재현 순서에서 A의 INSERT는 멈추지 않는다.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- ...

일반 인덱스 상태에서 이걸 돌려 보니 교착은 사라졌지만 두 INSERT가 모두 성공해 같은 회원의 행이 두 줄 생겼다. 갭 락이 없으면 없는 행에 건 FOR UPDATE는 아무것도 잠그지 않는다. READ COMMITTED만으로는 부족하고 유니크 인덱스가 같이 있어야 중복이 막힌다.

조건도 따라온다. 문서에 따르면 READ COMMITTED는 행 기반 바이너리 로그만 지원하고, binlog_format=MIXED라면 서버가 알아서 행 기반으로 기록한다. 팬텀 행이 생길 수 있다는 점도 받아들여야 한다. 전역 설정을 바꾸면 갭 락에 기대던 다른 쿼리가 조용히 달라질 수 있어서 이 트랜잭션에만 적용했다.

INSERT … ON DUPLICATE KEY UPDATE: 한 문장으로 끝내기

유니크 인덱스가 있으면 확인과 삽입을 한 문장으로 묶을 수 있다. MySQL 8.0.19부터는 행 별칭을 쓸 수 있고, 8.0.20부터 VALUES() 함수는 폐기 예정이다.

INSERT INTO notification_settings (user_id, push_enabled)
VALUES (15, 1) AS new
ON DUPLICATE KEY UPDATE push_enabled = new.push_enabled;

중복이 나면 공유 잠금이 아니라 배타 잠금이 걸린다. 기본 키 중복이면 레코드 잠금이고, 보조 유니크 키 중복이면 갭까지 덮는 배타 넥스트키 잠금이라, 요청이 몰리면 교착이 완전히 사라진다고 장담하기는 어렵다. 유니크 키가 여러 개인 테이블에서는 문장 기반 복제에 안전하지 않은 문장으로 분류된다.

영향받은 행 수도 한 번 걸렸다. 새로 넣으면 1, 기존 행을 바꾸면 2, 값이 그대로면 0이다. 나는 1이 아니면 실패로 처리하는 코드를 그대로 두었다가 멀쩡한 요청이 실패 로그에 쌓이는 걸 보고서야 알았다.

지금 쓰는 방식

요즘은 행이 없을 수 있는 자리에 SELECT ... FOR UPDATE를 먼저 걸지 않는다. 하나만 있어야 하는 것은 유니크 인덱스로 박아 두고, 만드는 쪽은 ON DUPLICATE KEY UPDATE로 한 문장에 끝낸다. 기존 값을 덮으면 안 될 때만 그냥 INSERT하고 1062를 받는다. 격리 수준은 기본값 REPEATABLE READ로 둔다.

교착을 0으로 만들 수는 없어서 1213을 받으면 트랜잭션 전체를 한두 번 다시 돌리는 재시도도 붙였다. 이렇게 바꾸고 한 주가 지나도록 알림 채널이 조용했을 때 비로소 안도했다.

그 일 뒤로 FOR UPDATE를 이렇게 이해한다. 있는 행을 붙잡는 도구이지, 없는 행의 자리를 맡아 두는 도구는 아니다. 자리를 맡아야 한다면 그 일은 잠금보다 제약이 더 확실하게 해낸다.