MySQL ‐ AUTO_INCREMENT LOCK - thought-corner/Backend-PlayGround GitHub Wiki

📚 AUTO_INCREMENT LOCK

  • MySQL에서 자동 증가하는 숫자 값을 추출하기 위해 AUTO_INCREMENT라는 칼럼 속성을 제공한다.
  • AUTO_INCREMENT 칼럼이 사용된 테이블에 동시에 여러 레코드가 INSERT되는 경우, 저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가진다.
  • InnoDB 스토리지 엔진에서는 이를 위해 내부적으로 AUTO_INCREMENT락이라고 하는 테이블 수준의 잠금을 사용한다.
  • AUTO_INCREMENT 락을 INSERT와 같이 새로운 레코드를 저장하는 쿼리에서만 필요하며, UPDATE나 DELETE 등의 쿼리에서는 걸리지 않는다.
  • InnoDB의 다른 잠금과 달리 AUTO_INCREMENT락은 트랜잭션과 관계없이 INSERT문에서 AUTO_INCREMENT값을 가져오는 순간만 락이 걸렸다가 즉시 해제된다.
  • MySQL 5.1 이상부터는 innodb_autoinc_lock_mode라는 시스템 변수를 이용해 자동 증가 락의 작동 방식을 변경할 수 있다.

innodb_autoinc_lock_mode=0

  • MySQL 5.0과 동일한 잠금 방식으로 모든 INSERT 문장은 자동 증가 락을 사용한다.

innodb_autoinc_lock_mode=1

  • 단순히 한 건 또는 여러 건의 레코드를 INSERT하는 SQL 중에서 MySQL 서버가 INSERT되는 레코드의 건수를 정확히 예측할 수 있을 때는 자동 증가 락을 사용하지 않고, 훨씬 가볍고 빠른 래치(뮤텍스)를 이용해 처리한다.
  • 이 개선된 래치는 자동 증가 락과 달리 아주 짧은 시간 동안만 잠금을 걸고 필요한 자동 증가 값을 가져오면 즉시 잠금이 해제된다.
  • 허나 INSERT ... SELECT와 같이 레코드 건수를 예측할 수 없을 때는 자동 증가 락을 사용한다.
  • 이 때, INSERT가 실행되기 전에 자동 증가 락은 해제되지 않기 때문에 다른 커넥션에서는 INSERT를 실행하지 못하고 대기하게 된다.
  • 대량 INSERT가 수행될 때는 InnoDB 스토리지 엔진은 여러 개의 자동 증가 값을 한 번에 할당받아서 INSERT되는 레코드에 사용한다.
  • 그래서 대량 INSERT되는 레코드는 자동 증가 값이 누락되지 않고 연속되게 INSERT가 된다. 하지만 한 번에 할당받은 자동 증가 값이 남아서 사용되지 못하면 폐기하므로 대량 INSERT 문장의 실행 이후에 INSERT되는 레코드의 자동 증가 값은 연속되지 않고 누락된 값이 발생할 수 있다. 이 설정에서는 최소한 하나의 INSERT 문장으로 INSERT되는 레코드는 연속된 자동 증가 값을 가진다. 그래서 이 설정 모드를 연속 모드라고도 부른다.

innodb_autoinc_lock_mode=2

  • InnoDB 스토리지 엔진은 절대 자동 증가 락을 사용하지 않고, 경량화된 래치를 사용한다.
  • 하지만 이 설정에서 하나의 INSERT 문장으로 INSERT 되는 레코드라고 하더라도 연속된 자동 증가 값을 보장하진 않는다.
  • 그래서 이 설정 모드를 인터리빙 모드라고도 부른다.
  • 이 설정 모드에서는 INSERT ... SELECT문과 같은 대량 INSERT 문장이 실행되는 중에도 다른 커넥션에서 INSERT를 수행할 수 있으므로 동시 처리 성능이 높아진다.
  • 하지만 이 설정에서 작동하는 자동 증가 기능은 유니크한 값이 생성된다는 것만 보장한다.
  • STATEMENT 포맷의 바이너리 로그를 사용하는 복제에서는 소스 서버와 레플리카 서버의 자동 증가 값이 달라질 수 있기 때문에 주의해야 한다.