MySQL ‐ AUTO_INCREMENT_LOCK - thought-corner/Backend-PlayGround GitHub Wiki

MySQL - AUTO_INCREMENT_LOCK

AUTO_INCREMENT_LOCK이란?

• 테이블을 설계할 때 기본키(PK)로 AUTO_INCREMENT를 많이 사용한다.
• 데이터베이스는 각 트랜잭션에게 절대 중복되지 않는 순차적인 ID를 부여해야 하는데 A와 B가 동시에 접근했을 때 둘 다 동일한 ID를 가져가면 PK 중복 에러가 발생한다.
• 이를 방지하기 위해 MySQL(InnoDB)은 새로운 ID 번호를 발급하는 아주 짧은 시간 동안 특수한 형태의 락을 거는데 이것이 바로 AUTO_INCREMENT_LOCK이다.
• 이 락을 얼마나 빡빡하게, 혹은 느슨하게 걸 것인지 결정하는 설정 값이 바로 innodb_autoinc_lock_mode이다.

락 모드(Lock Mode)의 3가지 유형

Mode 0: Traditional

• INSERT 쿼리가 시작해서 끝날 때까지 테이블 전체에 락을 유지한다.
• 트랜잭션 A가 100건을 넣는 동안 트랜잭션 B는 단 1건도 넣지 못하고 무조건 대기해야 한다.
• 성능이 매우 나쁘기 때문에 현재는 거의 쓰이지 않는다.

Mode 1: Consecutive

• 단순 삽입(Simple Insert)과 같이 삽입할 행 개수를 미리 알 수 있는 경우 무거운 테이블 락 대신 가벼운 뮤텍스 락을 사용한다. ID 개수만큼 메모리에서 번호만 빼가고 락을 풀기 때문에 매우 빠르다.
• 대량 삽입(Bulk Insert)과 같이 몇 건이 들어갈지 DB가 쿼리를 실행해봐야 아는 경우에는 테이블 전체 락을 건다.

Mode 2: Interleaved

• 개수를 알든 모르든, 절대 테이블 락을 걸지 않는다. 항상 아주 가벼운 뮤텍스 락만 사용하여 ID 번호만 할당하고 즉시 락을 해제한다.
• 동시성 처리 성능이 극도로 뛰어나다. 수많은 쓰레드가 동시에 INSERT를 때려도 병목이 거의 없다.
• 여러 트랜잭션이 동시에 실행되면 ID 번호가 연속적이지 않고 섞일 수 있으며 빈 공간이 발생할 수 있다.

-- 현재 AUTO_INCREMENT 락 모드 확인
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';