MySQL InnoDB 엔진에서 Repeatable read일 때도 phantom read가 일어나지 않는 이유에 대해 직접 테스트해보면서 Next Key Lock에 대해 알아보는 시간을 가져보겠습니다.
준비
우선 데이터베이스를 생성하겠습니다.
CREATE DATABASE mysql_study;테스트용 테이블을 만들겠습니다.
USE mysql_study;
CREATE TABLE isolation_level_test (
id INT PRIMARY KEY AUTO_INCREMENT,
ux_number INT UNIQUE NOT NULL,
last_name VARCHAR(10) NOT NULL,
first_name VARCHAR(20) NOT NULL,
INDEX idx_last_name (last_name)
);락의 범위를 확인하기 위해 primary 인덱스, 유니크 인덱스, 일반 인덱스, 인덱스가 없는 필드로 구성하였습니다. 트랜잭션에 대한 변화를 살펴봐야 하기에 MyISAM이 아닌 InnoDB로 생성하였습니다. 제대로 만들어졌는지 확인해 보겠습니다.
SHOW CREATE TABLE isolation_level_test;
CREATE TABLE `isolation_level_test` (
`id` int NOT NULL AUTO_INCREMENT,
`ux_number` int NOT NULL,
`last_name` varchar(10) NOT NULL,
`first_name` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `ux_number` (`ux_number`),
KEY `idx_last_name` (`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ciIsolation Level 테스트
우선 Isolation Level을 변화시키면서 각각의 특징을 간단히 짚고 넘어가보겠습니다.
SET @@session.transaction_isolation = 'READ-UNCOMMITTED'; READ-UNCOMMITTED는 커밋되지 않은 값도 확인할 수 있습니다. 테스트해봅시다.
start transaction;
INSERT INTO isolation_level_test(ux_number, last_name, first_name) VALUES (21, '김', '주호');트랜잭션을 활성화하고 값을 넣었습니다. 다른 커넥션으로 확인해보도록 하겠습니다.
SET @@session.transaction_isolation = 'READ-UNCOMMITTED';
SELECT * FROM isolation_level_test;
아직 커밋하지 않았는데도 값이 보입니다. 이처럼 커밋하지 않은 값을 볼 수 있는 문제를 Dirty Read라고 합니다.
Isolation Level을 높여보겠습니다.
SET @@session.transaction_isolation = 'READ-COMMITTED';
SELECT * FROM isolation_level_test;
보이지 않습니다. Dirty Read가 해결되었군요. 커밋을 수행하고 다시 확인해보겠습니다.
COMMIT;
값이 보입니다.
이번엔 하나의 트랜잭션에서 여러 번 같은 값을 확인해 보겠습니다.
방금 전과 같이 트랜잭션을 시작하고, 값을 확인하는 쿼리를 작성했습니다.
다른 커넥션에서 트랜잭션을 시작하고, id가 1인 값의 ux_number를 100으로 변화시켜 보겠습니다.
START TRANSACTION;
UPDATE ux_number=100 WHERE id = 1;
커밋이 이뤄지지 않았으므로, 아직 21이 보이는군요.
커밋을 해보겠습니다.
COMMIT;
다른 커넥션에서 커밋이 일어났더니, 기존에 보이던 21이 아닌 변경된 값인 100이 보이는군요.
그러나 같은 트랜잭션 내에서 조회되는 값이 계속 변동된다면, 추후 데이터의 정합성에 문제가 생길 수 있습니다. 이를 NON-REPEATABLE READ라 합니다.
Isolation Level을 더 높여 해결해 봅시다.
SET @@session.transaction_isolation = 'REPEATABLE-READ';다시 트랜잭션을 활성화하고 같은 방식으로 테스트해보겠습니다.
START TRANSACTRION;
UPDATE isolation_level_test SET ux_number=21 WHERE id = 1;
COMMIT;
트랜잭션 시작하자마자 한번, 업데이트문을 돌린 뒤 한번, 커밋한 뒤 한번 SELECT했으나 모두 같은 값이 확인됩니다.
커밋으로 트랜잭션을 닫고 확인해보겠습니다.
반영된 값이 잘 떴습니다.
INSERT도 이처럼 커밋하기 전에는 보이지 않을까요? 테스트해보겠습니다.
START TRANSACTRION;
INSERT INTO isolation_level_test(ux_number, last_name, first_name) VALUES (22, '심', '규선');
COMMIT;
역시, 커밋 이후에 값이 반영되는 것을 확인할 수 있었습니다.
Repeatable Read와 Phantom Read
해당 글을 쓰게 된 이유입니다.
이번에는 FOR UPDATE로 쓰기락을 걸면서 조회해보도록 하겠습니다.
그 후 데이터를 삽입해봅시다.
Lock에 가로막혀서 INSERT가 되지 않았습니다.
우선 Phantom Read를 확인하기 위해 커넥션들을 Read Commited로 변경한 후 다시 시도해 보겠습니다.
SET @@session.transaction_isolation = 'READ-COMMITTED'; 
값이 추가되었습니다.
FOR UPDATE의 쓰기락에 의한 가로막힘이 아님을 확인할 수 있습니다.
프라이머리 인덱스
다시 FOR UPDATE를 사용하며, 어떠한 락이 걸리는지 확인해봅시다.
2~4번에 대해 쓰기락을 걸었습니다.
select * from performance_schema.data_locks\G*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:1064:5433767512
ENGINE_TRANSACTION_ID: 1415
THREAD_ID: 51
EVENT_ID: 149
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 5433767512
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:3:4:3:5436495384
ENGINE_TRANSACTION_ID: 1415
THREAD_ID: 51
EVENT_ID: 149
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5436495384
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 2
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:3:4:4:5436495384
ENGINE_TRANSACTION_ID: 1415
THREAD_ID: 51
EVENT_ID: 149
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5436495384
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 4
3 rows in set (0.00 sec)2, 4번에 대해 락이 걸렸습니다.
3번에 값을 넣어봅시다.
mysql> INSERT INTO isolation_level_test(id, ux_number, last_name, first_name) VALUES (3, 5, '락', '확인');
Query OK, 1 row affected (0.01 sec)
커밋이 되었고, 값이 조회됩니다.
Repeatable Read에서 같은 방식으로 진행해봅시다.
SET @@session.transaction_isolation = 'REPEATABLE-READ';
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:1064:5433767512
ENGINE_TRANSACTION_ID: 1375
THREAD_ID: 51
EVENT_ID: 98
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 5433767512
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:3:4:3:5436495384
ENGINE_TRANSACTION_ID: 1375
THREAD_ID: 51
EVENT_ID: 98
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5436495384
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 2
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:3:4:1:5436495728
ENGINE_TRANSACTION_ID: 1375
THREAD_ID: 51
EVENT_ID: 98
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5436495728
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: supremum pseudo-record
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:3:4:4:5436495728
ENGINE_TRANSACTION_ID: 1375
THREAD_ID: 51
EVENT_ID: 98
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5436495728
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 4
4 rows in set (0.00 sec)기존과 다른 락이 걸린 것을 확인할 수 있습니다.
X,REC_NOT_GAP은 레코드락으로, 해당하는 레코드만 잠깁니다.
X는 넥스트 키 락으로, 현재 2~4 사이의 갭락과 4~ 이후의 갭락이 걸렸음을 유추할 수 있습니다.
mysql> INSERT INTO isolation_level_test(id, ux_number, last_name, first_name) VALUES (3, 50, '락', '확인');
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> INSERT INTO isolation_level_test(id, ux_number, last_name, first_name) VALUES (5, 50, '락', '확인');
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted실제로 id=3,5번에 대해 INSERT했을 때 락에 의해 동작하지 않습니다(타임아웃 되기 전 임의로 취소).
락의 범위를 좁혀보겠습니다.
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:1064:5433767512
ENGINE_TRANSACTION_ID: 1378
THREAD_ID: 51
EVENT_ID: 102
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 5433767512
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:3:4:3:5436495384
ENGINE_TRANSACTION_ID: 1378
THREAD_ID: 51
EVENT_ID: 102
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5436495384
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 2
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:3:4:4:5436495728
ENGINE_TRANSACTION_ID: 1378
THREAD_ID: 51
EVENT_ID: 102
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5436495728
LOCK_TYPE: RECORD
LOCK_MODE: X,GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 4
3 rows in set (0.00 sec)4번이 포함되지 않았기에, 4번 전까지의 갭락만이 걸린 것을 확인할 수 있습니다.
mysql> INSERT INTO isolation_level_test(id, ux_number, last_name, first_name) VALUES (3, 50, '락', '확인');
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> INSERT INTO isolation_level_test(id, ux_number, last_name, first_name) VALUES (5, 50, '락', '확인');
Query OK, 1 row affected (0.00 sec)id가 3일 때는 INSERT되지 않지만, 5일때는 잘 들어감을 확인할 수 있습니다.
유니크 인덱스
프라이머리 인덱스가 아닌, 유니크 인덱스는 어떨까요?
현재 25~30에 해당되는 값은 없습니다. 해당 범위로 락을 걸어봅시다.
SELECT * FROM isolation_level_test WHERE ux_number BETWEEN 25 AND 30 FOR UPDATE;*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:1064:5433767512
ENGINE_TRANSACTION_ID: 1385
THREAD_ID: 51
EVENT_ID: 109
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 5433767512
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:3:5:5:5436495384
ENGINE_TRANSACTION_ID: 1385
THREAD_ID: 51
EVENT_ID: 109
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: ux_number
OBJECT_INSTANCE_BEGIN: 5436495384
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 50, 5
2 rows in set (0.00 sec)레코드락이 걸렸으니, 24~50의 범위에 락이 걸렸음을 유추할 수 있습니다.
mysql> INSERT INTO isolation_level_test(ux_number, last_name, first_name) VALUES (24, '락', '갭락');
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> INSERT INTO isolation_level_test(ux_number, last_name, first_name) VALUES (10, '락', '갭락');
Query OK, 1 row affected (0.00 sec)실제로 25~30의 범위에 락이 걸렸다면 24번이 INSERT되어야 할 텐데, 그렇지 않았음을 확인할 수 있습니다.
일반 인덱스
일반 인덱스는 어떨까요?
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:1064:5433767512
ENGINE_TRANSACTION_ID: 1389
THREAD_ID: 51
EVENT_ID: 118
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 5433767512
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:3:6:2:5436495384
ENGINE_TRANSACTION_ID: 1389
THREAD_ID: 51
EVENT_ID: 118
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: idx_last_name
OBJECT_INSTANCE_BEGIN: 5436495384
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: '김', 1
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:3:6:4:5436495384
ENGINE_TRANSACTION_ID: 1389
THREAD_ID: 51
EVENT_ID: 118
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: idx_last_name
OBJECT_INSTANCE_BEGIN: 5436495384
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: '루', 4
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:3:6:5:5436495384
ENGINE_TRANSACTION_ID: 1389
THREAD_ID: 51
EVENT_ID: 118
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: idx_last_name
OBJECT_INSTANCE_BEGIN: 5436495384
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: '락', 5
*************************** 5. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:3:6:6:5436495384
ENGINE_TRANSACTION_ID: 1389
THREAD_ID: 51
EVENT_ID: 118
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: idx_last_name
OBJECT_INSTANCE_BEGIN: 5436495384
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: '락', 7
*************************** 6. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:3:4:2:5436495728
ENGINE_TRANSACTION_ID: 1389
THREAD_ID: 51
EVENT_ID: 118
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5436495728
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 1
*************************** 7. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:3:4:5:5436495728
ENGINE_TRANSACTION_ID: 1389
THREAD_ID: 51
EVENT_ID: 118
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5436495728
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 5
*************************** 8. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:3:4:6:5436495728
ENGINE_TRANSACTION_ID: 1389
THREAD_ID: 51
EVENT_ID: 118
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5436495728
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 7
8 rows in set (0.00 sec)굉장히 많은 락이 걸립니다.
검색된 1, 4, 5번에 대해서 프라이머리 키 락과 나머지는 다 넥스트 키락이 걸렸습니다.
특히, 성이 '루'인 경우에도 넥스트 키락이 걸려버렸네요.
mysql> UPDATE isolation_level_test SET first_name='시안' WHERE last_name='루';
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted실제로 락에 막혀 업데이트가 되지 않습니다.
갭락이 걸리지 않을까 했는데, 넥스트 키락이 걸려서 조금 당황스럽습니다(이유를 아시는 분은 알려주시면 감사하겠습니다).
mysql> UPDATE isolation_level_test SET first_name='시안' WHERE id='4';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
그러나 id로는 락이 걸리지 않았으므로 id를 사용한 업데이트 쿼리는 동작합니다.
필드
인덱스가 아닌 필드에는 어떻게 락이 걸릴까요?
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:1064:5433767512
ENGINE_TRANSACTION_ID: 1393
THREAD_ID: 51
EVENT_ID: 130
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 5433767512
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:3:4:1:5436495384
ENGINE_TRANSACTION_ID: 1393
THREAD_ID: 51
EVENT_ID: 130
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5436495384
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:3:4:2:5436495384
ENGINE_TRANSACTION_ID: 1393
THREAD_ID: 51
EVENT_ID: 130
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5436495384
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 1
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:3:4:3:5436495384
ENGINE_TRANSACTION_ID: 1393
THREAD_ID: 51
EVENT_ID: 130
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5436495384
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 2
*************************** 5. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:3:4:4:5436495384
ENGINE_TRANSACTION_ID: 1393
THREAD_ID: 51
EVENT_ID: 130
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5436495384
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 4
*************************** 6. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:3:4:5:5436495384
ENGINE_TRANSACTION_ID: 1393
THREAD_ID: 51
EVENT_ID: 130
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5436495384
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 5
*************************** 7. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4705062808:3:4:6:5436495384
ENGINE_TRANSACTION_ID: 1393
THREAD_ID: 51
EVENT_ID: 130
OBJECT_SCHEMA: mysql_study
OBJECT_NAME: isolation_level_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5436495384
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 7
7 rows in set (0.00 sec)모든 값에 넥스트 키 락이 걸렸습니다.
mysql> INSERT INTO isolation_level_test(ux_number, last_name, first_name) VALUES (9, '락', '인덱스아님');
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted값이 추가되지 않음을 확인할 수 있습니다.
이는 해당 쿼리가 인덱스를 타지 않음으로 조건에 맞는 값을 확인하기 위해 모든 레코드를 확인해야 하며, 이로 인해 전체적인 넥스트 키 락이 걸리게 되었다 생각합니다.
마치며
Real Mysql 중 이러한 설명이 있습니다.
...
이렇게 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다가 안 보였다가 하는 현상을 PHANTOM READ(또는 PHANTOM ROW)라고 한다.
SELECT .. FOR UPDATE 쿼리는 SELECT 하는 레코드에 쓰기 잠금을 걸어야 하는데, 언두 레코드에는 잠금을 걸 수 없다.
그래서 SELECT .. FOR UPDATE나 SELECT .. LOCK IN SHARE MODE로 조회되는 레코드는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 되는 것이다.
...언두 레코드에 대한 이야기가 갑자기 왜 나오는 지 이해가 되지 않아 직접 테스트해보게 되었습니다.
아직도 잘 이해가 되지 않지만, 아마 쓰기 잠금은 현재의 값을 기반으로 하는 것이기에 새로 추가되는 값은 막을 수 없다이지 않나 싶습니다.
MySQL에서는 이러한 문제를 해결하기 위해 넥스트 키 락을 같이 걸어 Repeatable Read에서도 Phantom Read까지 막는 것을 위의 테스트들을 통해 직접 확인할 수 있었습니다.
나누며 타오르는 프로그래머, 타프입니다.