Record Lock, Gap Lock, Next Key Lock

 

출처 : RealMySQL

 

레코드 락

레코드 자체를 잠그는 락을 레코드 락(Record Lock, Record Only Lock) 이라 부릅니다.

 

InnoDB 스토리지 엔진은 레코드 자체를 잠그는 대신 인덱스의 레코드를 잠그는 특성이 있습니다. 만약 테이블이 인덱스 없이 정의된 경우에도 숨겨진 클러스터링 인덱스(GEN_CLUST_INDEX)를 생성하여 해당 인덱스 레코드에 락을 겁니다. 6바이트의 단순 증가 필드를 추가해 데이터가 삽입되는 순서대로 정렬됩니다.

 

갭 락

갭 락은 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격 만을 잠그는 것을 의미합니다.

 

갭 락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 삽입(INSERT) 되는 것을 제어하기 위함입니다.

 

넥스트 키 락

넥스트 키 락은 레코드 락과 갭 락을 합쳐 놓은 형태의 잠금을 부릅니다.

 

STATEMENT 포맷의 바이너리 로그를 사용하는 MySQL 은 REPEATABLE READ 격리 수준을 사용해야 하며 innodb_locks_unsafe_forbinlog 시스템 변수가 비활성화되면 변경을 위한 검색 레코드는 네스트 키 락 방식으로 잠금이 걸립니다.

참고로 InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주목적이라고 합니다.

 

넥스트 키 락은 쿼리에 s_lock, x_lock 을 설정할 경우 설정되는 옵션으로 파악됩니다. 내용만으로는 이해가 되지 않아 샘플 테이블과 데이터를 추가해서 확인해보았습니다. 참고로 transaction isolation level 은 default value 인 REPEATABLE_READ 이며 확인 방법은 아래와 같습니다.

-- (1) transaction isolation level 확인
SHOW VARIABLES LIKE 'transaction_isolation';

-- (2) 샘플 테이블 생성
CREATE TABLE IF NOT EXISTS member (
    id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    CONSTRAINT testTable_PK PRIMARY KEY(id)
);

-- (3) 샘플 데이터 추가
INSERT INTO member VALUES (1, 'user_1');
INSERT INTO member VALUES (2, 'user_2');
INSERT INTO member VALUES (3, 'user_3');
INSERT INTO member VALUES (4, 'user_4');
INSERT INTO member VALUES (5, 'user_5');

INSERT INTO member VALUES (11, 'user_11');
INSERT INTO member VALUES (12, 'user_12');
INSERT INTO member VALUES (13, 'user_13');
INSERT INTO member VALUES (14, 'user_14');
INSERT INTO member VALUES (15, 'user_15');

 

 

넥스트 키 락 - 테스트1. 단순 조회

첫 번째로 락을 설정하지 않은 쿼리를 비교해보면 transaction isolation level 에 따라 각 트랜잭션의 고립 상태를 보장합니다. 하지만 다른 트랜잭션의 BETWEEN 절 사이에 값이 추가되는 것으로 보아 일반 조회에서는 해당하지 않은 것 같습니다.

첫 번째 테스트. 단순 조회 비교

 

넥스트 키 락 - 테스트2. 락 설정을 통한 조회

두 번째로는 공유 락(s_lock) 을 설정한 쿼리를 통해 비교해보겠습니다. s_lock 을 설정한 쿼리의 경우에는 다른 트랜잭션이 값을 추가하고자 하는 경우에는 lock 으로 인해 현재 트랜잭션이 무한정 대기하다가 트랜잭션이 커밋될 경우 락이 해제됩니다. 해당 테스트를 통해서 범위 조회에 락을 제공하기 위한 내용으로 파악됩니다.

 

 

조금 더 명확한 확인을 위해 INNODB ENGINE 상태를 확인해보면 BETWEEN 를 기준의 레코드 범위에 락이 설정되는 것을 확인할 수 있습니다. 이번 테스트를 통해 넥스트 키 락은 s_lock, x_lock 을 설정할 경우에 범위 검색을 위해 제공되는 락임을 확인할 수 있었습니다.

SHOW ENGINE INNODB STATUS;

 

결론

• transation isolation level 은 각각의 트랜잭션의 고립성을 보장하기 위해 제공하는 기능이다.
• next key lock 은 범위 검색에 s_lock, x_lock 을 설정 시에 제공되는 기능이다.

(혹시 잘못된 내용이 있다면 댓글 부탁드립니다 🙏)

 

Reference

• Real MySQL 1권 : https://product.kyobobook.co.kr/detail/S000001766482
• MySQL reference : https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html