InnoDB Locking

Shared and Exclusive Locks

MySQL InnoDB는 두 종류의 행 기반 Lock( s lock , x lock )을 사용한다.

  • S Lock은 트랜젝션이 행을 읽기 위해 잠금을 획득하는 것을 허용합니다.
  • X Lock은 트랜잭션이 행을 업데이트하거나 삭제하는 행위에 대한 락을 가질수 있도록 허용한다

예시

  1. 트랜잭션 T1이 row r에 대하여 S Lock을 걸었을 경우 T2 트랜잭션은 r 행에 대해 S Lock을 요청할 수 있으며 결과적으로 T1, T2 모두 행 r에 대한 S Lock을 획득할 수 있습니다. 하지만 만약 T2 트랜젝션이 행에 대한 X Lock을 요청한다면 즉각적으로 획득할 수 없도록 되어있습니다.

2) 트랜잭션 T1이 row r에 대하여 X Lock을 가지고 쥐고 있는 상황이라면 T2가 r에 대하여 X Lock을 요청시 즉각적으로 부여하지 않고 T1 트랜젝션이 보유하고 있는 X Lock을 내려놓을 때까지 기다립니다.

Intention Locks

InnoDB는 행 잠금과 테이블 잠금의 공존이 가능하도록 multiple granularity locking을 지원합니다. InnoDB는 세밀한 수준의 잠금을 지원하기위해 Intention 락을 사용합니다. intention lock은 트랜잭션이 후에 행에다가 어떤 종류의 잠금(S or X)을 요청할 건지를 가리키는 테이블 수준의 락입니다.

Intention Lock 종류

  • Intention shared lock (IS) 는 트랜젝션이 테이블의 개개의 행에 대하여 S 락을 요청할 수 있다는 것을 테이블에 표시한다고 생각하면 될 것 같습니다. 예) SELECT ... LOCK IN SHARE MODE
  • Intention exclusive lock (IX) 는 트랜젝션이 테이블의 각 행에 대하여 X락을 요청할 수 있다는 것을 의미합니다. 예) SELECT ... FOR UPDATE

Intention lock 프로토콜은 다음과 같습니다.

  • 트랜젝션이 테이블의 행에 S 락을 얻기 위해서는 테이블로부터 먼저 IS락 이상의 락을 습득해야합니다.
  • 트랜젝션이 테이블의 행에 X 락을 얻기 위해서는 테이블로부터 먼저 IX 락을 습득해야합니다.

Intention Lock 호환성 표

  X IX S IS
X 불가능 불가능 불가능 불가능
IX 불가능 가능 불가능 가능
S 불가능 불가능 가능 가능
IS 불가능 가능 가능 가능

만약 트랜젝션이 기존 락과 비교하여 호환가능하다면 락이 부여되지만 호환 불가능한경우에는 락이 부여되지 않으며 기존 락을 놓기전까지 트랜젝션은 대기하게 됩니다. 만약 기존 락과 호환불가능한 락을 요청하여 교착 상태가 발생하였고 이로 인해 권한을 부여할 수 없는 경우 오류를 발생시키게 됩니다.

Intention 락은 전체 테이블 요청(LOCK TABLES ... WRITE와 같은)을 제외하고는 어떠한 요청도 차단하지 않습니다. Intention 락의 주된 목적은 누군가 행을 잠금시킬 것이라는 보여주거나 현재 해당 행이 잠금되어있다는 것을 보여주기 위함입니다.

show engine innodb status 구문을 통해서 Intention 락에 대한 정보를 아래와 같이 확인할 수 있습니다.

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

Record Lock

레코드 락은 인덱스 레코드에 잠금을 시키는 락입니다. 예를 들면 SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;는 다른 트랜젝션에서 t.c1 = 10인 행들에 대하여 삽입, 갱신, 삭제하는 행위를 예방합니다.

테이블에 인덱스가 없이 정의된 경우에도 레코드 락은 항상 레코드에 잠금을 걸어야 하기 때문에 InnoDB는 숨겨진 클러스터형 인덱스를 생성하고 이 인덱스를 인덱스 락에 사용합니다.

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 Lock

Gap Lock은 인덱스 레코드 사이, 첫 번째 이전 또는 마지막 인덱스 이후의 간격에 대한 잠금입니다. 예를 들어 설명하자면 테이블 (A)의 컬럼 c가 있다고 가정해보자. 해당 테이블에는 컬럼 c가 10, 14인 데이터가 두개 들어있다 가정했을 때 아래와 같은 구문을 살펴보자.

SELECT * FROM A WHERE c between 1 AND 20;

그러면 c가 1보다 크면서 10보다 작은 범위 + c가 10보다 크면서 14보다 작은 범위 + c가 14보다 크면서 20보다 작은 범위에 갭락이 걸리게 된다.

gap lock은 성능과 동시성 사이의 트레이드 오프의 일부이며, 일부 트랜젝션 isolation 레벨에서 사용됩니다.

gap 잠금은 유니크 인덱스를 사용해서 고유행(=unique row)을 검색할 때는 갭을 잠글 필요가 없습니다(검색 조건에 다중 열 고유 인덱스의 일부 열만 포함되는 경우는 포함되지 않습니다. 이 경우 공백 잠금이 발생합니다). 예를 들어 만약 id 컬럼에 유니크 인덱스가 걸려있는 경우 아래의 구문에서는 오직 인덱스가 100인 레코드에만 인덱스 레코드 락이 걸리며 다른 세션이 행을 삽입할 때는 문제가 되지 않습니다.

SELECT * FROM child WHERE id = 100;

만약 id 에 인덱스가 걸려있지 않거나, 유니크가 아닌 다른 인덱스가 걸려있다면 위의 구문에서는 gap 잠금이 일어납니다

서로 다른 트랜젝션에 의해 상충되는 갭락이 걸릴수도 있다. 예를 들면 트랜젝션 A는 어떤한 범위에다가 S Gap락을 보유하고 있을 때, 트랜잭션 B가 같은 범위에 대하여 X Gap 잠금을 보유할 수 있습니다. 상충되는 갭 락이 허용되는 이유는 인덱스에서 레코드를 제거할 경우 서로 다른 트랜잭션이 레코드에 보유하고 있는 갭락을 병합해야 하기 때문이다.

InnoDB의 갭락은 ‘purely inhibitive(순수한 억제?)’이며 그들의 유일한 목적이 서로 다른 트랜잭션에서 어떤 범위에 대하여 레코드를 삽입하는 것을 예방하기 위함임을 뜻한다. 하나의 트랜잭션이 갭락을 취한다고 해서 다른 트랜잭션이 동일한 갭에 대하여 갭 잠금 하는 것을 막을 수 없다. S 갭락과 X 갭락은 차이가 없다. 그들은 서로 충돌하지 않으며, 같은 기능을 수행한다.

당신이 트랜잭션 Isolation 레벨을 READ COMMITED로 바꾸거나 혹은 시스템 변수의 innodb_locks_unsafe_for_binlog 을 가능하게 한다면 갭락은 명시적으로 비활성화할 수 있습니다 (지금은 더이상 사용되지 않음). 이 경우 갭 잠금은 검색 및 인덱스 검색에 대해 비활성화되며 외래키 제약 조건 검사 및 중복 키 검사에만 사용됩니다.

READ COMITTED isolation 레벨을 사용 및 innodb_locks_unsafe_for_binlog를 활성화하면 다른 효과도 있습니다. MySQL이 WHERE 조건문을 평가한 후 일치하지 않는 행에 대해 레코드 잠금이 해제됩니다. 업데이트 구문에 대해서 InnDB는 ‘semi-consistent(반정합)’ 읽기를 수행하여 MySQL에 최신 커밋 버전을 반환하여 행이 업데이트의 WHERE 조건과 일치하는지 여부를 확인할 수 있습니다.

Next-Key Locks

넥스트 키 락은 1) 인덱스 레코드의 레코드 잠금과 2) 인덱스 레코드 이전의 갭에 대한 갭 잠금의 조합입니다.

InnoDB는 테이블 인덱스를 검색하거나 스캔할 때 마주하는 인덱스 레코드에 S락 혹은 X락을 설정하는 방식으로 행 수준의 잠금을 수행합니다. 따러서 행 수준의 락은 실제로 인덱스 레코드 락입니다. 인덱스 레코드에 대한 넥스트키 잠금은 인덱스 레코드 이전의 갭에도 영향을 미칩니다. 즉, 다음 키 잠금은 인덱스 레코드 앞에 있는 공백에 인덱스 레코드 잠금을 더한 것 입니다. 한 세션이 인덱스의 레코드 R에 대한 공유 또는 배타적 잠금을 가지고 있는 경우 다른 세션은 인덱스 순서에서 R바로 앞의 간격에 새 인덱스 레코드를 삽입할 수 없습니다.

기본적으로 InnoDB는 REPEATEABLE_READ isolation 레벨의 트랜잭션으로 작동합니다. 이 경우 InnoDB는 검색 및 인덱스 검색에 넥스트 키 잠금을 사용하여 팬텀을 방지합니다.

Insert Intention Lock

삽입 의도 잠금은 행 삽입 전에 Insert 작업에 의해 세팅되는 갭 잠금 유형중 하나입니다. 동일한 인덱스 갭에 삽입되는 여러 트랜잭션이 갭 내에서 동일한 위치에 삽입되지 않을 경우 서로 기다릴 필요가 없는 방식으로 삽입하려는 의도를 나타냅니다. 값이 4와 7인 인덱스 레코드가 있다고 가정하자. 5와 6을 삽입하려는 별도의 트랜잭션이 있다. 각각은 삽입된 행에 대한 베타적 잠금을 얻기 전 삽입 의도 잠금으로 4와 7 사이의 간격을 잠그지만, 서로 행이 출동하지 않기 때문에 차단하지 않습니다.

다음 예는 삽입된 레코드에 대한 베타적 잠금을 얻기 전에 삽입 의도 잠금을 사용하는 트랜잭션을 보여줍니다.

클라이언트 A는 두 개의 인덱스 레코드(90, 102)를 포함하는 테이블을 만든 다음 ID가 100보다 큰 인덱스 레코드에 베타적 잠금을 설정하는 트랜잭션을 시작합니다. 베타적 잠금에는 102 인덱스 레코드 갭 잠금이 포함됩니다.

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

START TRANSACTION;
SELECT * FROM child WHERE id > 100 FOR UPDATE;

클라이언트 B는 위의 갭 사이에 레코드를 삽입하기 위해 트랜잭션 시작합니다. 그 트랜잭션은 베타적 잠금을 획득하기 위해 기다리는 동안 삽입 의도 잠금을 사용합니다.

START TRANSACTION;
INSERT INTO child (id) values (101);

삽입 의도 잠금에 대한 트랜잭션 데이터는 show engine inodb 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 잠금에 사용되는 알고리즘을 제어합니다. (auto-increment 값의 예측 가능한 시퀀스)와 (삽입 작업에 대한 최대 동시성) 둘 사이의 균형을 맞추는 방법을 선택할 수 있습니다.