MySQL은 어떻게 ID 값을 순차적으로 넣어주는 것일까? (Feat. Auto Increment Lock)

간단한 URL 단축기 설계 프로젝트를 진행하던 중 아래와 같은 궁금증이 생기게 되었다.

• MySQL에서 ID 값을 AUTO INCREMENT 로 했을 때 동시성 문제는 없나?

• 여러 트랜잭션에서 INSERT를 진행할 때 어떻게 ID 값을 순차적으로 주는 것일까?

• JPA를 통해 SAVE 후 ID 값은 어떻게 받아오는 것일까?

단순히 '데이터베이스에서 해주더라' 보다는 내부적으로 어떻게 동작을 하는지 궁금해졌다.

---

전체적인 흐름

결론부터 말하자면,

• MySQL 내부에서 Auto Increment Lock을 통해 동시성을 제어한다.

• Hiberante는 JDBC의 Statement.getGeneratedKeys() 를 통해 필요한 데이터들을 함께 가져온다.

이러한 방식을 통해 JPA를 사용할 때 ID 값을 Auto Increment로 사용하게 될 경우 데이터베이스가 ID를 관리하기 때문에 별도의 처리가 필요없는 것이다.

중요한 점은, AUTO_INCREMENT Lock은 트랜잭션 유무와 관계없이 Statement가 종료되면 락이 해제된다.

처음부터 하나씩 살펴보자.

Entity (JPA)

@Entity
@Table(name = "url")
@Getter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public class Url {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String shortUrl;

    private String originalUrl;
    
    // ...
}

위와 같은 엔티티가 존재하며 INSERT (JPA에서 SAVE) 작업이 일어날 때 어떻게 아이디 값을 순차적으로 넣어주는 것일까?

데이터 삽입 테스트

동시성 문제가 없는지 한 번 테스트 해보자.

A Transaction

START TRANSACTION;

INSERT INTO url(originalUrl, shortUrl) 
VALUES('http://test.com/1234', '1234');

B Transaction

START TRANSACTION;

INSERT INTO url(originalUrl, shortUrl)
VALUES('http://test.com/5678', '5678');

A, B 트랜잭션 모두 커밋을 하지 않은 상태이다. 각각의 트랜잭션에서 조회를 한다면 ID 값이 어떻게 나오게 될까? (기존에 1개의 데이터가 있는 상태)

• A 트랜잭션이 먼저 수행이 되었으므로, ID = 2

• B 트랜잭션은 이후에 수행되었으므로, ID = 3

여기서 궁금했던 점은 별도의 트랜잭션을 통해서 진행했는데 어떻게, 어디에서 다음 ID 값을 가져오는 것인지 궁금해졌다.

참고로 이 때, 롤백이 일어나게 된다고 하더라도, 다음 INSERT 발생시 마지막에 넣었던 ID + 1 값으로 넣어진다.

어떻게 MySQL은 ID 값을 순차적으로 올리는 걸까?

궁금증을 해결하기 위해서 MySQL 공식문서를 찾아보자.

MySQL :: MySQL 8.4 Reference Manual :: 17.6.1.6 AUTO_INCREMENT Handling in InnoDBdev.mysql.com

innodb_autoinc_lock_mod

InnoDB를 기본으로 사용하고 있는 MySQL에서는 innodb_autoinc_lock_mode 시스템 변수를 통해서 어떤 락 기법을 사용할 것인지 지정할 수 있다.

아래에서 설명될 INSERT 방식은 아래와 같이 분류한다.

• INSERT-like

  • 새로운 행(row)를 생성하는 모든 구문

  • 아래의 3가지 단순, 벌크, 복합-모드 모두 포함

• 단순 INSERT

  • INSERT 수를 사전에 예측할 수 있는 문장을 말함.

  • 단일 행(single-row), 복수 행(multiple-row)의 INSERT, REPLACE가 해당된다.

• 벌크 INSERT

  • INSERT 수를 사전에 알 수 없는 문장을 말함.

  • InnoDB는 각 행이 처리될 때 AUTO_INCREMENT 컬럼의 새로운 값을 하나씩 할당

• 복합-모드 INSERT

  • 단순 INSERT 문장에 AUTO_INCREMENT 값을 지정하는 경우를 말함

innodb_autoinc_lock_mode 종류

• 0: traditional lock mode

• 1: consecutive lock mode ( ~ 5.7 Deafult)

• 2: interleaved lock mode (8.0 Default)

MySQL - innodb_autoinc_lock_mode 조회

Traditional Lock Mode (0)

• 테이블 수준의 락을 사용

• INSERT 문이 완료될 때까지 잠금을 유지

• 동시성이 낮지만, 안전한 방법

Consecutive Lock Mode (1)

• '단순 INSERT'의 경우 테이블 잠금 대신 Mutex 사용

  • 필요한 수의 AUTO INCREMENT 값을 획득하여 할당할 때 까지만 Lock

• '대량 INSERT'의 경우에만 테이블 잠금을 사용

  • 삽입해야할 데이터가 많은 경우 경쟁이 심해질 수 있음

• '복합-모드 INSERT'의 경우 사용자가 값을 명시함

• MySQL 5.7까지 기본 값

Interleaved Lock Mode (2)

• 테이블 수준의 잠금은 사용하지 않음, Mutex만 사용

• 가장 빠르고 확장성이 좋음

• 동시 처리 성능이 가장 높지만, STATEMENT 기반 복제시 안전하지 않음

• AUTO INCREMENT 값에 차이가 생길 수 있다.

• MySQL 8.0부터 기본 값

여담으로, LINE 오픈챗(OpenCat) 서버의 개선 내용에서도 innodb_autoinc_lock_mode=1 으로 인한 병목 지점을 파악하고 락 모드를 2로 변경하여 다음과 같은 결과를 만들기도 하였습니다.

• 기존: 벌크 INSERT로 인한 테이블 락으로 인해 CPU 사용률이 100%에 도달하여 타임아웃 발생

• 이후: 1초에 수천 개의 참여 요청에도 CPU 사용률 10~20% 사이를 유지하며 안정적으로 처리

그렇다면, 어떻게 자동으로 다음 ID 값을 할당해줄까?

InnoDB의 AUTO_INCREMENT 동작 방식을 보자면:

1. 초기화 방식

   1. 기본적으로 .cfg 메타데이터 파일에서 AUTO_INCREMENT 값을 읽는다.

   2. 만약, .cfg 파일이 없는 경우 SELECT MAX(ai_col) 쿼리로 초기화를 한다.

2. 값 변경과 유지

   1. ALTER TABLE로 AUTO_INCREMENT 값을 변경할 때는 현재 최대값을 확인한다.

   2. 현재 최대값보다 작은 값으로는 설정할 수 없다.

   3. 서버가 재시작되어도 설정된 AUTO_INCREMENT 값은 유지된다.

.cfg 파일은 InnoDB 테이블의 메타데이터 정보를 포함하고 있으며, AUTO_INCREMENT 카운터의 현재 최대값도 저장하고 있다.

위와 같은 방식을 통해서 MySQL에서 처리를 한다고 한다.

실제로 보는 방법

use sys;

SELECT *
FROM schema_auto_increment_columns;

Spring Data JPA - Auto Increment

조금 더 나아가서, 스프링에서는 ID 값을 지정해주지 않고 저장을 하는데, 어떻게 다시 값을 가져올 수 있을까? 간단하게 코드를 참고해서 확인해보자.

UrlService.java

@Service
@RequiredArgsConstructor
public class UrlService {
    
    private final Base62Conversion conversion;
    private final UrlRepository urlRepository;
    
    
    @Transactional
    public ShortenResponse shortenUrl(ShortenRequest request) {
        String originalUrl = request.getOriginalUrl();
        
        // DB 조회 후 존재하면 가져오기
        // ...
        
        Url url = Url.builder()
                    .originalUrl(originalUrl)
                    .build();
                    
        // 새로운 ID를 할당받는 과정
        Url savedUrl = urlRepository.save(url); 
        log.info("url ID = {}", savedUrl.getId()); 
        
        // ...
    }
}

Url 엔티티의 ID는 @GeneratedValue(strategy = GenerationType.IDENTITY) 상태입니다.

java.sql.Statement

Hibernate 내부적으로 Statement의 getGeneratedKeys() 메서드를 통해 자동으로 생성된 ID 값 뿐만 아니라 필요한 정보들을 획득해오는 방식을 사용한다.

Hibernate ORM 6.5 - Arbitrary generated values retrieval - In Relation ToIn Relation To