글을 작성하게 된 계기
MySQL에서는 REPEATABLE READ 격리수준 에서 대부분의 상황 에서는 팬텀 리드(Phantom Read)가 발생하지 않는데, 이에 대해 학습한 내용을 정리하기 위해 글을 작성하게 되었습니다.
이번 포스팅은 꼭 실습을 하며 따라해볼 것을 권장드립니다.
1. 팬텀 리드와 MVCC
팬텀 리드(Phantom Reads)는 특정 격리 수준에서 다른 트랜잭션이 새로운 행을 삽입/삭제 할 때 발생하는 데이터 집합의 변경 경험 을 말합니다. 즉, 한 트랜잭션이 실행중일 때, 다른 트랜잭션에서 삽입/삭제를 실행한 결과가 보이는 것입니다.
MVCC(Multi-Version Concurrency Control)는 동시성 제어 메커니즘으로, 트랜잭션이 시작될 때 데이터의 스냅샷을 생성하여 트랜잭션 내에서 일관된 데이터를 보장합니다. Repeatable Read 격리 수준에서는 트랜잭션이 시작된 시점의 데이터를 유지하여 동일한 트랜잭션 내에서 반복적인 읽기 시 데이터 변화를 방지합니다.
MySQL에서는 REPEATABLE READ 격리 수준에서 MVCC 덕분에 대부분의 경우 팬텀 리드가 발생하지 않습니다. MVCC는 각 트랜잭션에 대해 데이터의 스냅샷(Snapshot) 을 만들어, 트랜잭션이 시작된 시점의 데이터 를 읽도록 보장하기 때문입니다. 아래 예시에서 T1은 트랜잭션 시작 시점의 스냅샷을 읽고, 그 후 변경된 데이터나 삽입된 새로운 데이터를 볼 수 없습니다. 이로 인해 T1은 트랜잭션이 종료될 때까지 일관된 데이터를 읽을 수 있게 됩니다.
1
2
T1: |----•-------------------•----> (T1 트랜잭션 - 일관된 데이터 읽기)
T2: |-----•----•-----> (T2 트랜잭션 - 수정)
하지만 새로운 레코드 삽입이 포함된 경우, 팬텀 리드가 발생할 수 있습니다. T1이 데이터셋을 조회할 때, T2가 새로 추가한 레코드를 T1이 조회할 수 있기 때문입니다. 이로 인해 T1의 쿼리 결과는 트랜잭션이 진행되는 동안 예상치 못하게 달라지며, 이는 애플리케이션의 논리적 일관성 에 문제를 일으킬 수 있습니다. 따라서 팬텀 리드는 REPEATABLE READ 격리 수준에서도 완전히 차단되지 않으며, 새로운 레코드 삽입이 포함된 경우 발생할 수 있기 때문에 주의가 필요합니다.
1
2
3
4
T1: |----•-------------------•----> (T1 트랜잭션 - 일관된 데이터 읽기)
T2: |-----•----•-----> (T2 트랜잭션 - 수정)
↑
T2가 새로운 레코드를 삽입, T1은 새 레코드를 볼 수 있음
2. 갭 락/넥스트 키락
MySQL에서는 이를 방지하기 위해 갭 락(Gap Locks) 과 넥스트 키락(Next-Key Lock) 을 이용해 팬텀리드를 방지하고 있는데, 이에 대해 먼저 살펴보겠습니다.
- 갭 락(Gap Locks)
- 넥스트 키 락(Next-Key Lock)
2-1. 갭 락
갭 락(Gap Locks)은 인덱스 레코드 간의 간격, 또는 첫 번째 및 마지막 인덱스 레코드 전후의 간격에 적용되는 잠금 입니다.
이는 다른 트랜잭션이 특정 간격 에 데이터 삽입 을 방지할 때 사용합니다. 따라서 레코드 자체에 대한 변경/업데이트를 하진 않으며, 여러 트랜잭션 간 공존 할 수 있습니다. 한 트랜잭션이 특정 위치에 갭 락을 걸더라도 다른 트랜잭션도 동일한 위치에 갭 락을 걸 수 있는 것이죠.
이를 그림으로 보면 다음과 같은데, 각 인덱스 사이에 락을 걸어 다른 데이터의 삽입을 막습니다.
갭 락은 유니크한 인덱스를 사용해 특정 데이터를 조회할 때는 필요하지 않습니다. 이는 고유 인덱스가 이미 행의 고유성을 보장하기 때문입니다. 예를 들어, 다음과 같은 쿼리는 특정 행에만 잠금을 걸고 해당 행의 주변 간격에는 잠금을 걸지 않습니다.
1
SELECT * FROM users WHERE id = 100 FOR UPDATE;
반면 복합 인덱스 를 사용할 때는 결과가 여러 행이 될 수 있으므로 갭 락이 필요할 수 있습니다. 복합 인덱스는 두 개 이상의 칼럼으로 구성된 인덱스인데, 인덱스의 일부 칼럼만 사용하면 나머지 칼럼값에 따라 여러 결과가 반환될 수 있기 때문입니다. 간단한 실습으로 이를 살펴보겠습니다. 먼저 테이블을 생성하고 3개의 데이터를 INSERT 합니다.
1
2
3
4
5
6
7
8
9
10
CREATE TABLE sub_category (
id INT,
category VARCHAR(50),
name VARCHAR(100),
PRIMARY KEY (id, category)
);
INSERT INTO sub_category (id, category, name) VALUES (100, 'A', 'Electronic');
INSERT INTO sub_category (id, category, name) VALUES (100, 'B', 'Sports');
INSERT INTO sub_category (id, category, name) VALUES (101, 'A', 'Electronic');
다음으로 트랜잭션을 시작한 후, ID가 100인 데이터를 조회합니다.
1
2
START TRANSACTION;
SELECT * FROM sub_category WHERE id = 100;
결과를 보면 다음과 같이 S, GAP 락이 걸린 것을 볼 수 있습니다. 즉, 복합 인덱스를 사용할 때, 일부 칼럼 만 사용해 조회하면, 나머지 칼럼에 의해 다른 결과가 반환될 수 있으므로, 다른 데이터가 INSERT 되지 않도록 락을 거는 것이죠.
1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM performance_schema.data_locks WHERE OBJECT_NAME='sub_category';
|-----------------------|----------|-------------------------|-------------|-----------|
| ENGINE_TRANSACTION_ID | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+----------+-------------------------+-------------+-----------+
| 562948515421400 | TABLE | IS | GRANTED | NULL |
| 562948515421400 | RECORD | S | GRANTED | 100, 'A' |
| 562948515421400 | RECORD | S | GRANTED | 100, 'B' |
| 562948515421400 | RECORD | S,GAP | GRANTED | 101, 'A' |
+-----------------------+----------+-------------------------+-------------+-----------+
이는 performance_schema에서 필요한 값들만 추려낸 표입니다.
이는 다양한 격리 수준에서 사용할 수 있지만, 주로 Repeatable Read 및 Serializable 에서 자주 사용됩니다. 한 트랜잭션이 진행되는 동안 데이터의 일관성을 유지해야 하기 때문입니다. 이를 사용할 때는 락이 걸리기 때문에 성능 과 동시성의 균형 을 적절하게 조절해야 합니다.
갭 락을 사용하면 특정 조건에서는 데드락(Deadlock이 발생하기도 합니다. 이에 대해서는 해당 포스팅을 참조해보세요.
2-2. 넥스트 키 락
넥스트 키 락(Next-Key Locks)은 인덱스에 대한 레코드 락 과 그 인덱스 레코드 이전의 갭에 대한 갭 락 을 조합 한 것입니다.
이는 인덱스 레코드 락 과 그 사이의 갭 락 을 포함 합니다. 즉, 하나의 세션이 인덱스에서 레코드의 공유/배타적 락을 가지고 있다면, 다른 세션은 해당 인덱스 이전 구간에 새로운 레코드를 삽입할 수 없습니다. 말이 좀 어려울 수 있는데, 해당 레코드와 그 이전 구간에 락을 거는 것이죠. 이를 그림으로 보면 다음과 같습니다.
갭 락과 마찬가지로 간단한 실습을 해보겠습니다. 먼저 테이블을 초기화한 후 데이터를 INSERT 해줍니다.
1
2
3
4
5
6
7
DROP TABLE users;
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50)
);
INSERT INTO users (id, name) VALUES (1, 'Jun'), (3, 'Youl'), (10, 'Sim');
이후 트랜잭션을 시작하고 2번부터 6번 사이의 데이터를 SELECT FOR UPDATE 로 조회합니다.
1
2
3
4
5
6
7
mysql> SELECT * FROM users WHERE id BETWEEN 2 AND 6 FOR UPDATE;
+----+------+
| id | name |
+----+------+
| 3 | Youl |
+----+------+
1 row in set (0.00 sec)
이제 performance_schema를 조회하면 다음과 같은 결과를 볼 수 있습니다. 마지막 행을 보면 X, GAP 락을 볼 수 있는데, 이것이 특정 레코드와 그 사이 간격을 잠근 넥스트 키 락입니다.
1
mysql> SELECT * FROM performance_schema.data_locks WHERE OBJECT_SCHEMA = 'tx' AND OBJECT_NAME = 'users';
1
2
3
4
5
6
7
8
mysql> SELECT * FROM performance_schema.data_locks WHERE OBJECT_NAME='users';
|-----------------------|----------|-------------------------|-------------|-----------|
| ENGINE_TRANSACTION_ID | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+----------+-------------------------+-------------+-----------+
| 3071186 | TABLE | IX | GRANTED | NULL |
| 3071186 | RECORD | X | GRANTED | 3 |
| 3071186 | RECORD | X,GAP | GRANTED | 7 |
+-----------------------+----------+-------------------------+-------------+-----------+
3. 왜 팬텀 리드가 발생하지 않을까?
그렇다면 왜 MySQL에서는 팬텀 리드가 발생하지 않을까요? 이는 넥스트 키 락 로 인해, 레코드와 레코드 사이에 락이 걸리기 때문입니다. 예를 들어, 3번 노드를 INSERT 할 때, 넥스트 키 락을 걸면 다른 트랜잭션에서 접근할 수 없기 때문이죠.
글만 보면 이해가 잘 안될 수 있는데, 실습으로 살펴보겠습니다. 먼저 테이블을 생성한 후, 세 개의 데이터를 INSERT 합니다.
1
2
3
4
5
6
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50)
);
INSERT INTO users (id, name) VALUES (1, 'Jun'), (5, 'Youl'), (10, 'Sim');
다음으로 1번 터미널에서 ID 2번부터 9번까지를 SELECT FOR UPDATE 로 조회합니다.
1
2
3
# Terminal-1
START TRANSACTION;
SELECT * FROM users WHERE id BETWEEN 2 AND 9 FOR UPDATE;
2번 터미널에서는 Yui 데이터를 INSERT 합니다. 그러면 넥스트 키 락으로 인해 대기 상태에 걸리게 됩니다. 여기까지 왔으면 이제 넥스트 키 락을 실제로 볼 수 있게 됩니다.
1
2
3
# Terminal-2
START TRANSACTION;
INSERT INTO users (id, name) VALUES (3, 'Yui');
1번 터미널에서 SELECT * FROM performance_schema.data_locks WHERE OBJECT_NAME='${TABLE}'; 를 실행하면 다음과 같은 결과를 볼 수 있습니다. 다양한 데이터가 나오기 때문에 필요한 내용만 선별해서 보도록 하겠습니다.
아래 결과의 LOCK_STATUS 를 보면 5번 데이터에는 X, GAP, INSERT_INTENTION 락이, 10번 데이터에는 X, GAP 락이 걸린 것을 볼 수 있습니다. 그리고 2번 터미널은 대기 상태에 걸려있고요. 이것이 넥스트 키 락 인데요, 즉, 데이터를 INSERT 하려고 했지만, 넥스트 키 락으로 인해 데이터가 INSERT 되지 않고 있는 것입니다.
1
2
3
4
5
6
7
8
9
10
mysql> SELECT * FROM performance_schema.data_locks WHERE OBJECT_NAME='users';
|-----------------------|----------|-------------------------|-------------|-----------|
| ENGINE_TRANSACTION_ID | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+----------+-------------------------+-------------+-----------+
| 3071091 | TABLE | IX | GRANTED | NULL |
| 3071091 | RECORD | X,GAP,INSERT_INTENTION | WAITING | 5 |
| 3071090 | TABLE | IX | GRANTED | NULL |
| 3071090 | RECORD | X | GRANTED | 5 |
| 3071090 | RECORD | X,GAP | GRANTED | 10 |
+-----------------------+----------+-------------------------+-------------+-----------+
2번 터미널의 대기상태인 WAITING도 보입니다.
이를 그림으로 보면 조금 더 이해가 빠른데요, 이는 다음과 같습니다. 3번 노드에 데이터를 INSERT 하려고 했지만, 넥스트 키 락으로 인해 노드와 노드 사이, 노드에 락이 걸려있기 때문에 새로운 데이터가 INSERT 될 수 없는 것입니다. 이 때문에 MySQL에서는 데이터 INSERT로 인한 팬텀 리드가 발생하지 않는 것이죠.
ID가 2-9번 이지만 Next-Key Locks에 의해 1번과 10번도 잠금이 발생합니다.
4. 정말 발생 안 할까?
MySQL은 갭 락과 넥스트 키 락으로 인해 팬텀 리드가 발생 안 할 수도 있습니다. 하지만 팬텀 리드가 발생하는 상황도 존재하는데요, 이 경우도 한 번 살펴보겠습니다. 마찬가지로 테이블을 초기화한 후, 데이터를 INSERT 합니다.
1
2
3
4
5
6
7
DROP TABLE users;
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50)
);
INSERT INTO users (id, name) VALUES (1, 'Jun'), (5, 'Youl'), (10, 'Sim');
1번 터미널에서 데이터를 INSERT 합니다. 커밋은 하지 않고요.
1
2
3
# Terminal-1
START TRANSACTION;
INSERT INTO users (id, name) VALUES (3, 'Jung');
다음으로 2번 터미널에서 잠금 없이 사용자 아이디를 범위로 조회합니다. 당연히 1번 터미널에서 커밋을 하지 않았기 때문에 2개의 결과가 나오겠죠?
1
2
3
4
5
6
7
8
9
# Terminal-2
START TRANSACTION;
SELECT * FROM users WHERE id >= 2;
+----+---------+
| id | name |
+----+---------+
| 5 | Youl |
| 10 | Sim |
+----+---------+
이를 확인했다면 1번 터미널에서 커밋을 합니다.
1
2
# Terminal-1
COMMIT;
마지막으로 2번 터미널에서 SELECT FOR UPDATE 로 데이터를 조회합니다. 1번 터미널에서 커밋을 했기 때문에 이번엔 결과가 3개가 나오겠죠? 현재 2번 터미널은 커밋을 하지 않은 상태 입니다. 이렇게 되면 하나의 트랜잭션 에서 서로 다른 두 결과 가 나온 것이죠.
1
2
3
4
5
6
7
8
9
10
# Terminal-2
mysql> SELECT * FROM users WHERE id >= 2 FOR UPDATE;
+----+---------+
| id | name |
+----+---------+
| 3 | Jung |
| 5 | Youl |
| 10 | Charlie |
+----+---------+
3 rows in set (0.00 sec)
MySQL에 갭 락/넥스트 키 락이라는 특별한 개념이 있어 덜 한 것이지, 팬텀 리드는 언제든 발생할 수 있습니다.
왜 이런 결과가 나올까요? 이는 REPEATABLE READ 격리 수준에서 일반 SELECT 쿼리를 실행하면, InnoDB 스토리지 엔진은 최초 쿼리 시점의 데이터 스냅샷 을 사용해 결과를 반환합니다. 만약 아래 쿼리를 실행하면 2번 터미널이 커밋을 하기 전이므로, 두 개의 결과만 반환될 것이고요. 즉, 트랜잭션이 진행되는 동안 다른 트랜잭션에 의해 삽입된 새로운 데이터는 반영되지 않는 것이죠.
1
2
3
4
5
6
7
8
9
# Terminal-2
mysql> SELECT * FROM users WHERE id >= 2 FOR UPDATE;
+----+---------+
| id | name |
+----+---------+
| 5 | Youl |
| 10 | Sim |
+----+---------+
2 rows in set (0.00 sec)
하지만 SELECT FOR UPDATE 는 잠금을 요구하므로, 호출 시점에 데이터베이스의 최신 상태 를 반영해 결과를 반환합니다. 따라서 이 때문에 팬텀 리드가 발생할 수 있는 것입니다.
1
2
3
4
5
6
7
8
9
10
# Terminal-2
mysql> SELECT * FROM users WHERE id >= 2 FOR UPDATE;
+----+---------+
| id | name |
+----+---------+
| 3 | Jung |
| 5 | Youl |
| 10 | Charlie |
+----+---------+
3 rows in set (0.00 sec)
즉, 스냅샷이 아닌 실시간 데이터 상태를 조회하고 잠금을 적용하기 때문에 INSERT가 반영된 것입니다.
5. 정리
왜 MySQL에서 팬텀 리드가 발생하지 않을 수 있는지 살펴보았습니다. 팬텀 리드는 모든 데이터베이스에서 발생할 수 있는 문제이기 때문에 기본 개념을 잘 숙지하고, MySQL의 특성에 대해 알아두면 좋을 것 같습니다.