[Transaction] 02. Transaction Isolation Level

이번 포스트에서는 Transaction Isolation level에 대해 살펴보자.

 

독립성과 Isolation Level

 

Isolation Level

데이터베이스에서는 lock의 경직성에서 동시성을 확보하기 위해 트랜젝션에 isolation level을 줘서 독립성과 동시성을 적절한 레벨에서 선택해서 사용할 수 있게 한다. isolation level은 동시에 여러 트랜젝션이 처리될 때 특정 T.X가 다른 T.X에서 변경중이거나 조회중인 데이터를 불 수 있게 허용할 것인가를 결정하는 수준으로 Read Uncommitted, Read Committed, Repeatable Read, Serialize 4가지가 존재한다.

• Read Uncommitted: 각 T.X의 commit, rollback 여부와 상관 없이 다른 T.X의 값을 읽어올 수 있다. 이 수준은 데이터 정합성에 문제가 많기 때문에 권장하지 않는다. Dirty Read, Non Repeatable Read, Phantom Read가 발생한다.
• Read Committed: 각 T.X에서 commit, rollback을 하면 다른 T.X에서 읽을 수 있다. Oracle등 가장 많은 DBMS에서 기본으로 채택하고 있는 정책이다. Non Repeatable Read, Phantom Read가 발생한다.
• Repeatable Read: 한 T.X가 한번 조회한 값은 다른 T.X에서의 commit, rollback과 무관하게 언제나 변함이 없다. MySql에서의 기본 고립성 수준이다.select for update 등에서 Phantom Read가 발생한다.
• Serializable: 모든 select 쿼리가 select for update로 간주되서 lock을 필요로 하게 된다.어떤 문제도 발생하지 않는다.

Read Uncommitted -> Serializable로 갈 수록 문제가 줄어들기 때문에 안정성은 높아지지만 동시성 문제로 성능은 낮아질 수밖에 없다. 

위에서 말하는 Dirty Read, Non Repeatable Read, Phantom Read는 글로 설명하기가 까다롭다. 실제 쿼리를 해보면서 알아보자.

mysql transaction isolation level 조정

transaction isolation level도 autocommit 과 유사하게 조회 및 설정할 있는데 설정 후에는 commit 처리가 필요하다.

# 전역의 isolation 속성 변경
set @@global.transaction_isolation='repeatable-read'; # 또는 'read-committed'

# 현재 세션의 isolation 속성 변경
set @@session.transaction_isolation='repeatable-read'; # 또는 'read-committed'

# 속성 조회
select @@global.transaction_isolation, @@session.transaction_isolation;

# 적용
commit;

주의!!

mysql workbench의 경우 global isolation이 설정되어있더라도 자체적으로는 새로운 세션마다 언제나 repeatable-read로 시작하는 버그가 있다. (mac에서는 잘 저장되어있는 것으로 확인 된다. 정말 버그인가보다.) - 8.0.36에서 해결됨?

 

READ-UNCOMMITTED

 

2개의 transaction을 사용하는데 편의상 A가 편집하고 B가 읽는 형태로 테스트를 진행한다. 따라서 A의 level은 상관 없고 B의 level을 그때 그때 바꿔가며 테스트 하면 된다.

A TX는 account를 업데이트 하고 B TX는 200원 이상의 account를 갖는 고액 자산가를 찾는다고 생각해보자.

 

설정

B의 isolation level을 read-uncommitted로 설정한다.

set @@session.transaction_isolation='READ-UNCOMMITTED';
commit;
select @@session.autocommit, @@session.transaction_isolation;

 

테스트

Transaction A	Transaction B
select * from account;	select * from account where amount>=200;
update account set amount=200 where no=1; select * from account;	Transaction A가 update로 Transaction 을 시작함
commit 되지 않은 임시 데이터가 조회되고 있음  - B가 이 데이터를 참조해서 다른 행위를 했는데 A가 rollback 하면?  - Dirty Read 문제 발생	select * from account where amount>=200; 고액 자산가는 1명 있습니다.!

 

READ-COMMITTED

 

설정

B의 isolation level을 READ-COMMITTED로 변경한다.

set @@session.transaction_isolation='READ-COMMITTED';
commit;
select @@session.autocommit, @@session.transaction_isolation;

 

테스트

Transaction A	Transaction B
select * from account;	select * from account where amount>=200;
update account set amount=200 where no=1; select * from account;	Transaction A가 update로 Transaction 을 시작함
아직 commit 전이므로 새로운 값을 읽어들이지 않음  - Dirty Read 해소	select * from account where amount>=200; 현재는 고액 자산가가 없습니다.
commit;	Transaction A 가 commit으로 반영함
TX B의 입장에서는 아무일도 안하고 조회만 했는데 이전 값이 아님  - TX B 가 긴 작업을 하고 있는데 조회 과정에서 불일치 발생  - Non Repeatable Read 문제 발생	select * from account where amount>=200; 분명히 없었는데..

 

REPEATABLE-READ

 

설정

set @@session.transaction_isolation='REPEATABLE-READ';
commit;
select @@session.autocommit, @@session.transaction_isolation;

 

테스트

일반적인 select동작은 테이블에서 데이터를 조회하고 필요하면(현재의 T.X 시작 후 변경된 경우) undo 로그에서 현재의 T.X 이전의 데이터만 가지고 조회한다. 아래의 조회 내용에서 TX-ID는 이해를 돕기 위한 가상의 값이다. (테이블에서는 순서와 상관 없이 조회 가능하다.)

Transaction A(TX-ID: 3)	Transaction B(TX-ID:2)
select * from account;	select * from account where amount >=200; 우리는 고액 자산가가 없군!
update account set amount=200 where no=1; select * from account;	Transaction A가 update로 Transaction 을 시작함  - undo에 기존 자료를 백업하고 table에 임시 자료 저장
테이블의 값은 TX-ID가 낮으므로 스킵, UNDO 테이블에는 대상 자료가 없음  - Dirty Read 해소	select * from account where amount >=200; 우리는 고액 자산가 없지?
commit;	Transaction A 가 commit으로 반영함
최초 읽었던 데이터를 계속 반환함  - Non Repeatable Read 문제 해소	select * from account where amount>=200; 음.. 우리는 고액 자산가가 확실히 없군!
TX B가 작업을 끝냄  - 다음 TX-ID=4	commit; 고액 자산가 추출 작업 끝!
TX-ID=4번으로 조회	select * from account where amount>=200; 음. 그사이 한명이 늘었군.

 

Phantom Read

Repeatable Read는 select를 UNDO 영역에서 실행하므로 phantom read가 발생하지는 않는다. 그런데 select .. for update 처럼 DML을 위한 select가 있다. 이 녀석은 DML을 전제로 하기 때문에 데이터 락을 잡는데 UNDO 영역에는 lock을 잡을 수 없기 때문에 어쩔 수 없이 테이블을 대상으로 조회를 하게 되고 READ-COMMITTED처럼 동작한다. 그래서 이 경우에는 Phantom Read가 발생한다. (하지만 mysql innodb에서는 이 경우 next key lock(row lock + gap lock)을 잡아버리기 때문에 phantom read가 발생하지 않는다. 아래 내용은 이론으로만 정리하자.)

Transaction A(TX_ID:3)	Transaction B(TX_ID:2)
select * from account;	select * from account where amount>=100; 아직 우리는 중위권의 자산가가 1명 있군 업데이트 할까?
insert into account (name, amount) values('jang gil san', 300); commit; select * from account;	추가에 별도의 lock이 없기 때문에 insert가 진행된다. commit 도 완료해서 lock을 놓음
실제 테이블에서 조회를 진행하기 때문에 새로 추가된 데이터가 조회된다.  - Phantom Read 문제 발생	select * from account where amount>=100 for update;

 

Serialize

 

설정

set @@session.transaction_isolation='SERIALIZABLE';
commit;
select @@session.autocommit, @@session.transaction_isolation;

 

테스트

Transaction A	Transaction B
select * from account;	select * from account where amount >=200; 고액 자산가를 골라보자.
insert into account (name, amount) values('jang gil san', 300); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction	일반적인 select 지만 lock을 점유해버리기 때문에 commit 전까지는 동작하지 않음
commit으로 lock을 반환함	commit;
insert into account (name, amount) values('jang gil san', 300);	Transaction A는 자유롭게 자료를 추가할 수 있음