MySQL(InnoDB)이 잠금할 레코드를 고르는 방법

MySQL 을 처음 공부할 때, 레코드 잠금에 관해서 여러가지 궁금한 점들이 많았다. 그동안 궁금했던 세부적인 부분들에 대해서 정리해보고자 한다.

MySQL 은 기본적으로 잠금 읽기(locking read), update, delete 문에 대해서 잠금을 걸 때, 스캔되는 모든 인덱스에 잠금을 건. WHERE 조건에 특정 행(row)를 제외할 수 있다고 하더라도, 이와 관계 없이 스캔되는 모든 인덱스에 잠금을 걸게 된다.

Q. WHERE 절을 기반으로 스캔되는데, 왜 WHERE 절과 관계 없이 레코드 잠금이 걸린다고 할까?

참고로, 이 내용은 REPEATABLE READ 에 해당하는 내용으로, READ COMMITTED 에서는 다르게 동작한다.

 

WHERE 절을 기반으로 레코드를 조회하는 것은 맞다.

하지만, MySQL 에서 레코드를 조회하기 위해

1. 인덱스를 통해 먼저 스캔을 하고,
2. 이를 기반으로 레코드를 찾는다.

이를 이해하기 위해 아래의 예를 살펴보자.

CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB;

UPDATE t SET b = 3 WHERE b = 2 AND c = 3;

위 테이블이 레코드를 스캔하는 방식에 대해서 생각해보면, 아래와 같다.

1. b 인덱스 테이블을 통해 b = 2 인 행을 가져온다. (스캔 대상)
2. b 인덱스 테이블의 말단 노드에 있는 pk 를 기반으로 clustered index 테이블을 검색한다.
3. clustered index 테이블을 조회하면서, c = 3 인 행을 가져온다. ( WHERE 조건으로 필터 )

즉, c = 3 이라는 조건이 있지만 실제 인덱스를 통해 스캔하는 대상은 b = 2 에 해당하는 모든 레코드이다. 따라서, b = 2 인 레코드 전부가 잠금에 걸린다.

즉, MySQL 에서 인덱스에 잠금을 건다는 말은 레코드를 가져오기 위해 인덱스를 스캔하는 과정에서 매칭되는 row 들이 잠금에 걸린다고 볼 수 있다.

Q. 위 내용이 READ COMMITTED 에서도 동일할까?

아래는 MySQL 의 공식문서에서 READ COMMITTED 에 대한 내용이다.

For UPDATE or DELETE statements, InnoDB holds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. This greatly reduces the probability of deadlocks, but they can still happen.

READ COMMITTED 는 update 나 delete 가 수행될 때, 매칭되지 않는 레코드들에 대해서는 평가 후에 바로 락을 해제해버린다.

이를 확인하기 위해서는, performance_schema 스키마의 data_locks 테이블을 통해 확인할 수 있다.

실제로 확인해보면, 매칭되지 않는 레코드들은 락이 걸려있지 않으며, 락 대상인 경우 인덱스와 pk 모두 락이 걸린다.

 

Q. 보조 인덱스로 검색하는 경우, 보조 인덱스에만 락이 걸리나요?

“인덱스에 락이 걸린다” 라는 말을 들었을 때는, 보조 인덱스에만 락이 걸린다는 걸까? 그러면 다른 인덱스를 통해 접근하면 락을 걸어도 동시 수정이 가능할거 같은데? 라는 생각이 들었다.

위 질문에 대한 정답은 “보조 인덱스와 클러스터드 인덱스 모두 잠금에 걸린다” 이다.

예를들어, 아래와 같이 update, delete 문이 있을 때를 가정해보자.

// b 에 인덱스 
UPDATE ... WHERE b = 2
DELETE FROM ... WHERE b = 2

위와 같은 update, delete 문의 경우, 보조 인덱스가 사용되는 경우 보조 인덱스와 클러스터드 인덱스 모두 잠금에 걸린다. 즉, 칼럼 b = 2 인 레코드들이 보조 인덱스 쪽에도 잠금이 걸리고 클러스터드 인덱스에도 잠금이 걸린다.

Q. 보조 인덱스에 걸리지 않는 경우는 어떻게 되나요? (클러스터드 인덱스)

// b 는 유니크 인덱스
UPDATE ... WHERE b = 2
DELETE FROM ... WHERE b = 2

• unique 인덱스가 있는 경우

당연하게도, 유니크 인덱스의 경우 보조 인덱스에 속하고, 해당 레코드가 유일하기 때문에 당연히 하나의 레코드만 잠금에 걸리게 된다. update, delete 문이 있는 경우 WHERE 절에 unique 인덱스가 있는 경우 해당 레코드만 잠금에 걸린다.

• 인덱스가 없는 경우

이 질문은 보조 인덱스가 아예 없는 경우에 해당하는 것인데, 이 경우 격리수준에 따라 다르게 동작한다.

즉 read committed 와 repeatable read 가 동작이 다르다.

READ COMMITTED 사용시 update, delete 구문에서 잠금(lock) 과 관련해서 REPEATABLE READ와 다른 점이 있다.

이를 이해하기 위해서 인덱스가 없는 경우에 대해서 REPEATABLE READ의 동작에 대해서 조금 이해해야 한다.

아래 테이블이 있는 경우, Session A의 구문을 실행하면 어떤 레코드가 잠길까?

// 인덱스 없음
CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;

# Session A
START TRANSACTION;
UPDATE t SET b = 5 WHERE b = 3;

정답은 “전부 다 잠긴다” 이다.

MySQL은 세컨더리 인덱스가 없는 경우 클러스터 인덱스를 통해 직접 데이터를 읽게 된다. 그리고 update 문을 실행하면 아래 순서를 따른다.

1. 읽을 데이터에 잠금을 건다.
2. 데이터를 읽고 where 절과 매칭되는지 판단한다.

문제는 잠금을 걸면 REPEATABLE READ 는 트랜잭션이 종료될 때까지 락을 유지한다는 것이다.

반면, READ COMMITTED 를 사용하는 경우에는 where 절과 매칭되지 않는 경우 잠금을 해제한다. 따라서 아래의 구문을 실행해도 블락이 되지 않는다.

# Session B
UPDATE t SET b = 4 WHERE b = 2;

참고문서

https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html