데이터베이스 격리 수준: MySQL에서 Phantom Read가 발생하지 않는 이유
5분 소요
트랜잭션의 격리 수준은 한 트랜잭션이 동시에 실행 중인 다른 트랜잭션이 수정하거나 읽고 있는 데이터를 볼 수 있는지를 결정합니다.
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
READ UNCOMMITTED
커밋되지 않은 데이터까지 접근할 수 있는 격리 수준입니다.
가장 낮은 격리 수준이며, 일반적으로 사용하지 않습니다.
트랜잭션 1이 데이터를 수정하고 아직 커밋하지 않았더라도, 트랜잭션 2는 커밋되지 않은 데이터에 접근할 수 있습니다.
--- (transaction 1)
UPDATE MEMBER SET name = 'Seonggil2' WHERE ID = 1 --- not committed
--- (transaction 2)
SELECT * FROM MEMBER WHERE ID = 1
--- | ID: 1 | NAME: Seonggil2 |
트랜잭션 2가 데이터를 읽은 뒤 트랜잭션 1이 ROLLBACK을 수행하면 데이터 불일치가 발생할 수 있습니다 (Dirty Read).
READ COMMITTED
커밋된 데이터만 읽을 수 있습니다.
READ COMMITTED에서는 REPEATABLE READ에서 나타나는 Phantom Read 문제에 더해 Non-Repeatable Read도 발생할 수 있습니다.
트랜잭션 1이 데이터를 수정하고 아직 커밋하지 않은 경우입니다.
과정
- 트랜잭션 1이 데이터를 수정하지만 아직 커밋하지 않습니다.
- 테이블은 갱신되고, 이전 데이터는 Undo Log에 백업됩니다.
- 트랜잭션 2가 데이터를 조회합니다.
- 커밋된 데이터만 읽을 수 있으므로 Undo Log에서 이전 데이터를 가져옵니다.
--- (transaction 1)
UPDATE MEMBER SET name = 'Seonggil2' WHERE ID = 1 -- not committed
--- (transaction 2)
SELECT * FROM MEMBER WHERE ID = 1
--- | ID: 1 | NAME: 'Seonggil' |
하지만 Non-Repeatable Read가 발생할 수 있습니다.
Non-Repeatable Read: 하나의 트랜잭션 안에서 같은 SELECT 쿼리를 여러 번 실행했을 때 서로 다른 결과가 나오는 현상
과정
- 트랜잭션 1이 데이터를 수정하지만 아직 커밋하지 않습니다.
- 테이블은 갱신되고, 이전 데이터는 Undo Log에 백업됩니다.
- 트랜잭션 2가 수정된 값을 조건으로 데이터를 조회합니다.
- 아직 커밋되지 않았으므로 결과에 나타나지 않습니다.
- 트랜잭션 1이 커밋합니다.
- 트랜잭션 2가 수정된 값을 조건으로 다시 조회하면 이번에는 데이터가 반환됩니다 (문제 지점).
--- (Transaction 1)
UPDATE MEMBER SET NAME = 'Seonggil2' WHERE ID = 1 --- not committed
--- (Transaction 2)
SELECT * FROM MEMBER WHERE NAME = 'Seonggil2' --- no Result
--- (Transaction 1)
commit;
--- (Transaction 2)
SELECT * FROM MEMBER WHERE NAME = 'Seonggil2' --- | ID: 1 | NAME: 'Seonggil2'|
READ COMMITTED에서는 다른 트랜잭션의 커밋 여부에 따라 반복해서 읽을 때 결과가 달라질 수 있습니다.
이는 하나의 트랜잭션 안에서 같은 데이터를 읽고 수정할 때 문제를 일으킬 수 있습니다.
REPEATABLE READ
Non-Repeatable Read를 방지하는 격리 수준으로, 커밋된 데이터만 읽을 수 있고 자신보다 트랜잭션 번호가 낮은 트랜잭션이 커밋한 데이터만 읽을 수 있습니다.
같은 레코드의 여러 버전이 존재할 수 있으며, 이를 MVCC(Multi-Version Concurrency Control)라고 합니다.
각 트랜잭션은 순차적으로 증가하는 고유한 트랜잭션 번호를 가지며, 백업 레코드에는 이전 데이터와 그것을 수정한 트랜잭션 번호가 함께 저장됩니다.
과정
- 트랜잭션 1이 데이터를 조회합니다.
- 트랜잭션 2가 같은 데이터를 수정하고 커밋합니다.
- 트랜잭션 1이 데이터를 다시 조회합니다.
--- Transaction 1
SELECT * FROM MEMBER WHERE ID = 1 --- | ID: 1 | NAME: 'Seonggil' |
--- Transaction 2
UPDATE MEMBER SET NAME = 'Seonggil2' WHERE ID = 1
commit;
--- Transaction 1
SELECT * FROM MEMBER WHERE ID = 1 --- | ID: 1 | NAME: 'Seonggil' |
트랜잭션 1은 자신보다 트랜잭션 번호가 낮은 트랜잭션이 커밋한 데이터만 읽으며, 더 새로운 트랜잭션이 데이터를 수정했다면 Undo Log에서 이전 버전을 가져옵니다.
REPEATABLE READ는 Non-Repeatable Read를 방지하지만 새로운 레코드의 추가까지 막지는 않으므로 Phantom Read는 여전히 발생할 수 있습니다.
하지만 MVCC 덕분에 단순 조회에서는 이후 트랜잭션이 새로 삽입한 레코드가 무시되므로 일반적으로 Phantom Read가 발생하지 않습니다.
과정
- 트랜잭션 1이 사용자 목록을 조회합니다.
- 트랜잭션 2가 새로운 사용자를 삽입합니다.
- 트랜잭션 1이 목록을 다시 조회하지만, 트랜잭션 2가 추가한 사용자는 보이지 않습니다.
--- Transaction 1
SELECT * FROM MEMBER
--- |ID: 1|NAME: 'Sg02'|, |ID: 2|NAME: 'Seonggil'|
--- Transaction 2
INSERT INTO MEMBER VALUES (3, 'newUser')
commit;
--- Transaction 1
SELECT * FROM MEMBER
--- |ID: 1|NAME: 'Sg02'|, |ID: 2|NAME: 'Seonggil'| // return Same result
REPEATABLE READ에서 Phantom Read가 발생하는 경우
쿼리에서 잠금을 사용하면, 일반적인 RDBMS에서는 Phantom Read가 발생할 수 있습니다.
과정
- 앞선 예시와 마찬가지로 트랜잭션 1이 먼저 데이터를 읽으며 SELECT FOR UPDATE로 쓰기 잠금을 겁니다.
(쓰기 잠금: SELECT FOR UPDATE, 읽기 잠금: SELECT FOR SHARE)
잠금은 트랜잭션이 커밋되거나 롤백될 때 해제됩니다.
- 트랜잭션 2가 새로운 레코드를 삽입하려고 합니다.
대부분의 DBMS에서는 수정된 레코드만 잠그기 때문에, INSERT는 잠금 없이 즉시 실행될 수 있습니다.
--- Transaction 1
SELECT * FROM MEMBER WHERE id >= 2 FOR UPDATE
--- | ID: 2 | NAME: 'seonggil'| (count: 1)
--- Transaction 2
INSERT INTO MEMBER VALUES (3, 'newMember')
commit;
--- Transaction 1
SELECT * FROM MEMBER WHERE id >= 2 FOR UPDATE
--- | ID: 2 | NAME: 'seonggil'|, | ID: 3| NAME: 'newMember'| (count: 2)
새로운 레코드가 추가될 수 있으므로 같은 트랜잭션 안에서도 쿼리 결과가 달라질 수 있습니다.
이는 다른 트랜잭션이 레코드를 삽입하거나 삭제할 때 발생합니다.
MVCC가 Phantom Read를 막지 못하는 이유
FOR UPDATE를 사용했기 때문에 Phantom Read를 막을 수 없습니다. 잠금을 사용하는 읽기는 Undo Log가 아니라 테이블을 대상으로 동작합니다. 잠금 읽기는 먼저 행을 선택한 뒤 잠금을 적용합니다. Undo Log는 추가만 가능한(append-only) 구조라서 잠금을 걸 수 없습니다.
MySQL이 Phantom Read를 막는 이유 — Gap Lock
MySQL은 Gap Lock으로 이 문제를 방지합니다.
트랜잭션 1이 FOR UPDATE로 데이터를 읽으면 MySQL은 id >= 2 범위를 잠그는 Next Key Lock을 적용합니다. 따라서 트랜잭션 2의 INSERT는 트랜잭션 1이 끝날 때까지 차단됩니다.
과정
- 트랜잭션 1이 FOR UPDATE로 조회합니다.
- 트랜잭션 2가 데이터를 삽입하려고 합니다.
- 트랜잭션 2는 트랜잭션 1이 끝날 때까지(또는 Lock Timeout이 발생할 때까지) 대기합니다.
--- Transaction 1
SELECT * FROM MEMBER WHERE id >= 2 FOR UPDATE --- (NEXT KEY LOCK ID >= 2)
--- | ID: 2 | NAME: 'seonggil'| (count: 1)
--- Transaction 2
INSERT INTO MEMBER VALUES (3, 'newMember') --- Waits until Transaction 1 unlock
commit;
--- Transaction 1
SELECT * FROM MEMBER WHERE id >= 2 FOR UPDATE
--- | ID: 2 | NAME: 'seonggil'| (count: 1)
SERIALIZABLE
트랜잭션이 순차적으로 실행되도록 보장하는 가장 높은 격리 수준입니다.
트랜잭션이 서로 끼어들 수 없으므로 데이터 불일치는 발생하지 않지만, 동시 처리 성능이 크게 떨어집니다.
SELECT 쿼리는 공유 잠금(Shared Lock)을 획득하고, INSERT, UPDATE, DELETE 쿼리는 배타 잠금(Exclusive Lock)을 획득합니다 (MySQL에서는 Next Key Lock입니다).
