[MySQL] Transaction rollback 시 Auto Increment 이슈

Intro

개발중 @Transaction을 사용하다가 insert 실패 시 rollback되는 경우가 있었다. 다음 insert문을 실행했을 때, 순차적인 값이 아닌 값이 나타났다. (1,2,3,8,13 .. 이런식으로)

원인

Transaction으로 인해 로직 자체는 rollback되었지만, 이미 테이블에 적용된 Auto Increment 값은 증가한 뒤였다. auto_increment는 transaction이 적용되지 않는다. 따라서, rollback이 적용되지 않는 것이다.

내가 겪은 원인 외에도, insert ignore int 문을 사용할 때 statement문 오류로 인한 이슈, 동시에 다수의 쿼리가 insert될 때 auto increment 필드 값이 증가될 수 있다.

어떻게 해결할까?

auto_increment 값을 신뢰해서는 안된다. 만약 auto_increment를 중요하게 쓴다면 innodb_autoinc_lock_mode을 잘 알아야한다.

innodb_autoinc_lock_mode

1. 여러 트랜잭션이 insert 시도 - 먼저 들어온 트랜잭션A, 나중에 들어온 트랜잭션B
2. 트랜잭션A는 먼저 들어와 insert 진행
3. 트랜잭션B는 auto_increment를 잠궈 대기
4. 트랜잭션A의 연속적인 pk 값을 받아온 뒤, 트랜잭션B 진행

여기에도 또 단계가 있다. 락이 언제까지 유지되는지.

traditional mode - 0

• auto_increment 컬럼이 있는 테이블에 insert하는 모든 문장에 테이블 수준의 auto inc 잠금 수해
• insert 구문 수행까지 락 유지

  consecutive mode - 1

• insert되는 레코드 건수 예측 가능하면 lock을 걸지 않고 뮤텍스로 처리(아주 짧은 시간 잠금, auto_inc 가져오는 순간 해제)
• 예측이 불가능할 경우 auto lock을 건다

  interleaved mode - 2

• auto increment lock을 걸지 않고, 항상 뮤텍스를 이용한다.

여기서 잠깐!

뮤텍스 : 한 개 리소스에 대하여 Lock/Unlock 매커니즘을 사용하는 기술 (ex. 한칸짜리 전용 열쇠 화장실)

세마포어 : 프로세스들이 여러개의 리소스에 접근할 수 있는 카운터 수를 표현하는 기술 (ex. 여러 칸 있는 화장실)

InnoDB에는 여러 락이 있는데, 일단 이 부분만 보았지만 다른 여러가지 Lock도 공부해봐야겠다.

참고
https://suhwan.dev/2019/06/09/transaction-isolation-level-and-lock/
https://knight76.tistory.com/entry/auto-increment-%EC%9D%B4%EC%8A%88
https://rosebud90.tistory.com/entry/InoDB-%EC%9E%A0%EA%B8%88-2-record-lock-gap-lock-Next-Key-Lock-Insert-Intention-Lock-Auto-Inc-Lock