[MySQL] auto_increment locking 설정

대용량 데이터 Upsert(INSERT ~ ON DUPLICATE KEY UPDATE ~) Test 도중 auto_increment 컬럼이 생각한 것 처럼 순차적으로 증가하지 않고 중간에 건너뛰는 숫자들이 생겨났다.

⇒ 중복되는 row는 INSERT에 실패하고 UPDATE로 수행된다. 하지만 INSERT는 실패하였지만 auto_increment만 증가된 것을 확인할 수 있었다.

이는 innodb_autoinc_lock_mode를 0으로 설정해주면 해결 가능하다.

InnoDB AUTO_INCREMENT Lock Modes

innodb_autoinc_lock_mode 구성 매개 변수는 세 가지 가능한 설정이 있음

innodb_autoinc_lock_mode = 0 (traditional lock mode)

• 모든 INSERT-like 구문에 대해 Auto-increment lock이 테이블 레벨로 동작
• 트랜잭션이 끝날때까지 적용되는 것이 아닌 해당 구문의 실행시까지만 유지되는 lock

innodb_autoinc_lock_mode = 1 (consecutive lock mode)

• simple insert 구문에 대해서는 테이블 레벨 Auto-increment lock 회피 가능
• AUTO_INCREMENT values의 할당 과정에서만 mutex를 이용해 동시 접근을 제어하기 때문에 concurrency를 높일 수 있음 (구문이 실행이 끝날때까지 table-level lock을 적용하는 것 보다 훨씬 효율적)
• insert시 생성되는 AUTO_INCREMENT 값들은 mixed-mode insert의 예외 경우를 제외하고는 traditional lock mode를 사용했을때와 결과값이 항상 동일
• mixed-mode insert인 경우 InnoDB는 실제 인서트 되어야 하는 rows 수 보다 더 큰 AUTO_INCREMENT 값을 할당. 지정된 값을 제외하고 자동으로 할당된 값의 순차적인 증가는 보장됨

innodb_autoinc_lock_mode = 2 (interleaved lock mode)

• INSERT-like 구문에 대해서 테이블 레벨 Auto-increment lock을 사용하지 않음
• concurrency가 가장 높지만 SQL 바이너리 로그 replay를 사용한 복구가 힘듦
• AUTO_INCREMENT 값의 의 유니크성, 단조 증가성(monotonic increase)은 보장되지만 동일한 쿼리라도 실행 순서에 따라 매번 rows들이 가지는 AUTO_INCREMENT 값들이 달라질 수 있음
• AUTO_INCREMENT 값의 Gap 존재
  • “simple inserts”가 수행될때는 할당된 AUTO_INCREMENT 값들에 gap이 존재하지 않는다.
  • “bulk inserts”가 수행될때는 gap이 존재 할 수있다.

innodb_autoinc_lock_mode 설정하기

• mysql 접속

$ mysql -u root -p

• innodb_autoinc_lock_mode 설정

mysql> SET global innodb_autoinc_lock_mode=0;

• 터미널에서 위와 같이 입력 시 아래와 같은 에러 발생 가능

ERROR 1238 (HY000): Variable 'innodb_autoinc_lock_mode' is a read only variable

• /usr/local/etc/mysql/mysql.conf.d/mysql.cnf 파일을 열어서 innodb_autoinc_lock_mode = 0 을 추가해주면 됨
• mac finder에서 /usr 폴더에 접근하려면 Command + Shift + G 를 눌러 대화상자를 이용해 경로 지정 접근 가능