[Today I Learned - 9] MySQL 조인 관계에서 락 범위

상태 변경 배치를 Spring Batch를 통해 만들었고 멀티쓰레드로 처리하는 과정에서 데드락이 발생해서 문제의 원인을 파악해보려고 한다.

 

1. 문제가 발생하는 상황

---

SELECT * 
FROM A a JOIN B b ON a.id = b.a_id
WHERE a.col1 = 'A'
    a.col2 = 'B'
    a.col3 = 'C'
FOR UPDATE

 

문제되는 쿼리의 구조는 위와 같다. A 테이블의 col1, col2, col3 컬럼은 인덱스가 생성된 상태이고 B 테이블의 a_id에는 인덱스가 생성되지 않은 상태이다.

 

아래와 같이 실행계획으로 분석해보았을 때 A 테이블은 인덱스를 통해 검색되지만, B 테이블(아래줄)은 풀스캔이 진행되고 있었다. B 테이블의 a_id 컬럼에 인덱스가 없었기 때문에 풀스캔이 발생하는 상황은 이해가 됐는데 문제는 왜 데드락이 걸리는지 이해가 안됐다.

(멀티쓰레드에서 검색 조건이 각각 다르기 때문에 중복되는 레코드를 참조할 일이 없음)

 

2. 해결방법

---

결과적으로 내가 락이 걸리는 범위를 잘못 알고있었다. SELECT 쿼리의 최종 실행 결과에 락이 걸리는 것으로 알고 있었는데 확인해보니 쿼리를 수행할 때 참조되는 데이터들이 전부 락이 걸리도록 되어있다.

 

즉 A조건의 검색조건이 전부 달라서 중복되는 데이터가 나오지 않기 때문에 데드락이 발생하지 않을 것이라고 생각했는데 사실은 B 테이블은 풀스캔을 하기 때문에 B 테이블 전체에 대해 LOCK이 걸리면서 데드락이 발생하게 된 것이였다.

 

이와 같은 문제를 해결하기 위해 B 테이블의 a_id 컬럼에 인덱스를 추가해서 풀스캔이 발생하지 않도록 하였다. 변경 이후 실행계획을 보면 B 테이블에서 필요한 데이터만 참조하도록 변경된 것을 확인할 수 있다.