본문으로 건너뛰기

InnoDB 스토리지 엔진의 잠금

· 약 6분

InnoDB 스토리지 엔진의 잠금​

MySQL에서 제공하는 잠금과 별개로 스토리지 엔진 내부에서 로우 단위의 잠금을 지원한다.
보통 명시적으로 잠금을 사용하는 경우는 드물고, 격리 수준에 따라 묵시적으로 잠금이 사용된다.

동시성 제어 방식에는 낙관적인 방식과 비관적인 방식이 있다.
InnoDB는 기본적으로 MVCC(다중 버전 동시성 제어)를 통해 낙관적인 방식을 사용하고 락을 통해 특정 상황에서 비관적인 방식을 사용한다.

낙관적 동시성 제어(OCC, Optimistic concurrency control)

트랜잭션이 서로 충돌하지 않는다고 가정하는 방식

비관적 동시성 제어(PCC, Pessimistic Concurrency Control)

트랜잭션이 충돌하는 가정하에 잠금을 거는 방식
일반적으로 Shared Lock, Exclusive Lock을 통해 이를 구현한다.

Shared & Exclusive Locks​

InnoDB는 로우 단위의 잠금을 수행할 때 공유 잠금과 배타적 잠금을 사용한다.

공유 잠금(S, shared lock)

데이터 조회를 위한 락, 읽기 잠금(read lock)으로도 불린다.
다른 트랜잭션에서 읽기가 가능하지만, 쓰기는 불가능하다.
예) SELECT * FROM table_name WHERE id = 1 LOCK IN SHARE MODE;

배타적 잠금(X, exclusive lock)

데이터 변경을 위한 락, 쓰기 잠금(write lock)으로도 불린다.
락을 건 트랜잭션만이 해당 데이터에 접근 가능하다. 다른 트랜잭션의 경우 읽기, 쓰기가 불가능하다.
예) SELECT * FROM table_name WHERE id = 1 FOR UPDATE;

Intention Locks​

InnoDB는 로우 단위 잠금과 테이블 잠금의 공존을 위해 인텍션 잠금을 지원한다.
테이블에 있는 로우에 대해서 나중에 요청되는 것이 어떤 형태의 잠금인지 가리키기 위해 사용된다.
기본적으로 로우 단위 잠금을 수행하기 전에 인텐션 잠금을 먼저 획득한다.
인텐션 락은 기본적으로 충돌을 방지하고 데드락을 방지하는 역할을 한다.

인텐션 공유 잠금(IS, intention shared lock)

트랜잭션이 테이블의 개별 로우에 대한 공유 잠금을 수행하는 것을 의미한다.

인텐션 배타적 잠금(IX, intention exclusive lock)

트랜잭션이 테이블의 개별 로우에 대한 배타적 잠금을 수행하는 것을 의미한다.

** 잠금간의 호환성 **

XIXSIS
XConflictConflictConflictConflict
IXConflictCompatibleConflictCompatible
SConflictConflictCompatibleCompatible
ISConflictCompatibleCompatibleCompatible

Record Locks​

레코드 자체만을 잠그는 락이다.
InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다.

Gap Locks​

레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 락이다.
레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어하고, 넥스트 키 락의 일부로 사용된다.

Next-Key Locks​

레코드 락과 갭 락을 합쳐놓은 형태의 잠금으로 레코드와 그 레코드 앞의 갭 락을 포함한다.
REPEATABLE READ 격리 수준에서 팬텀 리드를 방지하기 위한 잠금이다.

AUTO-INC Locks​

AUTO_INCREMENT 칼림이 사용된 테이블에 동시에 여러 레코드가 INSERT되는 경우, 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 한다.
InnoDB 는 내부적으로 AUTO-INC 락이라고 하는 테이블 수준의 잠금을 사용한다.
트랜잭션과 관계 없이 INSERT나 REPLACE 문장에서 AUTO_INCREMENT 값을 가져오는 순간만 락이 걸렸다가 해제된다.

잠금 예시​

-- 레코드는 id 기준 10, 20, 30, 40, 50이 있다고 가정
-- Record Locks: 10에 대해 락이 걸린다.
SELECT * FROM table_name where id = 10 for update;

-- Gap Locks: 51부터 PositiveInfinity까지 락이 걸린다.
SELECT * FROM table_name where id > 100 for update;

-- Next-Key Locks: 21부터 30, 31부터 40에 락이 걸린다.
SELECT * FROM table_name where id BETWEEN 25 AND 35 for update;

참고 자료​

Real My SQL 8.0 - 5장 트랜잭션과 잠금, 백은빈, 이성욱
Optimistic and Pessimistic record locking, IBM
MySQL Innodb Locks, cecil1018
MySQL 8.0 InnoDB Locks, MySQL
Locks Set by Different SQL Statements in InnoDB, MySQL