[MySQL] innodb_autoinc_lock_mode 환경

■ InnoDB AUTO_INCREMENT Lock 모드

innodb_autoinc_lock_mode 구성 매개 변수에 대한 세 가지 가능한 설정이 있습니다. 설정은 "전통", "연속"또는 "인터리브 된"잠금 모드에 대해 각각 0, 1 또는 2입니다.

 

+ innodb_autoinc_lock_mode = 0 (“전통(traditional)” lock mode)

전통적인 잠금 모드는 innodb_autoinc_lock_mode 구성 매개 변수가 MySQL 5.1에 도입되기 전에 존재했던 것과 동일한 동작을 제공합니다. 기존 잠금 모드 옵션은 시맨틱의 차이로 인해 "혼합 모드 삽입"과 관련된 문제와의 호환성, 성능 테스트 및 이전 버전과의 호환성을 위해 제공됩니다.

 

이 잠금 모드에서 모든 "INSERT-문과 비슷한"문은 AUTO_INCREMENT 컬럼이있는 테이블에 삽입하기 위한 특수 테이블 수준 AUTO-INC 잠금을 얻습니다. 이 잠금은 일반적으로 자동 증분 값이 주어진 INSERT문 시퀀스에 대해 예측 가능하고 반복 가능한 순서로 지정되고 자동 증분을 보장하기 위해 일반적으로 명령문 끝(트랜잭션 끝이 아닌)에 유지됩니다. 주어진 명령문으로 지정된 값은 연속적입니다.

 

명령문 기반 복제의 경우 이는 SQL문이 복제본 서버에서 복제될 때 소스 서버에서와 동일한 값이 자동 증가 컬럼에 사용됨을 의미합니다. 여러 INSERT 문의 실행 결과는 결정론적이며 복제본은 소스에서와 동일한 데이터를 재생성합니다. 여러 INSERT 문으로 생성된 자동 증가 값이 인터리브된 경우 두 동시 INSERT문의 결과는 결정적이지 않으며 명령문 기반 복제를 사용하여 복제본 서버에 안정적으로 전파 될 수 없습니다.

 

이를 명확하게하기 위해 이 테이블을 사용하는 예제를 확인해 봅니다.

CREATE TABLE t1 (
  c1 INT(11) NOT NULL AUTO_INCREMENT,
  c2 VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (c1)
) ENGINE=InnoDB;

AUTO_INCREMENT 컬럼이있는 테이블에 각각 행을 삽입하는 두 개의 트랜잭션이 실행 중이라고 가정합니다. 한 트랜잭션은 1000 개의 행을 삽입하는 INSERT ... SELECT 문을 사용하고 다른 트랜잭션은 한 행을 삽입하는 간단한 INSERT 문을 사용합니다.

Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
Tx2: INSERT INTO t1 (c2) VALUES ('xxx');

 

InnoDB는 Tx1의 INSERT문에있는 SELECT에서 얼마나 많은 행을 검색했는지 미리 알 수 없으며 명령문이 진행될때 자동 증분 값을 한번에 하나씩 지정합니다. 명령문의 끝까지 테이블 레벨 잠금을 사용하면 테이블 t1을 참조하는 하나의 INSERT 문만 한번에 실행할 수 있으며 다른 명령문에 의한 자동 증가 번호의 생성은 인터리빙되지 않습니다. Tx1 INSERT ... SELECT 문으로 생성된 자동 증가 값은 연속적이며, Tx2에서 INSERT 문이 사용하는 (단일) 자동 증가 값은 Tx1에 사용된 모든 값보다 작거나 큽니다. 어떤 문을 먼저 실행하느냐에 따라 다릅니다.

 

바이너리 로그에서 재생할 때(문 기반 복제를 사용하거나 복구 시나리오에서) SQL문이 동일한 순서로 실행되며, 결과는 Tx1 및 Tx2가 처음 실행될 때와 같습니다. 따라서, 명령문의 끝까지 보유된 테이블 레벨 잠금은 auto-increment(자동 증가)을 사용하여 INSERT문을 명령문 기반 복제에 사용하기에 안전하게 만듭니다. 그러나 이러한 테이블 수준 잠금은 여러 트랜잭션이 동시에 insert 문을 실행하는 경우 동시성과 확장성에 제약이 많습니다.

 

앞의 예에서 테이블 레벨 잠금이 없는 경우 Tx2의 INSERT에 사용된 auto-increment컬럼의 값은 명령문 실행시기에 따라 달라집니다. Tx1의 INSERT가 실행되는 동안 (시작하기 전이나 완료된 후에가 아닌) Tx2의 INSERT가 실행되면 두 INSERT 문에 의해 지정된 특정 자동 증가 값은 결정적이지 않으며 실행마다 다를 수 있습니다.

 

연속 잠금 모드에서 InnoDB는 행 수를 미리 알고있는 "간단한 삽입"문에 테이블 수준의 AUTO-INC 잠금을 사용하지 않아도 되고, 문 기반 복제에 대한 결정적인 실행과 안전성을 유지합니다.

 

바이너리 로그를 사용하여 SQL 문을 복구 또는 복제의 일부로 재사용하지 않는 경우,인터리브 잠금 모드를 사용하여 테이블 레벨 AUTO-INC 잠금을 모두 제거함으로써 동시성과 성능을 더욱 높일 수 있으며, 명령문에 의해 할당된 자동 증가 번호의 간격을 허용하고 동시에 실행되는 명령문에 의해 할당된 번호가 인터리브 될 가능성이 있습니다.

 

+ innodb_autoinc_lock_mode = 1 ("연속" 잠금 모드)

이것이 기본 잠금 모드입니다. 이 모드에서 "대량 삽입물"은 특수 AUTO-INC테이블 레벨 잠금을 사용하고 명령문 끝까지 보유합니다. 이것은 모든 INSERT ... SELECT, REPLACE ... SELECT 및 LOAD DATA 문에 적용됩니다. 한번에 AUTO-INC 잠금을 보유하는 하나의 명령문만 실행할 수 있습니다. 벌크 삽입 조작의 소스 테이블이 목표 테이블과 다른 경우, 소스 테이블에서 선택된 첫번째 행에서 공유 잠금이 수행된 후 목표 테이블의 AUTO-INC 잠금이 수행됩니다. 대량 삽입 작업의 소스와 대상이 동일한 테이블 인 경우 선택한 모든 행에서 공유 잠금을 수행한 후 AUTO-INC 잠금이 수행됩니다.

 

"간단한 삽입"(삽입 할 행 수를 미리 알고 있음)은 뮤텍스(가벼운 잠금) 제어하에 필요한 auto-increment값 수를 가져 와서 테이블 레벨 AUTO-INC 잠금을 피합니다. 이는 명령문이 완료될 때까지가 아니라 할당 프로세스 기간 동안만 유지됩니다. 다른 트랜잭션에서 AUTO-INC 잠금을 유지하지 않으면 테이블 수준 AUTO-INC잠금이 사용되지 않습니다. 다른 트랜잭션이 AUTO-INC잠금을 보유하는 경우 "단순 삽입"은 마치 "대량 삽입"인 것처럼 AUTO-INC 잠금을 기다립니다.

 

이 잠금 모드를 사용하면 행 수를 미리 알 수 없는 INSERT 문이있을 때 (그리고 명령문이 진행됨에 따라 auto-increment(자동 증가) 번호가 할당 된 경우) "INSERT-like"로 지정된 모든 자동 증분 값이 보장됩니다. 명령문은 연속적이며 작업은 명령문 기반 복제에 안전합니다.

 

간단히 말해서, 이 잠금 모드는 확장 성을 향상시키면서 명령문 기반 복제에 안전하게 사용할 수 있습니다. 또한, "전통적인"잠금 모드에서와 같이, 주어진 명령문에 의해 할당된 자동 증가 번호는 연속적입니다. 한 가지 중요한 예외를 제외하고 자동 증가를 사용하는 명령문의 경우 "전통적인"모드와 비교하여 시맨틱에 변화가 없습니다.

 

"혼합 모드 삽입"의 경우는 예외입니다. 여기서 사용자는 여러 행의 "간단한 삽입"에서 일부는 아니지만 일부 행에 대해 AUTO_INCREMENT컬럼에 대한 명시적 값을 제공합니다. 이러한 삽입의 경우 InnoDB는 삽입할 행 수보다 많은 자동 증가 값을 할당합니다. 그러나 자동으로 할당된 모든 값은 가장 최근에 실행된 이전 명령문에 의해 생성된 자동 증가 값보다 연속적으로 생성됩니다. "초과"숫자가 손실됩니다.

 

+ innodb_autoinc_lock_mode = 2 ("동시(Interleaved)"수행 잠금 모드)

이 잠금 모드에서 "INSERT-like"문은 테이블 수준 AUTO-INC잠금을 사용하지 않으며 여러 문을 동시에 실행할 수 있습니다. 가장 빠르고 확장 가능한 잠금 모드이지만 바이너리 로그에서 SQL문을 재생할 때 명령문기반 복제 또는 복구 시나리오를 사용할 때는 안전하지 않습니다.

 

이 잠금 모드에서 auto-increment(자동 증가) 값은 고유하게 적용되며 동시에 실행되는 모든 "INSERT-like"문에서 단조롭게 증가합니다. 그러나 여러 명령문이 동시에 숫자를 생성 할 수 있으므로 (즉, 숫자 할당이 명령문에 인터리브 됨) 주어진 명령문에 의해 삽입된 행에 대해 생성된 값이 연속적이지 않을 수 있습니다.

 

실행되는 유일한 명령문이 삽입될 행의 수를 미리 알고있는 "간단한 삽입"인 경우, "혼합 모드 삽입"을 제외하고 단일 명령문에 대해 생성된 수에 차이가 없습니다. 그러나 "대량 삽입"이 실행될 때 주어진 명령문에 의해 할당된 자동 증가 값에 차이가 있을 수 있습니다.

 

 

■ InnoDB AUTO_INCREMENT 잠금 모드 사용 의미

+ 복제와 함께 자동 증가 사용

명령문 기반 복제를 사용하는 경우 innodb_autoinc_lock_mode를 0또는 1로 설정하고 소스 및 해당 복제본에서 동일한 값을 사용합니다. innodb_autoinc_lock_mode=2 ( "인터리브 된") 또는 소스와 복제본이 동일한 잠금 모드를 사용하지 않는 구성을 사용하는 경우 복제본에서 auto-increment(자동 증가) 값이 원본과 동일하지는 않습니다.

 

행 기반 또는 혼합 형식 복제를 사용하는 경우 모든 자동 auto-increment(증분 잠금) 모드는 안전합니다. 행 기반 복제는 SQL 문의 실행 순서에 민감하지 않으며 혼합 형식은 행 기반을 사용합니다. 명령문 기반 복제에 안전하지 않은 명령문에 대한 복제).

 

+ "손실"자동 증가 값 및 시퀀스 간격

모든 잠금 모드 (0, 1 및 2)에서 자동 증분 값을 생성한 트랜잭션이 롤백되면 해당 자동 증분 값은 "손실"됩니다. auto-increment(자동 증분) 컬럼에 대한 값이 생성되면 "INSERT문과 유사항"명령문이 완료되었는지 여부 및 포함된 트랜잭션이 롤백되는지 여부에 관계없이 롤백할 수 없습니다. 이러한 손실된 값은 재사용되지 않습니다. 따라서 테이블의 AUTO_INCREMENT 컬럼에 저장된 값에 차이가 있을 수 있습니다.

 

+ AUTO_INCREMENT 컬럼에 NULL 또는 0 지정

모든 잠금 모드 (0, 1 및 2)에서 사용자가 INSERT의 AUTO_INCREMENT 열에 NULL 또는 0을 지정하면 InnoDB는 값이 지정되지 않은 것처럼 행을 처리하여 새 값을 생성합니다.

 

+ AUTO_INCREMENT 컬럼에 음수 값 할당

모든 잠금 모드 (0, 1 및 2)에서 AUTO_INCREMENT 컬럼에 음수 값을 할당하면 자동 증분 메커니즘의 동작이 정의되지 않습니다.

 

+ AUTO_INCREMENT 값이 지정된 정수 유형의 최대 정수보다 큰 경우

모든 잠금 모드 (0, 1 및 2)에서 값이 지정된 정수 유형에 저장할 수있는 최대 정수보다 커지면 자동 증가 메커니즘의 동작이 정의되지 않습니다.

 

+ "Bulk Insert"에 대한 auto-increment(자동 증분) 값의 간격

innodb_autoinc_lock_mode를 0 ( "전통") 또는 1 ( "연속")로 설정하면 테이블 수준의 AUTO-INC 잠금이 한 번에 하나의 명령문만 실행할 수 있습니다.

innodb_autoinc_lock_mode를 2 (“인터리브”)로 설정하면“대량 삽입”으로 생성된 자동 증분 값에 차이가 있을 수 있지만“INSERT문과 유사한” 명령문을 동시에 실행하는 경우에만 가능합니다.

잠금 모드 1 또는 2의 경우, 대량 삽입의 경우 각 명령문에 필요한 정확한 auto-increment(자동 증분) 값 수를 알 수 없고 과대 평가가 가능해져 연속 명령문간에 간격이 발생할 수 있습니다.

 

+ "혼합 모드 삽입(mixed-mode inserts)"에 의해 할당 된 자동 증분(Auto-increment) 값

"단순 삽입"은 일부 (전체는 아님) 결과 행에 대한 자동 증가 값을 지정하는 "혼합 모드 삽입"을 사용합니다. 이러한 명령문은 잠금 모드 0, 1 및 2에서 다르게 작동합니다. 예를 들어, c1은 테이블 t1의 AUTO_INCREMENT 컬럼이고 가장 최근에 자동으로 생성된 시퀀스 번호는 100이라고 가정합니다.

mysql> CREATE TABLE t1 (
    -> c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    -> c2 CHAR(1)
    -> ) ENGINE = INNODB;
Now, consider the following “mixed-mode insert” statement:

mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

 

innodb_autoinc_lock_mode를 0 ("일반적인 모드")으로 설정하면 4 개의 새로운 행은 다음과 같습니다.

mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+

다음으로 사용 가능한 자동 증분 값은 103입니다. 자동 증분 값은 명령문이 시작될 때 한꺼번에 할당되는게 아니라 한번에 하나씩 할당되기 때문입니다. 이 결과는 모든 유형의 "INSERT문과 비슷한"문을 동시에 실행하는지 여부에 관계없이 적용됩니다.

 

innodb_autoinc_lock_mode를 1("연속")로 설정하면 새로운 네개의 행도 있습니다.

mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+

그러나 이 경우 명령문이 처리 될 때 4개의 자동 증분 값이 할당되지만 2개만 사용되므로 사용 가능한 다음 자동 증분 값은 103이 아니라 105입니다. 이 결과는 모든 유형의 "INSERT-문과 비슷한"문을 동시에 실행하는지 여부에 관계없이 적용됩니다.

 

innodb_autoinc_lock_mode를 모드 2("인터리브 된")로 설정하면 4개의 새로운 행은 다음과 같습니다.

mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
|   x | b    |
|   5 | c    |
|   y | d    |
+-----+------+

x와 y의 값은 고유하며 이전에 생성된 행보다 큽니다. 그러나 x와 y의 특정 값은 명령문을 동시에 실행하여 생성된 자동 증가 값의 수에 따라 다릅니다.

 

마지막으로 가장 최근에 생성된 시퀀스 번호가 100일 때 발행된 다음 명령문을 고려합니다.

mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (101,'c'), (NULL,'d');

innodb_autoinc_lock_mode 설정에서는 101이 행(NUL, 'b')에 할당되고 행(101, 'c') 삽입이 실패하기 때문에 이 문은 중복 키 오류 23000( 쓸 수 없음, 테이블에 중복 키)을 생성합니다.

 

+ 일련의 INSERT문 중간에 AUTO_INCREMENT 컬럼 값 수정

모든 잠금 모드 (0, 1 및 2)에서 일련의 INSERT 문 중간에 AUTO_INCREMENT컬럼 값을 수정하면 "중복 항목"오류가 발생할 수 있습니다. 예를 들어 AUTO_INCREMENT 열 값을 현재 최대 자동 증분 값보다 큰 값으로 변경하는 UPDATE 작업을 수행하면 사용되지 않는 자동 증분 값을 지정하지 않은 후속 INSERT 작업에서 "중복 항목"오류가 발생할 수 있습니다. 이 동작은 다음 예에서 설명합니다.

mysql> CREATE TABLE t1 (
    -> c1 INT NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (c1)
    ->  ) ENGINE = InnoDB;

mysql> INSERT INTO t1 VALUES(0), (0), (3);

mysql> SELECT c1 FROM t1;
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
+----+

mysql> UPDATE t1 SET c1 = 4 WHERE c1 = 1;

mysql> SELECT c1 FROM t1;
+----+
| c1 |
+----+
|  2 |
|  3 |
|  4 |
+----+

mysql> INSERT INTO t1 VALUES(0);
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'