upsert query에서 발생한 문제점

[환경]

Mysql5.7(InnoDB), Python

[상황]

upsert 쿼리에서 id(auto_increment)컬럼이 순차적이 아닌 건너 뛰면서 저장되고 있는 현상이였습니다.

[원인]

InnoDB에서 auto_increment 열은 각각의 트랜잭션에서 순차적으로 증가되며, 각 트랜잭션에서 할당된 auto_increment 값은 InnoDB의 메모리 버퍼에 저장되어, 나중에 디스크에 쓰여질 때까지 버퍼링 된다는 개념이 있습니다. 제 나름대로 정리를 하면 쿼리를 실행하기전 이미 auto_increment값이 각 각의 트랜잭션마다 할당 되어져 있다고 보입니다. 그렇기 때문에 만약 id가 1로 insert로 할려다가 이미 해당 row가 있다고 가정하면 update를 실행 후 그 다음 row를 실행하면 id가 그대로 1로 사용되어져야 하지만 이미 메모리에 저장 된 각각의 트랜잭션으로 인해 id가 2로 할당 되는 것이라고 정리가 되어집니다.

[결론]

여러 트랜잭션이 발생되지 않는 코드를 작성하면 되는 것으로 보입니다. php에서는 1개의 쿼리에 1개의 row만 영향을 줄 수 있어 별도의 트랜잭션으로 해당 오류가 나타나진 않을 것으로 보이지만 python에서 1개의 쿼리에 List 형식으로 쿼리를 실행해 여러 트랜잭션이 동시에 작동해 문제가 생긴 것으로 보였습니다. 즉 어떤 언어에서든 loop로 쿼리를 실행하면 되겠다고 정리하겠습니다.