MySQL 8.0 InnoDB Locking

현재 진행중인 교내 프로젝트에서 락을 걸어야하는 경우가 발생하여 DB로 사용중인 Mysql 8.X(Engine=InnoDB)의 Lock에 대해 알아보고자 합니다. 해당 글은 Mysql 8.0 공식 문서를 참고하여 작성된 글입니다. 자세한 내용은 하단의 Reference를 참고 하시면 됩니다!

Lock 종류

• Shared and Exclusive Locks
• Intention Locks
• Record Locks
• Gap Locks
• Next-Key Locks
• Insert Intention Locks
• AUTO-INC Locks
• Predicate Locks for Spatial Indexes

Shared and Exclusive Locks

• 공유(Shared) Lock과 배타적(Exclusive) Lock의 두 가지 잠금 유형은 표준 행 수준 잠금을 구현합니다.
• 공유(S)잠금은 잠금을 보유한 트랜잭션이 행을 읽을 수 있도록 허용합니다.
• 배타적(X) 잠금은 잠금을 보유한 트랜잭션이 행을 업데이트하거나 삭제할 수 있도록 허용합니다.

공유(S) 잠금 예시)
트랜잭션 T1이 행 r에 대한 공유(S)잠금을 보유하고 있는을 때, 일부 개별 트랜잭션 T2의 행 r에 대한 잠금 요청 하는 경우
-> T2의 S 잠금 요청은 즉시 승인 됩니다. 결과적으로 T1과 T2는 모두 r에 대한 S 잠금을 보유하게 됩니다.
-> T2의 X 잠금 요청은 즉시 승인될 수 없습니다.

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

Intention Locks

• 행 잠금과 테이블 잠금이 공존할 수 있는 다중 세분성 잠금을 지원합니다.

예를 들어, LOCK TABLES ... WRITE 문은 지정된 테이블에 배타적 잠금(X 잠금)을 사용합니다. 여러 세분화 수준에서 잠그는 것을 실용적으로 만들기 위해 InnoDB는 Intention 잠금을 사용합니다.

• Intention 잠금은 테이블의 행에 대해 나중에 트랜잭션에 필요한 잠금 유형(공유 또는 배타적)을 나타내는 테이블 수준 잠금입니다. Intention 잠금에는 두 가지 유형이 있습니다:
• 의도 공유 잠금(IS)은 트랜잭션이 테이블의 개별 행에 공유 잠금(S)을 설정하려는 의도를 나타냅니다.
• 의도 독점 잠금(IX)은 트랜잭션이 테이블의 개별 행에 배타적 잠금(X)을 설정하려는 의도를 나타냅니다.

테이블 수준 잠금 유형 호환성은 다음 표에 요약되어 있습니다.

• 요청하는 트랜잭션이 기존 잠금과 호환되는 경우 잠금이 부여되지만, 기존 잠금과 충돌하는 경우에는 잠금이 부여되지 않습니다. 트랜잭션은 충돌하는 기존 잠금이 해제될 때까지 대기합니다. 잠금 요청이 기존 잠금과 충돌하여 교착 상태가 발생하여 잠금을 부여할 수 없는 경우 오류가 발생합니다.
• Intention 잠금은 전체 테이블 요청(예: LOCK TABLES ... WRITE)을 제외하고는 아무것도 차단하지 않습니다. 즉, Intention 잠금의 주요 목적은 누군가 행을 잠그고 있거나 테이블의 행을 잠그려고 한다는 것을 표시하는 것입니다.

예시)표에서 IX와 IX는 Compatible이기에, 하나의 테이블에 트랜잭션 T1이 IX를 가지고 있으면, 트랜잭션 T2는 IX를 가질 수 있습니다. 그러나 한 행에 대해 트랜잭션 T1이 X를 가지면 트랜잭션 T2는 X를 가질 수 없습니다.

Record Locks

• Record Lock은 인덱스 레코드에 대한 잠금입니다.

Record Lock 예시) SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 는 다른 트랜잭션이 t.c1의 값이 10인 행을 삽입, 업데이트 또는 삭제하지 못하도록 합니다.

• 레코드 잠금은 테이블이 인덱스 없이 정의된 경우에도 항상 인덱스 레코드를 잠급니다. 이러한 경우 InnoDB는 숨겨진 클러스터된 인덱스를 생성하고 이 인덱스를 레코드 잠금에 사용합니다.

Gap Locks

• Gap Lock은 인덱스 레코드 사이의 간격에 대한 잠금 또는, 첫 번째 혹은 마지막 인덱스 레코드 앞뒤의 간격에 대한 잠금입니다.

Gap Lock 예시), SELECT c1 FROM t WHERE c1 BETWEEN 10과 20 FOR UPDATE; 는 범위의 모든 기존 값 사이의 간격이 잠겨 있으므로 열에 이미 해당 값이 있든 없든 다른 트랜잭션이 t.c1 열에 15라는 값을 삽입하지 못하도록 합니다.

• Gap은 단일 인덱스 값, 여러 인덱스 값에 걸쳐 있을 수도 있고 비어 있을 수도 있습니다.
• Gap Lock은 성능과 동시성 사이의 절충안의 일부입니다.
• 서로 다른 트랜잭션이 갭에 충돌하는 잠금을 보유할 수 있습니다. 예를 들어 트랜잭션 A는 하나의 갭에 Share Gap Lock(Gap S-lock)을, 트랜잭션 B는 동일한 갭에 Exclusive Gap Lock(Gap X-lock)을 유지할 수 있습니다. 충돌하는 Gap Lock이 허용되는 이유는 하나의 레코드가 인덱스에서 제거될 경우, 다른 트랜잭션이 해당 레코드에 보유한 갭 잠금을 병합해야 하기 때문입니다.
• 즉, InnoDB의 Gap Lock 기능은 "순수 억제형"으로, 다른 트랜잭션이 Gap에 삽입되는 것을 방지하는 것이 유일한 목적입니다.

Next-Key Locks

• Next Key Lock은 인덱스 레코드에 대한 Record Lock과 인덱스 레코드 앞의 Gap에 대한 Gap Lock을 조합한 것입니다.
• InnoDB는 테이블 인덱스를 검색하거나 스캔할 때 마주치는 인덱스 레코드에 Share 또는 Exclusive Lock을 설정하는 방식으로 행 수준 잠금을 수행합니다. 따라서 행 수준 잠금은 실제로는 인덱스 레코드 잠금입니다.
• 인덱스 레코드에 대한 Next-Key Lock은 해당 인덱스 레코드 앞의 'Gap'에도 영향을 미칩니다. 한 세션이 인덱스의 레코드 R에 Share 또는 Exclusive Lock을 설정한 경우, 다른 세션은 인덱스 순서에서 R 바로 앞의 갭에 새 인덱스 레코드를 삽입할 수 없습니다.
• Next Key Lock 예시) 인덱스에 값 10, 11, 13, 20이 포함되어 있다고 가정해 보겠습니다. 이 인덱스에 대해 가능한 다음 키 잠금은 다음 간격을 포함하며, 여기서 둥근 대괄호는 간격 끝점을 제외하고 대괄호는 끝점을 포함합니다.

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

• 마지막 간격에서 Next Key Lock은 인덱스에서 가장 큰 값과 실제 인덱스의 어떤 값보다 높은 값을 가진 "최상위" 의사 레코드 사이의 간격을 잠급니다. 최상위 레코드는 실제 인덱스 레코드가 아니므로 사실상 이 Next Key Lock은 가장 큰 인덱스 값 다음의 간격만 잠급니다.

Insert Intention Locks

• Insert Intention Lock은 행 삽입 전에 INSERT 연산에 의해 설정되는 일종의 Gap Lock 입니다.
• 동일한 인덱스 갭에 삽입하는 여러 트랜잭션이 갭 내의 동일한 위치에 삽입하지 않는 경우 서로를 기다릴 필요가 없도록 삽입 의도를 알립니다.

Insert Intention Locks 예시 1 ) 값이 4와 7인 인덱스 레코드가 있다고 가정해 보겠습니다. 각각 5와 6의 값을 삽입하려는 별도의 트랜잭션은 삽입된 행에 대한 독점 잠금을 얻기 전에 각각 삽입 의도 잠금으로 4와 7 사이의 간격을 잠그지만 행이 충돌하지 않으므로 서로를 차단하지 않습니다.

Insert Intention Locks 예시 2)다음 예시는 삽입된 레코드에 대한 Exclusive Lock을 얻기 전에 Insert Intention Lock을 취하는 트랜잭션을 보여줍니다. 클라이언트 A는 두 개의 인덱스 레코드(90과 102)가 포함된 테이블을 생성한 다음 ID가 100보다 큰 인덱스 레코드에 Exclusive Lock을 설정하는 트랜잭션을 시작합니다. 이 Exclusive Lock에는 102번 레코드 앞에 갭 잠금이 포함됩니다. 클라이언트 B는 갭에 레코드를 삽입하는 트랜잭션을 시작합니다. 트랜잭션은 Exclusive Lock을 얻기 위해 기다리는 동안 Insert Intention Lock을 취합니다.

// A
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);

AUTO-INC Locks

• AUTO-INC Lock은 AUTO_INCREMENT 열이 있는 테이블에 삽입하는 트랜잭션이 취하는 특수한 테이블 수준 잠금입니다.
• AUTO-INC Lock 예시) 한 트랜잭션이 테이블에 값을 삽입하는 경우 다른 트랜잭션은 해당 테이블에 삽입을 수행하기 위해 기다려야 합니다. 첫 번째 트랜잭션에 의해 삽입된 행은 연속적인 기본 키 값을 받게 됩니다.
• innodb_autoinc_lock_mode 변수는 자동 증가 잠금에 사용되는 알고리즘을 제어합니다. 이 변수를 사용하면 예측 가능한 자동 증가 값의 시퀀스와 삽입 작업의 최대 동시성 사이에서 절충하는 방법을 선택할 수 있습니다.

Predicate Locks for Spatial Indexes

• InnoDB는 공간 데이터가 포함된 열의 공간 인덱싱을 지원합니다.
• 공간 인덱스와 관련된 연산에 대한 잠금을 처리하기 위해 Next Key Lock은 REPEATABLE READ 또는 SERIALIZABLE 트랜잭션 격리 수준을 지원하는 데 적합하지 않습니다. 다차원 데이터에는 절대적인 순서 개념이 없기 때문에 어떤 것이 "Next" Key 인지 명확하지 않기 때문입니다.
• SPATIAL 인덱스가 있는 테이블에 대한 격리 수준을 지원하기 위해 InnoDB는 Predicate Lock을 사용합니다. SPATIAL 인덱스에는 최소 바운딩 사각형(MBR) 값이 포함되어 있으므로 InnoDB는 쿼리에 사용되는 MBR 값에 Predicate Lock을 설정하여 인덱스에서 일관된 읽기를 시행합니다. 다른 트랜잭션은 쿼리 조건과 일치하는 행을 삽입하거나 수정할 수 없습니다.

Reference

Mysql 8.0 InnoDB Locking