InnoDB Engine Auto_increment 동작

현상

MySQL 재시작 후 Table 내 AUTO_INCREMENT 값이 감소하는 현상이 발생

(예. 재시작 전: Auto_Increment 4000 , 재시작 후 Auto_Increment 3800)

원인

요약: MySQL InnoDB에서는 마지막 AUTO_INCREMENT을 메모리에만 저장

상세:

MySQL에서 InnoDB 엔진의 경우 AUTO_INCREMENT 값을 메모리에 저장하고 Insert 시 증가

 * 이슈 발생의 경우 tb_user 에서 data를 삭제 시 동일한 구조의 tb_user_del 값으로 insert 후 삭제하게 되는데

Table 의 AUTO_INCREMENT 값이 4000인 상태에서 SEQ가 3800~4000까지의 data를 tb_user_del 로 insert하고 삭제한 후 MySQL를 재시작하게 되면서 또 다시 SEQ가 3800부터 insert됨
(SEQ는 tb_user와 tb_user_del 모두 PK로 설정됨) 

재시작 이후 SEQ가 3800~4000인 data를 tb_user_del에 insert하면서 PK충돌이 일어나게 됨

해결 방법

AUTO_INCREMENT 를 MySQL 재시작시에도 유지하기 위해서는 엔진을 MyIsam으로 변경하거나 마지막 AUTO_INCREMENT값을 따로 기록을 하여야 함.

(발생한 이슈 해결을 위하여, tb_user_del의 PK를 nonclustered index로 변경하여 PK충돌을 해결하였음)