AUTO_INCREMENT와 Lock

데이터베이스에서 AUTO_INCREMENT는 유니크한 기본 키를 자동으로 생성하는 데 매우 유용한 기능이다. 하지만 다중 트랜잭션 환경이나 대량 삽입 작업에서 AUTO_INCREMENT 잠금(AUTO_INCREMENT Lock)이 성능 병목의 원인이 될 수 있다.

 

AUTO_INCREMENT의 동작 원리

---

기본 개념

• AUTO_INCREMENT는 테이블에 레코드를 삽입할 때, 고유한 값을 자동으로 생성해주는 기능이다.

• MySQL 기준으로, 기본적으로 AUTO_INCREMENT는 현재 테이블의 최대 값에 1을 더한 값을 새로운 기본 키로 사용한다.

 

이러한 AUTO_INCREMENT의 동작 원리는 다음과 같다.

 

1. AUTO_INCREMENT 값 할당 시점

• MySQL에서 AUTO_INCREMENT는 레코드 삽입 시점에 값이 할당된다.
• INSERT 문이 실행되면, 즉시 다음 AUTO_INCREMENT 값을 할당한다.
• 트랜잭션의 커밋(Commit) 또는 롤백(Rollback) 여부와 관계없이 값은 사용된 것으로 간주된다.

예제

START TRANSACTION;
INSERT INTO orders (product_id, quantity) VALUES (1, 10); -- AUTO_INCREMENT 값 1 할당
ROLLBACK; -- 값 1은 사용된 것으로 간주되어 다음 삽입은 2부터 시작

 

2. 트랜잭션 롤백 시 AUTO_INCREMENT 값

• 트랜잭션이 롤백되더라도 할당된 AUTO_INCREMENT 값은 복구되지 않는다.
• 이는 AUTO_INCREMENT 값이 다른 트랜잭션과의 충돌을 방지하고 고유성을 유지하기 위함이다.

결과

• 중간에 롤백이 발생하면 값의 연속성이 깨질 수 있다.
• ex) 값이 1, 2, 5, 6처럼 중간이 비는 상황 발생

 

3. AUTO_INCREMENT Lock과 트랜잭션

• AUTO_INCREMENT는 다중 트랜잭션 환경에서 값의 고유성을 보장하기 위해 잠금(Lock)을 사용한다.
• InnoDB에서는 AUTO_INCREMENT 잠금(AUTO_INCREMENT Lock)을 통해 동시 트랜잭션이 같은 값을 할당받지 않도록 관리한다.
• 잠금(Lock)은 트랜잭션이 종료되면 해제된다.

 

---

AUTO_INCREMENT와 트랜잭션 관련 문제

---

1. AUTO_INCREMENT 값 연속성 문제

• 문제: 트랜잭션이 롤백되면 할당된 AUTO_INCREMENT 값이 사용되지 않아 값의 연속성이 보장되지 않음.
• 동작 원리: MySQL은 트랜잭션 실행 시 AUTO_INCREMENT 값을 즉시 할당하며, 트랜잭션의 커밋 여부와 상관없이 할당된 값은 “사용된 것으로 간주”.

예제

START TRANSACTION;
INSERT INTO orders (product_id, quantity) VALUES (1, 10); -- 값 1 할당
ROLLBACK;

START TRANSACTION;
INSERT INTO orders (product_id, quantity) VALUES (2, 5); -- 값 2 할당
COMMIT;

 

결과

• AUTO_INCREMENT 값이 연속적(1, 2, 3, …)이지 않을 수 있음.
• 중간 값이 비는 상황 발생(예: 1, 3, 4, ...).

 

2. 성능 병목

• 문제: AUTO_INCREMENT Lock은 다중 트랜잭션 환경에서 성능 병목을 초래할 수 있음.
• 원인: 하나의 트랜잭션이 AUTO_INCREMENT 값을 할당받고 커밋/롤백되지 않으면, 다른 트랜잭션은 대기 상태에 빠짐.

사례

• 다중 트랜잭션 경쟁
  • 트랜잭션 A가 AUTO_INCREMENT 값을 할당받아 커밋 지연.
  • 트랜잭션 B는 트랜잭션 A의 잠금 해제(Lock Release)까지 대기.
  • 동시 삽입 작업이 많아질수록 병목 현상이 심화.
• 대량 삽입 작업
  • INSERT INTO ... SELECT 또는 LOAD DATA INFILE 같은 작업은 한꺼번에 많은 AUTO_INCREMENT 값을 소비하며, 다른 트랜잭션이 대기 상태로 전환.

예제

-- 트랜잭션 A
START TRANSACTION;
INSERT INTO orders (product_id, quantity) VALUES (1, 10); -- AUTO_INCREMENT 값 할당
-- 커밋 지연 중...

-- 트랜잭션 B
START TRANSACTION;
INSERT INTO orders (product_id, quantity) VALUES (2, 5); -- 대기 상태

 

 

3. 대량 삽입 작업에서의 비효율성

• • 문제: 대량 데이터를 삽입할 때 AUTO_INCREMENT 값이 빠르게 소비되며, 다른 트랜잭션의 삽입 작업이 지연될 수 있음.

사례

: INSERT INTO ... SELECT나 LOAD DATA INFILE 같은 대량 데이터 삽입이 실행되는 동안, 다른 트랜잭션의 삽입 작업은 잠금 해제 대기 상태.

 

예제

-- 대량 삽입 작업
INSERT INTO orders (product_id, quantity)
SELECT product_id, quantity
FROM temp_orders;

 

• 위 작업이 완료되기 전까지 다른 삽입 작업이 지연되어 애플리케이션 전체 성능이 저하될 수 있음.

 

---

AUTO_INCREMENT Lock 문제 해결 방법

---

InnoDB AUTO_INCREMENT Lock 모드 변경

---

MySQL의 InnoDB 스토리지 엔진에서 innodb_autoinc_lock_mode는 AUTO_INCREMENT 값이 할당되는 방식과 관련된 잠금(Lock) 메커니즘을 설정한다. 이 설정은 다중 트랜잭션 환경이나 대량 데이터 삽입에서 AUTO_INCREMENT를 효율적으로 관리하는 데 중요한 역할을 한다.

 

innodb_autoinc_lock_mode란?

• innodb_autoinc_lock_mode는 InnoDB에서 AUTO_INCREMENT 값을 할당할 때 사용되는 잠금 전략을 정의한다.

주요 목표

1. AUTO_INCREMENT 값의 고유성 유지
2. 동시성(Concurrency)을 최적화하여 성능 향상

 

 innodb_autoinc_lock_mode의 3가지 모드

1. 0 (전통 모드, “Traditional”)

특징

1. 테이블 수준 잠금(Table-Level Lock)
   • 모든 INSERT 작업에서 테이블 전체 잠금을 사용해 AUTO_INCREMENT 값의 고유성과 영속성 보장
2. 연속성 보장
   • 트랜잭션이 롤백되더라도, AUTO_INCREMENT 값의 연속성을 유지하기 위한 안정적인 방식
3. 대량 삽입 작업
   • 대량 삽입 작업(INSERT INTO ... SELECT, LOAD DATA INFILE)에서도 동일한 잠금 방식이 적용되어 동시성이 제한됨

장점

• 값의 고유성과 안정성이 완벽히 보장
• 단순하고 안정적인 방식으로, 예상치 못한 동작 가능성이 낮음

단점

• 모든 삽입 작업에서 테이블 잠금을 사용하기 때문에 동시성이 크게 제한됨
• 다중 트랜잭션 환경이나 대량 삽입 작업에서 성능 저하

사용 사례

• 동시성이 낮고, AUTO_INCREMENT 값의 연속성이 중요한 경우
• 데이터베이스 삽입 작업이 적은 환경

 

2. 1 (연속 모드, “Continuous”, 기본값)

특징

1. 동시성 최적화
   • 간단한 INSERT 문에서는 테이블 수준 잠금을 사용하지 않음
   • AUTO_INCREMENT 값을 내부 카운터(Shared Auto-Increment Counter)를 통해 동적으로 할당
2. 대량 삽입 작업
   • 대량 삽입 작업(ex. INSERT INTO ... SELECT 또는 LOAD DATA INFILE)에서는 테이블 잠금을 적용하여 AUTO_INCREMENT 값을 안전하게 할당
3. 연속성 보장
   • 간단한 INSERT 작업에서 AUTO_INCREMENT 값은 연속적으로 부여됨
   • 단, 롤백 시 중간 값은 비어 있을 수 있음

장점

• 간단한 삽입 작업에서 잠금을 최소화하여 삽입 성능과 동시성 최적화
• 일반적인 애플리케이션에서 성능과 안전성의 균형 제공

단점

• 대량 삽입 작업에서는 여전히 테이블 잠금이 발생하므로 동시성 제한 가능
• 트랜잭션 롤백 시 값의 연속성은 깨질 수 있음

사용 사례

• 다중 트랜잭션 환경에서 동시 삽입 작업이 많은 경우
• 일반적인 애플리케이션에서 성능과 안전성을 동시에 필요로 할 때

 

3. 2 (연속 모드, “Interleaved”)

특징

1. 테이블 잠금을 완전히 제거
   • 간단한 삽입(INSERT)과 대량 삽입 모두에서 테이블 수준 잠금을 사용하지 않음
   • 모든 AUTO_INCREMENT 값은 내부 카운터를 통해 동적으로 할당
2. 최고의 동시성 제공
   • 테이블 잠금을 제거하여 삽입 작업 간의 경쟁 상태를 줄이고, 동시 삽입 성능 극대화
3. AUTO_INCREMENT 값의 고유성 보장, 연속성은 보장되지 않음
   • 값의 고유성은 항상 유지되지만, ROLLBACK 또는 장애 발생 시 값의 연속성은 완전히 깨질 수 있음
   • 값 간 간격이 크게 벌어질 가능성 존재

장점

• 최고의 성능과 동시성: 테이블 잠금이 없으므로 당중 트랜잭션 환경에서 삽입 작업의 처리 속도가 가장 빠름
• 간단한 구현: AUTO_INCREMENT 값 할당이 내부 카운터에 의해 처리되므로 병렬 처리 환경에서 효율적

단점

• 연속성 미보장
  • 값이 크게 비거나 중간에 공백이 생길 가능성이 매우 큼
  • 값의 순차성이 중요한 애플리케잇녀에서는 부적합

사용 사례

• 삽입 작업이 매우 빈번하고, AUTO_INCREMENT 값의 연속성이 중요하지 않은 경우
• 대규모 트랜잭션 환경에서 성능과 동시성이 최우선인 애플리케이션

 

특징	모드 0 (전통)	모드 1 (연속)	모드 2 (완전 동시성)
동시성	낮음	중간	높음
테이블 잠금	항상 사용	간단한 INSERT에서는 X	사용하지 않음
대량 삽입 작업	안전하지만 늘미	잠금 사용	매우 빠름
연속성	보장	보장	미보장
권장 사용 환경	낮은 동시성, 연속성 중요	일반적인 환경	높은 동시성 요구, 연속성 불필요

※ 0번 모드는 ROLLBACK 이외에는 연속성이 깨질 일이 거의 없지만, 1번과 2번은 내부 카운터를 사용하면서 성능을 높이는 대신 ROLLBACK 이외에도 연속성이 자주 깨질 수 있다.

 

---

결론

---

 

• AUTO_INCREMENT는 편리한 기능이지만, 트랜잭션과 함께 사용할 때 값 연속성, 성능 병목, 대량 삽입 비효율성 같은 문제가 발생할 수 있다.
• 이러한 문제를 해결하기 위해 적절한 잠금 전략(innodb_autoinc_lock_mode 조정)과 대체 설계(UUID 또는 커스텀 키 사용) 등을 고려해야 한다.