InnoDB스토리지 엔진 수준의 락

 

InnoDB 스토리지 엔진은 레코드 기반의 잠금 기능을 제공합니다. 이 덕분에 MyISAM 보다 뛰어난 동시성 처리를 제공합니다.

또한, 잠금 정보가 상당히 작은 공간에서 관리되기 때문에 레코드 락이 페이지 락 또는 테이블 락으로 레벨업되지 않는 특징이 있습니다.

 

InnoDB 스토리지 엔진에서는 레코드 락, 갭락, 넥스트 키락을 제공합니다.

 

이번 포스팅에서는 레코드 락과 갭 락 그리고 넥스트 키 락에 관해 다뤄보겠습니다.

 

레코드 락(Record Lock)

 

일반적으로 레코드 락(record lock)은 테이블의 특정 레코드를 잠그는 기능으로 이해됩니다.
그러나 MySQL의 레코드 락은 테이블의 레코드를 직접 잠그는 것이 아니라, 인덱스의 레코드를 잠급니다.

이 개념을 이해하기 위해 특정 레코드를 수정하는 상황을 살펴보겠습니다.

1. 인덱스를 사용하는 경우
   
   특정 레코드를 수정하기 위해 조회시 인덱스를 활용했다면, MySQL은 해당 레코드뿐만 아니라 조회에 사용된 인덱스의 레코드를 잠금을 수행합니다. 이는 동일한 인덱스를 통해 접근할 수 있는 다른 레코드들에도 잠금이 적용된다는 뜻입니다.
   
   
2. 테이블을 풀 스캔하는 경우
   
   인덱스를 사용하지 않고 테이블을 풀 스캔(full table scan)하면, 스캔된 모든 레코드에 잠금이 걸립니다.

즉, MySQL의 레코드 락은 조회 방식에 따라 잠금의 범위가 달라집니다. 따라서, 효율적인 잠금 관리를 위해 인덱스 설계가 매우 중요합니다.

 

“인덱스의 레코드를 잠근다” 이게 뭘까?

 

employees 테이블에 first_name 컬럼과 last_name컬럼이 존재하고, first_name 컬럼에만 인덱스가 걸려있다고 가정합니다.

 

 

 

데이터 조회 결과

• first_name이 'Georgi'인 레코드는 253개입니다.
• first_name이 'Georgi'이고 last_name이 'Klassen'인 레코드는 1개입니다.

 

 

 

UPDATE 쿼리와 레코드 락

WHERE 절에서 first_name 컬럼만 인덱스를 사용하므로, MySQL은 인덱스 레인지 스캔(index range scan)을 수행합니다.

• first_name에 설정된 인덱스를 통해 접근 가능한 레코드는 253개입니다.
• 따라서, 253개의 레코드에 레코드 락이 걸립니다.

인덱스가 없는 경우

만약 first_name 컬럼에 인덱스가 없었다면, MySQL은 테이블 전체를 풀 스캔(full table scan)하여 UPDATE 작업을

수행했을 것입니다. 이 경우, employees 테이블의 모든 레코드가 잠금 대상이 됩니다.

레코드를 잠근다

 

이번엔 같은 레코드에 다른 커넥션에서 수정 쿼리를 연속으로 날려보겠습니다.

 

 

3개의 쿼리 모두 where절이 emp_no이기때문에 같은 레코드를 update하는 쿼리입니다. emp_no 레코드는 레코드 락이 걸리기 때문에 커넥션 1의 쿼리가 끝나기 전까지 커넥션 2, 커넥션 3의 쿼리는 대기 상태입니다. 따라서, 커넥션 1의 쿼리가 끝나고 commit 된 이후에야 커넥션 2의 쿼리가 실행될 수 있습니다.

갭 락(Gap Lock)

 

갭 락(Gap Lock)은 레코드가 아닌 인덱스 레코드와 인덱스 레코드 사이의 간격을 잠금으로써 레코드 간격에 새로운 레코드가 생성되는것을 제어합니다.

아래 그림인 상황에서 해당 쿼리를 실행시킨다고 가정해봅시다.

 

 SELECT * FROM EMPLOYEES WHERE id BETWEEN 1 AND 3 FOR UPDATE;

 

이렇게 할 경우 “레코드가 없는 빈 공간”에 갭 락이 걸리게 됩니다.

 

이렇듯 갭 락은 아직 존재하지는 않지만, 지정된 범위에 해당하는 인덱스 테이블 공간을 대상으로 거는 잠금입니다.

Gap Lock은 단독으로 사용되기 보다는 아래의 Next Key Lock과 함께 사용됩니다.

 

Gap Lock이 필요하지 않은 경우

 

갭 락은 고유 인덱스를 사용하여 특정 행을 검색하는 경우 필요하지 않습니다. 왜? 고유 인덱스를 사용할 경우 필요하지 않을까요?

 

Gap Lock의 역할은 다른 트랜잭션이 해당 갭에 데이터를 삽입하지 못하도록 막아 SELECT 쿼리의 일관성을 유지하는 것입니다.

SELECT 쿼리의 일관성이 깨질 가능성이 있는 경우는 여러 행을 조회하는 SELECT 쿼리입니다. 하지만 고유 인덱스(Unique Index) 를 사용하면, 해당 인덱스는 중복된 값을 가질 수 없으므로 항상 하나의 레코드만 조회됩니다.

 

즉, 고유 인덱스를 활용한 조회에서는 단 하나의 특정 행만 검색되므로 갭 락이 필요하지 않습니다.

 

 

넥스트 키 락(Next Key Lock)

 

Next-Key Lock은 인덱스 레코드에 대한 레코드 락과 해당 인덱스 레코드 앞의 갭에 대한 갭 락이 결합된 형태입니다.

예를 들어 아래와 같은 상황에서 넥스트 키 락이 걸리면 (1, 3], (3, 6) 구간에 넥스트 키 락이 걸립니다.

해당 구간에 락이 걸려 있으므로, 같은 트랜잭션 내에서 읽은 범위에 새로운 데이터가 삽입되어 결과가 달라지는 Phantom Read 문제를  방지할 수 있습니다.

 

 

 

 

출처

RealMySQL 8.0
https://dev.mysql.com/doc/refman/8.4/en/innodb-locking.html#innodb-shared-exclusive-locks