InnoDB Locking

InnoDB에서 사용하는 Lock Type에 대한 정리입니다.

 

Shared and Exclusive Locks

row-level의 lock에서 사용합니다. 

Shared lock(S)는 보유한 트랜잭션이 row를 읽기 위해서 허용해주는 Lock입니다.

Exclusive lock(X)은 보유한 트랜잭션이 row를 update하거나 delete하게 허용해주는 Lock입니다.

 

만약, 트랜잭션 T1이 row r의 Shared Lock을 잡고 있다면, 트랜잭션 T2에 대한 row r의 lock은 다음과 같이 처리됩니다.

- T2에 대한 Shared lock 요청 즉시 허용됩니다. 즉 T1과 T2 둘다 r의 Shared lock을 유지합니다.

- T2에 대한 Exclusive lock 요청은 즉시 허용되지 않습니다.

만약, 트랜잭션 T1이 row r에 대한 Exclusive Lock을 잡고 있다면, 트랜잭션 T2에 대한 row r의 lock은 즉시 허용되지 않습니다. 대신 트랜잭션 T2는 트랜잭션 T1의 lock이 풀릴때까지 대기합니다.

 

Oracle과 마찬가지로 select에 대한 lock은 row에 대한 변경사항이 없으므로 shared lock을 둘 다 획득하고 둘다 조회가 가능합니다. DML의 경우에는 이미 해당 row에 대해 적용한 트랜잭션이 있다면 이전 트랜잭션의 종료(commit or rollback)까지 대기하는 것입니다.

 

Intention Locks

InnoDB는 row lock과 table lock의 공존을 허용하는 다중 세분성 잠금(multuple granularity locking)을 지원합니다.

예를들어, 특정 테이블이 LOCK TABLES ... WRITE 문을 사용해 exclusive lock을 사용합니다. 

다중 세분성 수준을 실용적으로 만들기 위해 intention lock을 사용합니다.

table level lock이고, 테이블의 row에 대해 나중에 트랜잭션이 필요한 lock type(Shared 또는 Exclusive)을 나타냅니다.

두가지 유형이 있습니다.

intention shared lock(IS)는 트랜잭션이 테이블의 개별 row의 shared lock을 설정하려는 의도를 나타내는 lock입니다.

intention exclusive lock(IX)는 트랜잭션이 테이블의 개별 row의 exclusive lock을 설정하려는 의도를 나타내는 lock입니다.

 

SELECT ... FOR SHARE 는 IS lock을 설정하고, SELECT ... FOR UPDATE는 IX lock을 설정합니다.

 

-트랜잭션이 테이블의 row에 대한 shared lock을 획득하기 전에, 반드시 IS lock을 먼저 획득하거나 트랜잭션이 테이블에서 더 강한 트랜잭션이어야 합니다.

- 트랜잭션이 테이블의 row에 대한 exclusive lock을 획득하기 전에 반드시 IX lock을 먼저 획득해야 합니다.

 

아래의 표는 Lock type에 대한 테이블 레벨의 호환성을 나타냅니다.

 

	X	IX	S	IX
X	충돌	충돌	충돌	충돌
IX	충돌	호환	충돌	호환
S	충돌	충돌	호환	호환
IS	충돌	호환	호환	호환

기존 lock과 호환되는 경우 트랜잭션에 lock이 부여되지만 충돌하는 경우에는 허용되지 않습니다.

트랜잭션은 충돌하는 기존 잠금이 해제 될 때까지 대기합니다. 만약 lock이 기존 lock과 충돌하는 경우 deadlock이 발생하여 lock을 부여할 수 없으며 에러가 발생합니다.

 

intention lock은 전체 테이블 요청을 제외하고는 아무것도 차단하지 않습니다.(예 LOCK TABLES ... WRITE)

intention lock의 주된 목적은 누군가가 row에 lock을 걸고 있거나 테이블 내의 row에 lock을 걸고 있는것을 보여줍니다.

 

show engine innodb status에서 아래와 비슷하게 표시됩니다.

TABLE LOCK table `test`.`t` trx id 10080 lock mode IX

 

 

record Locks

인덱스 값에 대한 lock입니다. 예로 SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 을 수행하면 t.c1이 10의 값을 다른 트랜잭션의 insert,update,delete(DML)작업을 못하도록 막아줍니다.

 

record lock은 비록 테이블에 인덱스가 없더라도 항상 인덱스값을 잠급니다. 이런 경우에 InnoDB는 숨겨진 clustered index를 생성하고 이 인덱스를 recode lock에 사용합니다.

 

show engine innodb status에서 아래와 비슷하게 표시됩니다.

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 ;;

 

 

Gap Locks

인덱스 값 사이의 간격에 대한 lock 또는 '첫번째 전' 또는 '마지막 후' 인덱스 값의 간격에 대한 lock입니다.

예로 SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;을 수행하면 t.c1의 15의 값을 다른 트랜잭션이 15값을 입력 못하도록 막아줍니다.  해당 값이 있는지 여부에 관계없이 기존 값 사이의 간격에 lock이 걸립니다.

간격(Gap)은 단일 인덱스 값, 여러 인덱스 값에 걸쳐 있거나 비어있을 수도 있습니다.

 

Gap lock은 성능과 동시성간의 균형의 일부이며 트랜잭션 격리 수준 및 다른것이 아닙니다.

 

unique index을 사용하여 unique한 row를 검색하는 SQL문에는 Gap lock이 필요하지 않습니다. (검색조건에 여러 열의 일부 열만 포함되는 경우는 Gap lock이 발생합니다.)

예를들어 id열에 unique index가 있고 SELECT * FROM child WHERE id = 100; 이 SQL문은 id 값이 100인 row에 인덱스 record lock만 사용하고 gap lock이 발생하지 않습니다.

 

만약 id열이 nonunique index나 index가 없다면 해당 SQL문은 gap lock이 발생합니다.

 

Gap lock은 트랜잭션 격리 수준을 READ COMMIT으로 사용하면 발생합니다. 즉 비활성화를 명시적으로 할 수 있습니다. 이러한 상황에서 검색과 index scan과 오직 외래키 제약검사 및 중복 키 검사에만 비활성화되고 사용됩니다.

 

Next-Key Locks

인덱스 값에 대한 record lock과 이전 인덱스값에 대한 gap lock의 조합입니다.

InnoDB는 테이블 index scan하거나 검색할 때 발견된 인덱스 값에 대한 Shared 또는 exclusive lock을 설정하는 방식으로 row level lock을 수행합니다. 

즉, row level lock은 실제로 인덱스 record lock입니다. 인덱스 값의 next-key lock도 해당 인덱스 값 앞의 "gap"에 영향을 미칩니다.

한 세션의 인덱스값 R에 대한 shared 또는 exclusive lock이 설정되어 있을 때, 다른 세션에서 R 바로 앞의 간격에 새 인덱스 값을 삽입할 수 없습니다.

 

기본적으로 InnoDB는 트랜잭션 격리 수준이 REATUBLE READION 수준에서 작동합니다. 이 경우 InnoDB는 검색 및 인덱스 검색에 다음 키 잠금을 사용하여 phantom rows를 방지합니다.

 

 

Insert Intention Locks

행 삽입 전에 Insert 조작에 의해 설정된 gap lock 유형입니다. 이 lock은 동일한 index gap에 삽입하는 여러 트랜잭션이 gap 내에서 동일한 위치에 삽입뇌지 않는 경우 서로 기다릴 필요가 없도록 삽입하려는 의도를 나타냅니다.

 

값이 4와 7인 인덱스 값이 있다고 가정합니다. 각각 5와 6을 삽입하려고 시도하는 개별 트랜잭션은 삽입된 행에 대한 exclusive lock을 얻기 전에 insert intention lock으로 4와 7사이에 gap을 잠급니다. 하지만 행이 충돌하지 않기 때문에 서로 차단하지는 않습니다.

 

A는 두개의 인덱스 레코드(90 및 102)를 포함하는 테이블을 생성한 다음 ID가 100보다 큰 인덱스 값에 대한 exclusice lock을 설정하는 트랜잭션을 시작합니다. exclusive lock은 102 값 이전의 gap lock이 포함됩니다.

B는 값의 간격사이에 insert하는 트랜잭션입니다. 트랜잭션은 exclusive lock을 획득할 때까지 insert intention lock이 걸려있습니다. 

세션 A

 

mysql> create table child (id int(11) not null, primary key(id)) engine=InnoDB;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> insert into child(id) values (90),(102);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from child;
+-----+
| id  |
+-----+
|  90 |
| 102 |
+-----+
2 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from child where id > 100 for update;
+-----+
| id  |
+-----+
| 102 |
+-----+
1 row in set (0.00 sec)

 

세션 B

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into child (id) values (101);
Query OK, 1 row affected (0.00 sec)


ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

기다리다 에러 발생

 

세션 C에서 확인

SHOW ENGINE INNODB STATUS

------- TRX HAS BEEN WAITING 38 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 264 page no 4 n bits 72 index PRIMARY of table `test`.`child` trx id 2732835 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 00000029b315; asc    )  ;;
 2: len 7; hex 8200000106011d; asc        ;;

 

AUTO-INC Locks

AUTO_INCREMENT 열을 사용하여 테이블에 삽입되는 트랜잭션에 의해 수행되는 특수한 table level lock입니다.

다른 트랜잭션은 첫 번째 트랜잭션에 의해 삽입된 행이 연속적인 기본값을 받도록 해당 테이블에 삽입될 때까지 대기해야 합니다.

innodb_autoinc_lock_mode 파라미터는 자동증가에 사용되는 알고리즘을 제어합니다. 이 옵션을 사용하면 자동 증가 값의 예측 가능한 시퀀스와 삽입 작업에 대한 최대 동시성 사이에서 절충하는 방법을 선택할 수 있습니다.

 

mysql> show variables like '%innodb_autoinc_lock_mode%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 2     |
+--------------------------+-------+
1 row in set (0.01 sec)

 

 

MySQL의 다양한 lock에 대해서 정리해보았습니다. 데이터의 정합성과 성능을 위해서 다양한 Lock을 지원하고 있음을 알 수 있습니다.

아직은 매뉴얼의 내용을 정리만한 상태이고 제대로 머리속에 넣으려면 좀더 걸릴것 같습니다..

 

 

 

 

 

 

 

 

출처 : MySQL 8.0 Reference Manual