[SQL] MySQL InnoDB의 4가지 락(lock)

MySQL의 스토리지 엔진 레벨의 잠금에는 4가지 종류가 있다.

레코드 락(Record Lock), 갭 락 (Gap Lock), 넥스트키 락 (Next-key Lock), 자동증가 락(AUTO-INC Lock)

 

알아야 하는 이유

1) 데이터 정합성

트랜잭션은 여러 쿼리를 하나의 논리적 단위로 묶어 원자적으로 처리하기 위한 수단이다.

이 때 여러 트랜잭션이 동시에 같은 데이터를 다루면 경쟁 조건(race condition)이 발생할 수 있다. 락 (lock)은 바로 이런 경쟁 상황에서 데이터의 일관성과 정합성을 보장하는 핵심 도구이다. 만약 두 트랜잭션이 동시에 같은 재고 수량을 수정할 때, 락이 없다면 마지막에 커밋한 쪽만 반영되어 데이터에 유실이 발생할 것이다.

하지만 락이 걸려있다면 MySQL이 먼저 실행한 쿼리가 끝날 때까지 다른 트랜잭션이 대기하게 되어 정합성이 지켜지게 된다.

2) 데드락과 성능 병목 원인 파악

트랜잭션이 동시에 같은 리소스를 잠그려 할 때 데드락(DeadLock)이 발생한다.

어떤 쿼리가 락을 잡고, 누가 대기 중인지 이해하려면 MySQL이 어떤 단위로 락을 걸고 있는지 알아야 한다.

실제로 갭락이나 넥스트키 락은 SELECT ... FOR UPDATE (업데이트를 위해 먼저 락을 잡고 데이터를 읽는 문법) 시 예상보다 넓은 범위에 락을 걸 수 있는데, 이 사실을 모르면 조회인데 왜 락 대기 시간이 발생하는지 이해와 분석을 할 수 없을 것 이다.

락에 대해 이해하고 있다면 이 상황에서 SHOW ENGINE INNODB STATUS 와 같은 데드락 로그를 통해 락 충돌임을 파악 할 수 있게 된다.

3) 트랜잭션 격리 수준과 MVCC 이해

InnoDB의 트랜잭션 격리 수준 (기본 REPEATABLE READ)은 MVCC와 락 메커니즘을 함께 사용한다.

갭 락과 넥스트키 락은 REPEATBALE READ에서 Phantom Read (같은 조건의 select인데 새 행이 보이는 것)를 막기 위해 존재한다. 

4) 동시성 제어와 성능 최적화

어떤 쿼리에서 얼마나 큰 범위의 락을 잡는지를 이해하면 트랜잭션 간 병목을 최소화하도록 분석이 가능하고 쿼리를 재설계할 수 있다.

 

테스트를 위한 테이블과 데이터

CREATE TABLE people (
  id INT AUTO_INCREMENT PRIMARY KEY,
  age INT NOT NULL,
  name VARCHAR(50) NOT NULL,
  KEY idx_age (age)         -- 보조 인덱스(비유니크)
) ENGINE=InnoDB;

INSERT INTO people (age, name) VALUES
 (20, 'Ann'), (25, 'Bob'), (30, 'Cody'), (40, 'Duke');
-- 인덱스 순서 age: 20,25,30,40

idx_age 라는 보조 인덱스가 없다면 SELECT * FROM people WHERE age = 30; 와 같은 쿼리를 실행할 때 풀 스캔을 해버린다.

PK(id) 기본키에는 클러스터형 인덱스가 자동으로 생기지만, 보조 인덱스가 있다면 WHERE, ORDER BY, JOIN 등에서 성능 향상이 생긴다.

Secondary Index (idx_age on age)

Root
 ├── (24)
 │    └── [ (age=24, id=1), (age=24, id=3) ]
 ├── (30)
 │    └── [ (age=30, id=2) ]
 └── (40)
      └── [ (age=40, id=4) ]
      
 age로 정렬된 별도의 B+Tree 구조를 갖고 리프 노드에는 해당 age의 레코드가 가진 id값이 들어있음

SELECT * FROM people WHERE age = 24;
조회 시, 보조 인덱스 (idx_age)에서 age = 24에 해당하는 leaf node 탐색 후 
기본 인덱스의 B+Tree로 이동해서 id = 1, id = 3 실제 데이터를 가져옴.

1. 레코드 락 (Record Lock)

특정 인덱스 레코드 한 건에 걸리는 락. Primary key나 유니크 인덱스로 동등 비교(=) 검색 + FOR UPDATE 일 때 주로 해당 행만 잠금한다.

그래서 다른 트랜잭션이 해당 행을 수정하거나 삭제하는 것을 막는다.

// 트랜잭션 1
START TRANSACTION;
SELECT * FROM people WHERE id = 2 FOR UPDATE;  -- Bob 행만 '레코드 락'

// 트랜잭션 2 (동시)
UPDATE people SET name='Bobby' WHERE id = 2;   -- T1 커밋 전까지 대기(블로킹)
INSERT INTO people (id, age, name) VALUES (2, 27, 'X'); -- 유니크 위반으로 즉시 에러
INSERT INTO people (age, name) VALUES (27, 'Kim');      -- 가능(갭은 안 잠김)

이 경우 트랜잭션 1에서 COMMIT; 이 되어야 트랜잭션 2의 UPDATE가 진행된다.

id = 2에 대한 행만 수정/삭제가 막히고, 새로운 행의 INSERT는 막지 않는다.

2. 갭 락 (Gap Lock)

행과 행 사이의 빈 구간에 거는 락. 행 자체가 아니라 구간을 잠궈서, 그 구간에 새로 들어올 INSERT를 막는다.

REPEATABLE READ 격리 수준에서 범위 조건 + 인덱스를 사용할 때 필요에 따라 등장한다.

// 트랜잭션 1
START TRANSACTION;
-- age > 25 범위를 스캔 + 잠금 (인덱스: 30, 40, 그리고 (40, +∞)의 상한 갭까지 영향)
SELECT * FROM people WHERE age > 25 FOR UPDATE;

// 트랜잭션 2
INSERT INTO people (age, name) VALUES (35, 'Eve');  -- (30,40) 갭에 삽입 → '블로킹'
INSERT INTO people (age, name) VALUES (26, 'Foo');  -- (25,30) 갭에 삽입 → '블로킹'
INSERT INTO people (age, name) VALUES (22, 'Bar');  -- (20,25) 구간 → '비블로킹'(잠기지 않은 갭)

InnoDB는 인덱스 순서로 [25, 30), [30, 40), (40, +무한) 의 갭을 포함해 잠금을 설정한다.

이 역시 트랜잭션 1이 COMMIT; 되어야 트랜잭션 2에서 26, 35 에 대한 INSERT가 진행 된다.

3. 넥스트키 락 (Next-Key Lock)

레코드락 + 그 앞 갭을 합친 락. REPEATABLE READ 격리 수준에서 범위 스캔 시 기본으로 사용되어 Phantom Read를 방지하는데 사용된다. 결과적으로 지금 본 범위 안의 새로운 INSERT,  이미 있는 해당 레코드의 변경을 동시에 막는다.

// 트랜잭션 1

START TRANSACTION;
SELECT * FROM people
 WHERE age BETWEEN 25 AND 35
 FOR UPDATE;
-- 인덱스 레코드 25, 30을 만나며 각각의 앞 갭까지 포함한 '넥스트키 락'을 설정
-- 결과적으로 (20,25], (25,30], (30,35] 비슷한 범위가 보호되는 느낌

// 트랜잭션 2
UPDATE people SET name='B' WHERE age = 25;     -- 레코드 잠김 → 블로킹
INSERT INTO people (age, name) VALUES (28,'Z');-- (25,30) 구간 INSERT → 블로킹
INSERT INTO people (age, name) VALUES (24,'Y');-- (20,25) 바깥 구간이면 보통 OK

트랜잭션 1을 통해서 레코드 (25, 30)과 그 앞의 갭까지 잠겨서 해당 레코드에 대한 수정과 삭제, 그 구간에 대한 INSERT가 막힌다.

 

4. 자동증가 락 (AUTO-INC Lock)

AUTO_INCREMENT 값을 부여하는 순간에 짧게 잡는 특수한 테이블 수준의 락이다.

동시에 여러 INSERT가 들어와도 번호가 겹치지 않도록 보장한다. 쿼리 실행 동안만 잡혔다가 바로 풀리는 락이다.

트랜잭션이 만약 롤백되더라도, 이미 배정되었던 번호는 되돌리지 않는다.