(번역) InnoDB Locking

MySQL 공식 문서 중 14.7.1 InnoDB Locking 을 번역한 것입니다. 영어가 익숙하지 않아 의미가 잘 전달되지 않는 부분이 있을 수 있습니다. 틀렸거나 잘못 번역된 부분이 있다면 피드백 부탁드리겠습니다.

---

InnoDB Locking

이번 섹션에서는 InnoDB 가 사용하고 있는 잠금의 종류들에 대해서 알아봅니다.

• Shared and Exclusive Locks
• Intention Locks
• Record Locks
• Gap Locks
• Next-Key Locks
• AUTO-INC Locks

Shared and Exclusive Locks

InnoDB는 행 기반의 잠금을 구현합니다. Shared lock(S) 과 Exclusive lock(X) 두 종류의 잠금이 있습니다.

• Shared lock 은 트랜잭션이 하나의 행을 읽기 위해 잠금을 획득 것을 가능하게 합니다.
• Exclusive lock 은 트랜잭션이 하나의 행을 수정하거나 삭제하기 위해 잠금을 획득하는 것을 가능하게 합니다.

만약 트랜잭션 T1 이 행 r 에 대해 shared lock 을 잡고 있다면, 행 r에 대한 또 다른 트랜잭션 T2 의 잠금 요청은 다음과 같이 처리됩니다:

• T2 의 요청이 shared lock 을 위한 요청이라면 즉시 승인될 수 있습니다. 결과적으로 T1 과 T2 는 행 r 에 대해서 shared lock 을 잡습니다.
• T2 의 요청이 exclusive lock 을 위한 요청이라면 즉시 승인되지 않습니다.

만약 트랜잭션 T1 이 행 r에 대해서 exclusive lock 을 가지고 있다면, 또 다른 트랜잭션 T2로 부터의 요청은 두 가지 타입 모두 즉시 승인되지 않습니다. 게다가, 트랜잭션 T2는 트랜잭션 T1이 행 r 에 대한 잠금을 해제할 때까지 기다려야만 합니다.

Intention Locks

InnoDB는 multiple granularity lock(MGL) 을 지원합니다. MGL은 row lock 과 table lock 이 공존하는 하는 것을 허용합니다. 예를 들어 LOCK TABLES … WRITE 와 같은 구문은 테이블에 대해서 exclusive lock (X-lock) 을 획득합니다. MGL을 위해서, InnoDB는 intention lock 을 사용합니다. intention lock 은 테이블 레벨의 잠금입니다. intention lock 은 트랜잭션이 나중에 어떤 잠금을 요청할 것인지를 나타냅니다. Intention lock 도 두 가지가 있습니다.

• Intention Shared Lock(IS) 는 트랜잭션이 shared lock 을 잡을 의향이 있음을 의미합니다.
• Intention Exclusive Lock(IX) 는 트랜잭션이 exclusive lock 을 잡을 의도가 있음을 의미합니다.
  예를 들면, SELECT … LOCK IN SHARE MODE 구문은 IS lock 을 획득합니다. 그리고 SELECT … FOR UPDATE 는 IX lock 을 획득합니다.

Intention locking 프로토콜을 다음과 같습니다.

• 트랜잭션이 shared lock 을 얻을 수 있으려면 우선 테이블에 대해서 IS lock 혹은 더 강한 잠금 을 가지고 있어야 합니다.
• 트랜잭션이 exclusive lock 을 얻을 수 있으려면 우선 테이블에 대해서 IX 잠금 을 가지고 있어야 합니다.

테이블 레벨 잠금들 사이의 호환성은 다음 표와 같이 요약할 수 있습니다.

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

트랜잭션이 요청하는 잠금이 기존에 있던 잠금과 호환이 가능하다면, 해당 요청은 승인됩니다. 그러나, 이미 존재하는 잠금과 충돌한다면 승인되지 않습니다. 트랜잭션은 충돌하는 잠금이 해제될 때까지 기다립니다. 만약 잠금 요청이 기존에 있던 잠금과 충돌하거나, 데드락을 발생시킬 가능성이 있어 승인되지 않는다면, 에러가 발생합니다.

Intention lock 은 LOCK TABLES … WRITE 와 같은 전체 테이블에 대한 잠금을 요청하는 구문을 제외하고 어떠한 것도 블록하지 않습니다. Intention lock 의 주된 목적은 트랜잭션이 현재 잠금을 획득하고 있거나, 획득할 것이다는 것을 보여주기 위한 것 입니다.

SHOW ENGINE INNODB STATUS 구문을 통해서 레코드 잠금에 대한 트랜잭션 정보를 확인할 수 있습니다.

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 인 행들에 값을 삽입, 수정, 삭제하는 것을 방지합니다.

테이블에 인덱스가 정의되어 있지 않더라도, 레코드 잠금 은 항상 인덱스 레코드를 잠급니다. 그런 경우에는 InnoDB 가 가상의 clustered index 생성하고, 이 인덱스를 통해서 레코드를 잠급니다. 14.6.2.1 “Clustered and Secondary Indexes” 에서 자세한 정보를 확인할 수 있습니다.

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

갭 잠금 은 인덱스 레코드들 사이에 있는 갭 영역에 대한 잠금입니다. 또한 첫번째 인덱스 이전의 영역이나 마지막 인덱스 이후의 영역에 대한 잠금을 의미합니다. 예를 들어 SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 구문을 예로 들면, 해당 트랜잭션은 10 부터 20 까지 사이에 갭들을 잠그기 때문에, 컬럼에 해당 값이 있는지 없는지와는 상관없이, 다른 트랜잭션이 t.c1 컬럼에 15 라는 값을 삽입하는 것을 방지합니다.

갭은 하나의 인덱스 값이나 여러 인덱스 값들 심지어 비어있는 영역을 의미합니다.

갭 잠금 은 성능과 동시성 사이에서 트레이드 오프를 가집니다. 그리고 일부 트랜잭션 격리 수준에서 사용되어 집니다.

유니크 인덱스를 사용해서 특정 행을 검색할 때는 갭을 잠글 필요가 없습니다. (검색 조건이 multi-column unique index 를 사용하는 경우는 제외합니다, 이 경우에는 갭을 잠급니다. ) 예를 들어, id 컬럼이 유니크 인덱스를 사용하면, 아래의 구문은 id의 값이 100인 행에 대해서만 인덱스 레코드 잠금을 획득합니다. and it does not matter whether other sessions insert rows in the preceding gap:

SELECT * FROM child WHERE id = 100;

id가 색인되어 있지 않거나 유니크하지 않은 인덱스를 가지고 있다면, 구문은 갭을 잠급니다.

트랜잭션 B가 갭에 대해서 exclusive gap lock (gap X-lock) 을 획득하는 동안, 트랜잭션 A 는 해당 갭에 대해서 shared gap lock (gap S-lock) 을 획득할 수 있습니다. 잠금이 충돌하지만 만약 인덱스에서 레코드가 제거(purge) 된다면, 해당 레코드에 대한 다른 트랜잭션의 갭 잠금은 병합(merge) 되어야 하기 때문입니다.

InnoDB의 갭 잠금은 “purely inhibitive(전적으로 억제하는)” 합니다. 갭 잠금은 다른 트랜잭션들이 갭에 값을 삽입하는 것만 방지합니다. 갭 잠금은 상호 존재가 가능합니다. 갭 잠금은 다른 트랜잭션이 동일한 갭에 대해서 갭 잠금을 획득하는 것을 막지 않습니다. 갭에 대한 exclusive lock 과 shared lock 사이에는 아무런 차이가 없습니다. 갭에 대한 X-lock 과 S-lock 은 충돌하지 않으며, 같은 역할을 수행합니다.

트랜잭션 격리 수준을 READ COMMITTED 로 변경하거나, 8.0 버전 이후로 deprecated 된 시스템 변수 _innodb_locks_unsafe_binlog_ 를 활성화 시키면 명시적으로 갭 잠금을 사용하지 않을 수 있습니다. 이 조건에서는 검색을 하거나 인덱스틀 스캔하기 위해서 갭을 잠그지 않고 오직 외래키 제약을 확인하거나 중복키를 확인할 때만 갭 잠금을 사용합니다.

READ COMMITTED 격리 수준을 사용하거나 _innodb_locks_unsafe_binlog_ 을 활성화 하는 것은 또 다른 영향을 끼칩니다. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. InnoDB는 “semi-consistent” 읽기를 수행합니다. “semi-consistent”는 MySQL 이 행에 대한 UPDATE 구문의 WHERE 조건이 부합하는지 결정할 수 있게 하기 위해서, InnoDB 이 MySQL 에게 가장 최근에 커밋된 버전의 값을 반환해 주는 것을 의미합니다.

Next-Key Locks

넥스트-키 잠금 은 인덱스 레코드 잠금 과 갭 잠금 의 조합입니다.

InnoDB 는 검색을 하거나 테이블 인덱스를 스캔할 때, 인덱스에 해당하는 레코드(이하, 인덱스 레코드)들에 shared lock 이나 exclusive lock 을 획득하는 방식으로 행-레벨 잠금을 수행합니다. 따라서 사실 행-레벨 잠금은 인덱스-레코드 잠금을 의미합니다. 인덱스 레코드에 대한 넥스트-키 잠금 은 인덱스 레코드 앞에 있는 갭에도 영향을 미칩니다. 즉, 넥스트-키 잠금은 인덱스-레코드 잠금과 인덱스 레코드 앞에 있는 갭의 잠금을 더한 것입니다. 만약 하나의 세션이 인덱스에 포함되는 레코드 R 에 대해서 shared lock 이나 exclusive lock 을 이미 획득했다면, 다른 세션들은 인덱스 순서에서 R 앞에 위치하는 갭에 새로운 인덱스 레코드를 추가할 수 없습니다.

인덱스가 10, 11, 13, 20 을 포함하고 있다고 할 때, 존재할 수 있는 넥스트-키 잠금 은 다음 구간들을 포함합니다. 둥근 괄호는 구간의 끝지점을 포함하지 않는 것을 나타내고, 각진 괄호는 끝점을 포함하는 것을 의미합니다.

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

마지막 구간에서, 넥스트-키 잠금 은 마지막 인덱스의 마지막 값과 “supremum”라 불리는 임시의 레코드 다음에 있는 갭을 잠급니다. "supremum" 레코드 는 인덱스에서 실제 존재하는 값들 보다 더 큰 값을 가집니다. “supremum” 은 진짜 인덱스 레코드가 아닙니다. 그래서 이 넥스트-키 잠금은 가장 큰 인덱스 값 다음에 오는 갭만 잠급니다.

기본적으로 InnoDB 는 REPEATABLE READ 트랜잭션 격리 수준으로 동작합니다. 이 경우에 InnoDB 는 검색하거나 인덱스를 스캔할 때, 넥스트-키 잠금 을 사용합니다. 이를 통해서 phantom rows 가 발생하는 것을 방지합니다.

SHOW ENGINE INNODB STATUS 구문을 통해서 레코드 잠금에 대한 트랜잭션 정보를 확인할 수 있습니다.

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 Intetion Locks

insert intention lock 은 행 삽입에 앞서 INSERT 수행에 의해 만들어지는 갭 잠금의 한 종류입니다. 이 잠금은 삽입 의도를 나타냅니다. 여러 트랜잭션이 갭 내에서 같은 위치에 값을 삽입하는 것이 아니라면, 같은 인덱스 갭에 삽입하더라도 다른 트랜잭션의 작업이 끝나길 기다리지 않아도 되는 것을 의미합니다. 인덱스 4와 7이 있다고 했을 때, 5와 6에 값을 추가하려는 서로 다른 트랜잭션은 exclusive lock 을 획득하기 전에 insert intention lock 으로 4와 7 사이의 갭을 잠급니다. 그러나 삽입하고나 하는 행에서 충돌이 발생하지 않기 때문에 서로 블록하지 않습니다.

다음 예는 레코드에 대해서 exclusive lock 을 획득하기 전에 insert intention lock 을 획득하는 트랜잭션을 보여줍니다.

클라이언트 A 는 인덱스 레코드 90 과 102 을 토함하는 테이블을 생성하고 ID 가 100 보다 큰 레코드들에 exclusive lock 을 획득하는 트랜잭션을 시작합니다. exclusive lock 은 레코드 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 는 갭에 레코드를 삽입하기 위해서 트랜잭션을 시작합니다. 트랜잭션은 exclusive lock 을 획득하기 위해서 대기하는 동안 insert intention lock 을 획득합니다.

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

SHOW ENGINE INNODB STATUS 구문을 통해서 레코드 잠금에 대한 트랜잭션 정보를 확인할 수 있습니다.

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_ 컬럼이 있는 테이블에 값을 삽입하고자 하는 트랜잭션에 의해 획득되어지는, 특별한 테이블 레벨 잠금이다. 가장 간단한 경우에, 만약 한 트랜잭션이 테이블에 값을 삽입한다면, 다른 트랜잭션들은 테이블에 값을 삽입하기 위해서 기다려야 한다. 첫번째 트랜잭션에 의해서 삽입된 행들은 연속된 primary key 값을 가집니다.

_innodb_autoinc_lock_mode_ 설정 옵션은 auto-increment 잠금을 위해 사용되는 알고리즘을 조정합니다. 이를 통해서 당신은 auto-increment 값들의 예상되는 순서와 동시성 사이의 트레이드 오프하는 방법을 선택할 수 있습니다.

더 많은 정보는, 14.6.1.6 “AUTO_INCREMENT Handling in InnDB” 에서 확인할 수 있습니다.

---