[MySQL][InnoDB] InnoDB에서 다른 SQL 문으로 설정된 잠금

잠금상태에서 읽기, UPDATE 또는 DELETE는 일반적으로 SQL문 처리시 스캔되는 모든 인덱스 레코드에 대해 레코드 잠금을 설정합니다. 명령문에 행을 제외시킬 WHERE조건이 있는지 여부는 중요하지 않습니다. InnoDB는 정확한 WHERE조건을 기억하지 않지만 스캔된 인덱스 범위만 알고 있습니다. 잠금 장치는 일반적으로 레코드 바로 앞의 "GAP"에 삽입을 차단하는 Next-Key 잠금 장치입니다. 그러나 GAP 잠금을 명시적으로 비활성화하여 Next-Key 잠금을 사용하지 않을 수 있습니다. 트랜잭션 격리 수준은 설정된 잠금에 영향을 줄 수 있습니다.

 

검색에 보조 인덱스가 사용되고 설정될 인덱스 레코드 잠금이 배타적 일 경우 InnoDB는 해당 클러스터형 인덱스 레코드를 검색하여 잠금을 설정합니다.

 

명령문에 적합한 인덱스가 없고 MySQL이 명령문을 처리하기 위해 전체 테이블을 스캔해야하는 경우 테이블의 모든 행이 잠기고 다른 사용자가 테이블에 대한 모든 삽입을 차단합니다. 쿼리가 불필요하게 많은 행을 스캔하지 않도록 올바른 인덱스를 작성하는 것이 중요합니다.

 

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

+ SELECT ... FROM은 트랜잭션 격리 수준이 SERIALIZABLE로 설정되어 있지 않으면 데이터베이스의 스냅 샷을 읽고 잠금을 설정하지 않는 일관된 읽기입니다. 직렬화 가능 레벨의 경우, 검색은 발견된 인덱스 레코드에 공유된 Next-Key 잠금을 설정합니다. 그러나 고유 인덱스를 사용하여 행을 잠그는 명령문에는 고유 레코드를 검색하기 위해 인덱스 레코드 잠금만 필요합니다.

 

+ SELECT ... FOR UPDATE 또는 SELECT ... LOCK IN SHARE MODE의 경우 스캔된 행에 대해 잠금이 획득되고 결과 세트에 포함되지 않는 행 (예 : 그렇지 않은 경우)에 대해 잠금이 해제됩니다. WHERE 절에 주어진 기준을 충족해야 합니다. 그러나 경우에 따라 쿼리 실행 중에 결과 행과 원래 소스간의 관계가 손실되므로 행이 즉시 잠금 해제되지 않을 수 있습니다. 예를 들어, UNION에서는 테이블에서 스캔된 (잠긴) 행이 결과 세트에 적합한지 여부를 평가하기 전에 임시 테이블에 삽입될 수 있습니다. 이 상황에서 임시 테이블의 행과 원래 테이블의 행의 관계는 손실되고 후자의 행은 쿼리 실행이 끝날 때까지 잠금이 해제되지 않습니다.

 

+ SELECT ... LOCK IN SHARE MODE에서 잠금 검색시 발생하는 모든 인덱스 레코드에 Shared Next-Key 잠금을 설정합니다. 그러나 고유 인덱스를 사용하여 행을 잠그는 명령문에는 고유 레코드를 검색하기 위해 인덱스 레코드 잠금만 필요합니다.

 

+ SELECT ... FOR UPDATE는 검색시 발생하는 모든 레코드에 대해 독점인 다음 키 잠금을 설정합니다. 그러나 고유 인덱스를 사용하여 행을 잠그는 명령문에는 고유 레코드를 검색하기 위해 인덱스 레코드 잠금 만 필요합니다.

인덱스 레코드의 경우 검색에서 발생하는 SELECT ... FOR UPDATE는 다른 세션이 SELECT ... LOCK IN SHARE 모드를 수행하거나 특정 트랜잭션 격리 수준에서 읽지 못하게 차단합니다. 일관된 읽기는 읽기보기에 있는 레코드에 설정된 잠금을 무시합니다.

 

 

 

+ UPDATE ... WHERE ... 검색시 발생하는 모든 레코드에 대해 독점적인 다음키 잠금을 설정합니다. 그러나 고유 인덱스를 사용하여 행을 잠그는 명령문에는 고유 레코드를 검색하기 위해 인덱스 레코드 잠금만 필요합니다.

 

+ UPDATE가 클러스터형 인덱스 레코드를 수정하면 영향을 받는 보조 인덱스 레코드에 대해 암시적 잠금이 수행됩니다. UPDATE 조작은 새 2차 인덱스 레코드를 삽입하기 전에 중복 점검 스캔을 수행 할 때 및 2차 인덱스 레코드를 삽입할 때 영향을받는 2차 인덱스 레코드에서 공유 잠금을 수행합니다.

 

+ DELETE FROM ... WHERE ... 검색시 발생하는 모든 레코드에 대해 독점적인 다음키 잠금을 설정합니다. 그러나 고유 인덱스를 사용하여 행을 잠그는 명령문에는 고유 레코드를 검색하기 위해 인덱스 레코드 잠금만 필요합니다.

 

+ INSERT는 삽입된 행에 독점 잠금을 설정합니다. 이 잠금은 Next-Kye 잠금(즉, GAP 잠금이 아님)이 아니라 인덱스 레코드 잠금이며 다른 세션이 삽입된 행 이전의 갭에 삽입되는 것을 막지 않습니다.

행을 삽입하기 전에 insert intention gap lock이라고하는 간격 잠금 유형이 설정됩니다. 이 잠금은 동일한 인덱스 간격에 삽입하는 여러 트랜잭션이 간격 내의 동일한 위치에 삽입하지 않는 경우 서로를 기다릴 필요가 없도록 삽입하려는 의도를 나타냅니다. 값이 4와 7인 인덱스 레코드가 있다고 가정합니다. 각각 5와 6의 값을 삽입하려고하는 별도의 트랜잭션은 삽입 된 행에서 독점 잠금을 얻기 전에 삽입 의도 잠금으로 4와 7 사이의 간격을 잠그지만 행이 충돌하지 않기 때문에 서로 차단하지 않습니다.

 

중복키 오류가 발생하면 중복 인덱스 레코드의 공유 잠금이 설정됩니다. 다른 세션에 이미 독점 잠금이 있는 경우 여러 세션이 동일한 행을 삽입하려고 할때  공유 잠금을 사용하면 교착 상태가 발생할 수 있습니다. 다른 세션이 행을 삭제한 경우도 발생할 수 있습니다. InnoDB 테이블 t1의 구조는 다음과 같습니다.

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

 

이제 세 개의 세션이 다음 작업을 순서대로 수행한다고 가정합니다.

Session 1:
START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session 2:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session 3:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session 1:
ROLLBACK;

 

 

 

세션 1에 의한 첫번째 조작은 행에 대한 독점 잠금을 획득합니다. 세션 2와 3의 작업으로 인해 중복키 오류가 발생하고 행에 대한 공유 잠금을 요청합니다. 세션 1이 롤백되면 행에서 독점 잠금을 해제하고 세션 2및 3에 대해 대기중인 공유 잠금 요청이 승인됩니다. 이 시점에서 세션 2와 3의 교착 상태 : 다른 사람이 보유한 공유 잠금으로 인해 행에 대한 독점 잠금을 획득 할 수 없습니다.

 

테이블에 키 값이 1인 행이 이미 있고 세 개의 세션이 다음 작업을 순서대로 수행하는 경우 유사한 상황이 발생합니다.

Session 1:
START TRANSACTION;
DELETE FROM t1 WHERE i = 1;

Session 2:
START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session 3:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session 1:

COMMIT;

 

세션 1에 의한 첫번째 조작은 행에 대한 독점 잠금을 획득합니다. 세션 2와 3의 작업으로 인해 중복키 오류가 발생하고 행에 대한 공유 잠금을 요청합니다. 세션 1이 커밋되면 행에서 배타적 잠금을 해제하고 세션2 및 3에 대해 대기중인 공유 잠금 요청이 부여됩니다. 이 시점에서 세션 2와 3의 교착 상태 : 다른 사람이 보유한 공유 잠금으로 인해 행에 대한 독점 잠금을 획득 할 수 없습니다.

 

+ INSERT ... ON DUPLICATE KEY UPDATE는 중복키 오류가 발생할때 업데이트할 행에 공유 잠금이 아닌 배타적(exclusive) 잠금이 배치된다는 점에서 단순 INSERT와 다릅니다. 중복된 기본키 값에 대해 배타적(exclusive) 인덱스 레코드 잠금이 사용됩니다. 고유한 중복키 값에 대해서는 독점적인 next-key 잠금이 사용됩니다.

 

+ unique 키에 충돌이 없으면 교체는 삽입처럼 수행됩니다. 그렇지 않으면, 교체할 행에 배타적(exclusive) next-key 잠금이 배치됩니다.

 

+ SELECT INTO T IN SELECT ... FROM S WHERE ... T에 삽입된 각 행에 배타적(exclusive) 인덱스 레코드 잠금 (Gap 잠금없이)을 설정합니다. 트랜잭션 격리 레벨이 READ COMMITTED이거나 innodb_locks_unsafe_for_binlog가 사용 가능하고 트랜잭션 격리가있는 경우 레벨이 직렬화 가능하지 않은 경우 InnoDB는 S에서 검색을 일관된(contention) 읽기 (잠금 없음)로 수행합니다. 그렇지 않으면 InnoDB는 S의 행에 shared next-key 잠금을 설정합니다. 후자의 경우 InnoDB는 잠금을 설정해야합니다. 명령문 기반 바이너리 로그를 사용하여 롤 포워드 복구하는 동안 모든 SQL 문은 이전에 수행했던 방식대로 정확히 동일한 방식으로 실행되어야합니다.

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

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) 잠금을 설정합니다.

innodb_autoinc_lock_mode=0을 사용하면 InnoDB는 자동 증가 카운터에 액세스하는 동안 잠금이 현재 SQL 문의 끝(전체 트랜잭션의 끝이 아닌)까지 유지되는 특수 AUTO-INC 테이블 잠금 모드를 사용합니다. AUTO-INC 테이블 잠금이 유지되는 동안 다른 클라이언트가 테이블에 삽입 할 수 없습니다. innodb_autoinc_lock_mode = 1 인 "대량 삽입"에 대해서도 동일한 동작이 발생합니다. 테이블 레벨 AUTO-INC 잠금은 innodb_autoinc_lock_mode = 2와 함께 사용되지 않습니다.

InnoDB는 잠금을 설정하지 않고 이전에 초기화 된 AUTO_INCREMENT 열의 값을 가져옵니다.

 

+ FOREIGN KEY 제약 조건이 테이블에 정의된 경우 제약 조건 조건을 확인해야 하는 모든 삽입, 업데이트 또는 삭제는 제약 조건을 확인하기 위해 보고있는 레코드에 shared record-level 잠금을 설정합니다. InnoDB는 제약 조건이 실패한 경우에도 이러한 잠금을 설정합니다.

 

+ LOCK TABLES는 테이블 잠금을 설정하지만 이러한 잠금을 설정하는 것은 InnoDB 계층보다 높은 MySQL계층입니다. InnoDB는 innodb_table_locks=1(기본값)이고 autocommit=0 인 경우 테이블 잠금을 인식하고 InnoDB위의 MySQL계층은 행 수준 잠금에 대해 알고 있습니다.

그렇지 않으면 InnoDB의 자동 교착 상태 감지는 이러한 테이블 잠금이 관련된 교착 상태를 감지 할 수 없습니다. 또한이 경우 상위 MySQL 계층은 row-level 잠금에 대해 알지 못하므로 다른 세션에 현재 row-level 잠금이 있는 테이블에서 테이블 잠금을 얻을 수 있습니다. 그러나 트랜잭션 무결성을 위협하지는 않습니다.

 

+ LOCK TABLES는 innodb_table_locks = 1(기본값)인 경우 각 테이블에서 두 개의 잠금을 획득합니다. MySQL 계층의 테이블 잠금 외에도 InnoDB테이블 잠금을 획득합니다. 4.1.2 이전의 MySQL버전에서는 InnoDB테이블 잠금을 얻지 못했습니다. innodb_table_locks= 0을 설정하여 이전 동작을 선택할 수 있습니다. InnoDB 테이블 잠금을 획득하지 않으면 테이블의 일부 레코드가 다른 트랜잭션에 의해 잠긴 경우에도 LOCK TABLES가 완료됩니다.

MySQL 5.7에서 innodb_table_locks=0은 LOCK TABLES ... WRITE로 명시 적으로 잠긴 테이블에는 영향을 미치지 않습니다. LOCK TABLES ... WRITE에 의해 암시 적으로 (예 : 트리거를 통해) 또는 LOCK TABLES ... READ에 의해 읽기 또는 쓰기를 위해 잠긴 테이블에 영향을 미칩니다.

 

+ 트랜잭션이 커밋 또는 중단되면 트랜잭션이 보유한 모든 InnoDB 잠금이 해제됩니다. 따라서, 획득된 InnoDB 테이블 잠금이 즉시 해제되므로 autocommit=1 모드로 InnoDB 테이블에서 LOCK TABLES를 호출하는 것은 의미가 없습니다.

 

+ LOCK TABLES는 암시적COMMIT 및 UNLOCK TABLES를 수행하므로 트랜잭션 중간에 추가 테이블을 잠글 수 없습니다.

 

 

 

※도움이 되셨다면 광고클릭 한번 부탁드립니다.※