Last Updated on 8월 17, 2023 by Jade(정현호)
안녕하세요
이번 포스팅에서는 MySQL 의 자동 증가 번호 기능인 auto_increment 그리고 연관된 파라미터인 innodb_autoinc_lock_mode 에 대해서 확인해보도록 하겠습니다.
Contents
auto_increment
auto_increment 는 MySQL의 int(int,bigint,smallint 등) 자료형 컬럼에 기본키(PK) 가 설정된 컬럼에 대해서 자동 증가 번호 기능으로 레코드가 추가 시 자동으로 고유한(unique) 숫자가 부여하게 됩니다.
이런한 자동 숫자 증가 기능은 각 DB 별로 조금씩은 다른 형태로 유사한 기능을 제공되고 있습니다.
Oracle(오라클) 에서는 시퀀스(sequence) 라는 별도의 오브젝트를 생성하여 사용을 하고, SQL Server 에서는 MySQL 의 auto_increment 와 유사한 identity 있으며 또한 오라클 시퀀스와 유사한 시퀀스(sequence) 오브젝트도 있습니다.
테이블 생성 과 자동 번호 증가 사용
auto_increment 를 위해서 테스트 테이블 생성 과 데이터를 입력하면서 몇 가지를 확인해보겠습니다.
테이블 생성
mysql> create table tb_test_inc (id bigint unsigned NOT NULL AUTO_INCREMENT, col1 varchar(100), PRIMARY KEY(id) );
테이블 생성 시 위와 같이 int 형 컬럼에 auto_increment 를 지정하면 되며, auto_increment 로 지정한 컬럼은 기본키(PK) 를 설정하면 됩니다.
Note
auto_increment 를 사용한 컬럼에 기본키(PK) 를 지정하지 않고 테이블 생성시 아래와 같은 에러가 발생합니다.
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
Note
포스팅에서는 사용하지 않았으나 int 컬럼을 사용시 int(5) 이와 같이 괄호안의 숫자를 지정할 수 있습니다.
괄호안의 숫자, 자리수는 zerofill 속성으로 자릿수만큼 0을 채우는 것으로 사용하는 기능입니다. 버전 8.0.17 부터 Deprecated 되어 해당 버전 부터는 사용이 불가한 기능입니다.
[자세한 내용은 아래 포스팅 참조]
데이터 입력 및 조회
-- 1)
mysql> insert into tb_test_inc(col1) values
('A'),('B');
-- 2)
mysql> insert into tb_test_inc values
(NULL,'C'),(NULL,'D');
mysql> select * from tb_test_inc;
+----+------+
| id | col1 |
+----+------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
+----+------+
레코드 입력 시 Insert 절에서 auto_increment 사용 컬럼을 제외하고 데이터를 추가하거나, auto_increment 컬럼에 NULL을 입력하면 숫자가 증가하여 입력되게 됩니다.
테이블 생성 구문상의 정보
위에서 테이블을 생성 후에 레코드를 4개를 입력하였으며 해당 테이블에 대해서 생성 구문을 확인하게 되면 auto_increment 정보를 확인할 수 있습니다.
mysql> show create table tb_test_inc; +--------------------------------------------------------- | Create Table | +--------------------------------------------------------- CREATE TABLE `tb_test_inc` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `col1` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 | +-------------+-------------------------------------------
AUTO_INCREMENT=5 이와 같이 테이블 생성 구문을 확인하면 auto_increment 값을 까지 확인이 되게 됩니다.
해당 정보는 딕셔너리에 저장된 정보를 통해서 확인되는 정보로 information_schema 에서도 확인할 수 있습니다.
mysql> select table_name,auto_increment from information_schema.tables where table_name = 'tb_test_inc' and table_schema = DATABASE(); +-------------+----------------+ | table_name | auto_increment | +-------------+----------------+ | tb_test_inc | 5 | +-------------+----------------+
위의 생성 구문에서 테이블명만 변경하여 생성 후에 데이터를 입력해보겠습니다.
테이블 생성 및 데이터 입력, 조회
mysql> CREATE TABLE `tb_test_inc2` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`col1` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
mysql> insert into tb_test_inc2(col1) values
('A'),('B');
mysql> select * from tb_test_inc2;
+----+------+
| id | col1 |
+----+------+
| 5 | A |
| 6 | B |
+----+------+
테이블 생성 구문에 기재된 내용처럼 테이블 생성시 auto_increment 값이 있으면 해당 값을 다음 채번(번호추출)값으로 설정하여 사용되게 됩니다.
테이블을 복제하는 방법 중 create table like 구문이 있습니다 해당 구문으로 테이블을 복제하면 데이터는 제외하고 테이블의 구조(인덱스 포함) 그대로 다른 이름으로 복제를 하게 됩니다.
create table like 로 테이블을 생성하였을 경우에는 auto_increment 정보는 제외되고 생성되게 됩니다.
create table like 구문 사용
mysql> screate table tb_test_inc3 like tb_test_inc; mysql> show create table tb_test_inc3; +---------------------------------------------------- | Create Table | +---------------------------------------------------- CREATE TABLE `tb_test_inc3` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `col1` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +--------------+------------------------------------+
auto_increment 정보가 없는 것을 확인할 수 있습니다.
또한 mysqldump 를 통해 데이터를 추출하였을 경우에도 테이블 생성 구문에는 auto_increment 정보가 들어가게 됩니다
(아래에 내용이 있습니다)
번호는 항상 MAX+1로 증가
MySQL에서 데이터를 입력시 auto_increment을 통해서 번호가 생성(번호 추출) 되면서 입력시 사용자에 의한 Rollback 이거나 기타 이유에 의해서 rollback 이 되었을 경우, 그리고 가장 높은 값이 delete 가 되는 경우 등의 상황에서도 항시 번호 생성은 MAX+1 값, 즉 증가값이 계속 나오게 됩니다. (아래 예제에서 추가로 설명)
그래서 위와 같은 경우 번호가 중간에 건너뛰어져서 입력될 수 있습니다.
(1,2,3,4,7,8,9....)
이것은 오라클의 시퀀스 오브젝트도 유사 합니다. 오라클에서는 Insert 여부와 상관없이 시퀀스를 NEXTVAL 명령어로 번호를 채번 하였다면 다시 시퀀스를 조회 시 다음 번호가 추출되게 됩니다.
auto_increment는 별도의 오브젝트가 아니므로 별도로 채번 하는 것이 아닌 Insert를 수행을 하던 중에 롤백 되거나(하거나) 레코드가 삭제하는 하는 경우를 의미합니다.
아래 예제에서 내용을 확인해보도록 하겠습니다.
Insert 수행 후 롤백
-- 1) mysql> start transaction; mysql> insert into tb_test_inc values (NULL,'E'),(NULL,'F'); mysql> rollback; -- 2) mysql> start transaction; mysql> insert into tb_test_inc values (NULL,'E'),(NULL,'F'); mysql> commit; mysql> select * from tb_test_inc; +----+------+ | id | col1 | +----+------+ | 1 | A | | 2 | B | | 3 | C | | 4 | D | | 7 | E | | 8 | F | +----+------+
이번 예제에서는 start transaction 을 통해 명시적으로 트랜잭션을 시작 후에 Insert 를 진행하였습니다.
첫번째 Insert 시에는 rollback 을 하였고, 두번째 동일한 Insert 에서는 commit 을 하여 트랜잭션을 종료하였습니다.
테이블을 조회를 하면 5,6 숫자가 누락된 것을 확인할 수 있습니다. 이 부분은 예제에서 rollback 에 의해서 발생된 내역으로 위에서 설명한 것처럼 insert 구문에 의해서 5,6이 채번이 되었지만 rollback 이 되면서 테이블에서 기록은 되지 못한 상황입니다.
그 다음 두번째 Insert 시에는 이전의 Insert 에 의해서 6까지 채번이 된 상태임으로 7,8을 채번을 받게 되는 것입니다.
이처럼 auto_increment 도 상황에 따라서 중간의 번호가 빠지는 경우가 생길 수 있습니다.
추가로 Insert 를 다시 rollback 을 하고 한가지를 더 확인해보겠습니다.
mysql> start transaction; mysql> insert into tb_test_inc values (NULL,'E'),(NULL,'F'); mysql> rollback;
롤백이 되었다면 이번에는 mysqldump 로 테이블을 추출해보도록 하겠습니다.
mysqldump 수행
~$ mysqldump -u root -p \ --quick --single-transaction --extended-insert \ test tb_test_inc > test.tb_test_inc.sql
추출된 내용 중에 테이블 생성 구문을 보면 아래와 같이 AUTO_INCREMENT 정보가 포함된 것을 확인할 수 있습니다.
DROP TABLE IF EXISTS `tb_test_inc`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `tb_test_inc` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `col1` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4; /*!40101 SET character_set_client = @saved_cs_client */; AUTO_INCREMENT=11 <--
이전에 레코드는 8까지 입력된 상태에서 9,10에 해당하는 Insert 구문을 rollback 하였기 때문에 위의 생성 구문에서는 11로 확인되고 있습니다.
auto_increment 변경
auto_increment 의 값은 변경(또는 초기화,1로 변경) 를 할 수 있으며 아래의 alter 구문을 사용하게 됩니다.
mysql> alter table 테이블명 tb_test_inc auto_increment = 1;
다만 1로 초기화는 테이블 데이터가 모두 삭제되어야 가능 합니다. 그렇지 않을 경우 현재 테이블에서 가진 최고 값으로 변경됩니다.
아래 예제에서 확인해보겠습니다.
또는 아래와 같이 원하는 값으로 변경할 수 있습니다.
mysql> alter table 테이블명 tb_test_inc auto_increment = 20;
현재 테이블 정보
mysql> select * from tb_test_inc; +----+------+ | id | col1 | +----+------+ | 1 | A | | 2 | B | | 3 | C | | 4 | D | | 7 | E | | 8 | F | +----+------+ mysql> show create table tb_test_inc; +---------------------------------------------------------- | Create Table | +---------------------------------------------------------- CREATE TABLE `tb_test_inc` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `col1` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 | +--------------+------------------------------------------+
테이블에는 8까지 입력되어 있으나 그 이후 데이터는 Insert 시 롤백처리 하여서 숫자가 이미 채번은 되었지만 입력이 안된 상태입니다.
그래서 테이블 생성 구문 확인 시 AUTO_INCREMENT=11 로 확인되고 있습니다.
이 상태에서 alter table 을 실행해 보도록 하겠습니다.
alter table 실행
mysql> alter table tb_test_inc auto_increment = 1; mysql> show create table tb_test_inc; +---------------------------------------------------------- | Create Table | +---------------------------------------------------------- CREATE TABLE `tb_test_inc` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `col1` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 | +--------------+------------------------------------------+
테이블에는 8까지 입력 되어있기 때문에 alter table auto_increment 에 의해서 11에서 9로 변경된 것을 확인할 수 있습니다.
delete 로 데이터를 삭제하였을 경우도 동일하게 동작 합니다.
delete 후 alter table 실행
mysql> delete from tb_test_inc where id in (6,7,8); mysql> show create table tb_test_inc; +---------------------------------------------------------- | Create Table | +---------------------------------------------------------- CREATE TABLE `tb_test_inc` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `col1` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 | +--------------+------------------------------------------+ mysql> alter table tb_test_inc auto_increment = 1; mysql> show create table tb_test_inc; +---------------------------------------------------------- | Create Table | +---------------------------------------------------------- CREATE TABLE `tb_test_inc` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `col1` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 | +--------------+------------------------------------------+
1로 초기화 하기 위해서는 delete 나 truncate table 로 테이블 데이터를 모두 삭제해야 합니다.
truncate table 을 하게 되면 별도로 alter table auto_increment = 1 을 하지 않아도 됩니다.
mysql> truncate table tb_test_inc; mysql> show create table tb_test_inc; +------------------------------------------------------ | Create Table | +------------------------------------------------------ CREATE TABLE `tb_test_inc` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `col1` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +--------------+--------------------------------------+
delete 로 테이블 레코드를 모두 삭제하였을 경우에는 alter table auto_increment = 1 한번 실행해줘야 합니다.
(물론 delete 후 truncate 를 해도 동일한 결과입니다.)
그래서 auto_increment 값을 초기화를 하려고 할 경우 truncate table 을 이용하여 삭제를 진행하면 됩니다.
MySQL 재시작시 카운트 초기화(InnoDB)
MySQL 5.7 버전의 InnoDB 스토리지 엔진의 경우 auto_increment 카운트에 대한 정보를 디스크가 아닌 InnoDB 메모리에 저장하였습니다.
그리고 MySQL 재시작 하게 되면, 재시작 이후 auto_increment 포함된 테이블에 InnoDB는 다음 SQL을 통해 초기화를 하게 됩니다.
mysql> SELECT MAX(컬럼명) FROM 테이블명 FOR UPDATE;
이런 방식에서의 문제는 레코드의 삭제나 rollback 이 있었을 경우 재시작 이후 auto_increment 의 max 값이 달라질 수 있습니다.
5.7 버전에서 레코드를 삭제 후 재시작 하여 확인해보도록 하겠습니다.
현재 데이터 상황
mysql> select * from tb_test_inc;
+----+------+
| id | col1 |
+----+------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
+----+------+
mysql> select table_name,auto_increment
from information_schema.tables
where table_name = 'tb_test_inc'
and table_schema = DATABASE();
+-------------+----------------+
| table_name | auto_increment |
+-------------+----------------+
| tb_test_inc | 5 |
+-------------+----------------+
데이터를 삭제 후 다시 확인
mysql> delete from tb_test_inc
where id in (3,4);
mysql> select * from tb_test_inc;
+----+------+
| id | col1 |
+----+------+
| 1 | A |
| 2 | B |
+----+------+
mysql> select table_name,auto_increment
from information_schema.tables
where table_name = 'tb_test_inc'
and table_schema = DATABASE();
+-------------+----------------+
| table_name | auto_increment |
+-------------+----------------+
| tb_test_inc | 5 |
+-------------+----------------+
<-- 데이터를 삭제 하였지만 auto_increment 는 5임
MySQL 을 재시작 하도록 하겠습니다.
MySQL 재시작
~$ sudo systemctl restart mysqld
재시작이 완료되었다면 다시 조회해보도록 하겠습니다.
테이블과 information_schema 조회
mysql> select * from tb_test_inc;
+----+------+
| id | col1 |
+----+------+
| 1 | A |
| 2 | B |
+----+------+
mysql> select table_name,auto_increment
from information_schema.tables
where table_name = 'tb_test_inc'
and table_schema = DATABASE();
+-------------+----------------+
| table_name | auto_increment |
+-------------+----------------+
| tb_test_inc | 3 |
+-------------+----------------+
재시작 후에 auto_increment 가 5 -> 3으로 변경되었습니다.
Insert 작업이 롤백이 되거나 레코드가 Delete 된 경우 MySQL 5.7 버전에서는 재시작 시 auto_increment의 max 값이 달라 질 수도 있습니다.
MySQL 8.0 부터는 이러한 동작이 변경되었습니다
auto_increment 의 자동 증가 카운터 값은 변경될 때마다 리두 로그에 기록되고, 체크포인트 발생시 데이터 딕셔너리에 저장되게 됩니다.
그래서 이러한 방식의 변경은 현재 최대 자동 증가 카운트 값에 대해서 다시 시작해도 지속되게 됩니다.
실제로 테스트를 해보도록 하겠습니다.
데이터 및 카운터 확인
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.23 |
+-----------+
mysql> select * from tb_test_inc;
+----+------+
| id | col1 |
+----+------+
| 1 | A |
| 2 | B |
+----+------+
mysql> select table_name,auto_increment
from information_schema.tables
where table_name = 'tb_test_inc'
and table_schema = DATABASE();
+-------------+----------------+
| TABLE_NAME | AUTO_INCREMENT |
+-------------+----------------+
| tb_test_inc | 5 |
+-------------+----------------+
4개의 레코드에서 2개를 삭제하여 2개의 레코드가 조회되며 auto_increment 값은 5로 확인되고 있습니다.
8.0 버전에서 재시작 후 조회해보겠습니다.
## MySQL 재시작
~$ sudo systemctl restart mysqld
~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.23 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
< .. 중략 ..>
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.23 |
+-----------+
mysql> select * from tb_test_inc;
+----+------+
| id | col1 |
+----+------+
| 1 | A |
| 2 | B |
+----+------+
mysql> select table_name,auto_increment
from information_schema.tables
where table_name = 'tb_test_inc'
and table_schema = DATABASE();
+-------------+----------------+
| TABLE_NAME | AUTO_INCREMENT |
+-------------+----------------+
| tb_test_inc | 5 |
+-------------+----------------+
MySQL 5.7 과 달리 MySQL 8.0 에서는 auto_increment max 카운트 값이 그대로 유지되는 것을 확인할 수 있습니다.
MySQL 8.0 에서는
MySQL 이 정상적으로 종료 후 다시 시작하면 데이터 딕셔너리에 저장된 자동증가 증가 최대값을 확인하여 메모리의 자동 증가 카운트를 초기화 하게 됩니다.
크래시에 의해서 재시작이 되는 경우에는 데이터 딕셔너리에 저장된 자동 증가 최대값을 사용하거나, 마지막 체크포인트 이후 사용된 자동 증가 카운터 값은 리두 로그를 검색하게 됩니다. redo-logged 값이 in-memory counter 값보다 크면 redo-logged 값이 적용됩니다.
그러나 예기치 않게 서버의 종료 등의 장애에 의해서 redo 로그 버퍼(메모리)에 기록된 자동 증가 카운터 값 정보가 redo 로그 파일에 플러시(내려쓰기) 되기전에 문제가 발생되었다면, 이전 값을 참조하여 초기화하게 됩니다.
관련해서 MySQL8.0.33 버전에서 Bug Fix 된 부분이 있고 해당 내용에 대한 포스팅입니다.
innodb_autoinc_lock_mode
InnoDB 에서 auto_increment 를 사용하는 과정에서 사용되는 잠금 모드에 대해서 확인해보려고 합니다.
Insert 문 구분
Insert 종류(유형)에 따라서 AUTO_INCREMENT에서 자동 증분 값을 생성하는 데 사용되는 잠금 모드와 각 잠금 모드가 복제에 미치는 영향이 조금씩 달라지게 됩니다.
auto_increment 동작 관련하여 아래와 같이 Insert 를 다음과 같이 구분하게 됩니다.
INSERT-like 문
새로운 행을 생성하는 모든 구문을 의미합니다.
- INSERT,
- INSERT ... SELECT,
- REPLACE,
- REPLACE ... SELECT,
- LOAD DATA 포함하여 "단순 INSERT" , "대량 INSERT " 및 " 혼합 모드 INSERT"
Simple inserts(단순 인서트)
추가되는 레코드 수를 미리 알 수 있는 문장을 의미합니다(명령문 초기 처리 시)
nested subquery 가 없는 단일 행 과 복수 행의 INSERT 구문이나 REPLACE 구문이 해당됩니다.
다만 INSERT ... ON DUPLICATE KEY UPDATE는 해당되지 않습니다.
Bulk inserts(대량 인서트)
삽입할 레코드의 수(또는 필요한 자동 증분 값의 수)를 미리 알 수 없는 명령문을 의미합니다.
여기에는 INSERT ... SELECT 및 REPLACE ... SELECT 그리고 LOAD DATA 가 이에 해당됩니다.
각 행이 처리될 때 한 번에 하나씩 AUTO_INCREMENT 컬럼에 InnoDB는 새로운 값을 할당합니다.
Mixed-mode inserts(복합 모드 인서트)
simple insert 에서 아래와 같이 일부 row에 auto_increment 값을 명시하는 경우를 의미합니다 여기서는 c1 컬럼이 auto_increment 컬럼입니다.
1 과 5는 명시적으로 값을 입력한 것이며, 그외 다른 3개는 자동 증가 형태입니다.
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
또 다른 형태로는 INSERT ... ON DUPLICATE KEY UPDATE 도 복합모드 Insert 문으로 분류되어 있습니다.
잠금의 동작 모드
InnoDB 에서 auto_increment 를 사용할 때 안정성 과 확장성을 고려한 Lock 매커니즘 방식을 제공하고 있으며, innodb_autoinc_lock_mode 파라미터에서 설정할 수 있습니다.
innodb_autoinc_lock_mode 파라미터 값은 0,1,2 가 있으며 각 모드 별로 동작 과 제약이 다르게 됩니다.
innodb_autoinc_lock_mode 파라미터에 대해 세 가지 가능한 설정이 있습니다.
설정은 traditional , consecutive 또는 interleaved 이며 이에 대해 각각 0, 1 또는 2 로 설정합니다.
• traditional(0)
• consecutive(1)
• interleaved(2)
0 (traditional lock mode)
traditional lock mode 는 innodb_autoinc_lock_mode 변수가 도입되기 전에 존재했던 것과 동일한 동작을 제공합니다.
traditional lock mode 옵션은 이전 버전과의 호환성, 성능 테스트 및 의미 체계의 가능한 차이로 인한 "혼합 모드 삽입" 문제 해결을 위해 제공됩니다.
이 잠금 모드는 INSERT-like(모든 insert 범주에 들어가는 문장) 문장 실행 시 테이블 레벨의 AUTO-INC 락을 사용하게 되며 해당 Insert 구문이 끝날 때까지 유지됩니다.
이 모드의 경우 자동 증가 값은 연속으로 할당을 할 수 있게 됩니다.
해당 내용을 확인해보기 위해서는 아래와 같은 시나리오가 진행되게 됩니다.
예제 테이블
CREATE TABLE t1 ( c1 INT(11) NOT NULL AUTO_INCREMENT, c2 VARCHAR(10) DEFAULT NULL, PRIMARY KEY (c1) ) ENGINE=InnoDB;
두 개의 트랜잭션이 실행 중이고 한 트랜잭션은 INSERT ... SELECT 로 1000개의 행을 삽입하고 있고 다른 트랜잭션은 INSERT로 하나의 행을 삽입하는 간단한 명령문을 실행하고 있습니다.
Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
Tx2: INSERT INTO t1 (c2) VALUES ('xxx');
InnoDB는 Tx1의 INSERT 문의 SELECT에서 검색된 행 수를 미리 알 수 없으며, 그래서 진행됨에 따라 자동 증가 값을 한 번에 하나씩 할당하게 됩니다.
명령문의 끝까지 유지되는 테이블 레벨의 잠금을 사용하면 테이블에 INSERT는 하나의 명령문만(Tx1) t1 테이블에 실행할 수 있으며 AUTO-INC 잠금에 의해 대기하기 때문에 다른 명령문(tx2)에 의한 자동 증분 번호 생성에 대해서 인터리브되지 않습니다.
이 잠금 모드(traditional)에서는 행수를 사전에 모르는 경우에는 사용되며 명령문이 끝날 때까지 유지되며, 할당된 모든 auto increment 값은 반드시 연속적인 값이 되기 때문에 statement 기반 복제에도 안전하게 사용할 수 있습니다.
그러나 이러한 테이블 수준의 잠금은 여러 트랜잭션이 동시에 Insert 실행할 때 동시성과 확장성을 제한하게 됩니다.
연속 잠금 모드에서 InnoDB는 행 수를 미리 알고 있는 Simple Insert 명령문에 대해 테이블 수준 AUTO-INC 잠금을 사용하지 않고 명령문 기반 복제에 대한 결정적 실행과 안전성을 유지할 수 있습니다.
1 (consecutive lock mode)
innodb_autoinc_lock_mode 기본 설정값(Default) 버전 별로 다르며 버전 5.7에서 1 이며, 버전 8 부터 기본값이 2로 변경 되었습니다.
이 모드에서는 Bulk insert 사용 시 테이블 수준 잠금 AUTO-INC을 사용하고 명령문이 끝날 때까지 이를 유지합니다.
INSERT ... SELECT, REPLACE ... SELECT, LOAD DATA 가 이에 해당됩니다.
대량 삽입 작업의 원본 테이블이 대상 테이블과 다른 경우 대상 테이블에 대한 AUTO-INC 잠금은 소스 테이블에서 선택된 첫 번째 행에서 대해서 공유 잠금(Shared Lock) 이 취해진 후에 잠금이 수행됩니다.
대량 삽입 작업의 소스 및 대상이 동일한 테이블인 경우 선택한 모든 행에 대해 공유 잠금이 수행된 후 AUTO-INC 잠금이 수행됩니다.
이와 같이 Bulk Insert로 사용되어 성능에 저하를 겪은 케이스에 대해서 Tech-Verse 2022에서 발표가 되었고 얼마전에 라인의 기술 블로그에서도 포스팅 되었습니다.
해당 모드는 simple insert(삽입할 행의 수를 미리 알고 있음) 구문에서는 AUTO-INC 는 뮤텍스(경량 잠금)의 제어 하에 필요한 수의 자동 증가 값을 얻어 수행되며 테이블 수준 잠금을 방지하게 됩니다.
그렇기 때문에 auto_increment 값의 할당 과정에서 mutex 를 사용하기 때문에 동시성을 높일 수 있습니다.
그리고 명령문이 완료될 때까지가 아니라 할당 프로세스 기간 동안 유지되며, AUTO-INC 잠금이 다른 트랜잭션에 의해 유지되지 않는 한 테이블 수준의 AUTO-INC 잠금은 사용되지 않습니다.
만약 다른 트랜잭션이 AUTO-INC 잠금을 보유하고 있는 경우 "simple insert" 은 마치 "bulk insert"인 것처럼 AUTO-INC 잠금을 기다립니다.
이 잠금 모드는 행 수를 미리 알 수 없는 INSERT 문의 경우(그리고 문이 진행됨에 따라 자동 증분 번호가 할당되는 경우) 할당된 모든 auto_increment 값은 연속적인 값이 되기 때문에 statement 기반의 복제 환경에서도 안전하게 사용할 수 있습니다.
간단히 말해서 이 잠금 모드는 명령문 기반 복제와 함께 사용하기에 안전하면서 확장성을 크게 향상시킵니다.
또한 traditional 잠금 모드에서와 같이 주어진 명령문에 의해 할당된 자동 증분 번호는 연속적으로 할당할 수 있습니다.
한 가지 중요한 예외를 제외하고 자동 증분을 사용하는 모든 명령문에 대해 "traditional" 모드와 비교하여 동일 합니다.
예외에는 사용자가 다중 행 "simple insert" 에서 전체가 아닌 일부 행에 대해 AUTO_INCREMENT 열에 대한 명시적 값을 제공하는 "Mixed-mode insert" 경우는 예외입니다.
다중 행 "simple insert"는 다음와 같은 형태를 의미합니다.
mysql> INSERT INTO TABLE VALUES (1,'A'), (2,'B'), (3,'C');
혼합모드는 위에서 언급 한 것처럼 다중 행 Simple Insert 시 AUTO_INCREMENT 컬럼에 명시적으로 값을 제공 또는 NULL 로 기재하는 내용이 혼재 되어있을 경우이며 아래와 같은 SQL 구문입니다.
mysql> INSERT INTO TABLE VALUES (1,'A'), (NULL,'B'), (3,'C');
이러한 Mixed-mode insert 경우 InnoDB는 삽입할 행 수보다 더 많은 자동 증가 값을 할당하게 됩니다.
현재 테스트 데이터 현황
mysql> select * from tb_test_inc;
+----+------+
| id | col1 |
+----+------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
+----+------+
mysql> select table_name,auto_increment
from information_schema.tables
where table_name = 'tb_test_inc'
and table_schema = DATABASE();
+-------------+----------------+
| table_name | auto_increment |
+-------------+----------------+
| tb_test_inc | 5 |
+-------------+----------------+
Mixed-mode insert 실행
mysql> INSERT INTO tb_test_inc VALUES (5,'E'), (NULL,'F'), (NULL,'G'), (8,'H'), (NULL,'I');
다시 현황 조회
mysql> select * from tb_test_inc;
+----+------+
| id | col1 |
+----+------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
| 6 | F |
| 7 | G |
| 8 | H |
| 9 | I |
+----+------+
mysql> select table_name,auto_increment
from information_schema.tables
where table_name = 'tb_test_inc'
and table_schema = DATABASE();
+-------------+----------------+
| table_name | auto_increment |
+-------------+----------------+
| tb_test_inc | 11 |
+-------------+----------------+
위에서 설명된 내용 처럼 Mixed-mode insert 의 경우 삽입할 행 수 보다 더 많은 자동 증가 값을 할당을 하게 됩니다.
위의 예제로 입력은 9까지 되어있지만 auto_increment 값은 11 로 확인되어 더 많은 자동 증가 값을 할당 받은 상태입니다.
이 다음으로 자동으로 할당되는 Insert 문 실행시 최근에 실행된 이전 명령문에 의해 생성된 자동 증가 값보다 크게 증가하게 되고 이전의 초과된 숫자는 손실이 되게 됩니다.
아래에서 확인해보도록 하겠습니다.
Simple Insert 실행
mysql> INSERT INTO tb_test_inc VALUES
(NULL,'J'), (NULL,'K');
ysql> select * from tb_test_inc;
+----+------+
| id | col1 |
+----+------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
| 6 | F |
| 7 | G |
| 8 | H |
| 9 | I | <-- ID:10 누락
| 11 | J |
| 12 | K |
+----+------+
mysql> select table_name,auto_increment
from information_schema.tables
where table_name = 'tb_test_inc'
and table_schema = DATABASE();
+-------------+----------------+
| table_name | auto_increment |
+-------------+----------------+
| tb_test_inc | 13 |
+-------------+----------------+
위에서 설명한것 처럼 Mixed-mode insert 에 의해서 1 초과가 발생되었고 그에 따라서 ID:10 은 누락이 발생되게 됩니다.
2 (interleaved lock mode)
이 잠금 모드에서는 INSERT-like 명령문이 테이블 수준 AUTO-INC 잠금을 사용하지 않으며 그에 따라 여러 명령문이 동시에 실행될 수 있습니다. 그래서 이 모드가 가장 빠르고 확장 가능한 잠금 모드입니다.
다만 SQL 문이 바이너리 로그에서 재생될 때 statement 기반 복제 또는 복구 시나리오를 사용할 때 안전하지 않습니다.
즉 이 모드를 사용하려면 binlog_format 이 row 이거나 mixed 일 경우 사용해야 합니다.
MySQL 8 버전 부터 innodb_autoinc_lock_mode 기본 값이 2로 되었습니다.
MySQL 8.0부터는 기본값으로 2(interleaved)로 되었으며, 이전 버전에는 1(consecutive)이 기본값으로 설정되어 있습니다.
interleaved lock mode가 기본값으로 변경된 이유는 MySQL 5.7에서 기본 복제 유형이 문장 기반 복제(Statement-based replication)에서 행 기반 복제(row-based replication)로 변경되었기 때문입니다.
문장 기반 복제(Statement-based replication)는 SQL 문의 실행 순서에 민감하므로, 연속(consecutive) 자동 증가 잠금 모드를 사용하여 주어진 SQL 문의 시퀀스에 대해 예측 가능하고 반복 가능한 순서로 자동 증가 값이 할당되도록 보장합니다.
반면, 행 기반 복제(row-based replication)는 SQL 문의 실행 순서에 영향을 받지 않기 때문에 이러한 잠금 모드가 필요하지 않습니다.
이 잠금 모드에서 자동 증가 값은 동시에 실행되는 모든 INSERT-like 문에서 고유하게 증가하도록 보장됩니다.
그러나 여러 명령문이 동시에 숫자를 생성할 수 있으므로(즉, 숫자 할당이 명령문 간에 인터리브됨 ) 실행되는 Insert 명령문에 의해 삽입된 행에 대해 생성된 값이 연속적이지 않을 수도 있습니다.
실행되는 명령문이 행 수를 미리 알고 있는 Simple Insert 인 경우 생성되는 번호의 누락(gap)이 발생되지 않습니다(Mixed-mode insert 는 제외)
그러나 bulk insert 이 실행될 때 주어진 명령문에 의해 할당된 자동 증가 값에 공백이 있을 수 있습니다.
binlog_format 을 row 나 mixed 로 사용중 이라면 innodb_autoinc_lock_mode = 2 로 사용하는 것이 복제에도 문제가 없으며, 성능상 가장 빠르고 동시성이 좋다고 할 수 있을 것 같습니다.
Reference
Reference URL
• mysql.com/5.7/innodb-auto-increment
• mysql.com/8.0/innodb-auto-increment
• mysql.com/5.7/innodb_autoinc_lock_mode
• mysql.com/8.0/innodb_autoinc_lock_mode
• mysql.com/worklog/6204
관련된 다른 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
