816건 넣으려고 16,268행을 잠갔다 — MySQL INSERT SELECT 락 실측

같은 테이블을 읽어서 넣는 INSERT ... SELECT는 REPEATABLE READ에서 읽은 범위 전체를 잠근다. 실측해 보니 816건을 넣으려고 16,268행을 잠갔다. 테이블 전체 행 수보다 큰 값이고, 모든 행에 갭까지 잠갔다는 뜻이다. READ COMMITTED로 내리면 0이 된다. 다만 그게 안전한 전제가 하나 있다.

무슨 작업이었나

예약 슬롯 테이블에 21시 시간대를 추가해야 했다. 기존 행을 읽어서 시각만 바꿔 넣는, 같은 테이블 안에서의 INSERT ... SELECT다.

INSERT INTO RESERVE_SLOT (office_code, slot_date, slot_time, ...)
SELECT office_code, slot_date, '2100', ...
  FROM RESERVE_SLOT
 WHERE ...

7,770건. 흔한 작업이고, 개발에서 잘 돌았다.

돌리기 전에 락을 재 보기로 했다. 결과가 예상과 많이 달랐다.

실측

개발 DB(15,359행 테이블)에서 같은 문장을 격리수준만 바꿔 돌리고, performance_schema.data_locks로 잠긴 행을 셌다.

격리수준	삽입	잠긴 행
REPEATABLE-READ (기본)	816	16,268
READ-COMMITTED	816	0

16,268은 테이블 전체 행 수보다 크다. 모든 행 + 갭을 잠갔다는 뜻이다.

즉 816건을 넣으려고 테이블 전체를 잠갔다.

왜 그런가

REPEATABLE READ에서 INSERT ... SELECT는 읽는 쪽 행에도 공유 넥스트키 락을 건다.

이유는 복제 안전성이다. 문장 기반 복제에서 소스가 읽는 동안 값이 바뀌면 복제본과 결과가 달라진다. 그래서 읽은 범위를 통째로 잠가 고정한다.

문제는 WHERE가 인덱스 앞부분을 못 타면 풀스캔이 되고, 풀스캔의 범위는 테이블 전체라는 것이다.

READ COMMITTED로 내리면 왜 0이 되나

READ COMMITTED에서는 소스 읽기가 일관된 읽기(스냅샷)로 바뀌어 락을 안 건다.

이게 안전한 전제가 하나 있다. binlog_format = ROW. 행 기반 복제면 실제로 바뀐 행이 그대로 전달되므로, 읽는 동안 값이 바뀌어도 복제본이 어긋나지 않는다. 문장 기반이었다면 이 최적화를 쓸 수 없다.

우리 운영은 binlog_format=ROW였다. 그래서 쓸 수 있었다.

진짜 무서운 건 반대편이다

락을 거는 쪽만 보면 "잠깐이니 괜찮다"고 넘어가기 쉽다. 실제 사고는 락을 못 얻어 밀리는 쪽에서 난다.

전체 공유 락을 얻어야 시작하므로, 다른 세션이 그 테이블에 쓰기 트랜잭션을 하나만 열어 둬도 INSERT가 대기에 걸린다. 우리 설정은 이랬다.

innodb_lock_wait_timeout = 300

최대 5분을 기다린 다음 실패한다. 그동안 그 뒤에 줄 선 요청도 전부 밀린다.

과거 운영에서 "쿼리가 갑자기 멈췄다"고 겪은 증상이 이것이었다. 원인을 찾을 때 멈춘 쿼리를 들여다보게 되는데, 그건 피해자다. 거는 쪽을 찾으려면 느린 쿼리 로그를 켜 두는 것이 먼저다. 켜 놓지 않으면 지나간 일은 소급해서 볼 수 없다.

그래서 이렇게 한다

대량 INSERT/UPDATE 전에 세션 설정을 두 줄 넣는다.

SET SESSION transaction_isolation   = 'READ-COMMITTED';  -- 소스 읽기가 스냅샷이 됨
SET SESSION innodb_lock_wait_timeout = 10;               -- 막히면 10초 만에 실패

두 번째 줄이 중요하다. 막혔을 때 빨리 실패하는 게 오래 버티는 것보다 낫다. 5분을 기다리면 그 사이 다른 요청이 다 쌓인다.

한 가지 함정 — max_execution_time은 SELECT 전용이다. INSERT에는 안 걸린다. "타임아웃 걸어 뒀으니 괜찮다"가 성립하지 않는다.

더 안전한 방법

정말 큰 작업이면 스테이징 테이블을 거친다.

CREATE TABLE STG_SLOT AS SELECT ... FROM RESERVE_SLOT WHERE ...;  -- 읽기만
INSERT INTO RESERVE_SLOT SELECT * FROM STG_SLOT;                  -- 소스가 다른 테이블

소스와 대상이 다르니 원본에 락이 안 걸리고, 되돌릴 목록이 테이블로 남는다. 잘못 넣었을 때 무엇을 지워야 하는지가 명확해진다.

정리

• 같은 테이블을 읽어 넣는 INSERT ... SELECT는 REPEATABLE READ에서 읽은 범위 전체를 잠근다.
• WHERE가 인덱스를 못 타면 그 범위는 테이블 전체다.
• READ COMMITTED + binlog_format=ROW면 소스 읽기가 논락이 된다.
• 피해는 밀리는 쪽에서 난다. innodb_lock_wait_timeout을 짧게 두어 빨리 실패시킨다.
• max_execution_time은 SELECT 전용이라 INSERT를 못 막는다.
• 돌리기 전에 재 본다. performance_schema.data_locks로 잠긴 행을 셀 수 있다.

마지막이 결론이다. 이 건은 돌리기 전에 측정했기 때문에 사고가 안 났다.