MySQL auto_increment와 innodb_autoinc_lock_mode

 

auto increment을 처음봤을 때 이름 그대로 키 값을 알아서 자동으로 올려주는 기능으로만 생각했다. 

그런데 막상 사용하려니 동시 요청에 대해 auto increment는 어떻게 대처하는지 원리를 이해못했다. 

여기저기 찾아보니 알아둬야 할 내용들이라 생각하여 정리한다

 

 

AUTO INCREMENT란

• AUTO_INCREMENT 속성을 사용하여 새로운 행에 unique ID 생성
• 중복되지 않기 때문에 PK에 AUTO_INCREMENT를 많이 설정한다. 

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

 

• 어떤 값도 id에 설정하지 않았음에도 숫자 1부터 값이 들어간 것을 알 수 있다.
• 또는 id에 0 또는 NULL을 설정해도 auto increment로 동작을 한다.

( 만약 id를 0부터 시작하고 싶다면 SQL mode에서 NO_AUTO_VALUE_ON_ZERO 를 설정할 수 있다. 하지만 SQL mode를 변경하면 데이터 손실이 발생할 수 있기 때문에 주의해야 한다. 또한 보통은 1부터 시작하는 경우가 많다고 한다. )

 

 

INSERT 후 auto increment 

• 새로운 값이 추가하면, auto increment 대상 ID 컬럼에 auto increment 값이 추가된다. 그리고 다시 해당 컬럼에서
  (가장 큰 값 + 1)로 auto increment가 설정

• upate, delete 문 또한 동일하게 동장한다. 하지만 update, delete 후 가장 큰 값이 기존의 가장 큰 값보다 작으면 auto increment는 변경되지 않는다. 

mysql> INSERT INTO animals (id,name) VALUES(100,'rabbit');
mysql> INSERT INTO animals (id,name) VALUES(NULL,'mouse');
mysql> SELECT * FROM animals;

+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | dog       |
|   2 | cat       |
|   3 | penguin   |
|   4 | lax       |
|   5 | whale     |
|   6 | ostrich   |
|   7 | groundhog |
|   8 | squirrel  |
| 100 | rabbit    |
| 101 | mouse     |
+-----+-----------+

현재 auto increment는 102를 가리키게 된다.

 

• update 구문 예시

SELECT * FROM animals;
+----+------+
| id | name |
+----+------+
|  3 | dog2 |
|  4 | cat2 |
+----+------+

UPDATE animals WHERE id = 4 SET id = 6;

SELECT * FROM animals;
+----+------+
| id | name |
+----+------+
|  3 | dog2 |
|  6 | cat2 |
+----+------+

 show create table animals;
+---------+-----------------------------------------
| Table   | Create Table                                                                                                                                                                                         |
+---------+-----------------------------------------
| animals | CREATE TABLE `animals` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

>> id가 기존 AUTO_INCREMENT(5)보다 큰 값(6)으로 update 이후 
>> AUTO_INCREMENT 값이 7로 변경된 것을 확인



UPDATE animals WHERE id = 6 SET id = 4;
INSERT INTO animals(name) VALUES ('mouse');
SELECT * FROM animals;
+----+-------+
| id | name  |
+----+-------+
|  3 | dog2  |
|  4 | cat2  |
|  7 | mouse |
+----+-------+

>> id가 기존 AUTO_INCREMENT보다 작은 값으로 update 이후 변경이 없는 것을 확인

 

 

INSERT + ROLLBACK 후 auto increment 

• auto increment 값은 롤백대상이 아니다. 
• 다음 예제에서 롤백되지 않기 때문에 auto increment 값은 기존 순번 그대로 적용된다. 

START TRANSACTION;

INSERT INTO animals(name) values ('dog1'), ('cat1');
ROLLBACK;
INSERT INTO animals(name) values ('dog2'), ('cat2');

COMMIT;


SELECT * FROM animals;
+----+------+
| id | name |
+----+------+
|  3 | dog2 |
|  4 | cat2 |
+----+------+

 

MySQL 재시작시 카운트 초기화(InnoDB)

• MySQL 5.7 버전
  • InnoDB 스토리지 엔진의 경우 auto_increment 카운트에 대한 정보를 디스크가 아닌 InnoDB 메모리에 저장
  •  MySQL 재시작 하게 되면, 재시작 이후 auto_increment 포함된 테이블에 InnoDB는 다음 SQL을 통해 초기화

SELECT MAX(컬럼명) FROM 테이블명 FOR UPDATE;

• 이런 방식에서의 문제는 레코드의 삭제나 rollback 이 있었을 경우 재시작 이후 auto_increment 의 max 값이 달라질 수 있다. 

 

• MySQL 8.0 부터 동작이 변경
  • auto_increment의 자동 증가 카운터 값은 변경될 때마다 리두 로그에 기록되고, 체크포인트 발생 시 데이터 딕셔너리에 저장
  • 그래서 이러한 방식의 변경은 현재 최대 자동 증가 카운트 값에 대해서 다시 시작해도 지속 됨

 

 

InnoDB의 AUTO_INCREMENT 처리

• InnoDB란
  • MySQL을 위한 데이터베이스 엔진 (DBMS가 CRUD참조에 사용하는 소프트웨어)
  • MySQL 5.5 버전 이후 기본적으로 MyISAM 대신 InnoDB 사용
  • MySQL 바이너리에 내장되어 있음
  • 트랜잭션 지원, 동시성 제어 가능, 데이터 무결성 보장, 제약조건 및 외래 키 지원
  • Row-level Lock (행 단위 Lock) 사용으로 변경 작업(INSERT, UPDATE, DELETE)의 속도가 빠름
• innodb_autoinc_lock_mode 시스템 변수를 설명하기 앞서 INSERT 관련 용어 설명
  • “INSERT-like” statements
    • 새로운 row를 생성하는 모든 구문
    • (아래 3가지 구문 모두 포함)
  • “Simple inserts”
    • 몇 줄이 insert 될지 실행 전에 미리 알 수 있는 구문
    • 여러 줄을 한 번에 insert 하더라도 미리 몇 줄이 insert 될지 알 수 있다면 bulk insert가 아닌 simple insert
    • nested subquery가 없는 INSERT, REPLACE,
    • INSERT ... ON DUPLICATE KEY UPDATE는 포함되지 않음
  • “Bulk inserts”
    • 몇 줄이 insert 될지 미리 알 수 없는 구문
      • INSERT ... SELECT
      • REPLACE ... SELECT
      • LOAD DATA
    • InnoDB는 한 row가 처리될 때마다 AUTO_INCREMENT 컬럼의 값을 증가
  • “Mixed-mode inserts”
    • “simple insert”이긴 하지만 insert구문 내에 AUTO_INCREMENT 컬럼의 값을 일부만 명시적으로 지정해 준 경우.
    • 다음 tx에서 c1을 1, 5인 경우만 명시적으로 지정한 예시

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

 

 

innodb_autoinc_lock_mode 시스템 변수 값에 따른 3가지 lock mode

• traditional: 0 / consecutive: 1 / interleaved: 2
• MySQL 8.0 이후 디폴트 모드: interleaved
• MySQL 8.0 이전 디폴트 모드: consecutive

• “traditional” lock mode
  • innodb_autoinc_lock_mode = 0
  • traditional lock mode는 이전 버전과의 호환성 및 성능 테스트 용도로도 사용됨
  • 모든 “INSERT-like” 구문은 AUTO_INCREMENT 컬럼에 데이터 추가 시 table-level AUTO-INC lock을 가짐
  • table-level AUTO-INC lock은 statement 단위다. (transaction단위 아님) 따라서 해당 구문의 실행까지 테이블에 유지되는 lock이다.
  • 구문의 순서가 유지되는 한 statement-based replication은 안전하다
  • lock이 statement가 끝날 때까지 유지되기 때문에 auto increment 값은 연속적이지만, 동시성에 제약이 있다.
  • 다음 예제에서 Tx1실행 후 Tx2 순서로 실행된다. Tx2가 매우 작은 양의 데이터를 INSERT 한다면 순서에 의해 Tx1이 끝날 때까지 기다리게 된다.

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

 

• “consecutive” lock mode 
  • innodb_autoinc_lock_mode = 1
  • “bulk inserts” (INSERT 행 수를 알 수 없음)
    • AUTO-INC table-level lock을 구문 끝날 때까지 유지
    • buik 양이 과다하면 테이블을 기다리는 요청이 많아지고 경쟁이 심해지게 된다.
  • "Simple inserts"(INSERT 행 수를 미리 암)
    • 기본적으로 뮤텍스(a light-weight lock)의 제어하에 필요한 수의 auto-increment 값을 획득하여 auto-increment 값 할당할 때까지만 lock을 유지한다. 그렇기에 concurrency를 높일 수 있다.
    • 하지만 다른 transaction의 락으로 인해 테이블을 사용 못하면, AUTO-INC lock을 사용한다.
    • 구문의 순서가 유지되는 한 statement-based replication은 안전하다.
  • “Mixed-mode inserts”
    • auto-increment 값을 사용자가 명시적으로 지정하기 때문에 InnoDB는 삽입할 행 수보다 더 많은 auto-increment 값을 할당하게 된다.
    • 다음 예시에서 명시적으로 값을 넣은 후 추가 데이터를 넣어 확인해 보면 auto-increment 값이 삽입한 행 수보다 큰 것을 확인할 수 있다. 

mysql> SELECT * FROM ANIMALS;
+----+------+
| id | name |
+----+------+
|  1 | dog1 |
|  2 | dog2 |
+----+------+

mysql> SHOW CREATE TABLE animals;
| animals | CREATE TABLE `animals` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |


mysql> INSERT INTO animals(id, name) 
VALUES (3, 'dog3'), (4, 'dog4'), (5, 'dog5'), (null, 'dog6');
// id에 명시적으로 3, 4, 5를 추가

mysql> SELECT * FROM ANIMALS;
+----+------+
| id | name |
+----+------+
|  1 | dog1 |
|  2 | dog2 |
|  3 | dog3 |
|  4 | dog4 |
|  5 | dog5 |
|  6 | dog6 |
+----+------+


mysql> INSERT INTO animals(id, name) VALUES (0, 'dog7');
mysql> SELECT * FROM ANIMALS;
+----+------+
| id | name |
+----+------+
|  1 | dog1 |
|  2 | dog2 |
|  3 | dog3 |
|  4 | dog4 |
|  5 | dog5 |
|  6 | dog6 |
| 10 | dog7 |
+----+------+
// 7이 아닌 10이 설정 
// 이유는 명시적으로 정한 값과 auto-increment값이 만나게 되면 충돌을 피하고자, 
// 다음 auto-increment 값이 더 커지게 된다.

https://dba.stackexchange.com/questions/60295/why-does-auto-increment-jumps-by-more-than-the-number-of-rows-inserted

• “interleaved” lock mode
  • 해당 모드부터 “INSERT-like” 구문은 테이블 단위 AUTO-INC락을 잡지 않는다. 그렇기 때문에 다수의 구문이 동시에 실행이 가능하다.
  • 대신 consecutive 하지 않아 구문 기반 replication 또는 recovery 시 안전하지 않다.
  • 해당 모드로 변경 시 로직 중에 AUTO-INCREMENT 값이 연속된다는 가정이 없는 것을 확인필요
  • MySQL 8.0부터 기본값으로 설정
    • 이유는 MySQL 5.7의 기본 replication 유형이 Statement-based replication에서 row-based replication로 변경되었기 때문이다.
    • Statement-based replication은 SQL 문의 실행 순서에 민감하므로, 연속(consecutive) 자동 증가 잠금 모드를 사용하여 주어진 SQL 문의 시퀀스에 대해 예측 가능하고 반복 가능한 순서로 자동 증가 값이 할당되도록 보장
    • row-based replication은 SQL 문의 실행 순서에 영향을 받지 않기 때문에 테이블 단위 락 필요하지 않음
• 라인의 기술블로그에서 모드 설정을 2로 변경한 것만으로도 CPU 100% 사용률이 10~20% 사이로 떨어졌다는 자료도 있다.

 

 

참고 

https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html 

https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html 

https://hoing.io/archives/1289#1_consecutive_lock_mode 

https://engineering.linecorp.com/ko/blog/how-line-openchat-server-handles-extreme-traffic-spikes 

 

•