[mysql] auto increment 이슈
mysql 의 auto increment 쪽은 약간 살펴봐야할 내용들이 있다.
auto increment 필드(예, id)를 가진 테이블에 insert into문을 사용할 때, auto increment 필드(예, i)가 순차적인 일련번호가 아닌 값이 나타나는 겨우가 발생했다.
즉, insert into문을 사용할 때 일반적인 auto increment 필드는 1, 2, 3, 4, 5와 같은 순차적으로 늘어나는데, 1, 2, 3, 5 이렇게 나타날 수 있다.
여러 원인이 있을 수 있다.
1. insert ignore into 문을 사용하다가 statement문에 이슈가 발견되어 row가 insert되지 않아도 auto increment필드의 값이 증가될 수 있다.
2. insert into문이 포함된 transaction이 rollback 되는 경우 auto increment필드의 값이 증가될 수 있다.
3. concurrent한 insert into 문이 들어올 때 auto increment필드의 값이 증가될 수 있다.
따라서, auto increment 값을 신뢰해서 안되며 auto increment를 중요하게 쓸 것이라면, transaction에서는 사용하지 않는 것이 좋다. auto increment을 중요하게 쓸 계획이라면, innodb_autoinc_lock_mode을 알아야 한다.
(mysql db에서 show variables 커맨드를 사용하면 확인할 수 있다)
mysql 5.0까지는 auto increment는 테이블 락이었지만, 5.1 부터는 테이블 락이 아닌 갭 락(gap lock)을 사용한다. 또한, mysql 5.0까지는 innodb_autoinc_lock_mode의 기본값이 0이었다가, mysql 5.1부터는 기본값이 1이 되었다.
innodb_autoinc_lock_mode의 값 비교
0(tranditional)으로 설정하면, 테이블 단위의 락을 사용한다. 성능이 떨어진다.
1(consecutive)로 설정하면, 단순한 insert into문에 락을 사용하지 않는다. bulk insert 문을 사용할 때 mutex(light weight lock)를 사용해 락을 사용한다.
2(interleaved)로 설정하면, 락을 사용하지 않는다. 빠르고 확장성이 좋지만, 복구가 어렵다. bulk insert 문을 사용할 때 엉망인 auto increment 값이 나타날 수 있다.
innodb_autoinc_lock_mode의 값을 잘 보고 성능과 기능의 적당한 타협이 필요하다.
참조
출처: http://knight76.tistory.com/entry/auto-increment-이슈 [김용환 블로그(2004-2017)]