만료 배치에서 인덱스가 잠금 범위를 바꾸는 이유

얼쑤는 녹음 세션을 연 뒤 업로드가 오랫동안 오지 않으면 만료 배치에서 정리합니다. 쿼리는 RECORDING 상태이면서 시작 시각이 마감보다 이른 세션만 찾아 FAILED로 바꿉니다. 조건을 충족한 행이 적으면 쓰기 범위도 작아 보입니다. 잠금 범위를 검토할 때는 DB가 그 행들을 찾으려고 어떤 인덱스를 훑는지도 봐야 했습니다.

갱신 행 수와 잠금 범위의 차이

InnoDB의 UPDATE는 조건에 맞는 행을 찾는 동안 잠금을 잡습니다. REPEATABLE READ에서 범위를 훑으면 접근 경로에 따라 인덱스 레코드와 그 앞 간격에 next-key 잠금이 걸릴 수 있습니다. 조건을 좁히는 데 쓸 인덱스가 없어 전체 스캔을 택하면, 결과적으로 갱신한 행 수보다 넓은 범위가 잠길 수 있습니다. 새 세션을 넣을 자리가 그 간격 안에 있으면 삽입도 기다릴 수 있습니다. InnoDB에서 문장별로 설정하는 잠금

당시 스키마에는 생성 시각 인덱스와 회원·시작 시각 인덱스가 있었습니다. 만료 쿼리는 회원을 지정하지 않고 상태가 같은 오래된 세션을 찾습니다. 이 조건에 맞는 후보는 (status, started_at)입니다. 이 인덱스를 범위 스캔에 쓰는 계획이라면 상태가 같은 구간으로 먼저 들어간 뒤 마감 전 시각까지만 읽을 수 있습니다. 실제로 어느 범위를 잠그는지는 실행 계획과 격리 수준을 함께 봐야 합니다.

기존 인덱스를 유지한 이유

당시에는 초기 데이터 규모와 5분마다 실행하는 배치라는 조건을 고려해 기존 인덱스를 유지했습니다. 복합 인덱스는 행이 늘거나 잠금 대기가 관찰되면 다시 검토할 후보로 남겼습니다.

고정 시계를 사용한 임시 MySQL 8.4.9 검사에서는 세션을 연 뒤 40분에 1마이크로초 못 미친 경우, 정확히 40분인 경우, 40분을 넘긴 경우를 함께 넣었습니다. 오래된 PENDING과 기존 DONE도 준비했습니다. 첫 실행에서 40분을 넘긴 RECORDING 한 행만 FAILED로 바뀌었고, 같은 시각에 다시 실행하자 0행이 바뀌었습니다. 쿼리의 비교를 <에서 <=로 바꾸면 정각의 행까지 만료돼 검사가 실패했습니다.

이 검사는 어떤 행이 만료되는지 확인했습니다. 당시에는 운영 배치의 실행 계획과 잠금 대기를 측정하지 않았습니다.

같은 한 행을 바꾸고 다른 연결에서 삽입했습니다

격리한 MySQL 8.4.9의 InnoDB 테이블에 DONE 두 행과 RECORDING 두 행을 넣었습니다. 두 시험 모두 REPEATABLE READ에서 같은 만료 조건으로 한 행을 갱신한 뒤 트랜잭션을 열어 뒀습니다. 행이 네 개뿐인 표에서 실행 경로를 비교하기 위해 FORCE INDEX로 경로를 지정했습니다. 두 번째 연결은 기존 두 DONE 값 사이에 새 행을 넣고, 최대 1초 동안 잠금을 기다리도록 했습니다.

같은 한 행을 바꿨지만 다른 행의 삽입 결과가 달랐습니다. 이 결과는 네 행으로 만들고 경로를 지정한 시험에 한정됩니다. 운영에서 복합 인덱스를 추가하거나 옵티마이저가 스스로 어떤 계획을 택할지는 확인하지 않았습니다. MySQL 행 잠금 대기로 요청 경합 확인