[MySQL] 스토리지 엔진 수준의 락의 종류(레코드 락, 갭 락, 넥스트 키 락, 자동 증가 락)

데이터베이스

[MySQL] 스토리지 엔진 수준의 락의 종류(레코드 락, 갭 락, 넥스트 키 락, 자동 증가 락)

망나니개발자 2023. 5. 30. 10:00

이번에는 스토리지 엔진 수준의 락의 종류에 대해 알아보도록 하겠습니다. 아래의 내용은 RealMySQL과 MySQL 공식 문서 등을 참고하여 작성하였으며, 모든 내용은 InnoDB를 기준으로 설명합니다.

1. 스토리지 엔진 수준의 락의 종류

MySQL에서 사용되는 락은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다. 스토리지 엔진 레벨의 잠금은 테이블의 데이터를 다루기 위한 락이며, MySQL 엔진 레벨의 잠금은 테이블이나 데이터베이스 등과 같은 부분을 위한 락에 해당한다. 그리고 이번에 살펴볼 잠금은 스토리지 엔진 레벨의 잠금이다.

레코드 락(Record Lock)
갭 락(Gap Lock)
넥스트 키 락(Next Key Lock)
자동 증가 락(Auto Increment Lock)

[ 레코드 락(Record Lock) ]

일반적으로 레코드 락은 테이블 레코드 자체를 잠그는 락을 의미한다. 레코드 수준의 잠금은 상당히 작은 공간으로 관리되기 때문에 레코드 락이 페이지 락으로, 또는 테이블 락으로 레벨업되는 경우(락 에스컬레이션)는 없다. 하지만 MySQL에서의 레코드 락은 테이블의 레코드가 아닌 인덱스의 레코드를 잠근다는 점에서 중요한 차이가 있다.

인덱스 포스팅에서 설명하였듯, MySQL에서 인덱스와 테이블은 별도의 자료구조로 관리되는데, MySQL에서는 아래의 그림과 같이 인덱스에 락이 걸린다. 참고로 여기서 락이 걸리는 인덱스는 클러스터 인덱스(PK) 및 논클러스터 인덱스(세컨더리 인덱스) 모두를 포함한다. 만약 PK가 없는 테이블이라면 내부적으로 자동 생성된 PK를 이용해 설정한다.

인덱스 레코드에 락을 거는 것과 테이블 레코드에 락을 거는 것에는 큰 차이가 있다.

예를 들어 member 테이블에 성(last_name)이 J로 시작하는 구성원이 300명이 있다고 하자. 그리고 성(last_name)이 J로 시작하며 이름(first_name)이 MangKyu인 사원은 1명만 존재한다고 하자.

# member 테이블에서 last_name이'J'로 시작하는 구성원은 300명이다.
SELECT COUNT(*) FROM member WHERE last_name LIKE 'J%';

# 그 중에서 first_name이 MangKyu인 사원은 1명만 있다.
SELECT COUNT(*) FROM member WHERE last_name LIKE 'J%' AND first_name = 'MangKyu';

그리고 이때 성(last_name)에만 인덱스가 걸려있는 경우에, 성이 J로 시작하며 이름이 MangKyu인 구성원의 등록일을 변경하는 UPDATE 쿼리를 실행한다고 하자.

# member 테이블에는 last_name 컬럼만으로 구성된 인덱스 KEY idx_last_name(last_name)가 존재한다.
# 해당 구성원의 등록일을 오늘로 변경하는 쿼리를 실행해보자.
UPDATE member SET register_date = NOW() WHERE last_name LIKE 'J%' AND first_name = 'MangKyu';

UPDATE 문에 의해 영향받는 레코드는 1건이다. 하지만 1건을 업데이트 하기 위해 300건의 인덱스 레코드에 잠금이 걸린다. 왜냐하면 MySQL은 테이블 레코드가 아닌 인덱스에 잠금을 걸기 때문이다. 인덱스는 성(last_name)으로만 구성되어 있기 때문에, 해당 레코드를 갱신하기 위해서는 인덱스를 통해 검색되는 모든 레코드에 잠금을 걸게 된다.

만약 적당한 인덱스가 없다면 모든 테이블의 레코드에 락을 걸고, 테이블을 풀스캔 하면서 작업을 처리하게 된다. 그러면 동시성이 상당히 떨어지게 되므로, 특히 MySQL에서 인덱스의 설계는 중요하다.

이렇듯 레코드 락은 트랜잭션이 DML 구문을 실행할 때 자동으로 거는 락이며, 레코드 락 덕분에 여러 트랜잭션이 동시에 서로 다른 레코드에 접근할 수 있는 것이다.

[ 갭 락(Gap Lock) ]

레코드 락이 테이블이 아닌 인덱스에 걸린다는 점을 포함하여, 다른 DBMS와 차이나는 부분이 바로 갭 락이다. 갭 락(Gap Lock)은 레코드가 아닌 레코드와 레코드 사이의 간격을 잠금으로써 레코드의 생성, 수정 및 삭제를 제어한다.

예를 들어 현재 성이 J로 시작하는 레코드가 Jo, Joe 2개가 있다고 하자. 그리고 언제든지 다른 데이터들 ex) Jang, Jeong, Jung 이 추가될 수 있다. 따라서 현재 트랜잭션에서 조회를 할 때, 다른 트랜잭션에서 임의의 데이터가 추가되지 않도록 잠그려면 아래와 같은 쿼리를 실행해야 한다. 여기서 SELECT … FOR UPDATE 구문은 베타적 잠금(비관적 잠금, 쓰기 잠금)을 거는 것이다. 읽기 잠검을 걸려면 LOCK IN SHARE MODE 구문을 사용해야 한다. 락은 트랜잭션이 커밋 또는 롤백될 때 해제된다.

SELECT * FROM member WHERE last_name LIKE "J%" FOR UPDATE;         // 쓰기 잠금(베타락)
SELECT * FROM member WHERE last_name LIKE "J%" LOCK IN SHARE MODE; // 읽기 잠금(공유락)

갭 락은 인덱스 범위 조건 중에서 실제 레코드를 제외하고, 데이터가 추가될 수 있는 범위에 걸리게 된다. 이러한 구조를 그림으로 표현하면 다음과 같다. 인덱스는 정렬된 순서로 존재하므로, 현존하는 레코드의 앞 뒤에 갭 락이 걸린다.

더욱 쉬운 사례로 살펴보도록 하자. 예를 들어 num 테이블에 2, 3이라는 2개의 인덱스 레코드가 존재한다고 하자. 이때 테이블에서 1이상 5이하의 조건으로 데이터를 검색한다면, 현존하는 레코드인 2와 3에 걸리는 락이 바로 레코드 락이다. 그리고 아직 실존하지 않는 1과 4, 5가 추가될 수 있는 공간에 걸리는 락이 갭 락인 것이다.

즉, 갭 락은 아직 존재하지는 않지만 지정된 범위에 해당하는 인덱스 테이블 공간을 대상으로 거는 잠금이다. 따라서 데이터의 유일성이 보장되는 프라이머리 키(PK) 또는 유니크 인덱스에 의한 작업에서는 갭 락이 사용되지 않는다. 그리고 이러한 갭 락은 뒤에서 살펴볼 Pantom Read(유령 읽기)를 방지하는데 도움이 된다.

[ 넥스트 키 락(Next Key Lock) ]

넥스트 키 락(Next Key Lock)이란 레코드 락과 갭 락을 합친 잠금으로, 앞서 살펴본 갭 락은 단독으로 사용되기 보다는 넥스트 키 락의 일부로 함께 사용된다.

갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 리플리카 서버에서 실행될 때 소스 서버에서 만들어낸 결과와 동일한 결과를 만들어내도록 보장해주는 것이 주목적이라고 한다. 그런데 의외로 넥스트 키 락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션이 기다리는 일이 자주 발생하므로, 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋다고 한다.

[ 자동 증가 락(Auto Increment Lock) ]

MySQL은 자동 증가하는 숫자값을 채번하기 위해 AUTO_INCREMENT라는 컬럼 속성을 제공하며, 이는 주로 대체키에 사용된다. AUTO_INCREMENT 컬럼은 여러 레코드가 동시에 INSERT 되더라도 중복되지 않고 순차적으로 증가하는 일련번호를 제공하기 위해 내부적으로 테이블 수준의 잠금인 자동 증가 락(Auto Increment Lock)을 사용한다.

해당 락은 INSERT와 REPLACE와 같이 새로운 레코드를 저장하는 쿼리에서만 사용된다. 또한 트랜잭션과 관계없이 INSERT나 REPLACE 문장에서 AUTO_INCREMENT 값을 가져오는 순간에 락이 걸린다. 자동 증가 락은 테이블에 1개만 존재하기 때문에, 한 쿼리에서 락을 획득하여 채번중이라면 다음 쿼리는 락을 대기해야 한다. 하지만 아주 짧은 순간만 걸렸다가 즉시 해제되므로 대부분의 경우 문제가 되지 않는다.

자동 증가 락은 잠금을 최소화하기 위해 한 번 증가하면 절대 자동으로 줄어들지 않는다. 그리고 앞서 설명하였듯 이는 트랜잭션과도 무관하다. 따라서 자동 증가값 채번에 성공하였지만, 이후의 쿼리에서 실패하여 트랜잭션이 롤백되어도 자동 증가값은 복구되지 않고 그대로 남는다. 만약 해당 값을 초기화하려면 아래의 쿼리를 사용해야 한다.

ALTER TABLE tablename AUTO_INCREMENT = 1

이번에는 스토리지 엔진 수준의 락의 종류에 대해 알아보았습니다. 위의 내용을 알아야 하는 이유는 트랜잭션 격리 수준(Isolation Level)을 완벽하게 이해하기 위함입니다. 다음 포스팅에서는 트랜잭션 격리 수준에 대해 자세히 알아보도록 하겠습니다.