티스토리 뷰
개요
InnoDB 스토리지 엔진은 MySQL 엔진 레벨의 락과 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있다. 스토리지 엔진 레벨의 락은 테이블의 데이터를 다루기 위한 락이며, 종류는 다음과 같다.
- 레코드 락
- 갭 락
- 넥스트 키 락
- 자동 증가 락
레코드 락(Record Lock)
레코드 자체만을 잠그는 것이 레코드 락(Record lock)이라고 하며, InnoDB 스토리지 엔진이 다른 상용 RDBMS와 다른 점은 인덱스의 레코드를 잠근다는 점에 차이가 있다. 인덱스가 하나도 없는 테이블이더라도 내부적으로는 자동 생성된 클러스터 인덱스를 이용해 잠근다. 즉, 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 건다.
last_name에만 인덱스가 걸려 있다고 가정하고 예시를 살펴보자.
-- employees테이블에 last_name이 L자로인 사원은 253명 있다.
mysql> SELECT count(*) FROM employees WHERE last_name LIKE 'L%'
+------+
| 253|
+------+
-- 그중 first_name이 "JungBin"인 사원은 1명 존재한다.
mysql> SELECT count(*) FROM employees WHERE last_name LIKE 'L%' and first_name = 'JungBin'
+------+
| 1|
+------+
-- 이때 해당 사원의 입사일자를 오늘 날로 변경하고자한다.
mysql> UPDATE employees SET hire_date=NOW() WHERE last_name LIKE 'L%' and first_name = 'JungBin'UPDATE쿼리가 실행되면 1건의 레코드가 업데이트 될 것이다. "이 1건의 업데이트를 위해 몇개의 레코드에 락을 걸게될까?"
현재 last_name컬럼에만 인덱스가 걸려 있게 때문에 인덱스를 통해 last_name LIKE 'L%' 해당하는 모든 레코드에 잠금을 걸게 된다.
"만약 이 테이블에 인덱스가 하나도 없다면 어떻게 될까?" 이럴 경우에는 모든 테이블의 레코드에 락을 걸고, 테이블을 풀스캔하면서 작업을 처리하게 된다. 이 경우 동시성이 상당히 떨어져서 한 세션에서 update작업을 하는 중에는 다른 클라이언트는 그 테이블을 업데이트하지 못하고 기다리는 상황이 될 것이다. 정리하자면 인덱스 설계가 동시성 측면에서도 영향을 줄 수 있다.
갭 락(Gap Lock)
다른 RDBMS와 또 다른 차이가 바로 갭 락이다. 갭 락은 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격(Gap)을 잠그는 것을 의미한다. 즉, 트랜잭션이 특정 범위의 데이터를 조회하는 동안 해당 범위 내에서 새로운 레코드가 INSERT되는 것을 방지하기 위한 잠금이다.
갭 락이 걸리는 예시를 살펴보자. SELECT ... FROM UPDATE 구문은 베타 락(X-LOCK)을 걸지만, 단순히 레코드만 잠그는 것이 아니라, 갭 락이 함께 적용될 수 있다.
SELECT * FROM employees WHERE last_name LIKE 'L%' FOR UPDATE;
갭 락이 발생하는 이유로는 팬텀 리드(Phantom Read)를 방지하기 위함이다. 해당 쿼리는 'L%'의 조건에 만족하는 결과를 조회하는데 다른 트랜잭션이 같은 범위의 데이터를 삽입하면 조회 결과가 달라질 있기 때문에 새로운 행 삽입을 방지하여 일관성을 유지한다.
MySQL InnoDB에서는 REPEATABLE READ 격리 수준에서 갭 락이 기본적으로 활성화된다.
넥스트 키 락(Next Key Lock)
갭 락은 단독으로 사용되기보다는 레코드 락과 함께 넥스트 키 락(Next-Key Lock)의 형태로 적용된다. 특히, InnoDB에서 STATEMENT 기반 복제 방식을 사용할 때 넥스트 키 락이 필요하다.
STATEMENT 모드에서는 SQL문 자체를 바이너리 로그에 기록하고, 레플리카 서버에서 동일하게 실행하는 방식이다. 그런데, 만약 갭 락 없이 레코드 락만 걸리면, 레플리카에서 실행 시 레코드의 추가/삭제 타이밍이 달라질 가능성이 있다. 이로 인해 마스터와 레플리카 간 데이터 불일치가 발생할 수 있기 때문에, 넥스트 키 락이 필요하다.
따라서, 가능하면 바이너리 로그 포맷을 ROW 형태로 변경하여 넥스트 키 락과 갭 락을 최소화하는 것이 좋다.
자동 증가 락(AUTO_INCREMENT Lock)
MySQL에서 자동 증가하는 숫자 값을 생성할 때 AUTO_INCREMENT 속성을 사용한다. 이 속성이 적용된 테이블에서 동시에 여러 개의 INSERT가 발생하면, 각 레코드가 중복되지 않고 순차적인 일련번호를 가져야 한다. 이를 위해 InnoDB 스토리지 엔진에서는 "AUTO_INCREMENT 락"이라는 테이블 수준의 잠금을 사용한다.
AUTO_INCREMENT락은 트랜잭션과 관계없이 INSERT, REPLACE문장과 같이 새로운 레코드를 저장하는 구문에서 AUTO_INCREMENT값을 가져오는 순간만 락이 걸렸다가 즉시 해제된다. 두 개의 INSERT 쿼리가 동시에 실행되는 경우, 하나의 쿼리가 AUTO_INCREMENT락을 걸고 나머지 하나의 쿼리는 락을 기다려야한다. 아주 짧은 시간 동안 걸렸다가 해제 되는 잠금이라서 대부분의 경우에는 문제가 되지 않는다.
현재까지 설명한 AUTO_INCREMENT락은 MySQL 5.0이하 버전에서 사용한 락 방식이다. MySQL 5.1이상부터는 innodb_autoinc_lock_mode라는 시스템 변수를 설정하여 해당 락 작동 방식을 변경할 수 있다.
-- MySQL 5.0이하 버전과 동일한 잠금 방식(모든 INSERT 문장이 자동증가 락 사용)
innodb_autoinc_lock_mode = 0
해당 자동증가 락 설정(mode=1)은 MySQL서버가 INSERT되는 레코드 건수를 정확히 예측할 수 있을 때, 자동 증가 락을 사용하지 않고, 훨씬 가볍고 빠른 래치(뮤텍스)를 이용해서 처리한다. 하지만 예측할 수 없는 경우 기존 AUTO_INCREMENT 락을 그대로 사용하여 동작한다.
-- 단순 INSERT에서는 AUTO_INCREMENT락 사용하지 않고 래치(뮤텍스) 사용 하지만 예측 불가능한 경우 기존 락 동작과 동일
innodb_autoinc_lock_mode = 1
mode=2에서는 모든 INSERT에서 AUTO_INCREMENT 락을 사용하지 않고, 경량화된 래치(뮤텍스)를 사용한다. 때문에 동시 처리 성능이 가장 뛰어나지만, 일부 환경에서는 AUTO_INCREMENT 값이 순차적으로 증가하지 않을 수도 있다. 그래서 이 설정 모드를 인터리빙 모드(Interleaved mode)라고도 한다.
innodb_autoinc_lock_mode = 2
참고 자료
- 백은빈, 이성욱, Real MySQL8.0(1), 2021.09.08 위키북스, 166p-175p
'Database' 카테고리의 다른 글
| 예제로 알아보는 MySQL 트랜잭션 격리 수준과 부정합 문제들 (0) | 2025.03.14 |
|---|---|
| 레디스가 원자성을 보장하는 방법 (0) | 2025.02.04 |