Mysql Transaction 격리 수준
1. Transaction 격리 수준
Transaction(이하 tx) 격리 수준이란 쉽게 말해서 동시에 여러 tx가 처리될 때 특정 tx가 다른 tx가 변경하거나 조회할 때 데이터를 허락할지 말지 정하는 것입니다. tx는 기본적으로 고유한 tx번호를 가지며, 순차적으로 증가합니다. 격리 수준은 4가지로 나뉘며 하나씩 살펴보겠습니다.
2. 데이터 부정합
데이터 부정합에는 Dirt Read, Non Repeatable Read, Phantom Read 가 있습니다. 하나씩 알아보겠습니다.
2-1. Dirty Read
다른 트랜잭션에 의해 수정됐지만 아직 커밋되지 않은 데이터를 읽는 것을 말합니다. 문제점으로는 tx1이 데이터를 변경하고 tx2가 변경된 데이터를 읽었을 때 tx1이 rollback을 진행한다면, tx2는 잘못된 값을 가지고 로직을 진행하는 오류가 발생할 수 있습니다.
2-2. Non Repeatable Read
Non-Repeatable Read는 한 트랜잭션 내에서 같은 Key를 가진 Row를 두 번 읽었는데 그 사이에 값이 변경되거나 삭제되어 결과가 다르게 나타나는 현상을 말합니다. tx1이 시작해 한 Row를 읽고, tx2가 중간에 해당 Row를 변경했을 때 tx1이 처음 읽은 Row가 아닌 변경된 Row의 값을 조회하는 문제입니다.
2-3. Phantom Read
한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 첫 번째 쿼리에서 없던 유령(Phantom) 레코드가 두 번째 쿼리에서 나타나는 현상을 말합니다. tx1에서 한 건의 결과를 조회했는데 그 사이에 tx2가 한 건을 추가했을 때 tx1이 다시 조회했을 때 초기에 조회한 1건이 아닌 2건이 나타나는 문제입니다.
3. DB Setting
직접 MYSQL로 해보면서 할 예정이니 명령어를 알아보고 설정을 진행합니다.
3-1. Transaction Autocommit
기본적으로 tx autocommit이 켜져 있습니다. 아래의 명령어로 autocommit의 활성화 여부를 파악해줍니다.
숫자가 0이라면 꺼져있는 것이고, 1이라면 켜져있는 것입니다.
select @@autocommit;
실습을 위해 autocommit을 꺼줍시다.
set autocommit = false;
// 다시 키고자 할 때
set autocommit = true;
3-2. Transaction 격리 수준
tx 격리 수준 확인 mysql 5.x과 8.x 버전이 isolation 앞부분이 다르기 때문에 %로 처리해줘 두 버전 모두 사용할 수 있습니다.
show variables like '%isolation';
tx 격리 수준 변경은 글로벌과 현재 연결된 세션으로 구성돼 있습니다. 세션만 변경하도록 명령어를 사용합니다.
SET SESSION TRANSACTION ISOLATION LEVEL <변경할 격리 수준> // 대문자로 진행합니다.
3-3. 예제 테이블 생성
예제로 쓰일 user 테이블을 생성합니다.
create table user(
id int not null auto_increment,
money int,
primary key(id));
// 데이터 넣기
insert into user(money) values(100);
insert into user(money) values(150);
insert into user(money) values(200);
insert into user(money) values(250);
insert into user(money) values(300);
4. READ UNCOMMIETD
가장 낮은 격리수준이며, 특정 tx에 대해 데이터 변경 내용이 commit, rollback 여부에 상관없이 보입니다. tx 격리 수준을 READ uncommitted로 변경한 후 예제를 진행하겠습니다.
cmd 창을 두 개 열어줘 세션을 두 개 생성합니다. 두 개의 tx 격리 수준을 READ UNCOMMITED로 변경해줍니다.
한쪽 cmd 창에서 begin; 입력해 tx를 시작합니다. 그 후 money가 100인 사람의 money를 1000으로 변경하는 쿼리를 수행합니다.
update user set money = 1000 where money =100;
현재 commit이나 rollback하지 않은 상태입니다. 현재 cmd1 창에서 모든 user의 레코드를 확인해봅니다.
select * from user;
변경됐습니다. 하지만 commit하지 않은 상태입니다. 이제 나머지 한 개의 cmd2창에서 user의 모든 레코드 값을 확인해봅니다.
commit 하지 않았음에도 변경사항을 확인할 수 있습니다. 이때 cmd1창에서 변경한 내용을 rollback을 수행하면 변경사항이 없어집니다. 따라서 READ UNCOMMITTED에서는 Dirty Read, REPEATABLE READ, PHANTOM READ 모두 발생합니다. 해당 격리 수준은 데이터 정합성에 많은 문제를 초래합니다. 따라서 사용하길 권장하지 않는 격리 수준입니다.
5. READ COMMITTED
READ UNCOMMITTED와는 다르게 변경한 데이터에 대해서 commit한 데이터만 조회 가능하게 해 줍니다. 즉 변경했어도 commit 하지 않았다면 이전 데이터를 조회하게 해 줍니다. 이런 기능은 UNDO 영역을 사용함으로써 가능하게 해 줍니다. 데이터에 대한 변경이 있을 시 UNDO 영역에 변경 전 사항을 기록합니다. 이때 commit 하지 않은 내용을 조회 시 테이블이 아닌 UNDO 영역의 데이터를 읽는 것입니다. UNDO의 역할은 ROLLBACK시에도 이용됩니다. 예제를 진행합니다.
tx 격리 수준을 READ COMMITTED로 변경합니다. 그 후 위에서 봤던 것과 같이 cmd1 창에서 tx를 시작하고, 변경 사항을 진행합니다.
cmd1 에서는 변경 사항이 보입니다. 하지만 cmd2 에서는 변경 사항이 보이지 않습니다. 그 이유는 commit 되지 않은 데이터기 때문에 UNDO 영역에서 데이터를 읽습니다.
따라서 Dirty Read 문제는 해결할 수 있습니다. 이제 cmd1의 변경 사항을 commit 합니다. 이제 cmd2에서도 변경 사항이 적용됐습니다. 하지만 cmd2의 tx는 현재 끝나지 않은 상태입니다. 즉 같은 tx내에서 동일한 조회에도 다른 결과를 조회한 것입니다. 따라서 Non-Repeatable Read 문제는 해결할 수 없습니다.
6. REPEATABLE READ
MYSQL InnoDB 스토리 엔진에서 기본적으로 사용되는 tx 격리 수준입니다. 해당 격리 수준에서는 Non-Repeatable Read 문제가 발생하지 않습니다. 차이점을 먼저 보자면 READ COMMITTED와 같이 UNDO 영역을 사용합니다. 하지만 READ COMMITTED는 바로 이전의 UNDO만 적용하고, REPEATABLE READ는 여러 버전의 관리하는데 차이점이 있습니다. tx에서 데이터의 변경 사항이 있는 row에 대해서 변경한 tx의 고유한번호가 부여됩니다. 자신의 tx 번호보다 높다면 UNDO에서 확인하는 것입니다. 즉 특정 tx 번호의 구간에 따라서 조회되는 값이 변하게 됩니다.
이러한 것을 MVCC(Multi Version Concurrency Control)라고 합니다. 예제를 진행하겠습니다.
tx 격리 수준을 REPEATABLE READ로 변경합니다. 그리고 위에서 진행했던 값을 원래대로 변경해줍니다. 먼저 cmd2 창에서 tx를 시작하고, user 테이블을 전체 조회해서 tx 번호를 부여합니다. 그 후 위의 예제와 동일하게 진행합니다.
이제 cmd2 창에서 확인합니다. 데이터 변경 전의 사항을 확인할 수 있습니다. 그 이유는 cmd1의 tx 번호가 cmd2의 tx 번호보다 높기 때문에 cmd2에서는 cmd1에서의 변경 사항을 반영하지 않은 것을 볼 수 있습니다.
이제 cmd2의 tx를 끝마치고, 다시 한 번 조회를 해보면 cmd1의 조회값과 같은 것을 확인하실 수 있습니다.
7. SERIALIZABLE
tx 격리수준 중에서 가장 엄격한 격리 수준입니다. 안정성은 높지만 동시성이 떨어져 성능상 좋지 않습니다. 해당 격리 수준에서는 select 조회조차도 lock을 획득해야 합니다. tx 격리 수준을 SERIALIZABLE로 변경하고 예제를 진행합니다.
cmd1에서 tx 를시작하고 user 테이블을 조회합니다. 현재 읽기로 lock을 획득한 상황이므로 cmd2에서 변경을 하려고 해도 진행되지 않고, time out이 발생한 것을 알 수 있습니다.
이미 REPEATABLE READ에서 데이터 부정합을 해결할 수 있으므로 SERIALIZABLE을 성능상의 문제로 사용하지 않습니다.
8. 정리
InnoDB를 사용한다면, 데이터 부정합 문제를 REPEATABLE READ 수준에서 해결 가능하므로 REPEATABLE READ를 사용하는 것이 권장 사항입니다.