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) 실습해보기