서론
MySQL의 Repeatable Read에서는 Phantom Read가 발생하지 않는다는 것에 대해 자세한 내용이 궁금해서 서치해봤다.
https://parkmuhyeun.github.io/woowacourse/2023-11-28-Repeatable-Read/
이 글을 읽고 실제로 어떤지 테스트해보다가 인덱스 유무에 따라 걸리는 락 범위가 달라지는 것을 발견했다.
테스트 배경
user 테이블을 만들었다. user 테이블의 형태는 다음과 같다.
위 블로그를 따라 테스트를 진행하던 중 실제로 락이 어떻게 걸리는지 궁금해서 출력해봤다.
락은 select * from performance_schema.data_locks \G를 입력하면 아래와 같이 출력된다.
위는 select * from user where user_id = ''2038dc9a-0136-11ef-835f-00155d8b7809' for update 쿼리를 실행했을 때 걸리는 락이다.
"LOCK_MODE: X, REC_NOT_GAP" 부분을 보면 어떤 락이 어떤 범위로 걸렸는지 알 수 있다.
- X, REC_NOT_GAP : X락, 레코드락
- X, GAP: X락, 갭락
- X: Next Key락
X가 S면 S락이라는 뜻이다.
이상한 점
테스트 진행 중 select * from user where age >= 11 for update; 와 같은 범위 조건절을 포함한 쿼리를 치고 락을 확인해봤는데 내 예상과 다른 점이 있었다.
예상: age >= 11에 포함되는 레코드에 대해서만 넥스트 키락이 걸림
실제: 모든 레코드에 대해 넥스트 키락이 걸림.
그래서 원인을 찾아봤더니 https://wbluke.tistory.com/44 글에서 해답을 찾았다.
InnoDB에서는 UPDATE, DELETE 쿼리를 실행할 때 SQL 문장이 조건에 일치하는 레코드를 찾기 위해 참조하는 인덱스의 모든 레코드에 잠금을 건다.이는 해당 쿼리의 WHERE 조건에 일치하지 않는 레코드라도 잠금의 대상이 될 수 있음을 의미한다.
레코드에 잠금을 거는 주체는 InnoDB 스토리지 엔진이고, 업데이트할 레코드를 최종 결정하는 것은 MySQL 엔진인데, MySQL 엔진은 WHERE 절의 모든 조건이 아니라 인덱스를 사용할 수 있는 조건만 InnoDB 스토리지 엔진으로 전달한다. 따라서 해당 UPDATE, DELETE 쿼리가 이용할 인덱스가 없다면 InnoDB 스토리지 엔진은 모든 레코드를 대상으로 잠금을 건다.
쉽게 말해 age >= 11 이라는 where 절에서 참조할 인덱스가 없기 때문에 모든 레코드에 잠금을 건다는 것이다.
이렇게 되면 user 테이블의 모든 레코드와 범위에 대해 락이 걸렸으므로 user 테이블에 insert, update, delete 조작이 불가능하다.
age 인덱스 추가
그럼 age 컬럼에 인덱스를 걸면 어떻게 될까?
create index user_age_index ON user(age); 를 통해 인덱스를 생성해줬다.
그리고 다시 select * from user where age >= 11 for update; 쿼리를 실행했다.
실제로 걸린 락은 다음과 같았다.
age >= 11 인 레코드에 대해 Primary 인덱스는 레코드 락이 걸리고, user_age_index는 next key lock이 걸렸다.
이렇게 되면 age = 10인 레코드는 insert, update, delete 등 조작이 가능하고, age >= 11인 레코드들에 대해서는 insert, update, delete 조작이 불가능하다.
PK 값으로 조건 절을 작성하는 경우
select * from user where user_id <= '22cdef06-5b14-4d15-9e49-4f724af582b3' for update; 와 같이 PK로 조건 절을 작성하면 어떻게 될까?
user_id >= '22cdef06-5b14-4d15-9e49-4f724af582b3'인 범위에 대해서만 Primary 인덱스에 락이 걸린다.
위 캡처본을 보면 user_id = '2038dc9a-0136-11ef-835f-00155d8b7809'인 레코드에 대한 Next Key Lock과 해당 레코드와 다음 레코드 사이에 갭락이 걸렸다.
결론
인덱스가 걸리지 않은 컬럼에 대한 where 절이 포함된 쿼리를 실행하면
모든 레코드에 대해 락이 걸리게 된다.
(select ... for update, select ... for share, udpate, delete와 같이 락을 거는 쿼리에 한함)
따라서 적절한 인덱스 사용은 조회 속도를 빠르게 해줄 뿐만이 아니라 락이 걸리는 범위를 좁혀 동시성에도 영향을 미칠 수 있다.
