[MySQL] transaction isolation 이상현상 3가지(Dirty Read, Non-Repeatable Read, Phantom Read)

 여러 기술블로그(망나니 개발자), 유튜브 강의 영상, Chat GPT, 공식 메뉴얼을 찾아보았지만 MySQL 까보지 않는 이상 정확하게 이걸 이해하는게  현실적으로 많은 어려움이 있음을 깨달았다. 특히나 스냅샷과 Undo Log에 대해 정확하게 이해하는게 너무 어려운게 현재 레벨로 보이고, 우선은 이렇게 정리하고 후에 깊이 있게 공부할 기회가 있으면 추가로 정리를 해야겠다

---

Docker로 테스트 환경 구축

MySQL 8.0 설치하여 트랜잭션 이상현상 테스트를 수행할 수 있도록 한다

 

docker 로 mysql 설치

# 이미지 내려받기
$ docker pull mysql:8.0

# 이미지 확인
$ docker images

# 실행
$ docker run --name mysql-8 -e MYSQL_ROOT_PASSWORD=1234 -d -p 3306:3306 mysql:8.0

# 프로세스 확인
$ docker ps -a

# 접속
$ docker exec -it mysql-8 /bin/bash

 

mysql 접속 

$ mysql -uroot -p1234

 

스키마/유저 생성 및 권한 부여

# 스키마
> create database test default character set utf8;

> show databases;

# 계정
> create user 'tester'@'%' identified by 'qwer1234!@#$';

> GRANT ALL privileges ON test.* TO 'tester'@'%';

> create user 'tester'@'localhost' identified by 'qwer1234!@#$';

> GRANT ALL privileges ON test.* TO 'tester'@'localhost';

 

권한 확인

> use mysql

> select user, host from user;

> show grants for 'tester'@'%';

> show grants for 'tester'@'localhost';

 

MySQL Workbench 접속 

- hostname : 127.0.0.1 (= localhost)

- port : default 3306 포트로 포워딩 (docker run 명령어 참고)

- username/password : 신규 생성한 tester 유저로 접속

*기본적으로 실무에서 client 프로그램으로 root계정 접속하는 일은 없다

 

테이블 생성

CREATE TABLE member (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`));

 

데이터 생성

set autocommit = 1;

delete from member;

insert into member (id, name) values (49, 'Beck');

insert into member (id, name) values (50, 'Aimer');

 

이제 기본 준비가 끝났다

---

트랜잭션 이상현상 테스트

트랜잭션 격리 수준(isolation level)에 따라 아래와 같이 레벨별 이상 현상이 발생가능하다

-가장 낮은 레벨 : READ UNCOMMITED, 가장 높은 레벨 : SERIALIZABLE

-격리 수준이 높아질수록 동시성은 떨어지지만, 데이터 일관성/무결성은 보장이 된다

-반대로 격리 수준이 낮아질수록 데이터 일관성/무결성은 떨어지지만, 동시성은 높아진다.

-MySQL 은 REPEATABLE READ를 기본 격리 레벨로 사용하고 있다

 

 

*트랜잭션 격리 레벨 확인/변경 (MySQL8.0)

// 트랜잭션 격리 레벨 확인 (mysql 8.0)
> show variables like 'transaction_isolation';

// 트랜잭션 격리 레벨 변경
> SET transaction_isolation = 'READ-UNCOMMITTED';

 

*autocommit 설정 확인/변경 (MySQL8.0)

// autucommit 확인
> show variables like 'autocommit';

// autocommit 변경 (1: true, 0: false)
> set autocommit = 0;

 

참고.
보통 자동 커밋 모드가 기본으로 설정된 경우가 많기 때문에, '수동 커밋 모드(autocommit=false)’로 설정하는 것을 ‘트랜잭션을 시작'한다고 표현할 수 있다. 수동 커밋 설정을 하면 이후에 꼭 commit, rollback을 호출해야 한다.
→ timeout을 넘어가면 자동 rollback됨

 

 

1. Dirty Read 

- READ UNCOMMITTED 레벨에서 발생

- commit 되지 않은 데이터를 읽기 가능하면서 데이터 부정합 문제 발생하는 이상 현상

 

 

 

실습

1) 터미널로 두 개의 창(세션1, 2)를 열고 트랜잭션 레벨을 READ-UNCOMMITTED로 맞춘다

// 격리 레벨 변경
> SET transaction_isolation = 'READ-UNCOMMITTED';

// 확인
> show variables like 'transaction_isolation';

 

 

2) 신규 데이터 추가 후 조회

- Tx1(좌)에서 트랜잭션을 삽입하고 신규 데이터 추가

# 트랜잭션 시작
> set autocommit = 0;
> insert into member (id, name) values(51, 'Camel');

 

- Tx2(우)에서는 데이터 조회

#트랜잭션 시작
> set autocommit = 0;

> select * from member where id = 51; // 조회 결과 1건

commit하지 않은 데이터를 세션2에서 조회 가능함을 확인하였다

 

3) 신규 데이터 롤백 처리 후 조회 결과 재확인 

- Tx1(좌)에서는 트랜잭션을 rollback을 수행

> rollback;

> set autocommit = 1;

 

- Tx2(우)에서는 데이터 재조회

> select * from member where id = 51; // 조회 결과 없음

# 트랜잭션 종료
> set autocommit = 1;

 

 

 READ UNCOMMITTED에서는 commit 전 데이터도 읽을 수 있기 때문에 Tx2가 Tx1의 데이터를 읽을 수 있었다.

이후 Tx1이 rollback을 하고, Tx2가 해당 데이터를 조회하게 될 경우 데이터 부정합 문제가 발생하게 되고 이러한 현상을 Dirty Read(더티 리드)라고 부른다.

 

 위의 예제는 데이터 조회에 대한 내용을 다뤘지만, 현금 거래와 같은 상황을 생각한다면 잘못된 데이터로 인해 결과값이 틀려지게 되는 위험을 가지고 있음을 생각 가능했다.

 

 

2. Non-Repeatable Read

- READ UNCOMMITTED , READ COMMITTED 레벨에서 발생

- 트랜잭션 내에서 데이터 조회시 동일한 결과를 보장하지 않는 이상 현상 (데이터 단위)

 

 READ COMMITTED의 경우 커밋된 데이터만 읽을 수 있는다. 즉, 두 세션이 있을 때 Tx1에서 변경한 내용이 커밋되기 전까지는 Tx2에서 변경 내역을 조회할 수 없다. 

 

 

 Tx1이 데이터 수정하고 있을 때 Tx2가 데이터 조회하게 되면 Undo Log에 있는 백업된 데이터를 읽게 된다.

 

 이는 MySQL에서 말하는 Consistent Read 메커니즘이 적용된 것으로 트랜잭션이 일관된 스냅샷(undo log)을 만들어, 트랜잭션이 실행되는 동안에는 이 스냅샷(undo log)을 기반으로 데이터를 읽게 된다. 이로써 다른 트랜잭션이 동시에 데이터를 수정하더라도 트랜잭션은 격리를 유지하면서 일관된 데이터를 읽을 수 있는 것이다.

 

그리고 최종적으로 Tx1이 commit하게 되면 다른 트랜잭션에서도 새롭게 변경된 값을 참조할 수 있다.

 

참고. Undo log 용도에 대한 ChatGPT 답변

Undo log의 주요 용도는 다음과 같습니다:
롤백 시 복구: 트랜잭션이 롤백될 때, undo log를 사용하여 해당 트랜잭션이 변경한 내용을 이전 상태로 복구합니다.

동시성 제어를 위한 격리 수준 유지: Undo log는 동시성 제어를 위해 사용되는 여러 격리 수준에서도 중요한 역할을 합니다. 특히, READ-COMMITTED 격리 레벨 이상에서는 다른 트랜잭션이 커밋된 데이터를 읽을 수 있지만, 트랜잭션이 롤백될 경우 이전 상태로 복구해야 합니다.

요약하면, undo log는 데이터베이스의 일관성과 동시성을 보장하기 위한 메커니즘으로 사용됩니다.

undo log는 스냅샷의 구성 요소 중 하나로, 트랜잭션 간에 서로 공유되는 개념이지만, 실제로 데이터 스냅샷은 각 트랜잭션의 시작 시점에서 개별적으로 생성됩니다.

 

https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

MySQL :: MySQL 8.0 Reference Manual :: 15.7.2.3 Consistent Nonlocking Reads

 

READ-COMMITTED 레벨에서의 NON-REPEATABLE READ 이상 확인

 

실습

1) 두 세션의 격리 레벨을 READ-COMMITTED로 변경한다

// 격리 레벨 변경
> SET transaction_isolation = 'READ-COMMITTED';

// 확인
> show variables like 'transaction_isolation';

 

2) Tx2 조회시 결과가 없음을 확인한다

> set autocommit = 0;

> select * from member where name = 'Amy';
Empty Set

 

3) Tx1에서 데이터 수정 후 commit 할 경우 Tx2에서 데이터 재조회시 데이터가 확인된다

> set autocommit = 0;

> update member set name = 'Amy' where id = 50;

> commit;

> set autocommit = 1;

 

 

 분명 Tx2가 처음 조회했을 때 없던 데이터가 Tx1이 commit 한 후 조회되는 것을 확인 가능했다. 반복 읽기(조회)를 수행하면서 다른 트랜잭션의 커밋 여부에 따라 조회 결과가 달라 질 수 있다. 이러한 데이터 부정합 문제를 Non-Repeatable Read 라고 한다.

 

 

3. Phantom Read

- READ UNCOMMITTED , READ COMMITTED , REPEATABLE READ 레벨에서 발생

- 트랜잭션 내에서 데이터 조회시 다른 트랜잭션에 의해 레코드가 생기거나/사라짐으로써 데이터 부정합이 발생하는 현상 (레코드 단위)

- MySQL 의 REPEATABLE READ 에서는 해당 현상이 거의 발생하지 않는다고 알려져 있다

 

REPEATABLE READ에서는 각 트랜잭션 ID가 있을 때, 자신보다 먼저 실행된 트랜잭션의 데이터만 조회하는 특성을 보인다.

 

1) SELECT 이후 SELECT 하는 경우

-MVCC 때문에 동일한 조회 결과값 보장한다

-팬텀리드가 발생하지 않는다

 

 

// 격리 레벨 변경
> SET transaction_isolation = 'REPEATABLE-READ';

// 확인
> show variables like 'transaction_isolation';

 

① Tx2 부터 시작한다

> set autocommit = 0;

> select * from member where id >= 50;
+----+-------+
| id | name  |
+----+-------+
| 50 | Aimer |
+----+-------+

 

② Tx1에서 트랜잭션을 시작하고 신규 데이터 추가한다

> set autocommit = 0;

> insert into member values(51, 'Amy');

> commit;

 

③ Tx2에서 다시 조회할 경우 Tx1에서 추가한 데이터는 무시하고 기존과 동일한 결과(1건)를 확인가능

 

설명과 마찬가지로 REPEATABLE-READ 레벨에서는 트랜잭션 ID 값 이전에 데이터만 조회하는 것을 확인 가능했다

 

 

2) SELECT .. FOR UPDATE 후 SELECT 

    SELECT .. FOR UPDATE 후 SELECT .. FOR UPDATE

- FOR UPDATE 또는 FOR SHARE로 잠금을 하게 되면, 릴리즈 전까지 다른 트랜잭션은 대기 상태가 된다 

- 팬텀리드가 발생하지 않는다

 

// 격리 레벨 변경
> SET transaction_isolation = 'REPEATABLE-READ';

// 확인
> show variables like 'transaction_isolation';

 

① Tx2가 먼저 시작하여, select .. for update 구문으로 레코드와 조건 범위에 대한 잠금을 건다

 

② Tx1의 경우 신규 데이터 추가하려고 하지만 Tx2이 잠금을 해제(릴리즈)하기 전까지 대기 상태에 빠지게 된다.

 

③ 고로 Tx2는 다시 데이터를 조회할 경우 동일한 결과를 보장받게 된다. 

 

아래는 Tx2가 commit(=잠금 해제)을 한 후 Tx1이 동작하는 것을 확인 가능했다

 

*참고. 용어 

next key lock

A combination of a record lock on the index record and a gap lock on the gap before the index record.

 

record lock 

A lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10. Contrast with gap lock and next-key lock.

 

gap lock

A lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of 15 into the column t.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked. Contrast with record lock and next-key lock.

Gap locks are part of the tradeoff between performance and concurrency, and are used in some transaction isolation levels and not others.

 

 

3) SELECT 후 SELECT .. FOR UPDATE

두 번째 SELECT .. FOR UPDATE로 조회할 경우 Undo Log가 아닌 테이블로부터 레코드를 조회하므로 팬텀리드가 발생한다

 

앞서 1)번과 같이 SELECT 후 SELECT를 하게 될 경우 Tx-id 값이 이전인 데이터 조회했지만, undo log가 아닌 테이블에 레코드락과 갭락을 걸어버림으로써 팬텀리드 현상이 나타남을 확인가능했다.

 

---

 

참고.

 

https://mangkyu.tistory.com/299

[MySQL] 트랜잭션의 격리 수준(Isolation Level)에 대해 쉽고 완벽하게 이해하기

 

https://mangkyu.tistory.com/300

[MySQL] 트랜잭션 격리 수준과 부정합 문제들(Dirty Read, Non-Repeatable Read, Phantom Read) 실습해보기

 

https://zzang9ha.tistory.com/381#2-3-repeatable-read

[MySQL] - 트랜잭션의 격리 수준(Isolation level)

https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html#isolevel_repeatable-read

MySQL :: MySQL 8.0 Reference Manual :: 15.7.2.1 Transaction Isolation Levels