MySQL 자동 증가 칼럼의 최신 데이터 조회시 주의 사항

logs 테이블의 id 칼럼이 자동 증가 칼럼이라고 하자. 최근 id 칼럼 값이 4라고 할때 이 칼럼에 P1, P2, P3과 아래 그림과 같은 순서로 insert 쿼리를 실행한다고 하자.

P1이 세 번의 insert 쿼리를 실행하면 id 값은 5, 6, 7이 된다. P1의 트랜잭션이 끝나지 않은 상태에서 P2, P3가 각각 insert 쿼리를 실행하면 ID는 8, 9가 된다. P2와 P3는 트랜잭션이 끝나고 P1이 아직 트랜잭션 진행 중인 상태에서 P4가 id를 조회하면 [8, 9]를 리턴한다.

일정 간격으로 데이터를 복사할 경우 이 상황은 문제가 될 수 있다. 예를 들어 다음 로직을 사용해서 최신 데이터를 처리하는 로직이 있다고 하자.

1. 이전에 처리한 최대 ID를 구해 lastProcessedId에 할당한다.
2. 현재의 최대 ID를 구해 maxId에 할당한다.
3. lastProcessedId보다 크고 maxId보다 작거나 같은 ID의 값 목록을 구해 ids에 할당한다.
4. ids에 속한 데이터를 처리한다.

만약 lastProcessedId가 4이고 P4의 조회 시점에 maxId는 9라고 하자. 이 경우 과정 3에서 구하는 ids에는 5, 6, 7은 없고 8, 9만 담긴다. 과정 4를 처리한 뒤 다시 과정 1을 반복하면 이때 lastProcessedId는 9가 되어 5, 6, 7은 처리 대상에서 누락되는 문제가 발생한다.

이런 누락 문제가 발생하지 않도록 하려면 트랜잭션 격리 레벨을 높이거나 데이터 조회 시점과 최대 ID가 증가하는 시점에 차이를 둬야 한다. CDC(Change Data Capture)를 사용하는 방법도 있다.