SQL별 잠금

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

select .. from 은 일관된 읽기이며, 데이터베이스의 스냅샷을 읽으며 트랜잭션 isolation 레벨을 SERIALIZABLE로 설정하지 않는 한 잠금을 설정하지 않습니다. SERIALIZABLE 레벨 수준에서 검색을 하면 검색하면서 마주하는 인덱스 레코드에게 shared next key 잠금을 설정합니다. 그러나 유니크 행을 검색하기 위해 유니크 인덱스를 사용하여 행을 잠그는 구문의 경우 인덱스 레코드 잠금만 필요합니다.
SELECT … LOCK IN SHARE MODE는 검색된 모든 인덱스 레코드에 shared next-key 잠금을 겁니다. 그러나 고유행을 검색하기 위해 unique index를 사용하는 경우 오직 인덱스 레코드 잠금만 요구됩니다.
SELECT … FOR UPDATE의 경우 검색 시 발생하는 모든 레코드에 대해 exclusive next-key 잠금을 사용합니다. 그러나 고유행을 검색하기 위해 unique index를 사용한 경우 오직 인덱스 레코드 잠금만 요구됩니다.

SELECT … FOR UPDATE를 통해 검색된 인덱스 레코드들은 다른 세션들이 SELECT … LOCK IN SHARE MODE나 특정 고립 레벨의 트랜잭션이 읽지 못하도록 막습니다.
UPDATE … WHERE 는 검색된 모든 레코드에 exclusive next-key 잠금을 설정합니다. 그러나 고유 행을 검색하기 위해 unique index를 사용하여 행을 잠그는 명령문에는 인덱스 레코드 잠금만 필요합니다.
UPDATE가 클러스터링 인덱스 레코드를 변경하는 경우 영향을 받은 세컨더리 인덱스 레코드들에는 암시적인 잠금이 적용됩니다. UPDATE문은 또한 새로운 세컨더리 인덱스 레코드를 삽입하기 전 중복을 체크하기 위해 스캔된 세컨더리 인덱스 레코드에 S 잠금을 걸거나 새로운 세컨더리 인덱스 레코드를 삽입할 때 shared 잠금을 겁니다.
DELETE FROM … WHERE 는 검색된 모든 레코드에 exclusive next-key 잠금을 설정합니다. 그러나 고유 행을 검색하기 위해 unique index를 사용하여 행을 잠그는 명령문에는 인덱스 레코드 잠금만 필요합니다.
INSERT는 삽입된 행에 대하여 exclusive lock을 세팅합니다. 이 락은 next-key 잠금이 아니라 인덱스 레코드 잠금입니다. 삽입된 행 이전에 다른 세션으로부터 갭에 삽입되는 것을 막지 못합니다.

행을 삽입하기 전에 삽입 의도 갭 잠금이라 불리는 갭 락의 종류 중 하나인 잠금이 세팅됩니다. 이 잠금은 동일한 인덱스 갭에 삽입하는 여러 트랜잭션이 갭의 동일한 위치에 삽입하지 않는 경우 서로 기다릴 필요가 없도록 삽입하려는 의도를 나타냅니다. 값이 4와 7인 인덱스 레코드가 있다고 가정하고, 값 5와 6을 삽입하려고 시도하는 개별 트랜잭션이 있다고 가정합니다. 각 트랜잭션은 삽입된 행에 대한 베타적 잠금을 얻기 전에 삽입 의도 잠금으로 4와 7사이의 간격을 잠급니다. 각 행은 충돌하지 않기 때문에 서로를 막지 않습니다.

중복 키 오류가 발생하면 중복된 인덱스 레코드에 대한 shared 잠금이 설정됩니다. 다른 세션에 이미 exclusive 잠금이 있는 경우 동일한 행을 삽입하려는 세션이 여러 개 있을경우 이러한 shared 잠금을 사용하면 교착 상태가 발생할 수 있습니다.

InnoDB에 아래와 같은 구조를 가진 t1 테이브이 있다고 가정해봅시다.
```
  create table t1 (i int, primary key(i)) engine = InnoDB;
```
그리고 아래의 동작들을 순서대로 수행하는 3개의 세션이 있다고 가정해봅시다.

Session 1:
```
  begin;
  insert into t1 values(1);
```
Session 2:
```
  begin;
  insert into t1 values(1);
```
Session 3:
```
  begin;
  insert into t1 values(1);
```
Session 1:
```
  rollback;
```
첫 번째 세션1이 행에 대한 exclusive 잠금을 획득합니다. 그리고 두번째 세번째 세션들은 중복키 에러가나고 행에 S Lock을 요청합니다. 세션1이 롤백하면서 잠금이 해제되고 큐에 쌓인 s 잠금 요청이 세션2와 세션3에게 승인됩니다. 여기서 세션2와 세션3은 데드락이 발생합니다. 다른 곳에서 보유하고 있는 S 잠금때문에 어느쪽도 행에 대한 베타적 잠금을 획득할 수 없기 때문입니다.

테이블에 키 값이 1인 행이 이미 포함되어 있을 때에도 위와 같은 상황이 발생합니다.

Session 1:
```
  begin;
  delete from t1 where i = 1;
```
Session 2:
```
  begin;
  insert into t1 values(1);
```
Session 3:
```
  begin;
  insert into t1 values(1);
```
Session 1:
```
  commit;
```
INSERT … ON DUPLICATE KEY UPDATE는 중복키 오류가 발생했을 때 업데이트할 행에 공유 잠금이 아닌 exclusive 잠금이 설정된다는 점에서 단순 INSERT와 다릅니다. 중복 primary key 값에 대해서는 exclusive 인덱스 레코드 잠금이 사용되고 중복된 유니크 키 값에 대해서는 exclusive next-key 잠금이 사용됩니다.
REPLACE는 유니크 키에 충돌이 없는 경우 INSERT와 같이 수행됩니다. 그렇지 않으면 대체할 행에 exclusive next-key 잠금이 설정됩니다.
INSERT INTO T SELECT … FROM S WHERE 에서 T에 삽입된 각 행에 exclusive 인덱스 레코드 잠금을 설정합니다. 만약 트랜잭션 isolation 레벨이 READ COMMITTED이거나 혹은 innodb_locks_unsafe_for_binlog가 활성화되어있는 상태에서 트랜잭션 격리 수준이 설정된 경우 SERIALIZABLE이 아닌 경우에는 InnoDB는 consistent read로서 S에서 잠금 없이 검색을 수행합니다. 그렇지 않으면 InnoDB는 S의 행에 shared next-key 잠금을 설정합니다.

바이너리 로그를 사용하는 롤포워드 회복 기법 중에 모든 SQL문들은 그것의 원래 수행된 것과 똑같은 방식으로 실행되어야 해야할 때 잠금을 사용하기도 합니다. (롤포워드 회복기법을 잘 모르다 보니 맞게 해석이 된건지 의문입니다…)

CREATE TABLE … SELECT 문은 INSERT … SELECT와 같이 shared next-key 잠금을 하거나 일관된 읽기로 SELECT를 수행합니다.

REPLACE INTO t SELECT … FROM s WHERE 또는 UPDATE t WHERE col IN (SELECT .. FROM s..) 구성에서 SELECT가 사용되면 InnoDB는 테이블 S의 행에 shared next-key 잠금을 설정합니다.
InnoDB는 테이블에서 이전에 지정된 auto_increment 컬럼을 초기화하는 동안 auto_increment 컬럼과 연관된 인덱스에 exclusive 잠금을 설정합니다.
만약 외래키 제약이 테이블에 정의된 경우 제약 조건을 확인해야 하는 삽입, 변경 또는 삭제는 제약 조건을 확인하기 위해 레코드에 대해 shared 레코드 잠금을 서정합니다. InnoDB는 제약 조건이 실패한 경우에도 이러한 잠금을 설정합니다.
LOCK TABLES는 테이블 잠금을 설정하지만 이러한 잠금을 설정하는 것은 InnoDB 레이어의 상위에 있는 MySQL 레이어입니다. InnoDB 레이어에서는 innodb_table_locks = 1(기본값) 및 autocommit = 0인 경우 테이블 잠금을 인식할 수 있고 MySQL 레이어에서는 행 수준 잠금에 대해 알고 있습니다.
LOCK TABLES는 innodb_table_table=1(기본값)인 경우 각 테이블에 두 개의 잠금을 획득합니다. MySQL 계층의 테이블 잠금뿐만 아니라 InnoDB 테이블 잠금도 획득합니다. InnoDB 테이블 잠금을 획득하지 않으려면 innodb_table_locks을 0으로 설정해야합니다. InnoDB 테이블 잠금을 획득하지 않으면 테이블의 일부 레코드가 다른 트랜잭션에 의해 잠기더라도 LOCK TABLE이 완료됩니다.
트랜잭션에 의해 쥐어진 모든 InnoDB 잠금은 트랜잭션이 커밋되거나 중단될 때 해제됩니다. 따라서 획득한 InnoDB 테이블 잠금이 즉시 해제되므로 InnoDB 테이블에서 자동 커밋 모드(autocommit=1) LOCK TABLE을 호출하는 것은 그다지 의미가 없습니다.

Reference

https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html