seon99il

Database

데이터베이스 격리 수준: 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. 트랜잭션 1이 데이터를 수정하지만 아직 커밋하지 않습니다.
  2. 테이블은 갱신되고, 이전 데이터는 Undo Log에 백업됩니다.
  3. 트랜잭션 2가 데이터를 조회합니다.
  4. 커밋된 데이터만 읽을 수 있으므로 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. 트랜잭션 1이 데이터를 수정하지만 아직 커밋하지 않습니다.
  2. 테이블은 갱신되고, 이전 데이터는 Undo Log에 백업됩니다.
  3. 트랜잭션 2가 수정된 값을 조건으로 데이터를 조회합니다.
  4. 아직 커밋되지 않았으므로 결과에 나타나지 않습니다.
  5. 트랜잭션 1이 커밋합니다.
  6. 트랜잭션 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. 트랜잭션 1이 데이터를 조회합니다.
  2. 트랜잭션 2가 같은 데이터를 수정하고 커밋합니다.
  3. 트랜잭션 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. 트랜잭션 1이 사용자 목록을 조회합니다.
  2. 트랜잭션 2가 새로운 사용자를 삽입합니다.
  3. 트랜잭션 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. 앞선 예시와 마찬가지로 트랜잭션 1이 먼저 데이터를 읽으며 SELECT FOR UPDATE로 쓰기 잠금을 겁니다.

(쓰기 잠금: SELECT FOR UPDATE, 읽기 잠금: SELECT FOR SHARE)

잠금은 트랜잭션이 커밋되거나 롤백될 때 해제됩니다.

  1. 트랜잭션 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. 트랜잭션 1이 FOR UPDATE로 조회합니다.
  2. 트랜잭션 2가 데이터를 삽입하려고 합니다.
  3. 트랜잭션 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입니다).

이전 글

전체 글 보기 →

댓글