MySQL - 스토리지 엔진 수준의 락

🔒 MySQL 의 락

MySQL에서 사용되는 락은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다.

 

🧩 스토리지 엔진 레벨의 락

• 테이블의 데이터를 다루기 위한 락

 

🧩 MySQL 엔진 레벨의 락

• 테이블이나 데이터베이스 등과 같은 부분을 위한 락

 

🆎 스토리지 엔진 레벨의 락의 종류

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

 

1️⃣ 레코드 락

🎯 레코드 락은 테이블 레코드 자체를 잠그는 락을 의미한다.

 

 

❗MySQL에서 레코드 락은 테이블의 레코드가 아닌 인덱스의 레코드를 잠근다는 점이 다른 DBMS와의 차이점이다.

• MySQL에서는 레코드 자체를 잠그는 것이 아닌 인덱스를 잠그기 때문에, 변경해야 할 레코드를 찾기 위해 검색한 모든 인덱스에 락을 걸어야 한다.

 

🏃 예시 - employee 테이블

CREATE TABLE employee (
    emp_id INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(64),
    last_name VARCHAR(64),
    hire_at TIMESTAMP,
    PRIMARY KEY(emp_id),
    INDEX idx_last_name (last_name)  // last_name(성)에 대해서만 인덱스가 걸려 있음
) ENGINE=InnoDB

 

UPDATE 
    employee 
SET 
    hire_at = NOW() 
WHERE 
    last_name = 'Ko'           // 300건
    AND first_name = 'Beomsic'  // 1건

 

last_name = ‘Ko’를 만족하는 레코드가 300건, first_name = ‘Beomsic’를 만족하는 레코드가 1건이라고 하자

 

last_name 은 인덱스가 존재하지만, first_name에 대해서는 존재하지 않는다.

 

따라서, 1건을 업데이트하기 위해서 300건의 인덱스에 락을 걸어야 한다.

• 만약, idx_last_name 인덱스조자 없는 상황이라면 UPDATE 쿼리를 위해서 테이블을 풀 스캔하면서 레코드를 갱신하게 된다.
• 이때, 테이블의 모든 레코드를 잠그게 된다.

레코드를 갱신하기 위해 인덱스를 통해 검색되는 모든 레코드에 잠금을 걸게 된다.

 

2️⃣ 갭 락(Gap Lock)

• MySQL이 다른 DBMS와 차이가 나는 또 다른 부분이 갭 락이다.

 

📖 갭 락(Gap Lock)

 

레코드가 아닌 레코드와 레코드 사이의 간격을 잠금으로써 레코드의 생성, 수정 및 삭제를 제어한다.

 

📍 갭 락은 인덱스 범위 조건 중 실제 레코드를 제외하고, 데이터가 추가될 수 있는 범위에 걸린다.

MySQL Gap Lock 다시보기

 

위 테이블에서 id 값이 1, 3, 6을 제외한 값은 언제든지 INSERT 될 수 있는 상태이다.

• MySQL은 이런 상태의 테이블에서 id = 1인 레코드와 id = 3인 레코드 사이의 간격과 id = 3과 id = 6 사이의 간격을 잠글 수 있다.

 

 

⭐ 이렇게 실제 존재하지 않는 레코드 공간을 잠그는 것을 MySQL 서버에서는 Gap Lock이라 한다.

 

 

🤔 왜 Gap Lock 이 필요할까?

3가지 목적을 위해서 사용한다.

 

1. Repeatable Read 격리 수준 보장

 

2. Replication 일관성 보장

• 복제 Source DB와 Replica 간 데이터 부정합을 막기 위함

 

3. Foreign Key 일관성 보장

 

 

🎯 Gap Lock은 다른 트랜잭션이 대상 간격(Gap)에 새로운 레코드가 INSERT되는 것을 막는 것이 주 목적이다.

 

 

🏃 예시 - employee 테이블

• 성이 K 로 시작하는 레코드가 2개 있다고 할 때, 다른 데이터들이 생성될 수 있다.

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

 

한 트랜잭션에서 조회를 할 때, 다른 트랙잭션에서 임의의 데이터가 추가되지 않도록 잠구기 위해서는 위의 쿼리를 실행해야 한다.

• 락은 트랜잭션이 커밋 또는 롤백될 때 해제된다.

 

3️⃣ 넥스트 키 락(Next Key Lock)

 

📖 넥스트 키 락

 

레코드 락과 갭 락을 합친 잠금

• 갭 락은 넥스트 키 락의 일부로 함께 사용된다.

 

🎯 목적

• 바이너리 로그에 기록되는 쿼리가 리플리카 서버에서 실행될 때 소스 서버에서 만들어낸 결과와 동일한 결과를 만들어내도록 보장하는 것

 

4️⃣ 자동 증가 락(Auto Increment Lock)

MySQL은 자동 증가하는 숫자값을 위해 AUTO_INCREMENT 라는 컬럼 속성을 제공한다.

 

AUTO_INCREMENT 컬럼

• 동시에 Insert 되더라도 중복되지 않고 순차적으로 증가하는 일련번호를 제공하기 위해 내부적으로 테이블 수준의 잠금인 자동 증가 락(Auto Increment Lock)을 사용한다.

 

 

📖 자동 증가 락(Auto Increment Lock)

 

INSERT 와 REPLACE 와 같이 새로운 레코드를 저장하는 쿼리에서만 사용한다.

 

또한 트랜잭션과 관계없이 INSERT, REPLACE 에서 AUTO_INCREMENT 값을 가져오는 순간 락이 걸린다.

• 자동 증가 락은 테이블에 1개만 존재한다.

 

📕 참고자료

[MySQL] B-Tree로 인덱스(Index)에 대해 쉽고 완벽하게 이해하기

MySQL Gap Lock 다시보기

[10분 테코톡] ⛲️ 오즈의 데이터베이스 Lock