이번 시간에는 InnoDB가 쿼리마다 어느 구간을 잠그는지를 Docker로 띄운 MySQL과 performance_schema.data_locks로 직접 보면서, 갭 락 때문에 생기는 대기와 deadlock, READ COMMITTED에서 달라지는 점까지 확인하며 배워가는 시간입니다.
예를 들어서 회의실 예약 테이블에 예약 번호 10, 20, 30이 있다고 해볼게요.
한 요청이 예약 번호 15가 있는지 SELECT ... FOR UPDATE로 확인하는 동안, 다른 요청이 17번 예약을 INSERT해요. 17번은 아무도 쓰지 않는 번호인데도 INSERT가 끝나지 않고 기다립니다.
왜 그럴까요? InnoDB는 REPEATABLE READ에서 FOR UPDATE처럼 읽으면서 잠그는 잠금 읽기를 하면, 행만 잠그지 않고 인덱스 레코드 사이의 빈 구간도 잠그기 때문이에요. 이 빈 구간에 거는 잠금을 갭 락(gap lock)이라고 해요.
레코드 하나만 잠그는 잠금은 레코드 락(record lock)이고, InnoDB의 기본 행 잠금은 레코드와 그 앞 빈 구간을 함께 잠그는 넥스트 키 락(next-key lock)이에요.
실습 준비
이 글의 명령은 모두 실습 코드의 mysql-gap-lock/ 디렉터리에서 실행합니다.
compose.yaml: MySQL 8.4 컨테이너setup.sql: 회의실 예약 테이블과 행 세 개funcs.sh: 세션을 번갈아 실행하는 셸 함수(sql,locks,hold,release,waits)
Docker로 MySQL 8.4를 띄울게요. compose.yaml은 이렇습니다.
name: mygap
services:
mysql:
image: mysql:8.4
command: ["mysqld", "--innodb-lock-wait-timeout=3", "--innodb-buffer-pool-size=64M"]
environment:
MYSQL_ROOT_PASSWORD: root
ports:
- "7390:3306"
mem_limit: 1g
name: mygap: compose 프로젝트 이름이에요. 컨테이너 이름이mygap-mysql-1로 정해져요.--innodb-lock-wait-timeout=3: 잠금을 기다리는 최대 시간(초)이에요. 기본값은 50초인데, 실습에서 기다리는 시간을 줄이려고 3초로 낮췄어요.
setup.sql은 회의실 예약 테이블 shop.bookings를 만들어요. 예약 번호 id가 기본 키이고, 회의실 번호 room_id에는 유일하지 않은 인덱스 idx_room이 있어요.
여러 세션을 번갈아 실행해야 해서 셸 함수를 funcs.sh에 모아 두고 source ./funcs.sh로 불러 쓸게요. MYSQL_PWD 환경 변수로 비밀번호를 넘기는 방식은 실습에서만 써 주세요.
sql: 컨테이너 안의 mysql 클라이언트를 실행해요.locks:bookings테이블에 걸린 잠금을performance_schema.data_locks에서 조회해요.hold 이름 "SQL": 다른 세션에서 트랜잭션을 열고 SQL을 실행한 뒤, 커밋하지 않은 채 기다려요.idle로 그 세션이 SQL을 마치고 쉬는 상태가 될 때까지 확인한 다음 돌아와요.release 이름 "SQL":hold로 열어 둔 세션에 마지막 SQL을 보내요. 보통ROLLBACK;이나COMMIT;으로 끝내고, 세션은 그 SQL을 실행한 뒤 닫혀요.waits 개수: 잠금을 기다리는 요청이 그 개수가 될 때까지 기다려요.performance_schema.data_lock_waits의 행 수로 셉니다.
컨테이너를 띄우고 테이블을 만든 뒤 상태를 확인해 볼게요.
docker compose up -d
source ./funcs.sh
until sql -e "SELECT 1" > /dev/null 2>&1; do sleep 2; done
sql < setup.sql
sql -t -e "SELECT * FROM shop.bookings; SELECT @@transaction_isolation, @@innodb_lock_wait_timeout;"
+----+---------+------+
| id | room_id | note |
+----+---------+------+
| 10 | 1 | kim |
| 20 | 2 | lee |
| 30 | 3 | park |
+----+---------+------+
+-------------------------+----------------------------+
| @@transaction_isolation | @@innodb_lock_wait_timeout |
+-------------------------+----------------------------+
| REPEATABLE-READ | 3 |
+-------------------------+----------------------------+
실습 1: 빈 구간에 INSERT하면 기다립니다
세션 A가 없는 15번을 FOR UPDATE로 찾은 채 트랜잭션을 열어 두고, 다른 세션이 17번을 INSERT해 볼게요. INSERT를 백그라운드로 보내고, 기다리기 시작하면 잠금을 봅니다.
hold A "SELECT * FROM shop.bookings WHERE id = 15 FOR UPDATE;"
sql -e "INSERT INTO shop.bookings VALUES (17, 1, 'choi');" &
waits 1
locks
wait $!
release A "ROLLBACK;"
wait
+------+------------+-----------+------------------------+-------------+-----------+
| trx | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+------+------------+-----------+------------------------+-------------+-----------+
| 1826 | NULL | TABLE | IX | GRANTED | NULL |
| 1826 | PRIMARY | RECORD | X,GAP | GRANTED | 20 |
| 1827 | NULL | TABLE | IX | GRANTED | NULL |
| 1827 | PRIMARY | RECORD | X,GAP,INSERT_INTENTION | WAITING | 20 |
+------+------------+-----------+------------------------+-------------+-----------+
ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction
15번 행은 없어서 SELECT 결과는 비어 있지만, 테이블의 의도 잠금 IX와 함께 X,GAP 잠금이 생겼어요. LOCK_DATA가 20이니까 20 바로 앞, 즉 10과 20 사이 빈 구간이 잠긴 거예요.
INSERT를 실행한 트랜잭션에는 같은 20에 인서트 인텐션 락(insert intention lock) X,GAP,INSERT_INTENTION이 WAITING으로 나타났어요. INSERT는 행을 넣기 전에 그 위치의 빈 구간에 이 잠금을 요청하고, 다른 트랜잭션이 같은 구간에 갭 락을 쥐고 있으면 기다리다가 3초 뒤 1205를 받아요.
실습 2: 갭 락끼리는 서로 막지 않아서 생기는 deadlock
"예약 번호 15가 없으면 만든다"는 코드를 흔히 이렇게 짜요. 먼저 FOR UPDATE로 확인하고, 없으면 INSERT하는 거죠.
두 세션이 이 코드를 동시에 실행해 볼게요.
hold A "SELECT * FROM shop.bookings WHERE id = 15 FOR UPDATE;"
hold B "SELECT * FROM shop.bookings WHERE id = 15 FOR UPDATE;"
locks
release A "INSERT INTO shop.bookings VALUES (15, 1, 'kim'); COMMIT;"
waits 1
release B "INSERT INTO shop.bookings VALUES (15, 2, 'lee'); COMMIT;"
wait
sql -t -e "SELECT * FROM shop.bookings WHERE id = 15;"
+------+------------+-----------+-----------+-------------+-----------+
| trx | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+------+------------+-----------+-----------+-------------+-----------+
| 1849 | NULL | TABLE | IX | GRANTED | NULL |
| 1849 | PRIMARY | RECORD | X,GAP | GRANTED | 20 |
| 1850 | NULL | TABLE | IX | GRANTED | NULL |
| 1850 | PRIMARY | RECORD | X,GAP | GRANTED | 20 |
+------+------------+-----------+-----------+-------------+-----------+
ERROR 1213 (40001) at line 2: Deadlock found when trying to get lock; try restarting transaction
+----+---------+------+
| id | room_id | note |
+----+---------+------+
| 15 | 1 | kim |
+----+---------+------+
B의 SELECT는 기다리지 않았어요. 두 트랜잭션이 같은 빈 구간(20 앞)에 X,GAP을 함께 GRANTED로 쥐고 있죠.
갭 락은 INSERT를 막는 게 목적이라서, 갭 락끼리는 X든 S든 서로 충돌하지 않아요.
그래서 A의 INSERT는 B의 갭 락 때문에, B의 INSERT는 A의 갭 락 때문에 기다려야 했어요. 서로를 기다리는 순환이 생겨서 B가 1213을 받고 롤백됐고, A의 INSERT가 들어갔어요.
InnoDB가 어느 쪽을 롤백하는지, deadlock 기록을 어디서 읽는지 궁금하면 deadlock을 다룬 글을 참고하세요.
15번을 지워서 처음 상태로 돌릴게요.
sql -e "DELETE FROM shop.bookings WHERE id = 15;"
실습 3: READ COMMITTED에서는
READ COMMITTED에서는 검색과 인덱스 스캔에 갭 락을 쓰지 않아요. 실습 1을 READ COMMITTED로 다시 해 볼게요.
실습에서는 서버의 기본 격리 수준을 바꿔서, 이후 새로 연결하는 세션이 모두 READ COMMITTED로 시작하게 할게요.
sql -e "SET GLOBAL transaction_isolation = 'READ-COMMITTED';"
sql -t -e "SELECT @@transaction_isolation;"
hold A "SELECT * FROM shop.bookings WHERE id = 15 FOR UPDATE;"
locks
sql -e "INSERT INTO shop.bookings VALUES (17, 1, 'choi');"
release A "ROLLBACK;"
wait
sql -t -e "SELECT * FROM shop.bookings;"
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED |
+-------------------------+
+------+------------+-----------+-----------+-------------+-----------+
| trx | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+------+------------+-----------+-----------+-------------+-----------+
| 1876 | NULL | TABLE | IX | GRANTED | NULL |
+------+------------+-----------+-----------+-------------+-----------+
+----+---------+------+
| id | room_id | note |
+----+---------+------+
| 10 | 1 | kim |
| 17 | 1 | choi |
| 20 | 2 | lee |
| 30 | 3 | park |
+----+---------+------+
15번을 찾아도 테이블의 IX 말고는 잠금이 없어요. REPEATABLE READ에서 기다리다 1205를 받았던 17번 INSERT가 바로 들어갔어요.
READ COMMITTED로 바꾸면 갭 락으로 인한 대기와 deadlock은 줄어요. 하지만 같은 트랜잭션에서 같은 조건으로 읽은 결과가 달라질 수 있어요.
정리
범위: MySQL 8.4(mysql:8.4 이미지), 기본 격리 수준 REPEATABLE READ, innodb_lock_wait_timeout 3초, id 10, 20, 30 세 행짜리 테이블.
- 없는 15를
FOR UPDATE로 찾으면 10과 20 사이에X,GAP이 걸려 17번 INSERT가 3초 뒤 1205를 받았습니다. 없는 값을 잠금 읽기로 확인하는 코드는 그 빈 구간의 INSERT를 막는다고 보고 짜 주세요. - 두 세션이 없는 15번을
FOR UPDATE로 확인하고 INSERT하자 같은 구간의X,GAP을 둘 다 얻은 뒤 서로의 INSERT를 기다려 1213이 났습니다. "확인 후 INSERT"를 잠금 읽기로 짜지 마세요. - 이 글에 싣지 않은 추가 측정에서는 확인 없이 바로 INSERT하자 B는 중복 키를 검사하려고 15번 레코드의 공유 잠금(
S,REC_NOT_GAP)을 기다렸다가 1062를 받았고 deadlock은 없었습니다. 유일 키가 있으면 바로 INSERT하고 1062를 "이미 있음"으로 처리하거나INSERT ... ON DUPLICATE KEY UPDATE를 쓰세요. - READ COMMITTED에서는 없는 15를 찾아도
IX말고는 잠금이 없어 17번 INSERT가 바로 들어갔습니다. 읽은 범위가 그대로라고 믿는 코드가 있으면 READ COMMITTED로 바꾸지 마세요.