InnoDB Lock 범위 테스트

조건

• 테이블 크기 : 약 3백만건.
• 실행 조건 : select for update을 사용한 lock 범위 테스트.
  - 인덱스 없는 컴럼으로 where 조회.
  • 인덱스 컬럼, 인덱스 없는 컬럼 where 조회.
  • 복합인덱스 중 하나의 컬럼만으로 where 조회.

# 사용테이블
CREATE TABLE `bulk` (
  `seq` int NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) DEFAULT NULL,
  `middle_name` varchar(45) DEFAULT NULL,
  `last_name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`seq`),
)

인덱스 없는 컬럼 where 조회

# 실행 계획
EXPLAIN SELECT * FROM sakila.bulk WHERE middle_name = 'Jaime' FOR UPDATE;

# 테스트 코드
start TRANSACTION;

# COUNT = 20002
SELECT COUNT(*) FROM sakila.bulk WHERE middle_name = 'Jaime';

SELECT * FROM sakila.bulk WHERE middle_name = 'Jaime' FOR UPDATE;

ROLLBACK;

실행 계획

type 컬럼이 ALL == Table Full 스캔을 했다는 걸 알 수 있고, 약 300만건 row 중 300만건에 조금 못 미치는 row를 읽은걸 알 수 있다.

Lock 범위

# Lock 범위 조회 SQL
SELECT * FROM performance_schema.data_locks;

SELECT INDEX_NAME, lock_type, LOCK_MODE, COUNT(*)
FROM performance_schema.data_locks
GROUP BY INDEX_NAME, lock_type, LOCK_MODE;

인덱스가 없는 컬럼을 조회하니 PK기반으로 전체 record에 X락이 걸려있는 걸 확인 할 수 있다.

인덱스 컬럼, 인덱스 없는 컬럼 where 조회

# first_name = index

# 실행 계획
EXPLAIN SELECT * FROM sakila.bulk WHERE first_name = 'TestFirstName' AND middle_name = 'Jaime' FOR UPDATE;

# 테스트 코드
start TRANSACTION;

# COUNT = 2
SELECT COUNT(*) FROM sakila.bulk WHERE first_name = 'TestFirstName' AND middle_name = 'Jaime';

SELECT * FROM sakila.bulk WHERE first_name = 'TestFirstName' AND middle_name = 'Jaime' FOR UPDATE;

ROLLBACK;

실행계획

type컬럼과 key 컬럼을 보면 ix_first인덱스를 사용한 것을 알 수 있고, 결과 row는 2줄지만 인덱스 row만큼 50줄을 읽은걸 알 수 있다.

Lock 범위

# Lock 범위 조회 SQL
SELECT * FROM performance_schema.data_locks;

SELECT INDEX_NAME, lock_type, LOCK_MODE, COUNT(*)
FROM performance_schema.data_locks
GROUP BY INDEX_NAME, lock_type, LOCK_MODE;

결과 row수는 2개였지만 index record의 갯수 만큼 X락이 걸려있고 추가로 GAP락이 1row걸려있는걸 확인 할 수 있다.

복합인덱스 중 하나의 컬럼만으로 where 조회

# first_name, middle_name = 복합 index

# 실행 계획
EXPLAIN SELECT * FROM sakila.bulk WHERE first_name = 'TestFirstName' FOR UPDATE;

# 테스트 코드
start TRANSACTION;

# COUNT = 50
SELECT COUNT(*) FROM sakila.bulk WHERE first_name = 'TestFirstName';

SELECT * FROM sakila.bulk WHERE first_name = 'TestFirstName';

ROLLBACK;

실행계획

복합 인덱스 중에 하나를 사용했지만 사용한 컬럼(first_name)의 인덱스 만큼 row수를 읽는 걸 확인 할 수 있다.

Lock 범위

# Lock 범위 조회 SQL
SELECT * FROM performance_schema.data_locks;

SELECT INDEX_NAME, lock_type, LOCK_MODE, COUNT(*)
FROM performance_schema.data_locks
GROUP BY INDEX_NAME, lock_type, LOCK_MODE;

복합인덱스 중 사용한 컬럼의 인덱스 record만큼 X락이 걸려있는 걸 확인 할 수 있다.

한 줄평 : 중요한건 인덱스를 사용하면 인덱스 만큼 lock을 건다는 것인거 같다.