개요
MySQL에서 트랜잭션을 사용할 때, 잠금(Lock)은 개별적인 ROW가 아닌 Index를 기준으로 적용된다는 사실을 학습하며 정리합니다. 이를 통해 인덱스 설정의 중요성과 잘못된 설정이 초래할 수 있는 문제들을 이해할 수 있습니다.
주요 개념
- MySQL에서 잠금은 Index 단위로 적용되므로, WHERE절에서 Index가 적용되지 않는 컬럼을 사용하면 불필요한 Row들까지 잠금될 가능성이 높아짐.
- 올바른 Index 설정이 없을 경우, 의도치 않게 광범위한 Row에 Lock이 발생할 수 있음.
실험 데이터 및 쿼리
MEMBER 테이블 구조
- SEQ (Primary Key)
- TEAM_SEQ (Index 설정됨)
- ID (Index 없음)
- CITY (Index 없음)
현재 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 이 잡힌 상태인것을 알 수 있습니다.
원인 분석
- MySQL은 Index를 기준으로 잠금을 설정하므로, Index가 설정되지 않은 CITY 컬럼을 WHERE절에 추가해도 해당 컬럼을 기준으로 잠금되지 않음.
- 대신, WHERE절에서 Index가 적용된 TEAM_SEQ를 기준으로 Lock을 설정하기 때문에, TEAM_SEQ = 1을 만족하는 모든 Row가 Lock됨.
- 따라서 CITY = 'Seoul' 조건과 무관하게 TEAM_SEQ가 1인 모든 Row가 잠김.
ID 컬럼을 이용한 Lock
SELECT * FROM MEMBER WHERE ID = 'Orange' for update;
- ID 컬럼은 Index가 설정되지 않음.
- 결과적으로, MEMBER 테이블의 모든 Row가 Lock에 잡히는 현상 발생.
- 이는 다른 트랜잭션에서 해당 테이블 전체에 접근할 수 없게 만드는 위험한 상황을 초래함.
해결책 및 정리
- WHERE 절에 사용되는 컬럼들은 반드시 Index가 설정된 컬럼을 활용해야 함.
- Index가 설정되지 않은 컬럼을 이용해 Lock을 걸 경우, 예상보다 많은 Row에 잠금이 걸릴 가능성이 있음.
- 특히, Index가 설정되지 않은 컬럼을 사용한 FOR UPDATE 쿼리는 테이블 전체를 Lock할 수도 있음.
- Index를 효율적으로 설계하면 불필요한 Lock을 방지하고 성능을 최적화할 수 있음.
MySQL에서 Lock이 Index를 기준으로 적용된다는 점을 고려하여 적절한 Index 설정이 필요합니다. 잘못된 Index 설계는 예상보다 넓은 범위의 Row를 잠가 성능 저하와 데이터 경합 문제를 유발할 수 있으므로, 트랜잭션을 설계할 때 Index 전략을 철저히 검토해야 합니다.