MySQL5.6 InnoDB의 다양한 SQL문에서 설정된 잠금

14.2.8 InnoDB의 다양한 SQL문에서 설정된 잠금

일반적으로 잠금 읽기 , UPDATE 또는 DELETE 는 SQL 문 처리시 검색되는 모든 인덱스 레코드에 레코드 잠금이 설정됩니다. 행을 제외 WHERE 조건이 문 내에 존재하는지 여부는 관계 없습니다. InnoDB 는 정확한 WHERE 조건이 기억되지 않지만, 스캔 된 인덱스의 범위는 인식됩니다. 일반적으로 잠금 레코드의 직전에있는 '갭'에 삽입도 차단 넥스트 키 잠금 입니다. 그러나 갭 잠금 은 명시 적으로 해제 할 수 있습니다. 따라서 넥스트 키 잠금을 사용하지 않습니다. 자세한 내용은 섹션 14.2.6 "InnoDB 레코드 격차 및 넥스트 키 잠금" 을 참조하십시오. 트랜잭션 격리 수준에 따라 어떤 잠금이 설정되는지도 영향을받습니다. 섹션 13.3.6 "SET TRANSACTION 구문" 을 참조하십시오.

검색에서 보조 인덱스가 사용되어 설정되는 인덱스 레코드 잠금이 독점 인 경우, InnoDB 는 해당 클러스터 된 인덱스 레코드를 가져와 잠금을 설정할 수도합니다.

공유 잠금과 배타 락의 차이에 대해서는 섹션 14.2.3 "InnoDB 잠금 모드" 를 참조하십시오.

문에 적합한 인덱스가없고, MySQL가 문을 처리하기 위해 전체 테이블을 스캔해야하는 경우, 테이블의 모든 행이 잠겨 있습니다. 그 결과 해당 테이블에 다른 사용자가 모든 삽입이 차단됩니다. 쿼리에서 불필요하게 여러 행이 검색되지 않도록 적절한 인덱스를 만드는 것이 중요합니다.

SELECT ... FOR UPDATE 또는 SELECT ... LOCK IN SHARE MODE 에서는 스캔 된 행에 대해 잠금을 획득하고 WHERE 절에 지정된 조건을 만족하지 않는 등의 이유로 결과 세트에 포함 대상에서 제외됩니다 타행 내용은 잠금이 해제 될 것으로 예상됩니다. 그러나 경우에 따라서는 쿼리의 실행 결과 행과 원본과의 관계가 상실 되었기 때문에, 행 잠금이 즉시 해제되지 않을 수도 있습니다. 예를 들어 UNION 에서는 스캔 (및 잠금) 된 테이블의 행이 결과 집합에 포함 대상 여부의 평가 전에 임시 테이블에 삽입 될 수 있습니다. 이 상황에서는 임시 테이블의 행과 원래 테이블의 행과의 관계가 손실되어 있기 때문에 쿼리 실행이 끝날 때까지 후자의 행 잠금 상태가 해제되지 않습니다.

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

SELECT ... FROM 일관성 읽기이며, 데이터베이스의 스냅 샷을 읽기 트랜잭션 격리 수준이 SERIALIZABLE 로 설정되어야 잠금을 설정하지 않습니다. SERIALIZABLE 수준의 경우, 검색에서 발견 된 인덱스 레코드에 공유 넥스트 키 잠금이 설정됩니다.
SELECT ... FROM ... LOCK IN SHARE MODE 는 검색에서 발견 된 모든 인덱스 레코드에 공유 넥스트 키 잠금이 설정됩니다.
SELECT ... FROM ... FOR UPDATE 는 검색에서 발견 된 인덱스 레코드에 대해 다른 세션이 SELECT ... FROM ... LOCK IN SHARE MODE 를 수행하거나 특정 트랜잭션 격리 수준에서 읽어 하는 것을 차단합니다. 일관성 독해는 읽은 뷰에있는 레코드에 설정된 잠금은 무시됩니다.
UPDATE ... WHERE ... 검색에서 발견 된 모든 레코드에 배타 넥스트 키 잠금을 설정합니다.
DELETE FROM ... WHERE ... 검색에서 발견 된 모든 레코드에 배타 넥스트 키 잠금을 설정합니다.
INSERT 은 삽입 된 행에 배타적 잠금을 설정합니다. 이 락은 넥스트 키 잠금 대신 인덱스 레코드 잠금이다 (즉, 갭 잠금이 존재하지 않는) 때문에 다른 세션이 삽입 된 행 앞에있는 갭에 삽입하는 것은 피할 수 없습니다.

행 삽입 전에 삽입 인텐션 갭 잠금라는 일종의 갭 잠금이 설정됩니다. 이 잠금은 같은 인덱스 갭에 삽입하는 여러 트랜잭션은 그 차이의 동일한 위치에 삽입해야 서로 기다릴 필요가 없도록 의도적으로 삽입하는 것을 보여줍니다. 값이 4와 7의 인덱스 레코드가 존재한다고 가정합니다. 각 값 5와 6의 삽입을 시도 별도의 트랜잭션은 삽입 된 행의 배타 락을 취득하기 전에 삽입 인텐션 잠금을 사용하여 4와 7 사이의 갭을 잠그는하지만 행 충돌이 발생하지 않기 때문에 서로 차단되지 않습니다.

중복 키 오류가 발생하면 중복 인덱스 레코드에 공유 잠금이 설정됩니다. 여러 세션이 동일한 행을 삽입하려고 할 때, 다른 세션이 이미 단독 잠금을하지 않으면 이렇게 공유 잠금을 사용하여 교착 상태가 발생할 수 있습니다. 이것은 다른 세션이 그 행을 삭제 한 경우에 발생할 수 있습니다. InnoDB 테이블 t1 의 구조가 다음과 같이되어 있다고합니다.
```
 CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
```
다음 세 가지 세션이 다음 작업을 순차적으로 실행하는 것으로합니다.

세션 1 :
```
 START TRANSACTION;
 INSERT INTO t1 VALUES (1);
```
세션 2 :
```
 START TRANSACTION;
 INSERT INTO t1 VALUES (1);
```
세션 3 :
```
 START TRANSACTION;
 INSERT INTO t1 VALUES (1);
```
세션 1 :
```
 ROLLBACK;
```
세션 1에 의한 최초의 처리는 행의 배타적 잠금이됩니다. 세션 2와 3의 처리는 모두 중복 키 오류가 발생하고 어떤 세션도 행의 공유 잠금을 요청합니다. 세션 1은 롤백시 행에 대한 배타적 잠금을 해제하고 대기중인 세션 2와 3의 공유 잠금 요청이 부여됩니다. 이 시점에서 세션 2와 3에서 교착 상태가 발생합니다. 모두 다른이 보유하고있는 공유 잠금을 위해 행의 단독 잠금을 얻을 수 없습니다.

키 값이 1 행이 테이블에 포함되어있는 경우도 비슷한 상황이 발생하고 3 개의 세션이 다음 작업을 순차적으로 실행합니다.

세션 1 :
```
 START TRANSACTION;
 DELETE FROM t1 WHERE i = 1;
```
세션 2 :
```
 START TRANSACTION;
 INSERT INTO t1 VALUES (1);
```
세션 3 :
```
 START TRANSACTION;
 INSERT INTO t1 VALUES (1);
```
세션 1 :
```
 COMMIT;
```
세션 1에 의한 최초의 처리는 행의 배타적 잠금이됩니다. 세션 2와 3의 처리는 모두 중복 키 오류가 발생하고 어떤 세션도 행의 공유 잠금을 요청합니다. 세션 1은 커밋시 행에 대한 배타적 잠금을 해제하고 큐 세션 2와 3의 공유 잠금 요청이 부여됩니다. 이 시점에서 세션 2와 3에서 교착 상태가 발생합니다. 모두 다른이 보유하고있는 공유 잠금을 위해 행의 단독 잠금을 얻을 수 없습니다.
INSERT ... ON DUPLICATE KEY UPDATE 중복 키 오류가 발생했을 때 업데이트되는 행에 공유 잠금 대신 단독 넥스트 키 잠금이 배치된다는 점에서 간단한 INSERT 와 다릅니다.
REPLACE 는 고유 키가 충돌하지 않으면 INSERT 와 마찬가지로 작동합니다. 그렇지 않으면 대체되는 행에 독점적 넥스트 키 잠금이 배치됩니다.
INSERT INTO T SELECT ... FROM S WHERE ... 는 T 에 삽입 된 각 행에 격차 잠금없이 단독 인덱스 레코드 잠금을 설정합니다. 트랜잭션 격리 수준이 READ COMMITTED 인 경우, 또는 innodb_locks_unsafe_for_binlog 가 활성화되어 있고, 트랜잭션 격리 수준이 SERIALIZABLE 아닌 경우, InnoDB 는 일관성 읽기 (잠금 없음)로 S 에서 검색을 실행합니다. 그렇지 않으면, InnoDB 는 S 에서 인출 된 행에 공유 넥스트 키 잠금을 설정합니다. InnoDB 는 후자의 경우에 잠금을 설정해야합니다. 백업에서 롤 포워드 복구시에는 모든 SQL 문을 원래와 완전히 같은 방법으로 실행해야합니다.

CREATE TABLE ... SELECT ... 는 INSERT ... SELECT 의 경우와 마찬가지로 SELECT 를 공유 넥스트 키 잠금을 사용하여 실행하거나 일관성 독해로서 실행합니다.

구조 문 REPLACE INTO t SELECT ... FROM s WHERE ... 또는 UPDATE t ... WHERE col IN (SELECT ... FROM s ...) 에서 SELECT 가 사용되면, InnoDB 는 테이블 s 의 행 공유 넥스트 키 잠금을 설정합니다.
InnoDB 는 테이블에 사전에 지정된 AUTO_INCREMENT 컬럼의 초기화 동안 AUTO_INCREMENT 컬럼과 연관된 인덱스의 마지막에 배타적 잠금을 설정합니다. InnoDB 는 자동 증가 카운터에 액세스 할 때 잠금이 전체 트랜잭션의 끝까지가 아니라 현재의 SQL 문이 끝날 때까지 계속 특별한 AUTO-INC 테이블 잠금 모드가 사용됩니다. AUTO-INC 테이블 잠금이 유지되는 동안 다른 세션은 테이블에 삽입 할 수 없습니다. 섹션 14.2.2 "InnoDB 트랜잭션 모델 및 잠금" 을 참조하십시오.

InnoDB 는 잠금을 설정하지 않고 사전에 초기화 된 AUTO_INCREMENT 컬럼의 값을 가져옵니다.
FOREIGN KEY 제약이 테이블에 정의되어있는 경우에는 제약 조건을 확인해야 삽입, 업데이트 또는 삭제가 이루어지면 제약을 체크하기 위해 참조되는 레코드에 공유 레코드 수준 잠금 이 설정됩니다. InnoDB 는 제약이 실패 할 경우에 대비하여 이러한 잠금 설정도 실시합니다.
LOCK TABLES 테이블 잠금을 설정하지만 이러한 잠금을 설정하는 InnoDB 계층보다 상위의 MySQL 레이어입니다. InnoDB 는 innodb_table_locks = 1 (기본값)하고 autocommit = 0 의 경우 테이블 잠금을 인식하고 InnoDB 보다 상위의 MySQL 레이어는 행 레벨 락을 식별합니다.

그렇지 않으면, InnoDB 의 자동 데드 록 검출에서는 이러한 테이블 잠금이 참여하는 교착 상태를 감지 할 수 없습니다. 또한이 경우에는 상위의 MySQL 레이어는 행 레벨 락을 식별하지 않기 때문에 현재 다른 세션이 행 수준 잠금을 보유하고있는 테이블에서 테이블 잠금을 얻을 수 있습니다. 그러나 섹션 14.2.10 "교착 상태 감지 및 롤백" 에서 설명했듯이, 이는 트랜잭션의 무결성이 손상 될 수는 없습니다. 섹션 14.6.7 "InnoDB 테이블에서의 제한" 을 참조하십시오.