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)
레코드가 아닌 레코드와 레코드 사이의 간격을 잠금으로써 레코드의 생성, 수정 및 삭제를 제어한다.
📍 갭 락은 인덱스 범위 조건 중 실제 레코드를 제외하고, 데이터가 추가될 수 있는 범위에 걸린다.
위 테이블에서 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개만 존재한다.