MySQL lock & Isolation level

2021. 1. 1. 18:32ㆍ개발/DB

728x90

MySQL lock & isolation level

본 문서는 inno DB engine 기반으로 작성이 되어 있다.

MySQL lock types

아래와 같은 lock들이 존재한다.

1. Base lock

기본적으로 Row level lock이다.

S (shared lock): read a row
X (exclusive lock): update or delete a row

2. Intenstion lock

테이블 안의 “row에 대해서 나중에 어떤 row-level락을 걸 것”이라는 의도를 알려주기 위해 미리 table-level에 걸어두는 lock, multiple granularity locking 방식의 일부로 동작함

아래의 쿼리를 통해 intention lock의 상태를 볼 수 있다.

SHOW ENGINE INNODB STATUS

IS (intention shared lock)
IX (intention exclusive lock)

3. Record lock

index record에 걸리는 lock. 만약 index가 걸려있지 않다면 innoDB는 hidden clustered index를 생성하고, 해당 인덱스를 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        ;;

4. Gap lock

gap lock의 경우에는 range로 query를 실행할 때 걸린다.
반드시 gap lock이 수행되는 것이 아니고, where절에서 index 조건이 되지 않는 column을 검색할 때 수행되며,

transaction isolation level을 READ_COMMITTED 또는 innodb_locks_unsafe_for_binlog 해당 옵션을 enable 하게되면 해당 gap lock은 수행되지 않게 된다.

5. Next-Key lock ( 더 자세한 확인 필요 )

gap lock + record lock의 조합으로 만들어진다.
예를 들어서 1 < id < 30이면 record lock은 1과 30이 걸릴 수 있고,그 사이의 row들에게 gap lock이 걸리게 된다.
추측으로는 index를 a, b로 걸고 a로만 할 경우에 a값들이 record lock이 걸리고 그 사이 range들이 gap lock이 걸릴 것으로 추정된다.

6. AUTO-INC Lock

auto increment한 칼럼을 insert시에 발생하는 lock

Lock table

다음은 lock type 호환성에 대한 테이블이다.
Compatible은 충돌이 안나기 때문에 두 lock이 deadlock을 유발할 수 있는 가능성이 없고,
Conflict는 충돌로 인해 대기상태로 빠져 deadlock을 유발할 수 있는 연산을 의미한다. 여기서 볼 것은 같은 IX, IX의 경우 compatible 한데, 이는 multi-insert시에 gap lock이 걸려 있더라도, 같은 row를 업데이트 하지 않는 이상 대기 상태에 빠지지 않는다.

	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
IS	Conflict	Compatible	Compatible	Compatible

Transaction isolation level

Mysql은 여러가지 isolation level을 지원하는데, 해당 내용에 관련하여, 몇가지 용어를 우선 설명하려고 한다.

Dirty Read: commit이 확정되지 않은 데이터도 읽을 수 있다.
Non-Repeatable Read: 한 트랜잭션에서 select를 두 번 할 경우 다른 값이 읽혀질 수 있다.
Phantom Read: 한 트랜잭션 내에서 다른 레코드가 추가 삭제되는 현상이 있을 수 있다.

위의 현상을 참조하여 inno DB engine의 isolation level은 다음과 같다.
아래로 갈수록 격리성 수준이 높다고 판단하면 된다.

Name	InnoDB description	Dirty Read	Non-Repetable Read	Phantom Read	Value
READ UNCOMMITTED	SELECT는 lock이 걸리지 않음. 그리고 uncommit된 데이터를 다른 트랜잭션에서 읽어들일 수 있음. (SNAPSHOT 생성이 되지 않음)	O	O	O	0
READ COMMITTED	같은 트랙잭션 내에 대해서도 fresh snapshot을 획득 가능함. 즉 다른 트랜잭션의 업데이트가 있을 경우 해당 내용이 중간에 반영될 수 있음. GAP lock은 foreign-key constraint를 체크할 경우에만 쓰이고, 쓰이지 않음. row based binary logging 지원.	X	O	O	1
REPEATABLE READ	기본 설정. 같은 트랜잭션에 대해서는 SNAPSHOT을 생성하여 consistent read가 가능하게 한다.(값이 중간에 바뀌지 않음), 위의 설명한 대로 multi search에서 lock의 경우 index를 타는 경우에는 index record에만 락이 걸리고, 아닌 경우 gap lock과 next-key lock을 활용함.	X	X	O	2
SERIALIZABLE	AUTO COMMIT이 비활성화 된 경우 모든 SELECT statement에 shared lock이 걸린다. AUTO_COMMIT이 활성화 된 경우 SELECT는 하나의 transaction으로 동작한다.	X	X	X	3

출처