MySql의 last_insert_id() 함수는 동시성 문제로부터 안전할까?

배경

프로젝트에서 쿼리 개선을 위해 JdbcTemplate를 통해 bulk insert를 구현하게 되었다.

우리는 MySql의 기본키 전략 중 Identity를 사용하고 있어 JPA의 saveAll 메서드로는 bulk insert가 되지 않기 때문이다.

 

그런데 bulk insert 해야 할 엔티티가 하나가 아니고, 해당 엔티티의 Id를 FK로 가지는 엔티티도 bulk insert해주어야 하는 상황이었다.

따라서 첫 번째 bulk insert의 결과인 Id들을 얻어와야 했고, 그 방법으로 last_insert_id() 함수를 사용할 수 있었다.

 

insert를 한 뒤, Id를 조회해 계산하는 아이디어 자체는 이전에도 생각했다.

하지만 가장 최근 Id를 조회한다면, 의존하는 엔티티의 저장을 마치기 전까지는 테이블 락을 걸어두어야 할 것이다.

그 사이에 다른 트랜잭션에서 데이터를 삽입할 수도 있기 때문이다.

그렇게 하나의 쓰기 작업을 위해 테이블 락을 거는 것은 꽤나 큰 손해일 것이라 생각했다.

 

대신, last_insert_id() 함수를 사용하면 그런 문제를 신경쓸 필요가 없었다.

 

이 함수는 최근의 Id나 auto_increment 값을 조회하는 것과 어떻게 다른지,

어떻게 이 함수를 활용해 동시성 문제에 대한 걱정 없이 bulk insert를 구현할 수 있었는지 정리해보겠다.

 

해당 작업에 대한 상세한 내용은 아래 PR에서 확인할 수 있다.

https://github.com/woowacourse-teams/2023-map-befine/pull/617

last_insert_id()

공식 문서의 설명에 따르면, 이 함수의 반환 값은

“해당 클라이언트에 의해 실행된 가장 최근의 INSERT문에 의해 생성된 첫 번째 AUTO_INCREMENT 값”이다.

이 함수는 Connection에 따라 다르므로, 반환 값은 삽입을 수행하는 다른 Connection의 영향을 받지 않는다.

 

You can retrieve the most recent automatically generated AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. These functions are connection-specific, so their return values are not affected by another connection which is also performing inserts.

 

해당 클라이언트에 의해 실행된 가장 최근의 INSERT 문 이라는 말에 주목해보자.

 

Connection마다 독립적으로 관리

각 Connection에서 실행된, 가장 최근의 INSERT문에 의해 만들어진 AI 값을 저장해둔 뒤 반환해준다.

그러니까 AUTO_INCREMENT 의 현재 값을 찾아주는 거랑은 다르다.

클라이언트마다 가지고 있는 일종의 캐시 데이터처럼 생각하면 된다.

 

그래서 아래와 같은 걱정을 할 필요가 없다!

• 서로 다른 트랜잭션에서 동시에 함수를 호출해서 같은 PK를 사용하면 어떡하지?
• 삽입 후 Id를 조회하기 전에, 다른 트랜잭션에서 데이터 삽입을 해 Id가 더 증가하면 어떡하지?

때문에, 이 함수를 사용해서 앞서 생각했던 방법으로 연관 관계를 가진 엔티티의 FK를 설정해 삽입할 수 있었다.

 

💡 이 때 주의할 점은!
해당 Connection에서 INSERT가 실행된 적이 없다면, 0을 반환한다.
기존의 AI 상태를 조회하기 위한 함수라고 생각하고 사용했다가는, 문제가 생길 수 있다.

 

확인해보기

실제로 IntelliJ에 두 개의 콘솔을 켜놓고 서로 다른 클라이언트에서 트랜잭션을 만들어 확인해보았다.

 

 

트랜잭션도 만들어 진행한 이유는, Auto_Increment의 값은 상관없이 공유되어 증가한다는 점,

또 그럼에도 동시성에 문제가 없음을 함께 확인하기 위함이다.

 

 

다중 행 삽입 시 꼭 알아야 하는 것

또 한가지 특이한 점은, 여러 행을 동시에 삽입하는 bulk insert 쿼리를 실행할 경우 마지막으로 획득한 AI가 아닌 첫 AI를 반환한다는 것이다.

레플리카에 올바르게 재생성하기 위함이라고 하는데, 레플리카를 안 해봐서 이 이유는 아직 잘 모르겠다.

For a multiple-row insert, LAST_INSERT_ID() and mysql_insert_id() actually return the AUTO_INCREMENT key from the first of the inserted rows. This enables multiple-row inserts to be reproduced correctly on other servers in a replication setup.

 

 

참고로, Auto_Increment에서는 삽입되는 순간에만 사용되는 전용 락(Auto_Increment 락)이 있어서

동시에 삽입될 경우 PK 정합성에 대한 우려는 하지 않아도 될 듯하다.

 

동시성 문제로부터 안전한가?

추가로, 글의 제목에 답해보며 마무리하겠다.

 

동시성 문제란 뭘까? 동시에 들어온 여러 요청을 처리하는 방식에 따라 발생하는 문제들을 뜻한다고 생각한다.

서로 다른 요청들은 다른 Connection을 가질 것이다.

그리고 이 함수는 Connection 별로 Id 값을 관리한다.

그렇기 때문에, Auto_Increment 락만 잘 작동한다면 문제가 없을 것이라 생각한다.

적어도 지금 내가 생각하기로는 그렇다.

 

하지만, "Connection 별로 Id 값을 관리한다"라는 뜻이 Auto_Increment의 증가까지도 막아준다고 착각한다면,

즉 이 함수를 잘못 사용한다면 생각하지 못한 동시성 문제를 만날 수는 있겠다는 생각은 든다.

 

 

참고 자료

https://helloworld.kurly.com/blog/bulk-performance-tuning/

https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id