MySQL - 트랜잭션 격리 수준 실습

⚙️ 실습 세팅

트랜잭션 격리 수준을 실습해보기 위해서 테이블을 만들고 데이터를 넣어두었다.

 

👤 Member Table

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, 'Beomsic');
INSERT INTO MEMBER (id, name) VALUES(2, 'Beomseok');
INSERT INTO MEMBER (id, name) VALUES(3, 'KO');

 

💻 2개의 데이터베이스 세션 설정

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

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

각각의 세션에서 자동 커밋 모드를 꺼준다.

 

🧩 SERIALIZABLE

 

🔍 데이터 조회

 

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

 

SERIALIZABLE 은 일반적인 SELECT 작업에서도 대상 레코드에 넥스트 키 락을 읽기 잠금으로 건다

따라서, 한 트랜잭션에서 읽은 레코드를 다른 트랜잭션에서도 조회할 수 있다.

 

 

🔄 데이터 수정

 

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

 

 

🔒 (A) 잠금 대기 상태

 

 

 

📍 COMMIT 이후 (B)

 

 

✔️ 결과

SELECT * FROM MEMBER WHERE id >= 3;

// id : 3 name : KOBEOMSEOK

 

SERIALIZABLE 격리수준에서 한 트랜잭션이 읽은 데이터를 다른 트랜잭션에서 수정하는 것은 불가능하다.

• 이유 : 읽기 잠금에 의해 변경을 하기 위해서는 락을 획득해야 하기 때문

 

🧩 REPEATABLE READ

 

🏃 REPEATABLE READ 정상 작동

사용자 A	사용자 B
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
	START TRANSACTION;
	SELECT * FROM member WHERE id >= 3; - 1건 조회 성공
START TRANSACTION;
UPDATE MEMBER SET name = ‘KO’ WHERE id = 3; - 업데이트 성공
COMMIT;
	SELECT * FROM member WHERE id >= 3; - 1건 조회 성공 - name : KOBEOMSEOK(변경 전 데이터)

 

REPEATABLE READ에서는 기본적으로 다른 트랜잭션에서 변경한 내용이 보이지 않는다.

 

👻 Phantom Read 발생 (부정합 문제)

사용자 A	사용자 B
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
	START TRANSACTION;
	SELECT * FROM member WHERE id >= 3; - 1건 조회 성공
START TRANSACTION;
INSERT INTO MEMBER (id, name) VALUES (4, 'phantomname'); - 성공
COMMIT;
	SELECT * FROM member WHERE id >= 3; - 2건 조회 (MySQL 이 아닌 경우) - 1건 조회 (MySQL)

 

🧩 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 = ‘KKK’ WHERE id = 3; - 업데이트 성공
	SELECT * FROM member WHERE id >= 3; - 1건 조회 성공 - 변경 전 데이터
COMMIT;
	SELECT * FROM member WHERE id >= 3; - 1건 조회 성공 - name : KKK(변경 후 데이터)

 

 

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

사용자 A	사용자 B
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
	START TRANSACTION;
	SELECT * FROM member WHERE name = ‘KOKO’ - 0건 조회 성공
START TRANSACTION;
UPDATE MEMBER SET name = ‘KOKO’ WHERE Id = 3; - 성공
COMMIT;
	SELECT * FROM member WHERE name = ‘KOKO’ - 1건 조회

 

READ COMMITTED 에서 한 트랜잭션내에서 반복 읽기를 수행할 때, 다른 트랜잭션의 커밋 여부에 따라 조회 결과가 달라진다.

• Non-Repeatable Read

 

🧩 READ UNCOMMITTED

 

💣 Dirty Read 발생 (부정합 문제)

사용자 A	사용자 B
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;	SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
	SELECT * FROM member WHERE name = ‘KKK’ - 0건 조회 성공
UPDATE MEMBER SET name = ‘KKK’ WHERE Id = 3; - 성공
	SELECT * FROM member WHERE name = ‘KKK’ - 1건 조회

 

READ COMMITTED 에서 한 트랜잭션내에서 반복 읽기를 수행할 때, 다른 트랜잭션의 커밋 여부에 따라 조회 결과가 달라진다.

• Non-Repeatable Read

 

📕 참고자료

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