MySQL Gap Lock (두번째 이야기)
Why ?
DBMS 서버를 공부하는 데 있어서, “왜?”라는 질문을 가지는 것은 매우 중요해요. 그런데 MySQL 서버를 공부하다 보면, 풀리지 않는 의문들로 머리 속이 복잡해지는 경우가 많아요. 어느 MySQL 전문가는 이런 말을 할 정도로 MySQL 서버에는 수많은 의문들이 있는 것 같아요. 😵💫
MySQL 서버에 대해서 너무 깊이 생각하면, 어느 순간 여러분은 의문 투성이 지옥에서 벗어나지 못하게 될 수 있어요. 왜 MySQL 서버는 Supremum Gap lock을 필요로 할까? 왜 Redundant Gap lock을 사용할까? 왜 Implicit lock을 사용하고 다시 이를 Explicit lock으로 변환할까? 등등 질문들이 끝없이 떠오르게 되고, 결국 여러분은 이런 풀리지 않는 질문들속에서 헤어나오지 못하게 될 거에요. 부디 질문을 멈추고 MySQL 서버로부터 여러분 자신을 구하세요. !!
오늘은 MySQL 서버의 잠금에서 미로 같은 혼란을 자주 유발하는 Gap lock에 대한 이슈 하나를 좀 살펴보려고 해요.
이해할 수 없는 잠금 현상
REPEATABLE-READ 격리 수준을 사용하는 MySQL 서버에서 간단한 잠금 테스트를 해 보려고 해요. 우선 아래와 같이 레코드 3건을 가지는 테이블을 생성해요.
SESSION-1 > CREATE TABLE lock_supremum (
id int NOT NULL AUTO_INCREMENT,
fd1 char(250) NOT NULL,
PRIMARY KEY (id)
);
SESSION-1 > INSERT INTO lock_supremum VALUES (3,'dummy-3'),
(5,'dummy-5'),
(7,'dummy-7');
SESSION-1 > SELECT * FROM lock_supremum;
+----+---------+
| id | fd1 |
+----+---------+
| 3 | dummy-3 |
| 5 | dummy-5 |
| 7 | dummy-7 |
+----+---------+이제 아래 2개 문장을 실행해서, 각 문장이 어떤 잠금을 가지는지 확인 & 비교해보려고 해요.
1. SELECT * FROM lock_supremum WHERE id BETWEEN 3 AND 5 FOR UPDATE;
SESSION-1 > BEGIN;
SESSION-1*> SELECT * FROM lock_supremum WHERE id BETWEEN 3 AND 5 FOR UPDATE;
+----+---------+
| id | fd1 |
+----+---------+
| 3 | dummy-3 |
| 5 | dummy-5 |
+----+---------+
SESSION-3 > SELECT * FROM performance_schema.data_locks;
+-----------------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+------------+-----------+---------------+-------------+-----------+
| 44585 | NULL | TABLE | IX | GRANTED | NULL |
| 44585 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 3 |
| 44585 | PRIMARY | RECORD | X | GRANTED | 5 |
+-----------------------+------------+-----------+---------------+-------------+-----------+2. SELECT * FROM lock_supremum WHERE id BETWEEN 3 AND 7 FOR UPDATE;
SESSION-1 > BEGIN;
SESSION-1*> SELECT * FROM lock_supremum WHERE id BETWEEN 3 AND 7 FOR UPDATE;
+----+---------+
| id | fd1 |
+----+---------+
| 3 | dummy-3 |
| 5 | dummy-5 |
| 7 | dummy-7 |
+----+---------+
SESSION-3 > SELECT * FROM performance_schema.data_locks;
+-----------------------+------------+-----------+---------------+-------------+------------------------+
| ENGINE_TRANSACTION_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+------------+-----------+---------------+-------------+------------------------+
| 44586 | NULL | TABLE | IX | GRANTED | NULL |
| 44586 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 3 |
| 44586 | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record |
| 44586 | PRIMARY | RECORD | X | GRANTED | 5 |
| 44586 | PRIMARY | RECORD | X | GRANTED | 7 |
+-----------------------+------------+-----------+---------------+-------------+------------------------+1번 쿼리는 id BETWEEN 3 AND 5 범위의 레코드를 FOR UPDATE로 잠금을 걸면서 SELECT 했으며, 2번 쿼리도 id BETWEEN 3 AND 7 범위의 레코드를 동일하게 FOR UPDATE 로 잠금을 걸면서 SELECT 하는 쿼리에요. 그런데 2번 쿼리에서 조금 이상한 것은 supremum psuedo-record에 대해서 X-lock 을 걸고 있다는 거에요. 1번 쿼리는 정확히 조건 범위의 레코드만 X-lock 이 걸린 것을 확인할 수 있는데, 2번 쿼리는 id=3부터 7까지의 레코드뿐만 아니라, id=7 다음에 위치한 supremum psuedo-record 에도 잠금을 걸고 있어요.
이 현상이 무슨 문제가 되나요 ?
위에서 살펴본 것과 같이 테이블의 마지막(Primary Key 정렬 순서상) 레코드 뒤에 위치한 supremum psuedo-record에 걸리는 X-lock이 어떤 문제를 유발하는지 간단히 살펴볼게요. 동일한 SELECT .. FOR UPDATE 문장이 실행중일때 다른 세션에서 INSERT 문장을 실행해보면, 쉽게 supremum에 걸려있는 X-lock의 영향 범위를 확인할 수 있어요.
SESSION-1 > BEGIN;
SESSION-1*> SELECT * FROM lock_supremum WHERE id BETWEEN 5 AND 7 FOR UPDATE;
SESSION-2 > INSERT INTO lock_supremum VALUES (9, 'dummy-9');Session-1 트랜잭션이 COMMIT 되지 않으면, Session-2의 INSERT 문장은 끝나지 않고 계속 잠금 대기를 해요. 이때 잠금 상태는 performance_schema.data_locks 테이블을 SELECT 해보면 쉽게 확인할 수 있어요.
SESSION-3 > SELECT * FROM performance_schema.data_locks;
+-----------------------+------------+-----------+--------------------+-------------+------------------------+
| ENGINE_TRANSACTION_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+------------+-----------+--------------------+-------------+------------------------+
| 44587 | NULL | TABLE | IX | GRANTED | NULL |
| 44587 | PRIMARY | RECORD | X,INSERT_INTENTION | WAITING | supremum pseudo-record |
| 44586 | NULL | TABLE | IX | GRANTED | NULL |
| 44586 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 5 |
| 44586 | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record |
| 44586 | PRIMARY | RECORD | X | GRANTED | 7 |
+-----------------------+------------+-----------+--------------------+-------------+------------------------+위의 결과를 보면, Session-2의 44587번 트랜잭션이 supremum pseudo-record에 대해서 X,INSERT_INTENSION 잠금을 대기( Waiting)하고 있다는 것을 알 수 있어요. 여기에서 잠금 대기가 발생하는 이유는 X,INSERT_INTENSION 잠금과 X 잠금은 서로 호환되지 않기 때문이에요. 즉, 여기에서는 Session-1의 트랜잭션이 완료( COMMIT 또는 ROLLBACK )되어야만 Session-2의 INSERT 는 완료될 수 있다는 거죠. 만약 Session-1이 innodb_lock_wait_timeout (50초) 보다 오랜 시간동안 잠금을 해제하지 않는다면, Session-2는 Innodb lock wait timeout exceeded 라는 에러를 발생하게 될 거에요.
여기까지는 “당연한 것 아닌가?”라고 생각하실 수 있어요. 그런데 supremum psuedo-record 에서 supremum은 인덱스의 키 값이 가질 수 있는 가장 큰 가상의 값을 의미해요. 즉, supremum pseudo-record의 X-lock은 테이블의 인덱스에 (현재) 존재하는 가장 큰 값 이후의 모든 간격을 잠그는 잠금인 거에요. 그래서 테이블에 잠재적으로 INSERT될 수 있는 모든 값들이 저장될 간격을 잠궈버리면서, 동시 처리 성능에 상당히 부정적인 영향을 미치게 되는 거에요. 특히 다음에 저장될 값이 항상 증가하기만 하는AUTO_INCREMENT Primary Key를 사용하는 테이블에서는 이 현상의 영향 범위가 훨씬 더 크게 느껴질 수 있어요.
만약 여러분이 사용하는 테이블이 큐(Queue)와 비슷한 처리를 하는 형태로 사용된다면, 많은 쓰레드에서 이 테이블에 INSERT를 함과 동시에 다른 쓰레드에서는 레코드를 SELECT 하게 되는거죠. 이때 Subscriber는 동기화 처리를 위해서 SELECT .. FOR UPDATE 구문을 사용하게 되는데, 이때 SELECT .. FOR UPDATE 가 실행중인 동안에는 INSERT 를 동시에 실행하지 못하게 되는 동시 처리 문제가 발생하게 되는거에요.
이 현상은 왜 발생하나요 ?
이제 SELECT .. WHERE id BETWEEN 3 AND 7 FOR UPDATE 구문이 왜 id>7 범위의 간격(Gap)에 잠금을 걸어야 하는지 의문이 생기게 되었을거에요. 충분히 납득할만한 이유가 있는 것일까요 ? 우선 이 질문에 대한 답변을 한다면, “그렇지 않다"라고 생각해요. 그래서 MySQL 버그 레포트(사실은 Bug report가 아니라 Feature request를 했던 것이지만)를 했지만, MySQL 담당자는 “버그 아님"이라는 답변만 해주었어요. 그리고 이 잠금이 필요한 이유에 대해서 설명을 부탁했지만, 좋은 답변은 듣지 못했어요.
이 문제에 대해서 “Why?”를 고민하는데 많은 주말과 퇴근 후 시간들을 투자하고서야, 비로소 나름의 (조금은 허무한) 답변을 찾게 되었어요.
처음에는 왜 테이블의 마지막 레코드에 대해서만 이런 현상(Supremum gap lock 잠금)이 발생할까를 집중적으로 고민했어요. 소스 코드를 뒤지고 인터넷을 뒤져봤지만 납득할만한 이유는 보이지 않았어요. Stack exchange에는 전혀 논리적이지 않은 내용도 정답인 것처럼 적어둔 답변도 있었어요🤣.
그러던 어느날 테이블의 마지막 레코드가 아니라, 모든 데이터 페이지의 마지막 레코드에서 모두 이 현상이 있는 것 아닐까라는 의문이 들었어요. 그래서 아래와 같이 많은 레코드를 가진 테이블을 생성하고, B-Tree의 중간쯤에 위치한 데이터 페이지의 마지막 레코드에 대해서 동일 SELECT .. FOR UPDATE 문장으로 테스트를 실행해봤어요.
/* 하나의 레코드가 254 바이트를 사용하도록 테이블 준비 */
mysql> CREATE TABLE lock_supremum2 (
id INT NOT NULL AUTO_INCREMENT,
fd1 CHAR(250) NOT NULL,
PRIMARY KEY (id)
) CHARSET=latin1 COLLATE=latin1_bin;
mysql> INSERT INTO lock_supremum2 SELECT NULL, 'dummy'
FROM information_schema.COLUMNS LIMIT 150;
mysql> SELECT MAX(id), COUNT(*) FROM lock_supremum2;
+---------+----------+
| MAX(id) | COUNT(*) |
+---------+----------+
| 150 | 150 |
+---------+----------+위와 같이 테이블을 생성하고 INSERT 문장을 실행하면, lock_supremum2 테이블은 1부터 시작해서 150까지 연속된 id 값을 가지는 150건의 레코드를 가지게 되요. 아주 컴팩트하게 레코드를 저장했기 때문에, Primary Key를 구성하는 B-Tree 인덱스의 리프 페이지(Leaf page)는 대략 55개 정도의 레코드를 저장하게 되었어요. 이렇게 데이터가 저장된 B-Tree의 구조를 그림으로 살펴보면 아래와 같아요.
이제 각 리프 페이지의 마지막 id 값을 BETWEEN으로 조회하는 쿼리를 실행해서, 어떤 레코드가 잠기는지 살펴보도록 할게요.
SESSION-1 > BEGIN;
SESSION-1*> SELECT * FROM lock_supremum2 WHERE id BETWEEN 26 AND 27 FOR UPDATE;
SESSION-1*> SELECT * FROM performance_schema.data_locks;
+-----------------------+------------+-----------+---------------+-------------+------------------------+
| ENGINE_TRANSACTION_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+------------+-----------+---------------+-------------+------------------------+
| 1890665 | NULL | TABLE | IX | GRANTED | NULL |
| 1890665 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 26 |
| 1890665 | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record |
| 1890665 | PRIMARY | RECORD | X | GRANTED | 27 |
+-----------------------+------------+-----------+---------------+-------------+------------------------+
SESSION-1 > BEGIN;
SESSION-1*> SELECT * FROM lock_supremum2 WHERE id BETWEEN 81 AND 82 FOR UPDATE;
SESSION-1*> SELECT * FROM performance_schema.data_locks;
+-----------------------+------------+-----------+---------------+-------------+------------------------+
| ENGINE_TRANSACTION_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+------------+-----------+---------------+-------------+------------------------+
| 1890666 | NULL | TABLE | IX | GRANTED | NULL |
| 1890666 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 81 |
| 1890666 | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record |
| 1890666 | PRIMARY | RECORD | X | GRANTED | 82 |
+-----------------------+------------+-----------+---------------+-------------+------------------------+
SESSION-1 > BEGIN;
SESSION-1*> SELECT * FROM lock_supremum2 WHERE id BETWEEN 136 AND 137 FOR UPDATE;
SESSION-1*> SELECT * FROM performance_schema.data_locks;
+-----------------------+------------+-----------+---------------+-------------+------------------------+
| ENGINE_TRANSACTION_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+------------+-----------+---------------+-------------+------------------------+
| 1890667 | NULL | TABLE | IX | GRANTED | NULL |
| 1890667 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 136 |
| 1890667 | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record |
| 1890667 | PRIMARY | RECORD | X | GRANTED | 137 |
+-----------------------+------------+-----------+---------------+-------------+------------------------+예상했던대로 테이블의 마지막 레코드가 문제가 아니라, 테이블을 구성하는 데이터 페이지의 마지막 레코드였기 때문에 supremum pseudo-record 에 Gap lock이 걸린 것이었어요.
왜 supremum 레코드를 잠그나요 ?
아래 문장이 실행되면, MySQL 서버는 BETWEEN 처리를 위해서 id=136 인 레코드를 찾고 그 레코드부터 id≤137 인 지점까지 하나씩 순서대로 읽으면서(스캔) 잠금을 걸어요.
SELECT FROM lock_supremum2 WHERE id BETWEEN 136 AND 137 FOR UPDATE
이 과정을 자세히 살펴보면 아래와 같아요.
id=136인 레코드 읽기WHERE조건에 일치하므로 레코드 잠금id=136보다 큰 (첫번째) 레코드인id=137읽기WHERE조건에 일치하므로 레코드 잠금 (레코드 및 간격까지 잠금)id=137보다 큰 다음 레코드 (supremum) 읽기supremum레코드는 가상 레코드이므로 잠금 (레코드 및 간격까지 잠금)- B-Tree의 리프 레벨에서 다음 페이지 읽기
- 다음 페이지에서
id=137보다 큰 (첫번째) 레코드인id=138읽기 WHERE조건에 일치하지 않으므로 잠금 걸지 않음- 쿼리 완료
그래서 WHERE id BETWEEN 136 AND 137 문장은 id=137 이후에 존재하는 supremum pseudo-record + gap에 대해서 잠금을 거는 것이죠.
그런데 만약 id=137 레코드가 (리프 페이지에서) 마지막 레코드가 아니고 그 뒤에 id=138 이 있었다면, MySQL 서버는 id=138 레코드를 읽고 스캔을 끝냈을 거에요. 그래서 이 경우에는 supremum 레코드 잠금이 필요치 않은거죠. 그리고 테이블의 마지막 레코드인 경우에는 이 현상이 더 심각하고 폭넓은 영향을 미치게 되는 것이고요.
"왜 WHERE 조건에 부합되지도 않는 간격을 잠그는 걸까?"라는 질문의 답은 의외로 간단한 것이었어요. 너무 오래전에 메뉴얼에서 봤던 내용이라, 머릿속에서 잊혀져 버렸던 거죠.
By default,
InnoDBoperates inREPEATABLE-READtransaction isolation level. In this case,InnoDBuses next-key locks for searches and index scans, which prevents phantom rows
REPEATABLE-READ 격리 수준을 사용하는 MySQL 서버에서는 검색하고 스캔했던 인덱스 레코드를 잠근다는 아주 기본적인 규칙 때문에 잠금이 발생하고 있었던 것이죠. 사용자 데이터가 아닌 시스템 레코드(supremum)까지도 말이죠.
이 현상을 어떻게 피할 수 있나요 ?
이렇게 테이블에서 가장 큰 값 이후의 간격이 잠기는 현상을 회피할 수 있는 방법은 다음과 같이 2가지가 있어요.
- 트랜잭션 격리 수준을
READ-COMMITTED로 변경 id BETWEEN 149 AND 150대신id IN (149, 150)으로 쿼리 변경
MySQL 서버의 supremum 레코드 잠금은 사실 레코드 자체에 대한 잠금이 아니라 Gap 에 대한 잠금이에요. 위의 잠금 내용 조회 결과에서 supremum pseudo record 잠금은 X-lock (Record lock 과 Gap lock이 결합된 Next-key lock)으로 표시되어 있지만, MySQL 서버는 supremum 레코드에 걸린 Next-key lock은 Gap-lock으로만 해석해요. 그런데 트랜잭션의 격리 수준이 REPEATABLE-READ 가 아닌 READ-COMMITTED가 사용되면, MySQL 서버는 레코드 간의 간격에 대한 Gap-lock 을 거의 사용하지 않아요. 그래서 READ-COMMITTED 에서는 Gap-lock 의 성격만 가진 supremum 잠금은 불필요해요.
그리고 쿼리를 id BETWEEN ? AND ? 가 아니라 id IN (?, ?, ?, ..) 형태로 사용하면, MySQL 서버는 내부적으로 id=? 조건 여러 개를 합친 것으로 해석해요. 이때 id=? 검색 조건(Primary Key를 포함한 유니크 인덱스를 구성하는 모든 컬럼에 대한 동등 검색 조건)은 UNIQUE 조건이기 때문에REPEATABLE-READ 격리 수준에서도 Gap-lock 을 사용하지 않아요. 그래서id=?와 같은 단일 레코드를 보장하는 동등 비교 조건의 쿼리는 supremum 레코드 잠금이 사용되지 않아요. 만약 트랜잭션의 격리 수준을 READ-COMMITTED로 변경할 수 없다면, 2번 방법을 사용하는 것도 좋은 회피책이라 생각해요. 크게 신경쓰지 않고 있지만, 은연중에 우리 프로그램은 (MySQL 서버의 기본 격리 수준인) REPEATABLE-READ 격리 수준에 많이 의존하고 있을 수 있기 때문에, 격리 수준을 바꾸는 것은 조심해야 해요.
(추가로) infimum 과 supremum
B-Tree의 중간에 위치한 리프 페이지의 supremum pseudo-record Gap lock으로 인해서 정말 INSERT가 안되는 현상이 동일하게 발생하는지 아래와 같이 눈으로 확인해볼 수 있어요.
/* INSERT 테스트를 위해서 B-Tree 리프 페이지의 경계에 있는 id=82와 id=83 레코드 삭제 */
SESSION-1 > BEGIN;
SESSION-1*> DELETE FROM lock_supremum2 WHERE id IN (82, 83);
SESSION-1*> COMMIT;
/* id>=80 AND id<=81 조건의 레코드를 조회하면서 Exclusive Lock 획득 */
SESSION-2 > BEGIN;
SESSION-2*> SELECT * FROM lock_supremum2 WHERE id BETWEEN 80 AND 81 FOR UPDATE;
+----+-------+
| id | fd1 |
+----+-------+
| 80 | dummy |
| 81 | dummy |
+----+-------+
2 rows in set (0.00 sec)
/* id=83은 INSERT가 되지만, id=82 인 레코드는 Wait하게 됨 */
SESSION-3 > BEGIN;
SESSION-3*> INSERT INTO lock_supremum2 VALUES (83, 'dummy');
Query OK, 1 row affected (0.00 sec)
SESSION-3*> INSERT INTO lock_supremum2 VALUES (82, 'dummy');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionSession-2의 SELECT .. FOR UPDATE 로 인해서 2번째 리프 페이지(page-no=6)의 supremum pseudo-record 에는 배타적 잠금이 걸렸어요. 이 상태에서 Session-3에서 id=83 레코드를 INSERT하면, 이 레코드는 3번째 리프 페이지(page-no=7)에 저장되기 때문에 Session-2에서 점유하고 있는 배타적 잠금에 관계없이 INSERT가 정상 완료될 수 있어요. 하지만 id=82 레코드를 INSERT하면, 2번째 리프 페이지(page-no=6)의 supremum pseudo-record 에 걸려있는 배타적 잠금 때문에 실행되지 못하고 대기해요. 그리고 위의 예제에서는 50초 동안 대기하다가, 결국 Lock wait timeout exceeded 에러로 실패하는 것을 확인할 수 있어요.
직전에 살펴보았던 예제에서 한가지 더 이상한 것은, id=83 레코드는 SELECT .. FOR UPDATE 의 배타적 잠금 영향을 받지 않았다는 것인데요. 이 부분을 더 자세히 살펴보기 위해서는, B-Tree 인덱스 페이지의 구조를 더 자세히 살펴볼 필요가 있어요.
위 그림에서는 각 인덱스 페이지에 infimum 과 supremum 이라는 인덱스 키가 2개 더 추가되었는데, 이는 MySQL 서버에서 관리하는 가상의 최소 & 최대 레코드를 의미해요. 여기에서 가상의 최소 & 최대 값이란 해당 데이터 페이지내에서 최소 및 최대를 의미해요. MySQL 서버에서 infimum 레코드는 잠금의 대상이 아니지만, supremum 레코드는 사용자 데이터와 비슷하게 잠금의 대상이에요.
그래서 SELECT .. WHERE id BETWEEN 80 AND 81 FOR UPDATE 구문이 실행되면 MySQL 서버는 id=80인 레코드부터 page-no=6 페이지의 supremum 레코드까지 잠금을 걸기 때문에, page-no=7 페이지의 제일 앞부분에 저장되는 id=83 레코드는 대기없이 INSERT되었어요. 하지만 ipage-no=6 페이지의 마지막에 저장되어야 하는 id=82 레코드는 INSERT되지 못하고 대기하고 있었던 것이죠.
