안녕하세요 깐지꾼지파파 한희성입니다 ㅎㅎ
오늘은 mysql 격리 수준을 주제로 테스트 결과 및 과정을 공유 하려고 합니다 !!
주요 시스템 : mysql 8.0, InnoDB
실습 툴 : heidiSQL
현재 사내에서 사용중인 mysql 버전입니다 ~~
바로 본론..
아래 4가지 격리 수준에 대해 공부한 내용을 각 테스트 과정과 사진을 첨부하여 공유 하겠습니다.
1. READ UNCOMMITTED
2. READ COMMITTED
3. REPEATABLE READ(기본 레벨)
4. SERIALIZABLE
실습하기 전 미리 실습에 필요한 테이블 생성 및 데이터 저장
CREATE TABLE user (
idx INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100)
);
INSERT INTO user (name) VALUES ("testUser1");
INSERT INTO user (name) VALUES ("testUser2");
INSERT INTO user (name) VALUES ("testUser3");
SELECT * FROM user;
1. READ UNCOMMITTED
아직 다른 트랜잭션에서 commit 되지 않은 데이터들을 읽어 올 수 있는 레벨, 만약 이 상황에서 다른 트랜잭션이 commit 되지 않고 rollback 되어 버린다면 결국 존재해서는 안 될 데이터를 읽어 온 것이 되어 버린다.. 이것을
dirty read 부름
SET SESSION transaction isolation level READ UNCOMMITTED;
show variables like 'tx_isolation';
TRANSACTION - 1
START TRANSACTION;
SELECT * FROM user;
TRANSACTION - 2
START TRANSACTION;
UPDATE user SET name = "1번 유저 업데이트" WHERE id = 1;
INSERT INTO user (name) VALUES ("testUser4");
SELECT * FROM user;
-- commit 또는 rollback 되지 않은 상황
TRANSACTION - 1
select * from user;
2번 트랜잭션에서 아직 commit 또는 rollback 되지 않은 데이터 확인
정리
1. commit 되지 않은 데이터도 조회 가능(dirty read)
2. 동일한 트랜잭션에서 동일한 select 쿼리가 다를 수 있음(non-repeatable read)
3. 이전의 없던 select 쿼리의 결과가 생김(phantom read)
2. READ COMMITTED
다른 트랜잭션에서 commit 된 데이터만 조회 가능
SET SESSION transaction isolation level READ COMMITTED;
show variables like 'tx_isolation';
TRANSACTION - 1
TRANSACTION - 2
TRANSACTION - 1
select * from user;
TRANSACTION - 2
commit;
TRANSACTION - 1
select * from user;
정리
1. commit 되지 않은 데이터는 조회 불가능
2. 동일한 트랜잭션에서 동일한 select 쿼리가 다를 수 있음(non-repeatable read)
3. 이전의 없던 select 쿼리의 결과가 생김(phantom read)
3. REPEATABLE READ
mysql innodb의 기본 격리수준. 동시성과 안전성의 균형이 잘 이루어질 레벨
SET SESSION transaction isolation level REPEATABLE READ;
show variables like 'tx_isolation';TRANSACTION - 1
START TRANSACTION;
SELECT * FROM user;TRANSACTION - 2
START TRANSACTION;
UPDATE user SET name = "1번 유저 업데이트" WHERE idx = 1;
INSERT INTO user (name) VALUES ("testUser4");
SELECT * FROM user;TRANSACTION - 1
아직 커밋되지 않은 결과 조회 못함
TRANSACTION - 2
commit ;TRANSACTION - 1
SELECT * FROM user;
정리
1. commit 되지 않은 데이터는 조회 불가능
2. 동일한 트랜잭션에서 동일한 select 결과
3. 이전의 없던 select 쿼리의 결과 생기지 않음
mysql 공식 문서에 따르면 REPEATABLE READ, READ COMMITTED 두가지 트랜잭션에 대해 select 쿼리로 데이터를 읽어 올때 테이블 락을 걸지않고, 해당 시점의 상태를 스냅샷을찍어 스냅샷 데이터를 읽어 온다. READ COMMITTED 는 select 쿼리를 실행할때 마다 스냅샷을 처리하여 하나의 트랜잭션이지만 다른 트랜잭션에서 데이터 저장을 한다면 select결과가 다르기도 했다.
하지만 REPEATABLE READ 는 한 트랜잭션에서 처음 select 결과 상태를 계속 불러오기때문에 조회 쿼리의 결과들이 항상 처음과 동일한 것! 공식문서 참고
MySQL :: MySQL 8.0 Reference Manual :: 15.7.2.1 Transaction Isolation Levels
15.7.2.1 Transaction Isolation Levels Transaction isolation is one of the foundations of database processing. Isolation is the I in the acronym ACID; the isolation level is the setting that fine-tunes the balance between performance and reliability, consi
dev.mysql.com
재미있는 사실은 동일한 트랜잭션에서 select 결과는 항상 동일하더라도 update, delete 후 결과는 달라짐 새로운 스냅샷을 찍음
4. SERIALIZABLE
동시성을 상당부분 포기하고 안정성에 큰 비중을 둔 레벨, select 쿼리를 사용하게 되면 모든 행들에 shared lock 또는 read lock을 거는 쿼리
SET SESSION transaction isolation level SERIALIZABLE;
show variables like 'tx_isolation';TRANSACTION - 1
START TRANSACTION;
SELECT * FROM user;
TRANSACTION - 2
SELECT * FROM user;
행들에 shared lock 이 걸려 있어서 조회는 가능하지만
TRANSACTION - 2
UPDATE user SET name = "1번 유저 업데이트테스트" WHERE idx = 1;update 또는 insert 쿼리는 해당 행의 lock 가 풀리기 전까지 수정이나 추가를 할수가 없다.
따라서 위 쿼리를 날리면 결국 실행되지 않고 time out 시간까지 기다린 후 시간이 지나면 아래와 같은 에러 발생
만약 time out 전에 1번 트랜잭션이 commit 상태가 된다면 update 쿼리가 정상적으로 실행 될 것이다.
정리
1. commit 되지 않은 데이터는 조회 불가능
2. 동일한 트랜잭션에서 동일한 select 결과
3. 이전의 없던 select 쿼리의 결과 생기지 않음
위 내용들은 모두 아래 블로그를 참고하여 해당 실습을 직접 진행 하였습니다.
https://jupiny.com/2018/11/30/mysql-transaction-isolation-levels/#repeatableread
댓글