콘텐츠로 이동
hyoguoo.log
Mysql

InnoDB Storage Engine Lock

- Last update:
hyoguoo
Backend Engineer

InnoDB 스토리지 엔진의 다른 MySQL 스토리지 엔진과는 다음과 같은 차이점이 있다.

  • 레코드 기반의 잠금(Row-level Lock)을 제공하여 높은 동시성 처리 가능
  • 잠금 정보를 효율적인 자료구조로 관리하므로 잠금의 개수가 많아져도 레코드 락이 페이지 락이나 테이블 락으로 확장되는 잠금 에스컬레이션(Lock Escalation)이 발생하지 않음

잠금의 기본 모드

섹션 제목: “잠금의 기본 모드”

InnoDB의 모든 레코드 수준 잠금은 기본적으로 공유(Shared) 모드와 배타적(Exclusive) 모드로 나뉜다.

  • 공유 잠금 (Shared Lock, S-Lock): 여러 트랜잭션이 동시에 동일한 레코드에 대해 공유 잠금을 가질 수 있음
    • 데이터를 읽는 것은 허용하지만, 다른 트랜잭션이 해당 레코드를 변경(배타적 잠금 획득)하는 것은 허용하지 않음
    • SELECT ... LOCK IN SHARE MODE 구문을 통해 획득
  • 배타적 잠금 (Exclusive Lock, X-Lock): 오직 잠금을 소유한 트랜잭션만이 해당 레코드를 읽고 변경할 수 있음
    • 배타적 잠금이 설정된 레코드는 다른 어떤 트랜잭션에서도 공유 잠금이나 배타적 잠금을 추가로 획득할 수 없음
    • SELECT ... FOR UPDATE, UPDATE, DELETE 구문을 통해 획득

레코드 락(Record Lock)

섹션 제목: “레코드 락(Record Lock)”

레코드 락은 인덱스의 레코드에 잠금을 설정하는 가장 단순한 형태의 잠금이다.

  • 동작 방식: 데이터 레코드 자체가 아닌, 해당 레코드를 가리키는 인덱스 엔트리를 잠금
    • 테이블에 인덱스가 없다면, InnoDB가 내부적으로 생성한 클러스터 인덱스(ROW_ID)를 사용해 잠금 설정
  • 잠금 범위: 쿼리가 어떤 인덱스를 통해 데이터에 접근하는지에 따라 잠금의 범위가 결정
    • 프라이머리 키나 유니크 인덱스를 통해 단일 레코드를 조회하고 잠그는 경우, 정확히 해당 인덱스 레코드 하나에만 잠금이 설정

레코드 자체 잠금 & 인덱스의 레코드 잠금 차이

섹션 제목: “레코드 자체 잠금 & 인덱스의 레코드 잠금 차이”

레코드 락은 테이블 레코드가 아닌 인덱스를 잠그는 방식으로 처리하기 때문에, 아래와 같은 상황을 주의해야 한다.

-- 테이블 정보
-- TABLE NAME: employees
-- KEY ix_firstname (first_name)
SELECT COUNT(*)
FROM employees; -- 300000


SELECT COUNT(*)
FROM employees
WHERE first_name = 'Kwon'; -- 253


SELECT COUNT(*)
FROM employees
WHERE first_name = 'Kwon'
  AND last_name = 'Ogu'; -- 1


UPDATE employees
SET hire_date = NOW()
WHERE first_name = 'Kwon'
  AND last_name = 'Ogu';
  • 위의 실행 문에서 UPDATE 문장은 단 한 건의 레코드만 변경
  • 하지만 이 문장의 조건에서 인덱스를 이용할 수 있는 조건은 first_name 컬럼 하나만 존재
  • 때문에 first_name 컬럼의 인덱스를 잠그게 되고, 이에 따라 first_name 컬럼의 값이 Kwon인 모든 레코드가 락 생성
  • 인덱스를 통해 스캔할 수 없는 상황에는 레코드 조회 시 테이블을 풀 스캔하면서 30만 건의 레코드 전부 락이 걸리게 됨

갭 락(Gap Lock)

섹션 제목: “갭 락(Gap Lock)”

레코드가 지정된 범위에 해당하는 인덱스 테이블 공간을 대상으로 거는 잠금으로, 실제 레코드가 아닌 구간(Gap)에 대해 설정된다.

  • 목적: 이 간격 내에 다른 트랜잭션이 새로운 데이터 INSERT 방지
  • 동작: 갭 락은 그 자체로 특정 레코드를 잠그는 효과는 없으며, 오직 새로운 항목의 삽입만 방지
    • 갭 락끼리는 서로 충돌하지 않으며, 여러 트랜잭션이 동일한 갭에 대해 갭 락을 동시에 보유 가능
-- 51 ~ 55 사이의 레코드에 대해 베타적 잠금 획득하는 쿼리
SELECT *
FROM member
WHERE 51 <= age
  AND age <= 55
    FOR
UPDATE;

age 52 / 53을 가진 레코드가 있을 때, 위 쿼리가 실행된다면, 실제 존재하는 레코드 뿐만 아니라, 51과 55 사이의 공간에 대해 갭 락이 걸리게 된다.

agePK잠금 여부
………
5059X
갭 락(51 ~ 52 사이의 공간)
5261레코드 락
5362레코드 락
갭 락(53 ~ 55 사이의 공간)
5665X

넥스트 키 락(Next-Key Lock)

섹션 제목: “넥스트 키 락(Next-Key Lock)”

넥스트 키 락은 레코드 락과 갭 락을 합친 형태로, InnoDB의 기본 격리 수준인 REPEATABLE READ에서 팬텀 리드(Phantom Read)를 방지하기 위한 핵심적인 메커니즘이다.

  • 특정 인덱스 레코드에 대한 레코드 락 + 해당 인덱스 레코드 이전의 간격에 대한 갭 락
  • 특정 레코드와 그 앞뒤 간격을 포함하는 범위에 대해 (prev, current] 방식으로 잠금 설정

즉, 쿼리에서 참조한 인덱스 레코드뿐만 아니라 그 이전과 다음 레코드와의 갭까지 함께 잠금이 걸리므로, 조회된 범위 내에 새로운 레코드가 삽입되는 것을 차단하는 데 사용된다.

예시

섹션 제목: “예시”
agePK
50101
52102
56103

age 컬럼에 대한 인덱스를 가진 member 테이블이 있고, 다음과 같은 쿼리를 실행하면 InnoDB는 넥스트 키 락을 설정하게 된다.

SELECT *
FROM member
WHERE age = 52 FOR
UPDATE;

위 쿼리는 단일 값(age = 52)만 조회하지만, 실제로는 다음과 같은 넥스트 키 락이 설정된다.

  • (50, 52] 범위: 레코드 52와 그 앞 공간
  • (52, 56] 범위: 레코드 52 다음 값과의 갭도 포함

이처럼 유니크 하지 않은 인덱스의 단일 값을 조회하더라도 넥스트 키 락은 인덱스 순서상 앞뒤 갭을 모두 포함해서 잠금하게 된다.

구분범위설명
넥스트 키 락(50, 52]레코드 52와 앞의 갭 포함
넥스트 키 락(52, 56]레코드 52 이후 인덱스상 다음 레코드와의 갭
레코드 락52명시적으로 선택된 레코드 자체

단일 조회 시에도 범위에 대해 넥스트 키 락이 걸리는 이유

섹션 제목: “단일 조회 시에도 범위에 대해 넥스트 키 락이 걸리는 이유”

락을 이용한 읽기, UPDATE, DELETE와 같은 명령문은 SQL 명령문 처리 시 InnoDB는 정확한 WHERE 조건을 기억하지 않고, 스캔된 인덱스 범위에 대해 잠금을 설정한다.

  • 유니크한 인덱스를 사용할 경우: InnoDB는 발견된 인덱스 레코드만 잠금
  • 유니크하지 않은 인덱스 or 범위 조건: InnoDB는 스캔된 인덱스 범위에 대해 넥스트 키 락을 설정

자동 증가 락(Auto-Increment Lock)

섹션 제목: “자동 증가 락(Auto-Increment Lock)”

AUTO_INCREMENT 속성을 가진 컬럼에 대해 중복되지 않는 유니크한 값을 할당하기 위해 내부적으로 사용되는 특수한 테이블 수준의 잠금이다.

  • 내부적으로 테이블 수준의 잠금을 걸어 중복되지 않는 값을 보장
  • 명시적으로 획득하거나 해제할 수 없음
  • MySQL 8.0에서는 잠금을 걸지 않는 방법을 기본 값으로 사용 중이며 innodb_autoinc_lock_mode 시스템 변수를 이용하여 변경 가능
값AUTO_INCREMENT 값 연속성동시 INSERT 처리 성능락 동작 방식 설명
0(TRADITIONAL)항상 연속적매우 낮음 (직렬 처리)모든 INSERT에서 테이블 단위 락을 걸고 순차적으로 AUTO_INCREMENT 값을 할당
1(CONSECUTIVE)대부분 연속적보통삽입 수를 알 수 있는 단일 INSERT는 락을 최소한으로 걸음 / INSERT ... SELECT 같은 삽입 수를 알 수 없는 경우 구문이 끝날 때까지 테이블 단위 락 유지
2(INTERLEAVED)연속성 보장 안 됨매우 높음락을 잡지 않아 빠르게 삽입하나 ID는 비연속적일 수 있음

레코드 수준의 잠금 확인 및 해제

섹션 제목: “레코드 수준의 잠금 확인 및 해제”

테이블 잠금은 잠금의 대상이 테이블 자체이므로 쉽게 문제 파악이 되지만, 레코드 수준의 잠금은 걸려있는지 확인하기가 어렵다.

커넥션1커넥션2커넥션3
BEGIN;
UPDATE employees SET birth_date=NOW() WHERE emp_no=10001;
UPDATE employees SET hire_date=NOW() WHERE emp_no=10001;
UPDATE employees SET hire_date=NOW(), birth_date=NOW() WHERE emp_no=10001;

위 시나리오는 커넥션 1이 아직 COMMIT을 실행하지 않은 상태이므로 해당 레코드의 잠금을 그대로 가지고 있으며, 커넥션 2 / 커넥션 3은 해당 레코드의 잠금을 대기하고 있음을 확인할 수 있다.

SELECT
    -- 대기 중인 트랜잭션 정보
    r.trx_id              waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query           waiting_query,
    -- 잠금을 보유한 트랜잭션 정보
    b.trx_id              blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query           blocking_query
FROM performance_schema.data_lock_waits w
         INNER JOIN information_schema.innodb_trx b
                    ON b.trx_id = w.blocking_engine_transaction_id
         INNER JOIN information_schema.innodb_trx r
                    ON r.trx_id = w.requesting_engine_transaction_id

MySQL 8.0 기준 performance_schema 테이블을 이용하여 잠금과 대기 순서를 확인할 수 있다.

waiting_trx_idwaiting_threadwaiting_queryblocking_trx_idblocking_threadblocking_query
0x7f9b1c00033UPDATE employees...0x7f9b1c00022UPDATE employees...
0x7f9b1c00033UPDATE employees...0x7f9b1c00011NULL
0x7f9b1c00022UPDATE employees...0x7f9b1c00011NULL

위 결과를 보고 각 스레드가 어떤 쿼리를 실행하고 어떤 스레드의 잠금을 대기하고 있는지 확인할 수 있다.

  • 2번 스레드: 1번 스레드의 락 대기
  • 3번 스레드: 2번 스레드 + 1번 스레드의 락 대기
참고자료
섹션 제목: “참고자료”

마지막 업데이트:

MySQL