17.7.1 InnoDB Locking

17.7.1 InnoDB Locking

이 절에서는 InnoDB에서 사용되는 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

InnoDB는 표준 row-level locking을 구현하며, 여기에는 두 가지 유형의 lock, 즉 shared (S) locks와 exclusive (X) locks가 있습니다.

• shared (S) lock은 lock을 보유한 transaction이 row를 읽을 수 있도록 허용합니다.

• exclusive (X) lock은 lock을 보유한 transaction이 row를 update하거나 delete할 수 있도록 허용합니다.

transaction T1이 row r에 대해 shared (S) lock을 보유하고 있다면, 다른 transaction T2가 row r에 대해 lock을 요청할 때 다음과 같이 처리됩니다:

• T2가 S lock을 요청하는 경우, 즉시 부여될 수 있습니다. 그 결과 T1과 T2는 모두 r에 대해 S lock을 보유합니다.

• T2가 X lock을 요청하는 경우, 즉시 부여될 수 없습니다.

transaction T1이 row r에 대해 exclusive (X) lock을 보유하고 있는 경우, 다른 transaction T2가 r에 대해 어떤 유형의 lock을 요청하더라도 즉시 부여될 수 없습니다. 대신 transaction T2는 transaction T1이 row r에 대한 lock을 해제할 때까지 기다려야 합니다.

Intention Locks

InnoDB는 row lock과 table lock이 공존할 수 있도록 하는 _multiple granularity locking_을 지원합니다. 예를 들어, LOCK TABLES ... WRITE 같은 statement는 지정된 table에 대해 exclusive lock (X lock)을 획득합니다. 여러 granularity 레벨에서의 locking을 실용적으로 만들기 위해 InnoDB는 intention locks를 사용합니다. intention lock은 table-level lock으로서, transaction이 나중에 table의 row에 대해 어떤 유형의 lock (shared 또는 exclusive)을 필요로 하는지 나타냅니다. intention lock에는 두 가지 유형이 있습니다:

• intention shared lock (IS)은 transaction이 table 내 개별 row에 shared lock을 설정할 의도가 있음을 나타냅니다.

• intention exclusive lock (IX)은 transaction이 table 내 개별 row에 exclusive lock을 설정할 의도가 있음을 나타냅니다.

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

intention locking protocol은 다음과 같습니다:

• transaction이 table의 row에 대해 shared lock을 획득하기 전에, 먼저 그 table에 대해 IS lock 또는 더 강한 lock을 획득해야 합니다.

• transaction이 table의 row에 대해 exclusive lock을 획득하기 전에, 먼저 그 table에 대해 IX lock을 획득해야 합니다.

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

lock은 기존 lock과 호환되면 요청한 transaction에 부여되지만, 기존 lock과 충돌하면 부여되지 않습니다. transaction은 충돌하는 기존 lock이 해제될 때까지 기다립니다. 어떤 lock 요청이 기존 lock과 충돌하고, 그 lock을 부여하면 deadlock이 발생하는 경우, error가 발생합니다.

intention lock은 full table 요청(예: LOCK TABLES ... WRITE)을 제외하고는 어떤 것도 block하지 않습니다. intention lock의 주요 목적은 누군가 table의 row에 lock을 설정했거나, 설정하려 하고 있음을 보여 주는 것입니다.

intention lock에 대한 transaction data는 SHOW ENGINE INNODB STATUS 및 InnoDB monitor 출력에서 대략 다음과 같이 나타납니다:

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

Record Locks

record lock은 index record에 대한 lock입니다. 예를 들어, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 는 다른 어떤 transaction도 t.c1 값이 10인 row를 insert, update 또는 delete하지 못하도록 방지합니다.

record lock은 table이 index 없이 정의되어 있더라도 항상 index record를 lock합니다. 이런 경우 InnoDB는 숨겨진 clustered index를 생성하고, 이 index를 record locking에 사용합니다. Section 17.6.2.1, “Clustered and Secondary Indexes”를 참조하십시오.

record lock에 대한 transaction data는 SHOW ENGINE INNODB STATUS 및 InnoDB monitor 출력에서 대략 다음과 같이 나타납니다:

1RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
2trx id 10078 lock_mode X locks rec but not gap
3Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
4 0: len 4; hex 8000000a; asc     ;;
5 1: len 6; hex 00000000274f; asc     'O;;
6 2: len 7; hex b60000019d0110; asc        ;;

Gap Locks

gap lock은 index record 사이의 gap에 대한 lock이거나, 첫 번째 index record 이전 또는 마지막 index record 이후의 gap에 대한 lock입니다. 예를 들어, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 는 다른 transaction이 column t.c1에 값 15를 insert하는 것을 방지합니다. 해당 column에 이미 그런 값이 존재했는지 여부와 관계없이, 범위 내의 모든 기존 값들 사이의 gap이 lock되기 때문입니다.

gap은 하나의 index 값, 여러 index 값을 포함할 수도 있고, 심지어 비어 있을 수도 있습니다.

gap lock은 성능과 동시성 사이의 트레이드오프의 일부이며, 일부 transaction isolation level에서는 사용되고, 다른 level에서는 사용되지 않습니다.

unique index를 사용하여 unique row를 검색하면서 row에 lock을 거는 statement에는 gap locking이 필요하지 않습니다. (이는 검색 조건에 multiple-column unique index의 일부 column만 포함하는 경우는 포함하지 않습니다. 그 경우에는 gap locking이 발생합니다.) 예를 들어, id column에 unique index가 있는 경우, 다음 statement는 id 값이 100인 row에 대해서만 index-record lock을 사용하며, 다른 session이 그 이전 gap에 row를 insert하는지는 중요하지 않습니다:

1SELECT * FROM child WHERE id = 100;

id가 index가 아니거나 nonunique index를 가진 경우, 해당 statement는 앞쪽 gap도 lock합니다.

여기서 주목할 점은 서로 다른 transaction이 동일한 gap에 대해 서로 충돌하는 lock을 보유할 수 있다는 것입니다. 예를 들어, transaction A는 어떤 gap에 대해 shared gap lock(gap S-lock)을 보유하고, transaction B는 같은 gap에 대해 exclusive gap lock(gap X-lock)을 보유할 수 있습니다. 서로 충돌하는 gap lock을 허용하는 이유는, 어떤 record가 index에서 purge될 경우, 해당 record에 대해 여러 transaction이 보유했던 gap lock을 병합해야 하기 때문입니다.

InnoDB의 gap lock은 “순수하게 억제적(purely inhibitive)”이며, 그 유일한 목적은 다른 transaction이 그 gap에 insert하는 것을 방지하는 것입니다. gap lock은 공존할 수 있습니다. 하나의 transaction이 획득한 gap lock은 다른 transaction이 같은 gap에 gap lock을 획득하는 것을 막지 않습니다. shared gap lock과 exclusive gap lock 사이에는 차이가 없습니다. 서로 충돌하지 않으며 동일한 기능을 수행합니다.

gap locking은 명시적으로 비활성화할 수 있습니다. 이는 transaction isolation level을 READ COMMITTED로 변경하는 경우에 발생합니다. 이 경우, gap locking은 검색 및 index scan에서는 비활성화되고, foreign-key constraint 검사 및 duplicate-key 검사에만 사용됩니다.

READ COMMITTED isolation level을 사용할 때는 다른 효과도 있습니다. 일치하지 않는 row에 대한 record lock은 MySQL이 WHERE 조건을 평가한 후 해제됩니다. UPDATE statement의 경우, InnoDB는 “semi-consistent” read를 수행하여, MySQL이 해당 row가 UPDATE의 WHERE 조건과 일치하는지 판단할 수 있도록 최신 commit된 버전을 MySQL에 반환합니다.

Next-Key Locks

next-key lock은 index record에 대한 record lock과, 해당 index record 이전 gap에 대한 gap lock을 조합한 것입니다.

InnoDB는 table index를 검색하거나 scan할 때, 그때그때 만나는 index record에 shared 또는 exclusive lock을 설정하는 방식으로 row-level locking을 수행합니다. 따라서 row-level lock은 실제로 index-record lock입니다. index record에 대한 next-key lock은 또한 그 index record 이전의 “gap”에도 영향을 줍니다. 즉, next-key lock은 index record lock과 그 index record 앞의 gap에 대한 gap lock을 합친 것입니다. 하나의 session이 index에서 record R에 대해 shared 또는 exclusive lock을 보유하고 있는 경우, 다른 session은 index 순서에서 R 바로 앞 gap에 새로운 index record를 insert할 수 없습니다.

index에 값 10, 11, 13, 20이 들어 있다고 가정해 봅시다. 이 index에 대해 가능한 next-key lock들은 다음 구간을 포함하며, 둥근 괄호는 구간 끝점을 제외함을, 대괄호는 끝점을 포함함을 나타냅니다:

1(negative infinity, 10]
2(10, 11]
3(11, 13]
4(13, 20]
5(20, positive infinity)

마지막 구간의 경우, next-key lock은 index에서 가장 큰 값 위쪽의 gap과, 실제 index에 존재하는 어떤 값보다도 큰 값을 가진 “supremum” pseudo-record를 lock합니다. supremum은 실제 index record가 아니므로, 사실상 이 next-key lock은 가장 큰 index 값 뒤의 gap만을 lock합니다.

기본적으로 InnoDB는 REPEATABLE READ transaction isolation level에서 동작합니다. 이 경우 InnoDB는 검색과 index scan에 next-key lock을 사용하여 phantom row를 방지합니다( Section 17.7.4, “Phantom Rows” 참조).

next-key lock에 대한 transaction data는 SHOW ENGINE INNODB STATUS 및 InnoDB monitor 출력에서 대략 다음과 같이 나타납니다:

1RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
2trx id 10080 lock_mode X
3Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
4 0: len 8; hex 73757072656d756d; asc supremum;;
5
6Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
7 0: len 4; hex 8000000a; asc     ;;
8 1: len 6; hex 00000000274f; asc     'O;;
9 2: len 7; hex b60000019d0110; asc        ;;

Insert Intention Locks

insert intention lock은 row insert 이전에 INSERT operation에 의해 설정되는 gap lock 유형입니다. 이 lock은 여러 transaction이 동일한 index gap에 insert하더라도, gap 내에서 동일한 위치에 insert하는 것이 아니라면 서로를 기다릴 필요가 없도록, insert 의도를 표시합니다. 예를 들어, 값 4와 7을 가진 index record가 있다고 합시다. 각각 5와 6을 insert하려는 별개의 transaction은, insert된 row에 대한 exclusive lock을 얻기 전에, insert intention lock으로 4와 7 사이의 gap을 lock하지만, row가 서로 충돌하지 않기 때문에 서로를 block하지는 않습니다.

다음 예시는, inserted record에 대한 exclusive lock을 얻기 전에 transaction이 insert intention lock을 취득하는 과정을 보여 줍니다. 이 예제에는 두 개의 client, A와 B가 등장합니다.

Client A는 두 개의 index record(90과 102)를 포함하는 table을 생성한 후, ID가 100보다 큰 index record에 exclusive lock을 거는 transaction을 시작합니다. 이 exclusive lock에는 record 102 이전의 gap에 대한 gap lock도 포함됩니다:

1mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
2mysql> INSERT INTO child (id) values (90),(102);
3
4mysql> START TRANSACTION;
5mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
6+-----+
7| id  |
8+-----+
9| 102 |
10+-----+

Client B는 해당 gap에 record를 insert하는 transaction을 시작합니다. 이 transaction은 exclusive lock을 얻기 위해 기다리는 동안 insert intention lock을 취득합니다.

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

insert intention lock에 대한 transaction data는 SHOW ENGINE INNODB STATUS 및 InnoDB monitor 출력에서 대략 다음과 같이 나타납니다:

1RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
2trx id 8731 lock_mode X locks gap before rec insert intention waiting
3Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
4 0: len 4; hex 80000066; asc    f;;
5 1: len 6; hex 000000002215; asc     " ;;
6 2: len 7; hex 9000000172011c; asc     r  ;;...

AUTO-INC Locks

AUTO-INC lock은 AUTO_INCREMENT column이 있는 table에 insert하는 transaction이 취득하는 특별한 table-level lock입니다. 가장 단순한 경우, 한 transaction이 table에 값을 insert하는 동안, 다른 어떤 transaction도 그 table에 insert를 수행하기 위해 기다려야 합니다. 이렇게 해서 첫 번째 transaction이 insert한 row들은 연속적인 primary key 값을 받게 됩니다.

innodb_autoinc_lock_mode 변수는 auto-increment locking에 사용되는 알고리즘을 제어합니다. 이 변수는 auto-increment 값의 예측 가능한 시퀀스와 insert operation에 대한 최대 동시성 사이에서 어떻게 트레이드오프할지를 선택할 수 있게 해 줍니다.

자세한 내용은 Section 17.6.1.6, “AUTO_INCREMENT Handling in InnoDB”를 참조하십시오.

Predicate Locks for Spatial Indexes

InnoDB는 spatial data를 포함하는 column에 대한 SPATIAL indexing을 지원합니다(Section 13.4.9, “Optimizing Spatial Analysis” 참조).

SPATIAL index가 관련된 operation에 대한 locking을 처리하기 위해, next-key locking은 REPEATABLE READ 또는 SERIALIZABLE transaction isolation level을 지원하는 데 적절하게 동작하지 않습니다. 다차원 데이터에는 절대적인 순서 개념이 없기 때문에, 어떤 것이 “next” key인지가 명확하지 않습니다.

SPATIAL index가 있는 table에 대해 isolation level을 지원하기 위해, InnoDB는 predicate lock을 사용합니다. SPATIAL index는 minimum bounding rectangle(MBR) 값을 포함하므로, InnoDB는 query에 사용된 MBR 값에 predicate lock을 설정하여 index에 대해 consistent read를 강제합니다. 다른 transaction은 해당 query 조건과 일치하게 되는 row를 insert하거나 수정할 수 없습니다.