--- title: "Auto-increment lock 모드: innodb_autoinc_lock_mode와 동시성" description: "InnoDB 자동 증가 값 할당 방식과 innodb_autoinc_lock_mode별 동시성, 복제 안전성, 운영 판단 기준을 정리한다." tags: [ MySQL, InnoDB, 트랜잭션, 성능최적화, 운영 ] image: "mysql-report-bg.png" published: "2026-08-04" updated: "2026-08-04" author: "MySQL 기술 노트" source_url: "" --- `AUTO_INCREMENT`는 단순히 이전 값에 1을 더하는 기능이 아니다. InnoDB는 여러 세션이 동시에 행을 삽입할 때 서로 겹치지 않는 값을 예약해야 하고, 한 문장 안에서 생성되는 값의 연속성도 가능한 범위에서 관리해야 한다. 이 과정의 직렬화 범위를 결정하는 핵심 설정이 `innodb_autoinc_lock_mode`이다. 이 설정을 잘못 이해하면 두 가지 상반된 문제가 생긴다. 동시 쓰기가 많은 테이블에서 필요 이상으로 보수적인 모드를 사용하면 삽입 처리량이 낮아질 수 있다. 반대로 값이 항상 빈틈없이 증가하거나, 동시 실행된 각 문장이 서로 겹치지 않는 연속 구간을 받는다고 가정하면 복구·복제·업무 로직에서 오류가 발생할 수 있다. 이 글은 자동 증가 값을 **식별자 할당 장치**로 보고, 내부 동작과 운영 기준을 구분해 설명한다. ## 1. 먼저 구분해야 할 세 가지 보장 자동 증가 열을 설계할 때는 다음 보장을 서로 다른 것으로 보아야 한다. 1. **유일성**: 같은 테이블에서 생성된 값이 충돌하지 않아야 한다. 2. **문장 내부 연속성**: 하나의 다중 행 `INSERT`가 연속된 값 구간을 받는가. 3. **업무적 무결번성**: 커밋된 행의 번호에 빈 값이 전혀 없는가. InnoDB의 핵심 목적은 첫 번째인 유일한 값 할당이다. 잠금 모드와 문장 종류에 따라 두 번째 보장 수준은 달라진다. 세 번째는 보장하지 않는다. 롤백, 중복 키 무시, 실패한 문장, 값 사전 예약, 서버 장애가 있으면 번호가 소모될 수 있다. 따라서 `AUTO_INCREMENT`를 회계 전표 번호, 외부 노출 순번, 법적 증빙 번호처럼 무결번성이 필요한 값으로 직접 사용해서는 안 된다. 그런 번호는 별도의 업무 규칙, 상태 전이, 감사 기록을 가진 번호 발급 절차로 관리해야 한다. ## 2. 값 할당 경로와 직렬화 지점 InnoDB는 삽입 문장을 처리하면서 필요한 자동 증가 값의 개수를 판단하고, 테이블의 메모리 내 자동 증가 카운터에서 값 또는 값 구간을 예약한다. MySQL 8.0의 InnoDB 자동 증가 카운터는 redo log에 기록되고 시스템 테이블에도 저장되므로 재시작 때마다 단순히 `MAX(id) + 1`만 계산하는 구조로 이해하면 안 된다. ```mermaid flowchart LR A[INSERT 문장 수신] --> B{필요 행 수를
미리 아는가} B -->|simple insert| C[필요한 값 구간 예약] B -->|bulk insert| D[행을 읽으며 값 할당] B -->|mixed-mode insert| E[명시 값과 생성 값 함께 처리] C --> F[레코드 삽입] D --> F E --> F F --> G{COMMIT 또는 ROLLBACK} G --> H[예약된 번호는 일반적으로 재사용하지 않음] ``` 여기서 중요한 것은 트랜잭션의 행 잠금과 자동 증가 값 할당을 위한 직렬화 장치가 같은 개념이 아니라는 점이다. 자동 증가 값은 문장 실행 중 예약될 수 있고, 이후 트랜잭션이 롤백되어도 예약이 되돌아가 다른 트랜잭션에 재사용되는 것이 아니다. 이 특성은 교착 방지와 동시성에는 유리하지만 번호의 무결번성과는 맞지 않는다. ## 3. 삽입 문장 분류가 잠금 동작을 바꾼다 `innodb_autoinc_lock_mode`를 해석하려면 먼저 MySQL이 삽입 문장을 어떻게 분류하는지 알아야 한다. ### 3.1 Simple insert 실행 시작 시 생성할 행 수를 알 수 있는 문장이다. 단일 행 또는 다중 행 `INSERT ... VALUES`, 일반적인 `REPLACE ... VALUES`가 대표적이다. 필요한 값 수를 미리 알 수 있으므로 값 구간을 한 번에 예약하기 쉽다. ### 3.2 Bulk insert 실행 전에 생성할 행 수를 정확히 알기 어려운 문장이다. `INSERT ... SELECT`, `REPLACE ... SELECT`, `LOAD DATA`가 대표적이다. 소스에서 행을 읽는 동안 값이 계속 필요할 수 있으므로 simple insert보다 보수적인 직렬화가 필요할 수 있다. ### 3.3 Mixed-mode insert 한 문장에 명시적 자동 증가 값과 자동 생성 값이 섞인 형태이다. 예를 들어 일부 행은 `id=100`을 지정하고, 다른 행은 `NULL`을 넣어 값을 생성하게 할 수 있다. 예약량과 실제 소비량이 달라질 수 있어 번호가 건너뛰는 현상이 더 쉽게 나타난다. 문장 형태만 보지 말고 실제 실행 경로를 함께 확인해야 한다. 트리거, `INSERT ... ON DUPLICATE KEY UPDATE`, 중복 키 처리도 값 소비와 결과 행 수의 관계를 복잡하게 만든다. ## 4. 세 가지 `innodb_autoinc_lock_mode` ### 4.1 모드 0: Traditional `innodb_autoinc_lock_mode=0`은 자동 증가 값을 생성하는 삽입 문장에 테이블 수준의 `AUTO-INC` 잠금을 사용하고 문장이 끝날 때까지 유지하는 가장 보수적인 방식이다. 같은 테이블에 대한 다른 자동 증가 삽입은 기다려야 하므로, 한 문장이 받은 값이 다른 문장의 값과 섞이지 않는 성질이 강하다. 장점은 statement-based replication과 과거 호환성 관점에서 실행 결과를 예측하기 쉽다는 점이다. 단점은 긴 `INSERT ... SELECT`나 `LOAD DATA`가 같은 테이블의 짧은 OLTP 삽입까지 대기시킬 수 있다는 것이다. 현대의 row-based replication 중심 환경에서는 보통 첫 선택이 아니다. ### 4.2 모드 1: Consecutive `innodb_autoinc_lock_mode=1`은 simple insert에는 가벼운 mutex 기반 값 예약을 사용하고, 행 수를 미리 알기 어려운 bulk insert에는 문장 종료까지 `AUTO-INC` 잠금을 사용하는 절충 방식이다. simple insert는 일반적으로 문장 안에서 연속된 값 구간을 받을 수 있고, bulk insert끼리 또는 bulk insert와 다른 삽입의 충돌은 더 보수적으로 직렬화된다. 과거 statement-based replication과의 호환성을 유지하면서 simple insert의 동시성을 개선하려는 환경에 적합했다. 다만 큰 bulk insert가 존재하면 테이블 수준 직렬화가 다시 나타날 수 있으므로, 설정값만 보고 병목이 사라졌다고 판단하면 안 된다. ### 4.3 모드 2: Interleaved `innodb_autoinc_lock_mode=2`는 자동 증가 값 할당을 위해 문장 전체를 덮는 테이블 수준 `AUTO-INC` 잠금을 사용하지 않는 방향으로 동작한다. 여러 문장이 동시에 값을 받을 수 있어 삽입 동시성이 가장 높다. 반면 bulk insert가 받는 값 사이에 다른 문장의 값이 끼어들 수 있으므로, 한 문장이 항상 하나의 연속 구간을 받는다고 가정해서는 안 된다. MySQL 8.0은 row-based binary logging이 기본이 된 흐름에 맞추어 모드 2를 기본값으로 사용한다. row-based replication은 원본에서 결정된 행 이미지를 전달하므로 동시 실행 순서에 따른 자동 증가 값 차이를 statement-based 방식보다 안전하게 처리한다. statement-based logging으로 자동 증가 결과를 재현해야 하는 환경에서는 모드 2가 안전하지 않은 문장을 만들 수 있다. | 모드 | 이름 | Simple insert | Bulk insert | 주된 운영 특성 | |---:|---|---|---|---| | 0 | Traditional | 문장 단위 `AUTO-INC` 잠금 | 문장 단위 `AUTO-INC` 잠금 | 가장 강한 직렬화, 낮은 삽입 동시성 | | 1 | Consecutive | 값 구간을 빠르게 예약 | 문장 단위 `AUTO-INC` 잠금 | 호환성과 동시성의 절충 | | 2 | Interleaved | 동시 예약 | 동시 할당 가능 | 높은 동시성, 문장 간 값 교차 가능 | 이 표의 “연속”은 번호에 빈틈이 없다는 뜻이 아니다. 한 문장이 예약한 범위의 형태에 대한 설명이며, 실패·롤백·중복 처리로 생기는 번호 소모는 별개이다. ## 5. 현재 설정과 복제 형식을 먼저 확인한다 다음 쿼리는 검증 환경의 서버 버전, 자동 증가 잠금 모드, binary log 형식을 함께 확인한다. `innodb_autoinc_lock_mode`는 실행 중 세션별로 임의 변경해 비교하는 동적 튜닝 변수가 아니다. 변경 가능 범위와 재시작 필요 여부는 대상 MySQL 배포판과 관리형 서비스의 파라미터 메타데이터로 확인해야 한다. ```sql SELECT VERSION() AS mysql_version, @@global.innodb_autoinc_lock_mode AS autoinc_lock_mode, @@global.binlog_format AS binlog_format; ``` 실행 결과(MySQL 8.0.x): ```text mysql> SELECT VERSION() AS mysql_version, -> @@global.innodb_autoinc_lock_mode AS autoinc_lock_mode, -> @@global.binlog_format AS binlog_format; +---------------+-------------------+---------------+ | mysql_version | autoinc_lock_mode | binlog_format | +---------------+-------------------+---------------+ | 8.0.46 | 2 | ROW | +---------------+-------------------+---------------+ 1 row in set (0.00 sec) ``` 운영에서는 값 하나만 확인하지 말고 다음 항목을 함께 기록한다. - writer 인스턴스의 `innodb_autoinc_lock_mode` - `binlog_format`과 실제 복제 토폴로지 - `INSERT ... SELECT`, `LOAD DATA` 같은 bulk insert의 존재 - 트리거와 mixed-mode insert 사용 여부 - 자동 증가 값을 정렬 순서나 업무 순번으로 해석하는 애플리케이션 코드 ## 6. Simple insert의 값 구간과 `LAST_INSERT_ID()` 다중 행 simple insert는 필요한 값 수를 미리 알 수 있다. 클라이언트가 `LAST_INSERT_ID()`로 얻는 값은 해당 문장에서 생성된 첫 번째 자동 증가 값이다. 마지막 값이나 커밋 순서를 뜻하지 않는다. ```sql DROP TABLE IF EXISTS autoinc_simple; CREATE TABLE autoinc_simple ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, payload VARCHAR(30) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB; INSERT INTO autoinc_simple(payload) VALUES ('alpha'), ('beta'), ('gamma'); SELECT LAST_INSERT_ID() AS first_generated_id, ROW_COUNT() AS affected_rows; SELECT id, payload FROM autoinc_simple ORDER BY id; DROP TABLE autoinc_simple; ``` 실행 결과(MySQL 8.0.x): ```text mysql> DROP TABLE IF EXISTS autoinc_simple; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE autoinc_simple ( -> id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, -> payload VARCHAR(30) NOT NULL, -> PRIMARY KEY (id) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO autoinc_simple(payload) -> VALUES ('alpha'), ('beta'), ('gamma'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT LAST_INSERT_ID() AS first_generated_id, -> ROW_COUNT() AS affected_rows; +--------------------+---------------+ | first_generated_id | affected_rows | +--------------------+---------------+ | 1 | 3 | +--------------------+---------------+ 1 row in set (0.00 sec) mysql> SELECT id, payload -> FROM autoinc_simple -> ORDER BY id; +----+---------+ | id | payload | +----+---------+ | 1 | alpha | | 2 | beta | | 3 | gamma | +----+---------+ 3 rows in set (0.01 sec) mysql> DROP TABLE autoinc_simple; Query OK, 0 rows affected (0.00 sec) ``` 이 예제는 경쟁 세션이 없는 단일 검증 환경이므로 1, 2, 3이 연속해서 보인다. 실제 동시 실행 환경에서 중요한 계약은 생성 키의 유일성이다. 애플리케이션이 “첫 ID부터 affected rows만큼 더하면 모든 생성 ID를 복원할 수 있다”고 가정하는 것은 문장 종류, 잠금 모드, 트리거, 중복 처리에 따라 깨질 수 있다. 생성된 각 키가 필요하다면 드라이버가 제공하는 generated keys 반환 동작을 대상 버전에서 검증해야 한다. ## 7. 번호가 비는 현상은 정상 동작일 수 있다 다음 예제는 중복 키를 `IGNORE`한 행도 자동 증가 값 예약에 영향을 줄 수 있음을 보여준다. 번호가 비었다는 사실만으로 데이터 손실이나 삭제를 단정할 수 없다. ```sql DROP TABLE IF EXISTS autoinc_gap; CREATE TABLE autoinc_gap ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, request_key VARCHAR(30) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uq_request_key (request_key) ) ENGINE=InnoDB; INSERT INTO autoinc_gap(request_key) VALUES ('req-A'), ('req-B'); INSERT IGNORE INTO autoinc_gap(request_key) VALUES ('req-B'), ('req-C'); SELECT id, request_key FROM autoinc_gap ORDER BY id; SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'autoinc_gap'; DROP TABLE autoinc_gap; ``` 실행 결과(MySQL 8.0.x): ```text mysql> DROP TABLE IF EXISTS autoinc_gap; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE autoinc_gap ( -> id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, -> request_key VARCHAR(30) NOT NULL, -> PRIMARY KEY (id), -> UNIQUE KEY uq_request_key (request_key) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO autoinc_gap(request_key) -> VALUES ('req-A'), ('req-B'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> INSERT IGNORE INTO autoinc_gap(request_key) -> VALUES ('req-B'), ('req-C'); Query OK, 1 row affected, 1 warning (0.00 sec) Records: 2 Duplicates: 1 Warnings: 1 mysql> SELECT id, request_key -> FROM autoinc_gap -> ORDER BY id; +----+-------------+ | id | request_key | +----+-------------+ | 1 | req-A | | 2 | req-B | | 3 | req-C | +----+-------------+ 3 rows in set (0.00 sec) mysql> SELECT AUTO_INCREMENT -> FROM information_schema.TABLES -> WHERE TABLE_SCHEMA = DATABASE() -> AND TABLE_NAME = 'autoinc_gap'; +----------------+ | AUTO_INCREMENT | +----------------+ | 5 | +----------------+ 1 row in set (0.01 sec) mysql> DROP TABLE autoinc_gap; Query OK, 0 rows affected (0.00 sec) ``` 두 번째 문장은 두 후보 행을 처리하지만 `req-B`는 unique key 충돌로 저장되지 않는다. 그 과정에서 예약된 자동 증가 값은 다시 채워 넣지 않는다. 결과적으로 저장된 행 수와 다음 자동 증가 값 사이에 간격이 생길 수 있다. 정확한 간격의 크기를 비즈니스 로직에 사용해서도 안 된다. 롤백도 같은 관점으로 해석해야 한다. 한 트랜잭션이 ID를 예약한 뒤 롤백하면 레코드는 사라지지만 번호 소비 흔적은 남을 수 있다. 이는 자동 증가 카운터를 트랜잭션 롤백과 강하게 결합할 때 발생할 전역 경합을 피하기 위한 설계 선택이다. ## 8. Bulk insert가 동시성에 미치는 영향 `INSERT ... SELECT`는 결과 행 수를 실행 전에 확정하기 어려운 bulk insert이다. 다음 예제는 단일 세션에서 문법과 결과만 검증한다. 잠금 모드별 대기 시간과 값 interleaving은 동시에 실행되는 별도 세션이 필요하므로 이 예제의 검증 범위에 포함하지 않는다. ```sql DROP TABLE IF EXISTS autoinc_target; DROP TABLE IF EXISTS autoinc_source; CREATE TABLE autoinc_source ( source_id INT NOT NULL PRIMARY KEY, payload VARCHAR(30) NOT NULL ) ENGINE=InnoDB; CREATE TABLE autoinc_target ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, source_id INT NOT NULL, payload VARCHAR(30) NOT NULL, PRIMARY KEY (id), KEY ix_source_id (source_id) ) ENGINE=InnoDB; INSERT INTO autoinc_source(source_id, payload) VALUES (10, 'ten'), (20, 'twenty'), (30, 'thirty'); INSERT INTO autoinc_target(source_id, payload) SELECT source_id, payload FROM autoinc_source ORDER BY source_id; SELECT id, source_id, payload FROM autoinc_target ORDER BY id; DROP TABLE autoinc_target; DROP TABLE autoinc_source; ``` 실행 결과(MySQL 8.0.x): ```text mysql> DROP TABLE IF EXISTS autoinc_target; Query OK, 0 rows affected (0.00 sec) mysql> DROP TABLE IF EXISTS autoinc_source; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE autoinc_source ( -> source_id INT NOT NULL PRIMARY KEY, -> payload VARCHAR(30) NOT NULL -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE autoinc_target ( -> id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, -> source_id INT NOT NULL, -> payload VARCHAR(30) NOT NULL, -> PRIMARY KEY (id), -> KEY ix_source_id (source_id) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO autoinc_source(source_id, payload) -> VALUES (10, 'ten'), (20, 'twenty'), (30, 'thirty'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> INSERT INTO autoinc_target(source_id, payload) -> SELECT source_id, payload -> FROM autoinc_source -> ORDER BY source_id; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT id, source_id, payload -> FROM autoinc_target -> ORDER BY id; +----+-----------+---------+ | id | source_id | payload | +----+-----------+---------+ | 1 | 10 | ten | | 2 | 20 | twenty | | 3 | 30 | thirty | +----+-----------+---------+ 3 rows in set (0.00 sec) mysql> DROP TABLE autoinc_target; Query OK, 0 rows affected (0.00 sec) mysql> DROP TABLE autoinc_source; Query OK, 0 rows affected (0.00 sec) ``` 모드 0에서는 모든 자동 증가 삽입이 문장 단위로 직렬화된다. 모드 1에서는 이 bulk insert가 `AUTO-INC` 잠금을 오래 보유할 수 있다. 모드 2에서는 다른 삽입이 중간에 값을 할당받을 수 있으므로 대상 행의 ID가 연속 구간이라는 가정이 약해진다. 대량 적재 작업이 OLTP 테이블을 직접 대상으로 한다면 모드만 바꾸기보다 staging table, 적재 배치 크기, 실행 시간대, 복제 지연, redo 생성량까지 함께 설계해야 한다. ## 9. 대기와 병목을 어떻게 진단할 것인가 `AUTO-INC` 경합은 일반적인 레코드 잠금 대기와 항상 같은 형태로 보이지 않는다. `performance_schema.data_locks`에 특정 행 잠금이 없다는 이유로 자동 증가 직렬화가 없다고 결론 내리면 안 된다. 다음 순서로 증거를 모으는 편이 안전하다. 1. 문제 시점의 `innodb_autoinc_lock_mode`와 `binlog_format`을 확인한다. 2. 느린 문장 또는 Performance Schema statement 이벤트에서 대상 테이블의 삽입 문장을 분류한다. 3. `INSERT ... SELECT`, `LOAD DATA`, 대형 다중 행 삽입의 실행 시간과 동시성을 확인한다. 4. `SHOW ENGINE INNODB STATUS`의 세마포어·트랜잭션 정보와 Performance Schema wait 이벤트를 함께 본다. 5. 애플리케이션 대기 시간, 초당 삽입 행 수, redo 처리량, CPU 사용률을 같은 시간축으로 비교한다. 운영 환경에서 현재 실행 중인 삽입을 좁혀 볼 때는 다음과 같은 쿼리를 사용할 수 있다. 이 쿼리는 시점에 따라 결과가 없을 수 있으며, statement instrumentation이 활성화되어 있어야 한다. ```sql SELECT t.PROCESSLIST_ID, t.PROCESSLIST_USER, t.PROCESSLIST_TIME, LEFT(e.SQL_TEXT, 120) AS sql_text FROM performance_schema.events_statements_current AS e JOIN performance_schema.threads AS t ON t.THREAD_ID = e.THREAD_ID WHERE e.SQL_TEXT IS NOT NULL AND (e.SQL_TEXT LIKE 'INSERT %' OR e.SQL_TEXT LIKE 'REPLACE %' OR e.SQL_TEXT LIKE 'LOAD DATA %') ORDER BY t.PROCESSLIST_TIME DESC LIMIT 20; ``` 실행 결과(MySQL 8.0.x): ```text mysql> SELECT t.PROCESSLIST_ID, -> t.PROCESSLIST_USER, -> t.PROCESSLIST_TIME, -> LEFT(e.SQL_TEXT, 120) AS sql_text -> FROM performance_schema.events_statements_current AS e -> JOIN performance_schema.threads AS t -> ON t.THREAD_ID = e.THREAD_ID -> WHERE e.SQL_TEXT IS NOT NULL -> AND (e.SQL_TEXT LIKE 'INSERT %' -> OR e.SQL_TEXT LIKE 'REPLACE %' -> OR e.SQL_TEXT LIKE 'LOAD DATA %') -> ORDER BY t.PROCESSLIST_TIME DESC -> LIMIT 20; Empty set (0.00 sec) ``` `Empty set`은 진단 객체가 없다는 뜻이 아니라 쿼리 실행 순간 조건에 맞는 활성 문장이 없다는 뜻일 수 있다. 장시간 bulk insert가 문제라면 일회성 조회보다 statement history, slow log, 애플리케이션 trace를 함께 사용해야 한다. ## 10. Statement-based replication과의 관계 모드 2에서 두 bulk insert가 동시에 실행되면 원본 서버에서 값 할당 순서가 실행 타이밍에 따라 달라질 수 있다. statement-based replication은 SQL 문장을 복제 서버에서 다시 실행하므로 원본과 같은 interleaving을 재현한다고 보장하기 어렵다. 이 때문에 모드 2와 statement-based logging의 조합에는 주의가 필요하다. 반면 row-based replication은 원본에서 확정된 행 값을 기록하므로 자동 증가 값의 실행 순서를 복제 서버가 다시 추론할 필요가 없다. 하지만 row-based라고 해서 모든 문제가 사라지는 것은 아니다. 애플리케이션이 ID 크기로 커밋 순서를 추정하거나, 여러 writer가 각자 ID 범위를 생성하거나, CDC 소비자가 ID 연속성을 기대하면 별도의 설계 문제가 남는다. `binlog_format=MIXED`도 이름만 보고 안전하다고 가정하지 말고, 해당 문장이 실제로 어떤 형식으로 기록되는지와 경고를 확인해야 한다. 복제 형식 변경은 자동 증가 값뿐 아니라 binary log 용량, 네트워크, replica 적용 특성, point-in-time recovery 절차에 영향을 주므로 독립적인 변경 관리가 필요하다. ## 11. Aurora MySQL에서의 운영 해석 Aurora MySQL도 MySQL 호환 계층에서 자동 증가 열과 `innodb_autoinc_lock_mode`를 다루지만, 실제 적용 가능 값·기본값·변경 적용 방식은 Aurora MySQL 엔진 버전과 DB cluster parameter group에서 확인해야 한다. Community MySQL의 기본값을 근거로 운영 클러스터의 값을 추정해서는 안 된다. Aurora에서는 다음 차이를 함께 고려한다. - 쓰기는 writer 인스턴스에서 수행되므로 자동 증가 경합 분석의 중심도 writer이다. - failover 후 새 writer에서도 키 유일성은 유지되어야 하지만, ID가 시간순·커밋순으로 빈틈없이 이어진다는 보장은 없다. - 파라미터가 static으로 분류되면 변경에 재부팅 또는 failover 계획이 필요할 수 있다. - Performance Insights와 Enhanced Monitoring은 SQL 부하와 대기 시간 파악에 유용하지만, 자동 증가 모드 자체의 의미를 대신 설명해 주지는 않는다. - 다중 리전 쓰기, 외부 CDC, 데이터 병합이 있으면 단일 테이블 자동 증가 값만으로 전역 순서를 표현할 수 없다. 관리형 서비스에서는 설정 변경보다 먼저 현재 writer의 파라미터 적용 상태, pending-reboot 여부, 복제·CDC 소비자의 요구를 확인해야 한다. ## 12. 흔한 오해와 실패 모드 ### 12.1 ID가 크면 더 늦게 커밋되었다 값 예약 시점과 커밋 시점은 다르다. 먼저 큰 구간을 예약한 트랜잭션이 늦게 커밋될 수 있고, 작은 ID를 가진 트랜잭션이 롤백될 수도 있다. 시간순 정렬에는 명시적 생성 시각과 필요한 동률 해소 기준을 사용해야 한다. ### 12.2 롤백하면 번호가 돌아온다 자동 증가 값은 일반적으로 재사용되지 않는다. 재사용을 기대하면 전역 동기화와 장애 복구가 복잡해진다. 번호 간격은 정상적인 운영 부산물로 받아들여야 한다. ### 12.3 모드 2면 모든 삽입이 항상 빨라진다 자동 증가 직렬화가 병목일 때만 직접적인 개선이 있다. secondary index 유지, foreign key 검사, redo flush, 페이지 분할, hot page, 스토리지 지연이 병목이면 모드 변경 효과는 제한적이다. 변경 전후를 같은 부하와 지표로 비교해야 한다. ### 12.4 모드 1이면 모든 다중 행 삽입이 연속 번호를 받는다 simple insert와 bulk insert를 구분해야 한다. mixed-mode, 중복 키 처리, 트리거, 실패한 행은 번호 소비를 복잡하게 만든다. “연속”을 업무 계약으로 승격해서는 안 된다. ### 12.5 `AUTO_INCREMENT` 값을 인위적으로 낮추면 빈 번호를 재사용할 수 있다 기존 최대값, 동시 삽입, 복제 상태를 무시한 카운터 조정은 충돌과 예측 불가능한 할당을 만들 수 있다. 용량 문제라면 번호 재사용보다 열 자료형의 상한, 증가 속도, 보존 정책을 점검해야 한다. ## 13. 변경 의사결정 체크리스트 ### 현재 상태 확인 - [ ] MySQL 또는 Aurora MySQL의 정확한 엔진 버전을 기록했는가. - [ ] writer의 `innodb_autoinc_lock_mode`와 `binlog_format`을 실제로 조회했는가. - [ ] simple, bulk, mixed-mode insert의 비율과 상위 SQL을 분류했는가. - [ ] `LOAD DATA`와 `INSERT ... SELECT`의 최대 실행 시간을 확인했는가. - [ ] 트리거, duplicate-key 처리, generated keys 사용 방식을 확인했는가. ### 안전성 검토 - [ ] statement-based replication 또는 statement 재실행 기반 CDC가 남아 있지 않은가. - [ ] 애플리케이션이 ID 연속성이나 ID 기반 커밋 순서를 가정하지 않는가. - [ ] 롤백·중복·장애 후 번호 간격을 정상으로 처리하는가. - [ ] 여러 writer 또는 데이터 병합 환경에서 키 충돌 방지 전략이 있는가. - [ ] Aurora parameter group의 적용 방식과 pending-reboot 영향을 확인했는가. ### 변경과 검증 - [ ] 운영과 같은 문장 유형을 포함한 동시성 부하 시험을 수행했는가. - [ ] 처리량뿐 아니라 p95/p99 삽입 지연과 오류율을 비교했는가. - [ ] replica lag, binary log/CDC, redo 및 CPU 지표를 함께 비교했는가. - [ ] failover와 재시작 후 자동 증가 값의 유일성을 검증했는가. - [ ] 문제가 생겼을 때 되돌릴 파라미터와 재시작 절차가 준비되어 있는가. ## 14. 결론 `innodb_autoinc_lock_mode`는 번호 모양을 꾸미는 설정이 아니라, 자동 증가 값 할당 과정에서 문장 간 직렬화를 어디까지 허용할지 결정하는 동시성 설정이다. 모드 0은 예측 가능한 문장 단위 직렬화, 모드 1은 simple insert 최적화와 bulk insert 호환성의 절충, 모드 2는 row-based replication을 전제로 한 높은 동시성에 초점이 있다. 어떤 모드를 선택하더라도 `AUTO_INCREMENT`가 보장하는 핵심은 유일한 식별자 생성이지 무결번성이나 커밋 순서가 아니다. 운영 변경은 복제 형식, 문장 유형, 대량 적재, Aurora 파라미터 적용 방식, 애플리케이션의 ID 해석을 함께 검토해야 한다. 다음 단계에서는 자동 증가 값 자체보다 더 넓은 범위에서 insert hot spot, clustered index의 우측 끝 페이지 경합, 순차 키와 무작위 키의 쓰기 비용을 연결해 살펴볼 수 있다.