Mysql 5 AutoIncrement Reset Issue

아래 코드는 최신의 5.7.41에서 테스트 되었다. 즉 모든 5.x 버전에 영향을 미친다 mysql 5에는 치명적이라면 치명적인 이슈가 하나 있다.

아래 코드를 보고 결과를 예측 해 보자

CREATE TABLE test
(
    id INT PRIMARY KEY AUTO_INCREMENT,
    dummy varchar(256) NOT NULL
);

INSERT INTO test(dummy) values ('1'), ('2'), ('3');
DELETE FROM test WHERE dummy = '3';
INSERT INTO test(dummy) values('4'), ('5'), ('6');

SELECT * FROM test;

자 결과는? 매우 당연하게도

id	dummy
1	1
2	2
4	4
5	5
6	6

와 같다 자 그럼 한가지 테스트를 더 해 보자

CREATE TABLE test
(
    id INT PRIMARY KEY AUTO_INCREMENT,
    dummy varchar(256) NOT NULL
);

INSERT INTO test(dummy) values ('1'), ('2'), ('3');
DELETE FROM test WHERE dummy = '3';

까지 실행 후, DB 서버를 Shutdown 후 재시작한다. 그리고

INSERT INTO test(dummy) values('4'), ('5'), ('6');

SELECT * FROM test;

을 이어서 실행해 보자

그럼 결과는?

id	dummy
1	1
2	2
3	4
4	5
5	6

가 나온다.

이렇게 된 원인은 간단한데, mysql 5.x의 AUTO_INCREMENT 의 구현이 비상식적이기 때문이다. 대부분의 DB 서버는 db 파일에 발급된 최신의 값을 별도로 저장 하고 있고, 서버 재시작 시 그 값을 읽어서 AUTO_INCREMENT 컬럼의 다음 값을 구현한다.

하지만 mysql 은 서버 시작 시, AUTO_INCREMENT가 걸린 Column의 마지막 값을 읽은 후 그 값부터 다시 발급한다. 즉 최신의 값이 삭제되었다면, 같은 값이 2번 이상 발급될 수 있다.

이 이슈를 일반적으로 보기 힘든 이유는 간단한데, DB 서버를 재시작 하는경우가 거의 없을 뿐더러, 대부분의 서비스는 SoftDelete(실제로 지우지 않고 deleted flag만 켜는 방식) 을 사용하기 때문이다. 하지만 이 이슈를 매우 자주 볼 수 있는 서비스가 있는데… 바로 온라인 게임이다.

온라인 게임의 경우 매주 점검때 서버를 재부팅할뿐더러, 점검때 사용하지 않는 데이터를 sweeping 하고, index rebuild를 하는 것이 보통이다. 이 경우 AUTO_INCREMENT 로 발급된 값을 (의미상의) FK로 사용 할 경우 치명적인 데이터 오염이 있을 수 있다.

간단히 일어날 수 있는 케이스를 살펴보면, item 테이블이 있고, 그 아이템의 마법부여나, 강화 등의 정보를 가지는 item_enchant 테이블이 별도로 있다고 가정하자 그리고 item_enchant 테이블은 item 테이블의 id를 fk 로 가지고 있다고 생각하겠다.

그런데 만약 강화하다가 item 이 깨질 경우 item 을 삭제(혹은 delete flag on) 을 하게 될텐데, 이 직후 서비스가 점검에 들어가서 데이터를 지우고 재부팅을 하게 될 텐데… 이때 item_enchant 를 같이 삭제하는것을 잊는 실수를 한다면(흔히들 나오는 실수다… 보통은 실수인줄도 모르고 넘어가지만) 다음에 새로운 아이템이 생성 될때, enchant 데이터가 그대로 남아 있기 때문에 아이템이 새로 생기자 마자 강화가 되어있는 현상이 나타날 수도 있게 된다.

이를 회피하려면 몇가지 방법이 있는데, 내가 흔히 사용하는 방법이 AUTO_INCREMENT 는 단순히 물리적인 순서를 유지하는 용도로만 쓰고, FK 는 다른 Unique 한 값을 쓰는 방식이다. 그 외에는당연히 mssql 등의 다른 DB에선 나타나지않는 현상이니 교체하는 방법과… mysql 을 8 이상의 버전으로 업그레이드 하는 방법이 있다. 8은 수정되었기 때문에…