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)은 레코드가 아닌 레코드와 레코드 사이의 간격을 잠금으로써 레코드 간격에 새로운 레코드가 생성되는것을 제어한다.

 

MySQL은 기본적으로 Repeatable Read 격리 수준을 지원합니다. 이 격리 수준은 다음 조건을 충족해야 합니다.

"하나의 트랜잭션에서 동일한 SELECT 쿼리를 실행했을 때 항상 같은 결과를 반환해야 한다."

 

갭 락은 이 조건을 만족시키기 위해 필요합니다. 간격에 대한 잠금을 통해 다른 트랜잭션에서 새로운 레코드를 추가하거나 변경하는 것을 방지하여, SELECT 쿼리 결과의 일관성을 유지합니다.

Gap Lock이 없다면, Repeable Read를 만족하지 못 한다.

 

 

 

트랜잭션 1 시작

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

 

트랜잭션 1은 id = 1과 id = 3 레코드에 대해 배타 락(exclusive lock)을 설정합니다.


트랜잭션 2에서 새로운 레코드 추가

INSERT INTO EMPLOYEES VALUES (2, 'SONNY');
COMMIT;

트랜잭션 2는 id = 2에 새로운 레코드를 삽입하고 이를 커밋합니다.갭 락이 적용되지 않았기 때문에 id = 1과 id = 3 사이의 간격에 대한 잠금은 설정되지 않아, 이 간격에 새로운 레코드가 추가될 수 있습니다.


트랜잭션 1에서 동일한 SELECT 실행

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

트랜잭션 1에서 동일한 SELECT 쿼리를 실행했을 때, 이전과 다른 결과가 반환됩니다.이는 id = 2 레코드가 중간에 삽입되었기 때문입니다.

위 예시에서, 트랜잭션 1은 id = 1과 id = 3 레코드에만 배타 락을 설정했지만, id = 2는 쿼리 시점에 존재하지 않았기 때문에 잠금에서 제외되었습니다. 결과적으로 트랜잭션 2가 id = 2를 삽입할 수 있었습니다.

갭 락은 이러한 상황을 방지하여, 트랜잭션 실행 중 동일한 SELECT 쿼리가 항상 일관된 결과를 반환하도록 보장합니다.

 

 

지금까지 InnoDB 스토리지에서 제공하는 레코드 락과 갭 락에 관해서 공부한 내용을 포스팅하였습니다.

잘못된 내용이나 조언해주실 부분이 있다면 댓글 남겨주시면 너무너무 감사하겠습니다! 😀

출처

RealMySQL 8.0

https://medium.com/daangn/mysql-gap-lock-%EB%8B%A4%EC%8B%9C%EB%B3%B4%EA%B8%B0-7f47ea3f68bc

MySQL Gap Lock 다시보기