데브러너 (DevRunner)

17.7.3 InnoDB에서 서로 다른 SQL 문이 설정하는 Lock

locking read, UPDATE, DELETE는 일반적으로 SQL 문을 처리하는 동안 스캔되는 모든 인덱스 레코드에 레코드 락을 설정합니다. WHERE 조건에 의해 해당 로우가 제외되는지 여부는 중요하지 않습니다. InnoDB는 정확한 WHERE 조건을 기억하지 않고, 어떤 인덱스 범위를 스캔했는지만 알고 있습니다. 락은 보통 next-key locks이며, 이는 레코드 바로 앞의 “갭”에 대한 insert도 차단합니다. 그러나 gap locking을 명시적으로 비활성화할 수 있으며, 이 경우 next-key locking은 사용되지 않습니다. 자세한 내용은 Section 17.7.1, “InnoDB Locking”을 참조하십시오. 또한 트랜잭션 격리 수준도 어떤 락이 설정되는지에 영향을 줄 수 있습니다. 이에 대해서는 Section 17.7.2.1, “Transaction Isolation Levels”을 참조하십시오.

검색에 보조 인덱스가 사용되고 설정해야 할 인덱스 레코드 락이 배타적인 경우, InnoDB는 해당하는 클러스터드 인덱스 레코드도 읽어 와서 락을 설정합니다.

해당 문에 적합한 인덱스가 없어서 MySQL이 문을 처리하기 위해 전체 테이블을 스캔해야 하는 경우, 테이블의 모든 로우가 락되며, 그 결과 다른 사용자가 해당 테이블에 insert하는 작업이 모두 블록됩니다. 따라서 쿼리가 불필요하게 많은 로우를 스캔하지 않도록 적절한 인덱스를 생성하는 것이 중요합니다.

InnoDB는 다음과 같이 특정 유형의 락을 설정합니다.

SELECT ... FROM은 일관성 읽기이며, 데이터베이스의 스냅샷을 읽고, 트랜잭션 격리 수준이 SERIALIZABLE로 설정되지 않는 한 어떠한 락도 설정하지 않습니다. SERIALIZABLE 수준에서는 검색이 발견하는 인덱스 레코드에 공유 next-key 락을 설정합니다. 그러나 유니크 인덱스를 사용하여 유니크 로우를 검색하고 로우를 락하는 문장의 경우에는 인덱스 레코드 락만 필요합니다.
유니크 인덱스를 사용하는 SELECT ... FOR UPDATE 및 SELECT ... FOR SHARE 문장은 스캔된 로우에 대해 락을 획득하고, 결과 집합에 포함될 자격이 없는 로우(예: WHERE 절에 지정된 조건을 만족하지 않는 로우)에 대해서는 락을 해제합니다. 그러나 일부 경우에는 쿼리 실행 중에 결과 로우와 그 원본 사이의 관계가 손실되기 때문에 로우가 즉시 언락되지 않을 수 있습니다. 예를 들어, UNION에서는, 테이블에서 스캔된(그리고 락된) 로우가 결과 집합에 포함될 수 있는지 평가하기 전에 임시 테이블에 삽입될 수 있습니다. 이 경우 임시 테이블의 로우와 원본 테이블의 로우 간의 관계가 손실되며, 원본 테이블의 로우는 쿼리 실행이 끝날 때까지 언락되지 않습니다.
locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE, DELETE 문장의 경우, 취해지는 락은 문장이 유니크 검색 조건을 가진 유니크 인덱스를 사용하는지, 아니면 범위형 검색 조건을 사용하는지에 따라 달라집니다.
- 유니크 검색 조건을 가진 유니크 인덱스의 경우, InnoDB는 발견된 인덱스 레코드만 락하고, 그 앞의 갭은 락하지 않습니다.
- 그 외 검색 조건 및 비유니크 인덱스의 경우, InnoDB는 스캔된 인덱스 범위를 락하며, gap locks 또는 next-key locks을 사용하여 다른 세션이 해당 범위에 포함된 갭에 값을 insert하는 것을 차단합니다. gap locks와 next-key locks에 대한 자세한 내용은 Section 17.7.1, “InnoDB Locking”을 참조하십시오.
검색이 만나는 인덱스 레코드에 대해, SELECT ... FOR UPDATE는 다른 세션이 SELECT ... FOR SHARE를 수행하거나 특정 트랜잭션 격리 수준에서 읽는 것을 블록합니다. 일관성 읽기는 read view에 존재하는 레코드에 설정된 락을 무시합니다.
UPDATE ... WHERE ...는 검색이 만나는 모든 레코드에 배타적인 next-key 락을 설정합니다. 그러나 유니크 인덱스를 사용하여 유니크 로우를 검색하고 로우를 락하는 문장의 경우에는 인덱스 레코드 락만 필요합니다.
UPDATE가 클러스터드 인덱스 레코드를 변경할 때, 영향을 받는 보조 인덱스 레코드에 대해 암시적 락이 설정됩니다. UPDATE 작업은 새로운 보조 인덱스 레코드를 삽입하기 전에 중복 검사 스캔을 수행할 때, 그리고 새로운 보조 인덱스 레코드를 삽입할 때, 영향을 받는 보조 인덱스 레코드에 대해 공유 락도 획득합니다.
DELETE FROM ... WHERE ...는 검색이 만나는 모든 레코드에 배타적인 next-key 락을 설정합니다. 그러나 유니크 인덱스를 사용하여 유니크 로우를 검색하고 로우를 락하는 문장의 경우에는 인덱스 레코드 락만 필요합니다.
INSERT는 삽입된 로우에 배타적인 락을 설정합니다. 이 락은 인덱스-레코드 락이며, next-key 락은 아닙니다(즉, 갭 락은 없으며), 다른 세션이 삽입된 로우 앞의 갭에 값을 insert하는 것을 막지 않습니다.

로우를 삽입하기 전에, insert intention gap lock이라고 불리는 유형의 갭 락이 설정됩니다. 이 락은 여러 트랜잭션이 같은 인덱스 갭에 삽입을 시도하더라도 갭 안의 같은 위치에 삽입하지 않는 한 서로 기다릴 필요가 없도록 insert 의도를 표시합니다. 예를 들어, 값 4와 7을 갖는 인덱스 레코드가 있다고 가정해 보겠습니다. 서로 다른 트랜잭션이 값 5와 6을 삽입하려고 시도하는 경우, 두 트랜잭션은 각각 삽입된 로우에 대한 배타적인 락을 획득하기 전에 4와 7 사이의 갭을 insert intention 락으로 락하지만, 로우가 충돌하지 않으므로 서로를 블록하지 않습니다.

duplicate-key 에러가 발생하면, 중복 인덱스 레코드에 대해 공유 락이 설정됩니다. 이런 공유 락의 사용으로 인해, 다른 세션이 이미 배타적인 락을 가지고 있는 상태에서 여러 세션이 동일한 로우를 insert하려고 하면 데드락이 발생할 수 있습니다. 이는 다른 세션이 그 로우를 delete한 경우에 일어날 수 있습니다. 예를 들어, InnoDB 테이블 t1의 구조가 다음과 같다고 가정합니다:
```
1CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
```
이제 세 개의 세션이 다음 작업을 순서대로 수행한다고 가정합니다:

Session 1:
```
1START TRANSACTION;
2INSERT INTO t1 VALUES(1);
```
Session 2:
```
1START TRANSACTION;
2INSERT INTO t1 VALUES(1);
```
Session 3:
```
1START TRANSACTION;
2INSERT INTO t1 VALUES(1);
```
Session 1:
```
1ROLLBACK;
```
session 1의 첫 번째 작업은 해당 로우에 대해 배타적인 락을 획득합니다. session 2와 session 3의 작업은 모두 duplicate-key 에러를 발생시키며, 둘 다 해당 로우에 대한 공유 락을 요청합니다. session 1이 롤백하면, 로우에 대한 배타적인 락을 해제하며, session 2와 session 3에 대한 대기 중이던 공유 락 요청이 허용됩니다. 이 시점에서 session 2와 session 3은 데드락 상태가 됩니다. 둘 모두 상대방이 보유한 공유 락 때문에 로우에 대한 배타적인 락을 획득할 수 없습니다.

비슷한 상황은 테이블에 이미 키 값 1을 가진 로우가 있는 상태에서 세 개의 세션이 다음 작업을 순서대로 수행할 때 발생합니다:

Session 1:
```
1START TRANSACTION;
2DELETE FROM t1 WHERE i = 1;
```
Session 2:
```
1START TRANSACTION;
2INSERT INTO t1 VALUES(1);
```
Session 3:
```
1START TRANSACTION;
2INSERT INTO t1 VALUES(1);
```
Session 1:
```
1COMMIT;
```
session 1의 첫 번째 작업은 해당 로우에 대해 배타적인 락을 획득합니다. session 2와 session 3의 작업은 모두 duplicate-key 에러를 발생시키며, 둘 다 해당 로우에 대한 공유 락을 요청합니다. session 1이 커밋하면, 로우에 대한 배타적인 락을 해제하며, session 2와 session 3에 대한 대기 중이던 공유 락 요청이 허용됩니다. 이 시점에서 session 2와 session 3은 데드락 상태가 됩니다. 둘 모두 상대방이 보유한 공유 락 때문에 로우에 대한 배타적인 락을 획득할 수 없습니다.
[INSERT ... ON DUPLICATE KEY UPDATE](https://dev.mysql.com/doc/refman/9.5/en/insert-on-duplicate.html "15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement)는 duplicate-key 에러가 발생했을 때 공유 락 대신 로우를 update하기 위한 배타적인 락을 설정한다는 점에서 단순 INSERT와 다릅니다. duplicate 프라이머리 키 값에 대해서는 배타적인 인덱스-레코드 락이 설정됩니다. duplicate 유니크 키 값에 대해서는 배타적인 next-key 락이 설정됩니다.
REPLACE는 유니크 키 충돌이 없는 경우, INSERT처럼 수행됩니다. 그렇지 않으면, 교체될 로우에 대해 배타적인 next-key 락이 설정됩니다.
INSERT INTO T SELECT ... FROM S WHERE ...는 T에 삽입되는 각 로우에 대해 배타적인 인덱스 레코드 락(갭 락 없음)을 설정합니다. 트랜잭션 격리 수준이 READ COMMITTED인 경우, InnoDB는 S에 대한 검색을 일관성 읽기(즉, 락 없이)로 수행합니다. 그렇지 않으면, InnoDB는 S의 로우에 대해 공유 next-key 락을 설정합니다. statement 기반 바이너리 로그를 사용한 롤-포워드 복구 중에는 각 SQL 문이 원래와 정확히 동일한 방식으로 실행되어야 하기 때문에, 후자의 경우에는 InnoDB가 락을 설정해야 합니다.

CREATE TABLE ... SELECT ...는 INSERT ... SELECT의 경우와 마찬가지로, SELECT를 공유 next-key 락 또는 일관성 읽기로 수행합니다.

SELECT가 REPLACE INTO t SELECT ... FROM s WHERE ... 또는 UPDATE t ... WHERE col IN (SELECT ... FROM s ...) 구문에서 사용될 때, InnoDB는 테이블 s의 로우에 공유 next-key 락을 설정합니다.
InnoDB는 테이블에 사전에 지정된 AUTO_INCREMENT 컬럼을 초기화하는 동안, 해당 컬럼과 연관된 인덱스의 끝에 배타적인 락을 설정합니다.

innodb_autoinc_lock_mode=0에서, InnoDB는 auto-increment 카운터에 접근하는 동안 락이 현재 SQL 문장의 끝까지(전체 트랜잭션의 끝이 아님) 획득 및 유지되는 특수한 AUTO-INC 테이블 락 모드를 사용합니다. AUTO-INC 테이블 락이 유지되는 동안 다른 클라이언트는 해당 테이블에 insert할 수 없습니다. 동일한 동작이 innodb_autoinc_lock_mode=1에서의 “bulk inserts”에 대해서도 발생합니다. innodb_autoinc_lock_mode=2에서는 테이블 수준 AUTO-INC 락이 사용되지 않습니다. 자세한 내용은 Section 17.6.1.6, “AUTO_INCREMENT Handling in InnoDB”를 참조하십시오.

InnoDB는 이미 초기화된 AUTO_INCREMENT 컬럼 값을 가져올 때 락을 설정하지 않습니다.
테이블에 FOREIGN KEY 제약 조건이 정의되어 있는 경우, 제약 조건을 검사해야 하는 insert, update, delete는 제약 조건을 검사하기 위해 확인하는 레코드에 대해 공유 레코드-수준 락을 설정합니다. 제약 조건이 실패하는 경우에도 InnoDB는 이러한 락을 설정합니다.
LOCK TABLES는 테이블 락을 설정하지만, 이 락은 InnoDB 레이어 위의 상위 MySQL 레이어에서 설정하는 것입니다. innodb_table_locks = 1(기본값)이고 autocommit = 0이며, 상위 MySQL 레이어가 로우-수준 락을 인식하는 경우, InnoDB는 테이블 락을 인식합니다.

그렇지 않은 경우, InnoDB의 자동 데드락 감지는 이러한 테이블 락이 관련된 데드락을 감지할 수 없습니다. 또한 이 경우 상위 MySQL 레이어는 로우-수준 락을 인식하지 못하므로, 다른 세션이 현재 해당 테이블에 로우-수준 락을 보유하고 있더라도 테이블 락을 획득할 수 있습니다. 그러나 이는 Section 17.7.5.2, “Deadlock Detection”에서 설명하듯이 트랜잭션 무결성을 위협하지는 않습니다.
LOCK TABLES는 innodb_table_locks=1(기본값)인 경우 각 테이블에 두 개의 락을 획득합니다. MySQL 레이어에서의 테이블 락에 더해, InnoDB 테이블 락도 획득합니다. InnoDB 테이블 락 획득을 피하려면 innodb_table_locks=0으로 설정하십시오. InnoDB 테이블 락이 획득되지 않는 경우, 테이블의 일부 레코드가 다른 트랜잭션에 의해 락되어 있더라도 LOCK TABLES는 완료됩니다.

MySQL 9.5에서는, innodb_table_locks=0은 LOCK TABLES ... WRITE로 명시적으로 락된 테이블에는 영향을 주지 않습니다. 그러나 트리거를 통해 암묵적으로 또는 LOCK TABLES ... READ에 의해 읽기 또는 쓰기용으로 락된 테이블에는 영향을 줍니다.
트랜잭션이 커밋되거나 중단(abort)될 때, 해당 트랜잭션이 보유한 모든 InnoDB 락은 해제됩니다. 따라서 autocommit=1 모드에서 InnoDB 테이블에 대해 LOCK TABLES를 호출하는 것은 큰 의미가 없습니다. 획득된 InnoDB 테이블 락은 즉시 해제되기 때문입니다.
LOCK TABLES는 암묵적으로 COMMIT을 수행하고 UNLOCK TABLES를 호출하므로, 트랜잭션 중간에 추가 테이블을 락할 수는 없습니다.

17.7.3 Locks Set by Different SQL Statements in InnoDB

17.7.3 InnoDB에서 서로 다른 SQL 문이 설정하는 Lock