MYSQL을 활용해서 배치 처리하기 (멀티 서버 환경을 중심으로)

상황

학교 다닐때 출석부를 보면 출석 기록들이 이미 빈값으로 등록 되어있다. 비슷한 맥락으로 수업일마다 출결 기록을 초기화하는 배치 작업이 필요했다. 이 때 Failover를 위해 서버 2대를 운영 중이었고, 모든 서버에서 동일한 배치 로직이 실행되고 있었다.

문제

다중 서버 환경에서 출결 데이터를 생성할 때, 서버 간 동일 데이터에 대한 락 경합과 중복 처리 문제를 해결함과 동시에, 향후 추가될 다양한 배치 작업들에서도 동일하게 적용 가능한 일관된 병렬 처리 방법을 생각해서 시스템의 처리 효율과 데이터 무결성을 확보해야 한다.

해결 방안 비교

선택지 1: 유니크 인덱스

가장 단순한 방법으로, 중복 삽입 시 DB 레벨에서 차단된다. 하지만 중복 INSERT 쿼리가 계속 발생하므로 불필요한 DB 리소스가 소모된다. 근본적인 해결이라고 생각되지 않았다.

 

선택지 2: 분산 락

Redis나 DB 기반으로 락을 구현해 병렬 처리를 할 수 있다. 작업 단위별로 락을 걸면 여러 서버가 동시에 처리 가능하다. 하지만 Redis를 도입했을때 아키텍처의 복잡도가 더 올라가는데, Redis를 추가해서 해결해야 한다고 생각하지 않았다.

 

선택지 3: SELECT FOR UPDATE SKIP LOCKED

MySQL 8.0.1에 도입된 SKIP LOCKED를 활용하면 된다. 각 서버가 처리할 row에 대해 락을 획득하고, 이미 락이 걸린 row는 건너뛰어 나머지를 처리한다. 별도 인프라 없이 MySQL만으로 병렬 처리가 가능하고, 서버 증설 시 처리량도 함께 증가한다.

 

하지만 skip locked는 분산 서버 환경에서 병렬 처리를 위한 방법으로 판단했지, 데이터 무결성은 보장하지 않기 때문에 unique 제약 조건은 필수다. 특정 날짜의 특정 수업에 이 학생의 기록은 하나만 있어야 한다는 조건으로 student_id, lesson_id, date에 composition unique index를 적용할 필요가 있다.

 

[출결을 생성해야할 대상 레슨을 찾는 Skip Locked Query]

SELECT l.* FROM lesson l
WHERE (l.status = 'UPCOMING' OR l.status = 'IN_PROGRESS')
AND NOT EXISTS (
    SELECT 1 FROM attendance a
    WHERE a.lesson_id = l.id
    AND a.date = :today
)
FOR UPDATE SKIP LOCKED
LIMIT 100;

결론

SKIP LOCKED를 선택했다. 유니크 인덱스만을 사용하면 커넥션을 굳이 점유해야하고, 분산 락은 Redis 도입이 필요했다. SKIP LOCKED는 별도 인프라 없이 DB만으로 안전한 병렬 처리와 수평 확장을 모두 달성할 수 있다고 판단했다.

 

ref: https://dev.mysql.com/blog-archive/mysql-8-0-1-using-skip-locked-and-nowait-to-handle-hot-rows/

MySQL :: MySQL 8.0.1: Using SKIP LOCKED and NOWAIT to handle hot rows

SKIP LOCKED is very handy in the case of multi-threaded workers trying to find the next N rows in a table that need processing.

 

A locking read that uses SKIP LOCKED never waits to acquire a row lock. The query executes immediately, removing locked rows from the result set. MySQL

SKIP LOCKED는 "락을 획득할 수 없는 행"을 스킵합니다. S락끼리는 호환되니까 획득 가능 → 부모 트랜잭션에서 S락으로 처리하고 자식 트랜잭션에서 S락으로 처리하면 중복으로 데이터 가져올 수 있음