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

이번에는 트랜잭션 격리 수준(Isolation Level)과 그로 인해 발생하는 부정합 문제를 직접 실습해보도록 하겠습니다. 혹시 트랜잭션 격리 수준(Isolation Level)에 대한 이해가 부족하다면 관련 포스팅을 참고해주세요.

 

 

 

 

1. 트랜잭션의 격리 수준(Transaction Isolation Level)

---

트랜잭션 격리 수준을 실습하기 위해서는 먼저 테이블을 만들고 데이터를 넣어두어야 한다. 아래의 쿼리를 통해 먼저 테이블을 생성해두도록 하자. 해당 테이블은 자동 증가열을 pk로 하며, name에는 넥스트 키 락을 위해 인덱스를 설정해두었다.

create table member (
    id bigint auto_increment primary key,
    name varchar(255) not null,
    constraint UK_name unique (name)
);

INSERT INTO member (id, name) VALUES (1, 'Hello')
INSERT INTO member (id, name) VALUES (2, 'Mang')
INSERT INTO member (id, name) VALUES (3, 'Kyu')

 

 

그리고 이제 서로 다른 2개의 데이터베이스 세션이 필요하다. 그리고 각각의 세션에서 자동 커밋 모드를 꺼주면 준비가 완료된다. 중간에 현재 세션의 격리 수준을 확인하려면 아래 쿼리를 실행하면 된다.

// 현재 세션의 자동 커밋 비활성화
set autocommit = FALSE;

// 현재 세션의 격리 수준 확인
SELECT @@tx_isolation;

 

사용자 A	사용자 B
set autocommit = FALSE;	set autocommit = FALSE;

 

 

 

 

[ SERIALIZABLE ]

SERIALIZABLE 데이터 조회

앞선 포스팅에서 설명하였듯 SERIALIZABLE은 순수한 SELECT 작업에서도 대상 레코드에 넥스트 키 락을 읽기 잠금(공유락, Shared Lock)으로 건다. 따라서 한 트랜잭션에서 읽은 레코드를 다른 트랜잭션에서도 조회할 수는 있다.

사용자 A	사용자 B
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;	SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
	START TRANSACTION;
	(1건 조회 성공) SELECT * FROM member WHERE id >= 3;
START TRANSACTION;
(1건 조회 성공) SELECT * FROM member WHERE id >= 3;
COMMIT;
	COMMIT;

 

 

 

SERIALIZABLE 데이터 수정

SERIALIZABLE 격리 수준에서 한 트랜잭션이 읽은 데이터를 다른 트랜잭션에서 수정하는 것은 불가능하다. 왜냐하면 읽기 잠금에 의해 변경을 하려면 락을 획득해야 하기 때문이다.

사용자 A	사용자 B
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;	SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
	START TRANSACTION;
	(1건 조회 성공) SELECT * FROM member WHERE id >= 3;
START TRANSACTION;
(잠금 대기)  UPDATE member SET name = 'MangKyu' WHERE id = 3;
	COMMIT;
UPDATE 쿼리 실행 완료
COMMIT;

 

 

 

 

 

[ REPEATABLE READ ]

REPEATABLE READ 정상 동작

REPEATABLE READ에서는 기본적으로 다른 트랜잭션에서 변경한 작업 내역이 보이지 않는다. 먼저 REPEATABLE READ가 정상 동작하여 데이터 부정합 문제가 발생하지 않는 상황을 살펴보도록 하자.

사용자 A	사용자 B
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
	START TRANSACTION;
	(1건 조회 성공) SELECT * FROM member WHERE id >= 3;
START TRANSACTION;
(쿼리 성공) UPDATE member SET name = 'Kyu' WHERE id = 3;
COMMIT;
	(1건 조회 성공, Mangkyu, 변경 전 데이터) SELECT * FROM member WHERE id >= 3;

 

 

 

REPEATABLE READ 부정합 문제 발생(Phantom Read)

앞선 포스팅에서 설명하였듯, MySQL의 REPEATABLE READ에서는 Phantom Read(유령 읽기)가 일반적으로 발생하지 않는다. MySQL에서 Phantom Read를 발생시키려면 사용자 B가 첫 조회에는 잠금이 없지만, 두 번째 조회에는 잠금을 획득해야 한다.

사용자 A	사용자 B
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
	START TRANSACTION;
	(1건 조회 성공) SELECT * FROM member WHERE id >= 3;
START TRANSACTION;
(쿼리 성공) INSERT INTO member (id, name) VALUES (4, 'Phantom');
COMMIT;
	(2건 조회 성공, Mangkyu & Phantom) SELECT * FROM member WHERE id >= 3 FOR UPDATE;

 

 

 

 

 

[ READ COMMITTED ]

READ COMMITTED 정상 동작

READ COMMITTED에서는 다른 트랜잭션에서 커밋한 내역만 조회할 수 있다. 먼저 READ COMMITTED가 정상 동작하여 데이터 부정합 문제가 발생하지 않는 상황을 살펴보도록 하자.

사용자 A	사용자 B
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
(쿼리 성공) UPDATE member SET name = 'God' WHERE id = 3;
	(1건 조회 성공, Kyu, 변경 전 데이터) SELECT * FROM member WHERE id >= 3;
COMMIT;
	(1건 조회 성공, God, 변경 후 데이터) SELECT * FROM member WHERE id >= 3;

 

 

 

READ COMMITTED 부정합 문제 발생(Non-Repeatable Read)

READ COMMITTED에서 발생하는 Non-Repeatable Read는 한 트랜잭션 내에서 반복 읽기를 수행하면 다른 트랜잭션의 커밋 여부에 따라 조회 결과가 달라지는 문제이다.

사용자 A	사용자 B
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
	START TRANSACTION;
	(0건 조회 성공) SELECT * FROM member WHERE name = 'Kong';
START TRANSACTION;
(쿼리 성공) UPDATE member SET name = 'Kong' WHERE id = 3;
COMMIT;
	(1건 조회 성공, Kong) SELECT * FROM member WHERE name = 'Kong';

 

 

 

 

 

 

[ READ UNCOMMITTED ]

READ UNCOMMITTED 부정합 문제 발생(Dirty Read)

READ UNCOMMITTED는 커밋하지 않은 데이터 조차도 접근할 수 있는 격리 수준이다. READ UNCOMMITTED에서는 다른 트랜잭션의 작업이 커밋 또는 롤백되지 않아도 즉시 보이게 된다.

사용자 A	사용자 B
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;	SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
(쿼리 성공) UPDATE member SET name = 'KongKong' WHERE id = 3;
	(1건 조회 성공, KongKong) SELECT * FROM member WHERE name = 'KongKong';

 

 

 

 

 

 

 

관련 포스팅

• 스토리지 엔진 수준의 락의 종류(레코드 락, 갭 락, 넥스트 키 락, 자동 증가 락)
• 트랜잭션의 격리 수준(Isolation Level)에 대해 쉽고 완벽하게 이해하기
• 트랜잭션 격리 수준과 부정합 문제들(Dirty Read, Non-Repeatable Read, Phantom Read) 실습해보기