개요
데드락의 원인을 찾던 도중, MySQL 내부의 락과 트랜잭션의 정확한 동작이 궁금해져 공식문서를 뒤적거리면서 관련된 내용을 정리해보고 싶어졌다.
MySQL Lock
Intention Lock
Intention Lock은 말 그대로 잠겨진 혹은 잠글 예정인 Lock으로, row에 Share Lock을 얻으면 해당 table에 IS Lock을 획득한다.
MySQL은 row lock과 table 락의 공존(동시 잠금)을 가능하게 하기 위해, multiple granularity locking을 지원한다. multiple granularity locking은 잠금을 획득하는 노드(페이지)의 하위 노드(페이지)에 대해 Intention Lock을 걸어, 해당 노드의 하위 노드에 삽입 동작이 수행될 예정임을 나타내는 잠금이다.
Lock과 동일하게 충돌이 없다면 잠금을 획득하고, 충돌이 발생한다면 기존 잠금이 해제될 때까지 대기한다.
TABLE LOCK table `test`.`t` trx id 10080 lock mode IX
SQL
복사
잠금요청이 기존 잠금과 충돌하여 교착 상태를 발생시킬 수 있다면 위와 같은오류가 발생한다. 이를 통해서 하위 노드에서 읽기 혹은 쓰기 작업이 수행 중일 때, 상위 노드의 직접적인 수정을 막는다.
Record Lock
레코드에 대한 잠금으로, 항상 인덱스 레코드를 잠근다. 설정이 설정된 인덱스가 없다면 클러스터 인덱스를 잠그고, 그 마저도 없으면 숨겨진 클러스터된 인덱스를 생성해 해당 인덱스를 레코드 잠금에 사용한다.
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 00000000274f; asc 'O;;
2: len 7; hex b60000019d0110; asc ;;
SQL
복사
Gap Lock
Gap Lock은 인덱스 레코드 사이의 간격 혹은 첫 번째 인덱스 레코드 이전, 마지막 인덱스 레코드 이후에 대한 잠금이다. 잠금은 단일 인덱스 혹은 여러 인덱스를 잠글 수 있고, 인덱스의 각 페이지의 infimum과 supremum 레코드를 잠금으로써 비어있는 레코드를 잠글 수도 있다.
SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;
SQL
복사
고유 인덱스의 경우에는 Gap Lock이 사용되지 않는다. 예를 들어 SELECT * FROM child WHERE id = 100에서 id가 인덱싱이 되어있지 않거나, unique가 아니라면 gap lock이 사용된다.
주목할 점은 Gap lock은 서로 충돌이 발생하는 경우에도 잠금을 보유할 수 있다.(gap S lock - gap X lock 을 동시에 서로 다른 트랜잭션에서 거는 것이 가능) 이는 Gap Lock은 순수 억제형으로 다른 트랜잭션이 갭에 삽입되는 것을 방지하는 것이 목적이기 때문이다.
gap lock은 명시적으로 비활성화 할 수 있으며, 트랜잭션 격리수준이 read committed인 경우에는 검색 및 인덱스 스캔에 gap lock이 사용되지 않고 외래키 제약 조검 검사 및 중복 키 검사에만 사용된다.
next-key lock
next-key lock은 gap lock과 index record lock의 조합으로, 테이블 인덱스를 검색하거나 스캔할 때 발견되는 인덱스에 S lock이나 X lock을 설정하는 방식으로 수행된다. 때문에 실질적으로는 next-key lock은 index record lock이다. next-key lock을 통해 index record lock과 그 앞의 gap에 대한 잠그게 되고, 잠금이 걸린 인덱스 레코드나 그 앞의 갭에 다른 세션은 새 인덱스 레코드를 삽입할 수 없다.
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 00000000274f; asc 'O;;
2: len 7; hex b60000019d0110; asc ;;
SQL
복사
기본 격리수준인 repeatable read에서는 next-key lock을 통해 phantom row 현상을 방지한다. 또한 next-key lock을 통해 존재하지 않는 데이터에 대한 잠금을 획득하는 것도 가능하다.
Insert Intention Lock
행 삽입 전에 Insert 연산에 의해 수행되는 일종의 gap lock으로, 삽입 의도를 알려 동일한 index gap에 삽입하는 여러 트랜잭션이 gap 내의 동일한 위치에 삽입하지 않는 경우 서로 기다릴 필요 없도록 한다. 예를 들어 4와 7인 인덱스 레코드가 있는 상황에서 서로 다른 트랜잭션에서 5와 6에 대한 삽입이 발생할 때 4와 7 사이의 간격에 insert intention lock을 획득하지만, 충돌하지 않기 때문에 서로를 차단하지 않는다.
MySQL Isolation Level
Read Committed
select for update나 update, delete 문은 인덱스 레코드만 잠그고 그 앞의 갭은 잠그지 않는다.(갭 잠금은 외래 키 제약 조건 검사 및 중복 키 검사에만 사용된다) 이로 인해 phantom row 문제가 생길 수 있다.
Repeatable Read
MySQL의 기본 격리 수준으로, consistent read를 통해 읽을 때 스냅샷(Undo 로그)을 생성하여 트랜잭션 내에서 일관성을 유지한다.
다음과 같이 select for update나 update, delete 문의 경우 조건에 따라 사용되는 잠금이 달라지게 된다.
•
검색에 유니크 인덱스가 사용되는 경우 index record만 record 락을 잠그고 그 이전 간격에 대한 gap lock은 사용하지 않음
•
그 외에는 gap lock 혹은 next-key lock을 사용해 스캔된 인덱스 범위를 잠금.
일반적으로 트랜잭션 잠금 쿼리(update, insert, delete, select for update)는 serializable한 상황을 원하기 때문에, 비잠금 select 문과 위의 잠금 쿼리를 혼합해서 사용하는 것은 권장되지 않는다.
Serializable
자동 커밋이 비활성화 되어있다면 모든 select를 암시적으로 select for update로 변환하여 수행한다. 반대로 자동 커밋이 활성화 되어있는 경우에는, select는 자체 트랜잭션으로 수행되기 때문에 읽기 전용(read-only)으로 수행되며 다른 트랜잭션에 대해 차단하지 않는다.
Serializable에서 select for share와 select for update에 대한 동작은 다음과 같다.
select for share
•
읽은 모든 행에 공유 잠금을 설정
•
다른 세션을 해당 행을 읽을 수 있지만 수정할 수 없다
select for update
•
읽은 모든 행에 배타 잠금을 설정
•
다른 트랜잭션은 해당 행을 업데이트하거나 select for share를 통한 조회를 할 수 없다
기타 관련 내용
MySQL 트랜잭션 스케줄링
MySQL에서 트랜잭션 스케줄링은 더 많은 다른 트랜잭션을 차단하는 트랜잭션을 우선하여 스케줄링하여 트랜잭션의 더 빠른 처리를 유도한다. 만약 두 트랜잭션의 우선순위가 같다면 더 오래 기다린 트랜잭션을 우선하여 처리한다.
Consistent Read
MySQL에서는 읽기의 일관성을 위해 몇 가지 Consistent Read에 대한 동작을 수행한다.
read committed에서는 읽기 수행할 때마다 스냅샷을 떠서 스냅샷의 데이터를 수정하기 때문에, 커밋하기 전에는 다른 트랜잭션에서 읽더라도 해당 내용이 보이지 않는다.
repeatable read에서는 트랜잭션 시작 시 스냅샷을 떠서 위와 동일하게 수행된다. 하지만 select for share와 같이 잠금을 획득하며 수행되는 읽기는 스냅샷에서 잠금을 수행할 수 없어 실제 레코드에서 읽어온다. 이 때문에 Phantom read 현상이 발생한다. select가 아닌 DML의 경우에도 잠금을 획득하기 때문에, 다른 repeatable read 트랜잭션에서 커밋된 DML(select 제외)이 영향을 미치게 된다.