MySQL의 isolation level은 ANSI 표준과 다소 다르다. 그래서 어떻게 다른지, 어떻게 isolation level을 유지하는지 확인해본다.
이 문서는 read-committed, repeatable-read에 대해 충분한 이해를 하고 있다는 가정이 포함되어 이 기능에 대한 상세한 설명은 생략한다.
만약, 격리 수준과 잠금에 대한 동작을 이해하고자 한다면 이 글은 적합하지 않다.
사용할 쿼리
많이 사용할 옵션 변경 쿼리는 아래와 같다.
auto commit
MySQL에서 auto commit을 비활성화 해두지 않으면 각 쿼리에 대해 begin, commit or abort를 자동으로 실행하기 때문에 원하는 실험을 올바르게 수행할 수 없다.
SET autocommit = 1;
SQL
복사
auto commit 모드 켜기
SET autocommit = 0;
SQL
복사
auto commit 모드 끄기
SHOW VARIABLES LIKE 'autocommit';
SQL
복사
auto commit 모드의 현재 상태
Isolation level 수준 변경
set tx_isolation = 'READ-COMMITTED';
SQL
복사
Read committed 로 변경하기
set tx_isolation = 'REPEATABLE-READ';
SQL
복사
Repeatable read 로 변경하기
SHOW VARIABLES LIKE 'transaction_isolation';
SQL
복사
현재 설정된 isolation level 확인하기
실행중인 잠금 확인하기
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
SQL
복사
테스트 시나리오
임의의 트랜잭션 a, b를 가정한다.
시나리오 1번 pk로 update 쿼리 실행하기 (repeatable-read)
1.
트랜잭션 a의 begin
2.
트랜잭션 a는 데이터 edison의 first_name을 edi로 변경한다.
update actor_demo set first_name = 'edi' where actor_id = 3;
SQL
복사
3.
트랜잭션 b에서 actor_id 3의 데이터를 확인한다.
4.
트랜잭션 b는 데이터 edisot의 first_name을 jam 으로 변경한다.
update actor_demo set first_name = 'jam' where actor_id = 3
SQL
복사
5.
잡혀있는 lock을 확인한다.
6.
트랜잭션 a를 commit 한다.
7.
잡혀잇는 lock을 확인한다.
8.
트랜잭션 b를 commit 한다.
시나리오 2번 pk에 대한 range로 갱신하기 (repeatable-read)
1.
트랜잭션 a의 begin
2.
트랜잭션 a는 데이터 edison의 first_name을 edi로 변경한다.
update actor_demo set first_name = 'edi' where actor_id >= 3 and actor_id < 4;
SQL
복사
3.
트랜잭션 b에서 actor_id 3의 데이터를 확인한다.
4.
트랜잭션 b는 데이터 edisot의 first_name을 jam 으로 변경한다.
update actor_demo set first_name = 'jam' where actor_id >= 3 and actor_id < 4;
SQL
복사
5.
잡혀있는 lock을 확인한다.
6.
트랜잭션 a를 commit 한다.
7.
잡혀잇는 lock을 확인한다.
8.
트랜잭션 b를 commit 한다.
시나리오 3번 pk로 update 쿼리 실행하기 (read-committed)
1.
트랜잭션 a의 begin
2.
트랜잭션 a는 데이터 edison의 first_name을 edi로 변경한다.
update actor_demo set first_name = 'edi' where actor_id =
SQL
복사
3.
트랜잭션 b에서 actor_id 3의 데이터를 확인한다.
4.
트랜잭션 b는 데이터 edisot의 first_name을 jam 으로 변경한다.
update actor_demo set first_name = 'jam' where actor_id = 3
SQL
복사
5.
잡혀있는 lock을 확인한다.
6.
트랜잭션 a를 commit 한다.
7.
잡혀잇는 lock을 확인한다.
8.
트랜잭션 b를 commit 한다.
시나리오 1번 테스트 결과
Repeatable read에서 임의의 동일한 data를 서로 다른 트랜잭션에서 update 할 때 lock이 걸릴까?
트랜잭션 a의 begin
actor_id 3번에 해당하는 데이터인 edison을 edi로 변경할 예정이다
트랜잭션 a는 데이터 edison의 first_name을 edi로 변경한다.
update actor_demo set first_name = 'edi' where actor_id = 3;
SQL
복사
이때 트랜잭션 a에서 결과는 아래와 같다.
Repeatable read는 MVCC이므로 snapshot을 열어 각 record의 버전을 따로 관리한다.
트랜잭션 b에서 actor_id 3의 데이터를 확인한다.
트랜잭션 b에선 트랜잭션 a에서 실행한 쿼리가 아직 commit이 되지 않았기 때문에 edi 로 반환되지 않는다.
트랜잭션 b는 데이터 edisot의 first_name을 jam 으로 변경한다.
update actor_demo set first_name = 'jam' where actor_id = 3
SQL
복사
이때 console을 보면 명령이 block 되어 있다.
잡혀있는 lock을 확인한다.
•
lock_mode: x
◦
Exclusive
•
lock_type: RECORD
•
lock_index: PRIMARY
트랜잭션 a를 commit 한다.
트랜잭션 a를 커밋하면 즉시 트랜잭션 b의 blocking이 풀린다. → lock이 릴리즈된다.
잡혀잇는 lock을 확인한다.
확인해보면 lock이 존재하지 않는다.
트랜잭션 b를 commit 한다.
최종적으로 jam으로 commit되어 있다.
결론, repeatable read에선 lock을 사용해 서로 다른 트랜잭션의 동시성을 유지한다.
시나리오 2번 테스트 결과
테스트의 결과가 시나리오 1번과 동일하여 갼략하게 설명한다.
•
트랜잭션 a, b를 시작한다.
•
트랜잭션 a에서 먼저 id = 3에 대해 update 를 실행한다.
◦
아직 commit은 하지 않는다.
•
트랜잭션 b에서 id = 3에 대해 update를 실행한다.
◦
Lock이 걸린다.
•
트랜잭션 a에서 commit을 실행하면 lock은 해제된다.
Range라 하더라도 index에 대해서 lock을 이용해 서로 다른 트랜잭션의 동시성을 유지한다.
여기서 사용되는 lock 역시 동일하게 record lock 이다.
테스트 시나리오 3번
Isolation level: Read-committed
Read committed에서 commit된 데이터의 repeatable read가 올바르게 동작할까?
Isolation level을 변경한다.
테스트 시나리오 1번과 동일하게 실행한다.
트랜잭션 a의 begin
transaction begin
actor_id = 3의 결과가 edison으로 반환된다.
트랜잭션 a는 데이터 edison의 first_name을 edi로 변경한다.
만약 read committed에서 MVCC가 없다면, commit 직후, 트랜잭션 b에선 edi가 반환되어야 한다.
트랜잭션 b의 결과
트랜잭션 a에서 commit한 직후, 트랜잭션 b에서 select를 해보면 edison이 edi로 변경된 것을 확인할 수 있다.
즉, read committed 에선 repeatable read는 사용되지 않는다. (읽기마다 데이터가 바뀔 수 있다.)
(참고) 트랜잭션 a가 commit 되기 전 트랜잭션 b에서 동일한 데이터를 수정하려고 하는 경우
테스트 시나리오 1, 2, 3에서 보면 record lock을 발견할 수 있다. record lock이란 무엇일까?
Shared lock, exclusive lock은 이미 많이 들었지만, record lock이 생소하다.
Record Locks
Record lock은 index record에 대해서 lock을 거는 것이다.
select c1 from t where c1 = 10 for update;
SQL
복사
이러한 쿼리를 실행했을 때 c1 = 10을 만족하는 다른 트랜잭션에 대해 inserting, updating, deleting 작업을 막는다.
Record lock은 항상 index records에 잠금을 거는 것이다.
만약 table에서 정의한 index가 없다 하더라도(이 경우 hidden clustered index가 생성되고 여기에 lock을 건다) index records를 대상으로 잠금을 수행한다.
Record lock이 실행되면 show engine innodb status 를 실행할 경우 아래와 같이 출력된다.
---TRANSACTION 3213, ACTIVE 33 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 18, OS thread handle 278483085056, query id 1946 localhost root updating
update actor_demo set first_name = 'jam' where actor_id = 3
------- TRX HAS BEEN WAITING 26 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 85 page no 3 n bits 280 index PRIMARY of table `sakila`.`actor_demo` trx id 3213 lock_mode X locks rec but not gap waiting
Record lock, heap no 210 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 000000000c8c; asc ;;
2: len 7; hex 5a00000195052a; asc Z *;;
3: len 3; hex 656469; asc edi;;
4: len 5; hex 4348415345; asc CHASE;;
5: len 4; hex 64f2ac32; asc d 2;;
SQL
복사
위 현상은 read committed 에서 show engine innodb status 를 실행한 결과이다.
•
TRX HAS BEEN WAITING 26 SEC FOR THIS LOCK TO BE GRANTED
◦
트랜잭션이 26초 동안 아래의 lock을 granted 되도록 기다리고 있다.
다음의 record lock을 설명을 참고하면, phantom read 까지 예방할 수 있다고 파악된다. (단, for update 가 붙은 exclusive lock인 경우)
A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.
질문
inserting과 deleting을 막는다는 것은 phantom read를 예방한다는 뜻일까?
Read-committed 에서 for update 를 사용하면 phantom read가 발생하는가?
트랜잭션 a에서 id가 11 미만인 대상을 읽는다.
트랜잭션 a의 결과
•
트랜잭션 a에서 begin을 수행한다.
•
select * from actor_demo where actor_id < 11 for update; 을 수행한다.
•
쿼리 결과는 1~10까지의 row가 반환된다.
트랜잭션 b에서 id가 0인 row를 추가한다.
INSERT INTO actor_demo (actor_id, first_name, last_name, last_update) VALUES (0, 'Joshua', 'Park', DEFAULT);
SQL
복사
ID(PK)가 0인 row를 추가한다. 아직 commit 전이다.
위 쿼리를 실행한 결과는 아래와 같다. (아직 트랜잭션 b에서 commit을 실행하지 않았다)
트랜잭션 b의 select 결과, insert한 row가 반환된다. (아직 commit 전)
트랜잭션 b는 0 부터 10까지 해당하는 데이터가 반환된다.
이때 트랜잭션 a에서 for update 를 제외한 쿼리의 결과는 아래와 같다.
트랜잭션 a의 결과, 트랜잭션 b에서 insert한 row가 반환되지 않는다.
ID가 0인 row는 반환되지 않는다.
하지만, for update를 붙여서 select 쿼리를 실행하면 아래와 같다.
Lock이 걸려있음을 확인할 수 있다. 이유는 트랜잭션 b에서 아직 commit을 하지 않았기 때문이다.
이때 걸려있는 lock을 확인해보면 동일하게 record lock이 사용되고 있다.
select .. for update 가 실행되지 않는 이유는 record lock이 실행중이다.
그럼 트랜잭션 b에서 commit을 하면 어떻게 될까?
트랜잭션 b에서 커밋을 실행하자 마자 lock이 풀렸다.
트랜잭션 a에선 lock이 풀리고, select for update의 결과는 새로 추가된 0번 row가 반환된다.
결론
즉, isolation level read committed 에선 phantom read 현상이 발생한다.
Isolation level repeatable read
그렇다면 repeatable read에선 phantom read가 발생하는가?
ANSI 표준의 isolation level을 따르면 phantom read는 serializable에서 예방된다.
set tx_isolation = 'REPEATABLE-READ';
SQL
복사
isolation level을 변경한다.
트랜잭션 a, b 모두의 isolation level을 repeatable read로 변경하고 0번 row를 제거하여 동일한 환경을 만들어 0번 row로 실험을 다시 해보자.
트랜잭션 a에서 begin, select ~ for update 를 실행한다.
최초 테스트 환경이 발생된다.
트랜잭션 b에서 insert를 실행한다.
0번 row를 생성하는 insert 쿼리를 실행하니 lock이 걸렸다.
걸려있는 lock을 확인해보면 X.GAP 락이 걸려있음을 확인할 수 있다.
이 lock은 트랜잭션 b에서 commit하면 해제된다.
즉, 트랜잭션 a에서는 몇 번을 select를 실행해도 동일한 결과를 확인할 수 있다. 팬텀 리드가 발생하지 않는 것이다.
select ~ for update 즉, exclusive lock 때문에 이런일이 발생하는게 아닐까?
일반적인 select를 사용하여 묵시적 for share(shared lock)을 실행하면 어떻게 될까?
Repeatable read에서 shared lock은 phantom read가 발생할까?
트랜잭션 a에서 select 를 실행한다.
트랜잭션 a의 실행 결과
트랜잭션 b에서 0번 row를 insert 한다. (with commit)
0번 row를 insert 하는 쿼리를 실행하면, lock이 걸리지 않고 insert가 된다.
여기서 commit을 실행하면 문제 없이 저장된다.
저장 후 commit까지 되어 확인해보면 table에 추가되었다.
만약, id 0번 row가 트랜잭션 a에 나타난다면 phantom read가 발생하는 것이고
나타나지 않는다면 phantom read가 없는 것이다.
트랜잭션 a에선 0번 row가 나타날까?
트랜잭션 a의 실행 결과
트랜잭션 a에선 트랜잭션 b가 추가하고, commit한 0번 row가 나타나지 않는다. → phantom read가 발생하지 않는다.
그리고 별다른 lock도 발견되지 않는다.
테스트 이후 결과
ANSI isolation level과 mysql isolation level의 구현을 고려해보면 다음과 같은 결론을 낼 수 있다.
MySQL에서 사용하는 Lock.
MySQL 문서를 확인해보면 다음의 lock들을 사용한다.
그런데 묵시적으로 lock을 사용하게 되는 것은 Record Locks 이다.
ANSI의 isolation level repeatable read와 mysql innoDB의 isolation level repeatable read는 구현이 다르다.
ANSI의 isolation level serializable은 동시성을 없애다 시피 하여 트랜잭션을 순서대로 나열한 것과 같다.
그래서 phantom read를 예방하는 것이고, phantom read와 같은 상황을 피하길 원한다면, serializable을 사용해야 한다.
하지만, MySQL InnoDB engine에선 repeatable read에서 phantom read를 예방함을 알 수 있다.
이러한 phantom read를 예방하는 이유는 X.GAP lock이다.
왜 이런 글을 쓰게 되었는가?
처음의 호기심은 다양한 포스팅에서 말하는 shared lock, exclusive lock이 어떻게 사용되는지 알고 싶었다.
일반적인 책이나 다른 포스팅에서 읽었을 때 select, update를 호출하면 묵시적으로 shared lock, exclusive lock이 동작하는 것으로 받아들였기 때문이다.
하지만, 개발 스승님은 언제나 책과 포스팅을 의심해야 한다고 하였고, 정말 그렇게 동작하는지 확인해보라 충고 해주셨다. 이제서야 확인해본 것이 오히려 부끄럽다.
MySQL의 모든 lock에 대해선 후속으로 포스팅 하겠다.
이후 추가된 테스트 시나리오
시나리오 3번(Isolation level: read-committed)에서 사용한 select 쿼리가 select … for update 이다.
만약 select for update를 사용하지 않는다면 어떨까?
begin;
update actor_demo set first_name = 'edi' where actor_id = 3;
SQL
복사
트랜잭션 a에서 실행한 쿼리
begin;
update actor_demo set first_name = 'jam' where actor_id = 3;
SQL
복사
트랜잭션 b에서 실행한 쿼리
트랜잭션 b는 잠금 대기 상태로 진입했다.
이 상태에 걸려있는 잠금은 RECORD lock이고, mode는 X이다.
트랜잭션 b는 충분한 시간이 지난 후 대기 상태에서 해제된다.
Lock wait timeout이 초과되어 wait이 종료되었다.
트린잭션 a가 commit 되면 어떻게 될까?
1.
트랜잭션 a: begin;
2.
트랜잭션 a: select actor_id = 3;
a.
first_name: edison
3.
트랜잭션 b: begin;
4.
트랜잭션 b: select actor_id = 3;
a.
first_name: edison
5.
트랜잭션 a: commit;
6.
트랜잭션 b: select actor_id = 3;
a.
first_name: edi (트랜잭션 a에서 변경한 값으로 반환)
b.
read-committed는 commit된 데이터를 읽는다.
Read-committed는 snapshot을 내부적으로 사용하지만, transaction id를 사용하여 undo log를 확인하여 이전 버전을 보여주는 일은 하지 않는다.
트랜잭션이 시작되어도 commit 되어 공통으로 수정하는 데이터가 변경되면 변경된 값이 반환된다.
위의 6번 상태에서 jam으로 변경한다면 기존의 edison은 edi를 거쳐 jam으로 first_name이 변경될 것이다.
