[MYSQL] 왜 insert 에 실패해도 auto_increment 값이 증가할까?

전 회사에서 마주했던 이슈중에 pk를 auto_increment 걸어놓은 테이블에 insert 시 idx 값이 이전에 삽입된 idx 값보다 이상하리만큼 높아진걸 발견했다. 한 두개 차이면 그냥 신경쓰지 않으려고 했는데 거의 1000이 넘게 뛰어있었다. 때문에 그냥 넘기기엔 나중에 문제가 생길 수 있을 거 같아 원인을 찾아봤고 정리한다. 

 

상황은 이랬다. 기존에 초기 테이블에 데이터를 넣을 때 임의로 운영팀이 스크립트 파일을 실행하여 다량의 데이터를 넣어야 하는 환경이다. 중간에 실패했다고 모든 쿼리를 중단하면 안되는 상황이라 insert ignore into를 사용했었다. 그런데 위에서 말한대로 idx가 널뛰기를 하는 상황이 생겼다. 찾아보고 나름 테스트를 해본 결과 innodb의 auto_increment_lock_mode 정책 때문으로 판단된다. 이게 원인인 것은 맞지만 결과 도출에선 오늘은 자신이 좀 없다.

5.6 버전의 공식 문서를 확인했다(https://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-handling.html).

 

 

설명에 앞서, insert 의 타입부터 설명하겠다.

insert 는 데이터를 추가하는 모든 것을 포함하므로 insert, insert ... select 말고도 replace, replace ... select, load data 도 포함된다.

 

insert는 크게 세 가지로 나뉜다.

• Simple insert (단순 insert). insert 될 row 수를 사전에 알 수 있는 insert 를 말한다. row 는 단일, 다중 전부 포함하나 서브 쿼리가 포함되면 안된다. 보통 일반적인 insert, replace를 말한다. insert ... on duplicate key update 는 포함되지 않는다. (insert ignore into는 단지 insert into 의 실패 결과를 무시하는 것이기 때문에 일반 insert 로 들어가는 듯 하다.)
• Bulk insert (벌크 insert). insert 될 row 수나 필요한 auto_increment 수를 사전에 알 수 없는 insert 를 말한다. insert ... select, replace ... select, load data 가 포함된다. 일반적인 insert 는 포함되지 않는다. innodb 엔진은 각 row가 처리될 때마다 새로 auto_increment 값을 할당 받는다.
• Mixed-mode insert(복합모드 insert). 복합 모드 insert 는 단순 insert 문에서 auto_increment 값을 일부만 지정해주는 경우(전체를 다 지정하면 단순 insert로 처리되는 듯)와 insert ... on duplicate key update 를 포함한다.

auto_increment_lock_mode 는 auto_increment 값 생성  시에 에 사용되는 잠금 모드를 선택하는 설정이다. auto_increment_lock_mode 값에 따라 위 insert 타입들 실행 시 생성되는 auto_increment 값이 달라진다.

 

• 0(tranditional) : 모든 insert 를 대상으로 테이블 레벨의 AUTO_LOC 을 사용한다. insert 결과를 예측하여 순서를 보장하기 위해 구문마다 락이 걸린다(트랜잭션 단위가 아니다!). 때문에 auto_increment 값이 순차적이나 아무래도 성능이 떨어진다고 한다. 
• 1(consecutive) : 단순 insert 에서는 테이블 레벨 AUTO_LOC이 아닌 mutex(경량 잠금) 레벨의 AUTO_LOC을 사용하며 구문마다 락이 걸리는 게 할당 프로세스 단위로 락이 걸린다고 한다. 따라서 traditional 보다 퍼포먼스가 좋다. 복합 모드 insert(일부가 명시되기 때문에 삽일될 수보다 많은 값을 할당받게 된다고 한다. 넘어가는 건 버려진다)를 제외하고는 traditional 과 결과과 동일하다(흠?).
• 2(interleaved) : 락을 사용하지 않는다. 빠르고 확장성이 좋지만, 복구가 어렵다. bulk insert 문을 사용할 때 순차적이지 못한 auto increment 값이 나타날 수 있다. 

 

 

(추가적으로, mysql 5.0까지는 auto increment는 테이블 락이었지만, 5.1 부터는 테이블 락이 아닌 갭 락(gap lock)을 사용한다. 또한, mysql 5.0까지는 innodb_autoinc_lock_mode의 기본값이 0이었다가, mysql 5.1부터는 기본값이 1이 되었다. 그러나 8.0 문서를 보면 기본값이 2로 변경되었다고 한다.)

 

 

문서를 확인하고 이 중 0과 1에 대하여 비교 실습을 진행하였다. 설명 중간에 취소선을 한번 그엇던 이유는 아래에서 설명하겠다.

 

아래는 테스트할 예시다. 컬럼은 인덱스 컬럼(auto increment)과 컨텐트 컬럼만 존재하고 인덱스는 물론, 컨텐트 컬럼의 값들 역시 겹치지 않게 유니크 키를 걸어놨다.

CREATE TABLE test( 
    idx INT NOT NULL AUTO_INCREMENT, 
    content VARCHAR(20) NOT NULL,
    primary key(idx),
    unique key uk (content)
); 

INSERT ignore INTO test(content) VALUES('content'); 
INSERT ignore INTO test(content) VALUES('content2'); 

#중복 데이터들
INSERT ignore INTO test(content) VALUES('content');
INSERT ignore INTO test(content) VALUES('content');
INSERT ignore INTO test(content) VALUES('content2');

INSERT ignore INTO test(content) VALUES('content3');
INSERT ignore INTO test(content) VALUES('content4');

select * from test;

위 쿼리를 두 가지 상황에서 실행해보자.

 

먼저 기본값인 innodb_autoinc_lock_mode 1 값으로 테스트 해보겠다.

(참고로 값은 show variables 로 확인해볼 수 있다!)

innodb_autoinc_lock

음? 인덱스가 순차적이지 못하다. 실패한 insert가 할당받을 인덱스들을 그대로 건너뛰었다.

 

이번엔 innodb_autoinc_lock_mode 값을 0로 하고 동일하게 쿼리를 실행해보겠다. 과연 결과가 다르게 나올까?

오우 이번엔 인덱스가 이쁘게 순차적으로 증가했다!!

 

 

결과에 차이가 있다는 게 이상했다. insert ignore into 가 아니라 insert into 로 각각 에러내면서 진행해도 동일한 결과가 나온다. 문서상에는 0, 1 두 모드가 단일 insert 에서는 동일한 결과를 보인다고 했다. 문서에 실패 시에 관한 내용이 마땅히 없었고(그냥 내가 영어를 못해서 못찾은 거일 수도 있지만) 아무래도 1 모드에선 단일 insert가 테이블 레벨도 아니고 구문 단위로 락이 걸리지 않아 실패 시에는 보장이 명확히 되지 않는 것으로 보인다.

 

결국 나의 상황에선 현장의 innodb_autoinc_lock_mode 가 1이었고, 현장 초기 세팅 시 한 단지의 모든 세대와 로비, 경비실기 등의 디바이스들의 계정을 등록해야 했기 때문에 (예를 들어 1000세대이면 한 세대당 등록되는 계정이 2개, 즉 2000개에 +α) 만약 디비 업데이트(insert ignore into로 짜여있는 스크립트 실행) 시에 저 각각의 쿼리가 죄다 실패하면 idx가 그만큼 점프하게 되는 것이었다. innodb_autoinc_lock_mode 를 0으로 바꿔볼 수도 있었지만 테이블을 이거 하나 사용하는 것도 아니고 자주 데이터가 추가되는 테이블도 있는 상황에다 무엇보다, 이 테이블이 사용되는 현장이 거의 없다보니 그냥 넘어가기고 팀분들과 합의 되었었다;;

auto_increment 가 명확해야하고 퍼포먼스에 크게 상관되지 않는 상황이라면 innodb_autoinc_lock_mode 를 0으로 사용해볼 수도 있다.

 

 

오늘은 이유를 명확히 도출해내지 못해서 많이 찝찝하다. 그래서 이걸 포스팅하는게 맞나 싶었지만 내 시간이 아까우니 그냥 올린다. 하지만 문서에서 확인하고 싶은 부분을 전부 찾진 못했고 당장 검색해보아도 다들 "innodb_autoinc_lock_mode 를 0으로 바꾸세요~^^" 라고만 하니 일단은 이렇게 마무리 지어야 겠다.

만약 잘못된 내용을 발견했거나 명확한 이유를 아시는 분들은 댓글부탁드립니다ㅎㅎ;;

 

 

(ps. 이거 테스트하겠다고 집컴에다 mysql 깔다가 dll 파티 터져서 때려치고 가상머신에 우분투 깔아서 설치하려했는데 apt-get 중간에 무한 홀딩되고 강제 취소했더니 나중에 설정 다 틀어지고 난리나고 그와중에 컴이 사망직전 상태라 다운되고!! 5시간 버려서 너무 화가나 중간에 맥주마시면서 해결했다...)