Lock 과 Index

이번 MySQL 개인공부를 진행하다가 기록하고 싶은 부분이 있어서 블로그 글에 남깁니다!!

 

바로 Lock 과 Index 에 대하여 기록을 남길 생각인데요 

개인 공부를 진행하다가 MySQL 에서 잠금은 ROW가 아닌 Index를 잠근다 라는 사실을 알게되었습니다.

 

한마디로, Index 에 따라서 잠금의 범위가 달라지게된다는 말이였습니다.

Query 의 WHERE절에서 Index가 적용되지 않는 컬럼으로 Lock을 잡으려고 할 때 제가 생각했던것과 다르게 

불필요한 Row 들에 잠금이 발생할수 있는것입니다.

 

그러면 Index의 설정이 굉장히 중요하겠죠..(잘못쓰면 큰일..)

 

MEMBER 테이블

 

현재 MEMBER 테이블은 기본키인 SEQ 컬럼과 TEAM_SEQ 컬럼에 INDEX 가 걸려있습니다.

SELECT * FROM MEMBER WHERE TEAM_SEQ = 1 AND CITY = 'Seoul';

 

위의 상황에서  Index 가 설정되지않는 City 컬럼에 대하여 WHERE 조건절을 추가하여 쿼리하게 되면 

 

 

위처럼 Row 의 결과수는 2개입니다. 하지만 Lock 획득을 위한 For Update 구문을 추가할시에 제가 원하는 방향과는 다르게 Lock 이 잡히게 됩니다.

 

START TRANSACTION;
SELECT * FROM MEMBER WHERE TEAM_SEQ = 1 AND CITY = 'Seoul' FOR UPDATE;

 

위의 쿼리를 통하여 우리는 2개의 Row 만을 Lock 을 취득하길 원했지만..

 

SELECT * FROM performance_schema.data_locks where LOCK_TYPE = 'RECORD';

 

 

위의 쿼리를 통해 Lock 이 걸린 레코드를 확인해보면 우리가 기대했던 2개의 Row가 아닌 TEAM_SEQ 가 1인 4개의 데이터를 모두 Lock 이 잡힌 상태인것을 알 수 있습니다.

 

4개의 데이터가 Lock 이 잡힌이유는 MySQL 에서는 Index에 대해서 Lock 을 잡기때문에 Index 설정이 되지않은 City 컬럼에 대하여 Lock을 잡지 못하기 때문에 선행조건인 TEAM_SEQ = 1 인 데이터들에 대하여 Lock 을 획득합니다.

 

이러한 현상들로 인해 다른 트랜잭션에서 City가 'Busan' 인 레코드에 대하여 Lock 을 획득하려 할때 이미 앞의 트랜잭션에서 의도치 않게 Lock을 점유하고 있기 때문에 Lock_wating 이 일어날 수 밖에 없습니다.

 

또한 정말 무시무시한 점은 아래처럼  Index 설정이 되지않은 ID컬럼을 이용하여 Lock 을 획득할때 Member 테이블의 모든 레코드가 Lock에 잡힐 수 있어 앞의 트랜잭션이 종료되기 전까지 다른 트랜잭션에서는 테이블에 아예 접근조차 되지 않는 상황이 일어날 수도 있습니다.

SELECT * FROM MEMBER WHERE ID = 'Orange' for update;

 

이렇게 Lock 과 Index의 상관관계를 공부하면서 Index에 대해 깊게 생각하여 설정해야겠다!! 라는 생각을 할 수 있는 좋은공부시간이 된거 같습니다.