[MySQL][InnoDB] Locking

■ Shared and Exclusive Locks

InnoDB는 공유 (S) 잠금과 배타적 (X) 잠금의 두 가지 유형의 잠금이있는 표준 행 수준 잠금을 구현합니다.

+ 공유 (S) 잠금은 잠금을 보유한 트랜잭션이 행을 읽을 수 있도록합니다.

+ 독점 (X) 잠금은 잠금을 보유한 트랜잭션이 행을 업데이트하거나 삭제할 수 있도록 합니다.

 

트랜잭션 T1이 행 r에 공유 (S) 잠금을 보유하는 경우, 행 r에 대한 잠금에 대한 일부 고유 트랜잭션 T2의 요청은 다음과 같이 처리됩니다.

+ T2가 S 잠금 요청을 즉시 승인 할 수 있습니다. 결과적으로 T1과 T2는 모두 r에 S 잠금을 유지합니다.

+ T2에서 X 잠금 요청을 즉시 승인 할 수 없습니다.

 

트랜잭션 T1이 행 r에서 배타적 (X) 잠금을 보유하는 경우, r에서 두 유형의 잠금에 대한 일부 고유 트랜잭션 T2의 요청을 즉시 승인 할 수 없습니다. 대신, 트랜잭션 T2는 트랜잭션 T1이 행 r에서 잠금을 해제 할 때까지 기다려야합니다.

 

■ Intention Locks

InnoDB는 행 잠금과 테이블 잠금을 같이 허용하는 다중 단위 잠금을 지원합니다. 예를 들어, LOCK TABLES ... WRITE와 같은 명령문은 지정된 테이블에서 독점 잠금 (X 잠금)을 사용합니다. 여러 세밀한 수준에서 잠금을 실용화하기 위해 InnoDB는 의도 잠금(intention lock)을 사용합니다. 의도 잠금은 트랜잭션이 테이블의 행에 대해 나중에 필요한 잠금 유형 (공유 또는 독점)을 나타내는 테이블 수준 잠금입니다. 의도 잠금에는 두 가지 유형이 있습니다.

+ 의도 공유 잠금 (IS:intention shared lock)은 트랜잭션이 테이블의 개별 행에 공유 잠금을 설정하려고 함을 나타냅니다.

+ 의도 배타 잠금 (IX:intention exclusive lock)은 트랜잭션이 테이블의 개별 행에 배타 잠금을 설정하려고 함을 나타냅니다.

 

예를 들어, SELECT ... LOCK IN SHARE MODE는 IS 잠금을 설정하고 SELECT ... FOR UPDATE는 IX 잠금을 설정합니다.

 

의도 잠금의 규약은 다음과 같습니다.

+ 트랜잭션이 테이블의 행에서 공유 잠금을 획득하려면 먼저 테이블에서 IS 잠금 이상을 획득해야합니다.

+ 트랜잭션이 테이블의 행에서 독점 잠금을 획득하려면 먼저 테이블에서 IX 잠금을 획득해야합니다.

 

위에 의도잠금에 따른 잠금규약은 잠금 종류에 따라 호환이 되고 안되는게 있습니다. 테이블 레벨 잠금 유형 호환성은 다음 매트릭스에 요약되어 있습니다.

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

 

기존 잠금과 호환이 가능하지만 기존 잠금과 충돌하지 않는 경우, 요청 트랜잭션에 잠금이 부여됩니다. 만약 기존 트랜잭션이 충돌하는 경우 충돌하는 기존 잠금이 해제 될 때까지 트랜잭션이 대기합니다. 잠금 요청이 기존 잠금과 충돌하고 교착 상태가 발생하여 권한을 부여 할 수없는 경우 오류가 발생합니다.

의도 잠금(intention lock)은 전체 테이블 요청 (예 : LOCK TABLES ... WRITE)을 제외하고는 아무것도 차단하지 않습니다. 의도 잠금의 주요 목적은 누군가 행을 잠그거나 테이블에서 행을 잠그는 것을 보여주는 것입니다.

의도 잠금에 대한 트랜잭션 데이터는 SHOW ENGINE INNODB STATUS 및 InnoDB 모니터 출력에서 다음과 유사하게 나타납니다.

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

 

 

 

■ Record Locks

레코드 잠금은 인덱스 레코드에 대한 잠금입니다. 예를 들어 어떤 트랜잭션이 SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;인 쿼리를 수행하게 되면 t.c1=10인 행은 모두 lock이 걸리게 됩니다 .이때 t.c1의 값이 10인 다른 트랜잭션은 행을 삽입, 업데이트 또는 삭제하지 못하게됩니다.

 

테이블이 인덱스없이 정의 된 경우에도 레코드 잠금은 항상 인덱스 레코드를 잠급니다. 이러한 경우 InnoDB는 숨겨진 클러스터형 인덱스를 생성하고 이 인덱스를 레코드 잠금에 사용합니다. 이게 MySQL의 여러 특징중 하나인데 테이블에 잠금을 사용하는게 아니라 인덱스에 잠금을 사용한다는 것입니다.

 

레코드 잠금에 대한 트랜잭션 데이터는 SHOW ENGINE INNODB STATUS 및 InnoDB 모니터 출력에서 다음과 유사하게 나타납니다.

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

갭 잠금은 인덱스 레코드 사이의 간격에 대한 잠금 또는 첫 번째 또는 마지막 인덱스 기록 이후의 간격에 대한 잠금입니다. 예를 들어, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 쿼리를 실행하면, 범위에있는 모든값들이 잠기기 때문에(10에서 20사이의 모든값. 10에서 20사이가 범위이기 때문에 이것을 갭(gap)이라고 한다.) 다른 트랜잭션이 열에 값이 이미 있는지 여부에 관계없이 열 t.c1에 값 15를 삽입하지 못하게합니다.

 

갭 잠금은 단일 인덱스 값, 여러 인덱스 값에 걸쳐 있거나 비어있을 수 있습니다.

 

갭 잠금은 성능과 동시성 간에 상충 관계의 일부입니다. 몇몇 트랜잭션 격리 수준에서 사용되며 다른 곳에서는 사용되지 않습니다. 유니크 인덱스를 사용하여 고유 행을 검색하고 행을 잠그는 명령문에는 갭 잠금이 필요하지 않습니다. (여기에는 검색 조건에 다중 열 유니크 인덱스의 일부 열만 포함되는 경우는 포함되지 않으며, 이 경우 갭 잠금이 발생합니다.) 예를 들어, id 열에 고유 인덱스가 있는 경우 다음 명령문만 사용합니다. id 값이 100 인 행에 대한 인덱스 레코드 잠금이며 다른 세션이 이전 간격에 행을 삽입하는지 여부는 중요하지 않습니다.

mysql > SELECT * FROM child WHERE id = 100;

id가 색인화되지 않거나 고유하지 않은 색인을 갖는 경우, 명령문은 선행 간격을 잠급니다.

 

또한 서로 다른 트랜잭션에 의해 충돌 잠금을 공백으로 유지할 수 있다는 점도 주목할 가치가 있습니다. 예를 들어, 트랜잭션 A는 갭에 공유 갭 잠금 (갭 S- 잠금)을 보유 할 수있는 반면, 트랜잭션 B는 동일한 갭에 독점 갭 잠금 (갭 X- 잠금)을 보유합니다. 갭 잠금 충돌이 허용되는 이유는 레코드에서 인덱스를 제거하는 경우 다른 트랜잭션에 의해 레코드에 보유 된 갭 잠금을 병합해야하기 때문입니다.

 

InnoDB의 갭 잠금은“순전히 금지 적”이므로 다른 트랜잭션이 갭에 삽입되는 것을 방지하는 것이 유일한 목적입니다. 갭 잠금 장치가 공존 할 수 있습니다. 한 트랜잭션이 수행 한 갭 잠금은 다른 트랜잭션이 동일한 갭에서 갭 잠금을 수행하는 것을 막지 않습니다. 공유 간격 잠금과 배타적 간격 잠금에는 차이가 없습니다. 이 잠금들은 서로 충돌하지 않으며 동일한 기능을 수행합니다.

 

갭 잠금은 명시 적으로 비활성화 할 수 있습니다. 트랜잭션 분리 레벨을 READ COMMITTED로 변경하거나 innodb_locks_unsafe_for_binlog 시스템 변수 (이제 더 이상 사용되지 않음)를 사용 가능하게하는 경우 발생합니다. 이러한 상황에서는 검색 및 인덱스 스캔에 갭 잠금이 비활성화되고 외래 키 제약 조건 검사 및 중복키 검사에만 사용됩니다.

 

READ COMMITTED 격리 수준을 사용하거나 innodb_locks_unsafe_for_binlog를 활성화하면 다른 효과도 있습니다. 일치하지 않는 행에 대한 레코드 잠금은 MySQL이 WHERE 조건을 평가 한 후에 해제됩니다. UPDATE 문의 경우 InnoDB는 "반 일치"읽기를 수행하여 최신 커밋 된 버전을 MySQL로 반환하여 MySQL이  해당 행이 UPDATE의 WHERE 조건과 일치하는지 확인할 수 있도록합니다.

 

■ Next-Key Locks

다음 키 잠금은 인덱스 레코드의 레코드 잠금과 인덱스 레코드 이전의 갭 잠금 간의 조합입니다.

 

InnoDB는 테이블 인덱스를 검색하거나 스캔 할 때 발생하는 인덱스 레코드에 공유 잠금 또는 독점 잠금을 설정하는 방식으로 행 수준 잠금을 수행합니다. 따라서 행 레벨 잠금은 실제로 인덱스 레코드 잠금입니다. 인덱스 레코드의 다음 키 잠금은 해당 인덱스 레코드 이전의 "갭"에도 영향을줍니다. 즉, 다음 키 잠금은 인덱스 레코드 잠금과 인덱스 레코드 앞의 간격에 대한 간격 잠금입니다. 한 세션의 인덱스에서 레코드 R에 대한 공유 또는 독점 잠금이있는 경우, 다른 세션은 인덱스 순서에서 R 바로 앞의 간격에 새 인덱스 레코드를 삽입 할 수 없습니다.

 

색인에 값 10, 11, 13 및 20이 포함되어 있다고 가정합니다.이 색인의 가능한 다음 키 잠금은 다음 간격을 포함합니다. 여기서 둥근 대괄호는 간격 끝점을 제외하고 대괄호는 끝점을 포함합니다.

(negative infinity, 10]

(10, 11]

(11, 13]

(13, 20]

(20, positive infinity)

 

마지막 간격 동안, 다음 키 잠금은 인덱스에서 가장 큰 값과 실제로 인덱스에 있는 임의의 값보다 높은 값을 갖는 "최고의"의사(pseudo) 레코드 위로 간격을 잠급니다. 최상위는 실제 인덱스 레코드가 아니므로 사실상 이 다음 키 잠금은 가장 큰 인덱스 값 다음의 간격만 잠급니다.

 

기본적으로 InnoDB는 REPEATABLE READ 트랜잭션 격리 수준에서 작동합니다. 이 경우 InnoDB는 검색 및 인덱스 스캔에 다음 키 잠금을 사용하여 팬텀 행을 방지합니다 (14.7.4 절“팬텀 행”참조).

 

다음 키 잠금에 대한 트랜잭션 데이터는 SHOW ENGINE INNODB STATUS 및 InnoDB 모니터 출력에서 다음과 유사하게 나타납니다.

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

 

■ Insert Intention Locks

삽입 의도 잠금은 행을 삽입하기 전에 INSERT 조작으로 설정된 갭 잠금 유형입니다. 이 잠금은 동일한 인덱스 간격에 삽입하는 여러 트랜잭션이 간격 내의 동일한 위치에 삽입되지 않는 경우 서로를 기다릴 필요가 없는 방식으로 삽입하려는 의도를 나타냅니다. 값이 4와 7 인 인덱스 레코드가 있다고 가정합니다. 각각 5와 6의 값을 삽입하려고 시도하는 개별 트랜잭션은 각각 삽입 된 행에서 독점 잠금을 얻기 전에 삽입 의도 잠금으로 4와 7 사이의 간격을 잠급니다. 행이 충돌하지 않기 때문에 서로를 차단하지 않습니다.

 

다음 예는 삽입 된 레코드에서 독점 잠금을 얻기 전에 삽입 의도 잠금을 취하는 트랜잭션을 보여줍니다. 이 예에는 두 명의 클라이언트 A와 B가 포함됩니다.

 

클라이언트 A는 두 개의 인덱스 레코드 (90 및 102)를 포함하는 테이블을 작성한 다음 ID가 100보다 큰 인덱스 레코드에 독점 잠금을 설정하는 트랜잭션을 시작합니다. 독점 잠금에는 레코드 102 이전의 간격 잠금이 포함됩니다.

 

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

 

클라이언트 B는 트랜잭션을 시작하여 간격에 레코드를 삽입합니다. 트랜잭션은 독점 잠금을 얻기 위해 기다리는 동안 삽입 의도 잠금을 사용합니다.

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

 

삽입 의도 잠금에 대한 트랜잭션 데이터는 SHOW ENGINE INNODB STATUS 및 InnoDB 모니터 출력에서 다음과 유사하게 나타납니다.

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 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 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

 

 

 

■ AUTO-INC Locks

AUTO-INC 잠금은 AUTO_INCREMENT 열이있는 테이블에 삽입하는 트랜잭션에 의해 수행되는 특수 테이블 수준 잠금입니다. 가장 간단한 경우, 한 트랜잭션이 테이블에 값을 삽입하는 경우 다른 트랜잭션은 해당 테이블에 자체 삽입을 수행하여 첫 번째 트랜잭션에 의해 삽입 된 행이 연속적인 기본 키 값을 수신하도록 대기해야합니다.

 

innodb_autoinc_lock_mode 구성 옵션은 자동 증분 잠금(auto-increment lock)에 사용되는 알고리즘을 제어합니다. 이를 통해 예측 가능한 자동 증분 값 시퀀스와 삽입 작업에 대한 최대 동시성 사이에서 균형을 유지하는 방법을 선택할 수 있습니다.

 

■ Predicate Locks for Spatial Indexes

InnoDB는 공간 열을 포함하는 열의 SPATIAL 색인 생성을 지원합니다.

 

SPATIAL 인덱스와 관련된 작업의 잠금을 처리하기 위해 다음 키 잠금은 반복 읽기 또는 직렬화 가능 트랜잭션 격리 수준을 지원하는 데 효과적이지 않습니다. 다차원 데이터에는 절대 순서 개념이 없으므로 "다음"키가 무엇인지 명확하지 않습니다.

 

SPATIAL 인덱스가있는 테이블에 대한 격리 수준을 지원할 수 있도록 InnoDB는 술어(predicate=sql의where조건) 잠금을 사용합니다. SPATIAL 인덱스에는 최소 경계 사각형 (MBR) 값이 포함되어 있으므로 InnoDB는 쿼리에 사용되는 MBR 값에 대한 술어(predicate=sql의 where조건) 잠금을 설정하여 인덱스에 대한 일관된 읽기를 시행합니다. 다른 트랜잭션은 쿼리 조건과 일치하는 행을 삽입하거나 수정할 수 없습니다.