InnoDB(MySQL, MariaDB) Lock 매커니즘

InnoDB 잠금(Lock)에는 잠금 모드와 잠금 유형이 있습니다. 잠금모드는 읽기, 쓰기와 같은 행위를 제한하는 것을 의미하고, 잠금 유형은 행위를 제한하는 대상을 말합니다.

이 문서는 MySQL 5.7 메뉴얼과 기타 웹 문서를 참고하여 MariaDB 10.6.5에서 테스트한 내용으로 작성 되었습니다.

잠금 모드(Lock Mode)

  • Shard Lock(S)

    • 읽기 전용 공유 잠금.
    • SELECT … LOCK IN SHARE MODE, LOCK TABLES … READ, UPDATE … (SELECT), INSERT … SELECT 사용 시
    • InnoDB는 Consistent Read를 하기 때문에 일반적인 읽기(SELECT)는 잠금을 사용하지 않습니다.

  • Exclusive Lock(X)

    • 쓰기 전용 배타적 잠금.
    • INSERT, UPDATE, DELETE, SELECT … FOR UPDATE 사용 시

  • Intent Lock

    • 리소스에 Shard Lock(S) 또는 Exclusive Lock(X)을 획득하기 전에 시스템 내부적으로 먼저 획득하는 테이블 수준의 세분성 잠금.

    • 잠금하려는 리소스에 충돌되는 다른 잠금이 있는지 파악하고, 잠금 호환성 여부에 따라 잠금을 획득하거나 블로킹 상태로 만드는 등의 관리 목적으로 사용.

    • Shard Lock(S)을 요청하면 먼저 테이블 수준의 intention shared lock(IS)을 획득한 다음 Shard Lock(S)을 획득합니다.

    • Exclusive Lock(X)을 요청하면 먼저 테이블 수준의 intention exclusive lock(IX)을 획득한 다음 Exclusive Lock(S)을 획득합니다.

    • 잠금 간에 호환성

잠금 유형(Lock Type)

  • Record Locks

    • 인덱스 레코드에 대한 잠금을 의미합니다.

    • 테이블이 인덱스 없이 정의된 경우에도 hidden clustered index를 내부적으로 만들어서 사용 합니다.

    • 클러스터 인덱스나 내부 인덱스가 생성되어 있더라도 조건절에 인덱스가 없는 컬럼을 사용하면 테이블 수준의 잠금을 획득합니다.

      • SQL Server도 조건절에 인덱스가 없으면 테이블 수준의 잠금이 됩니다.
      • Oracle 10g 이상의 경우에는 조건절에 인덱스가 없더라도 행 수준의 잠금을 합니다.

    • 잠금 경합이 발생하는 케이스

      • T1에서 UPDATE 조건절에 인덱스가 없는 컬럼을 사용하여 테이블 수준의 잠금을 획득하고 T2에서 INSERT나 UPDATE하는 경우.

        -- T1
UPDATE TEST
SET VALUE = 2
WHERE VALUE = 1;
      
-- T2 blocking
UPDATE TEST
SET VALUE = 6
WHERE INDEX_COLUMN = 'apple';

      • T1에서 UPDATE 조건절에 인덱스 컬럼을 사용하고, T2에서 UPDATE 또는 INSERT에서 조건절과 동일한 인덱스 컬럼 값을 사용한 경우.

        -- T1
UPDATE TEST
SET VALUE = 1
WHERE INDEX_COLUMN = 'apple';
      
-- T2 blocking
UPDATE TEST
SET VALUE = 6
WHERE INDEX_COLUMN = 'apple';
      
-- T2 blocking
UPDATE TEST
SET INDEX_COLUMN = 'appple'
WHERE ID = 6;
      
-- T2 blocking
INSERT INTO TEST(ID, INDEX_COLUMN, VALUE)
VALUES(100, 'apple', 100);

  • Gap Locks

    • 인덱스 레코드들 사이에 있는 갭 영역 잠금. 즉, 갭 영역에 존재하지 않는 레코드들이 추가되는 것을 방지하는 역할을 합니다.
    • Gap Locks은 Next-Key Locks에 포함되어 사용됩니다.
    • SQL Server나 Oracle은 Gap Lock이 없습니다.

  • Next-Key Locks

    • Record Locks과 Gap Locks를 결합하는 알고리즘.

    • 기본 격리수준 REPEATABLE READ에서만 발생합니다.

    • 테이블 인덱스를 검색하거나 스캔할 때 발견한 인덱스 레코드들에 Record Locks을 획득하고, 각 인덱스 레코드 사이의 갭 영역에 Gap Locks을 겁니다. 만약 다음 존재하는 인덱스 레코드가 없으면 무한대로 Gap Locks을 겁니다.

    • FOR UPDATE나 UPDATE 등 데이터 갱신을 목적으로 하는 연산의 경우 Consistent Read하지 않고 Locking Reads를 합니다. 즉, 스냅샷 데이터가 아닌 현재 시점의 데이터를 읽기 때문에 Phantom Rows가 발생할 수 있습니다. 그래서 InnoDB는 Phantom Rows를 방지 하기 위해 Next-Key Locks를 사용합니다.

    • 예시

      ID(PK) 1, 2, 3, 5, 6, 8, 12 레코드가 존재 한다고 가정하고 아래와 같은 DML문을 실행합니다.

      SELECT * 
FROM GABLOCK 
WHERE ID 5 BETWEEN 8 
FOR UPDATE
      • 조건을 만족하는 5, 6, 8은 Record Locks 획득하고 5, 6, 8에 대해 아래와 같이 Gap Locks을 획득함.
      • 5를 기준으로 다음 레코드 6 사이에 int형 갭 값이 없기 때문에 Gap Locks을 획득하지 않음.
      • 6을 기준으로 다음 레코드 8 사이에 갭 값 7에 대해서 Gap Locks 획득.
      • 8을 기준으로 다음 레코드 12 사이에 갭 값 9, 10, 11에 대해 Gap Locks 획득.
        • 조건절은 8까지지만 그것과 관계없이 다음 레코드인 12까지 Gap Locks을 획득합니다.
        • 만약 12라는 레코드가 없다면 9, 10, 11, 12, 13,……. 무한대까지 Gap Locks 범위가 됩니다.

image-20211228183939678

  • AUTO-INC Locks

    • AUTO_INCREMENT열이 포함된 테이블에 INSERT할 때 AUTO INCREMENT 값의 순차적인 할당을 위해 다른 트랜잭션의 INSERT문이 대기할 수 있도록 잠금을 합니다.

    • innodb_autoinc_lock_mode에 따라 동작이 다를 수 있으며 기본값은 1입니다.

      SELECT @@GLOBAL.innodb_autoinc_lock_mode;

    • 트랜잭션이 롤백된 여부와는 관계없이 자동 증가 컬럼의 값은 한 번 생성되면 롤백 할 수 없기 때문에 테이블의 AUTO_INCREMENT 컬럼에 저장되는 값에는 갭이 발생할 수가 있습니다.

    • 한번에 INSERT될 수 있는 최대 크기는 max_allowed_packet보다 작아야 합니다.

      SELECT @@GLOBAL.max_allowed_packet;

동시성 제어에서 중복 INSERT 방지

예를들어 사원을 등록할 때 존재하는 사원인지 검사한 후 등록하는 프로세스가 필요하다고 가정하고 아래와 같이 작성합니다.

SELECT * 
FROM 사원
WHERE 주민등록번호 = 'xxx'
FOR UPDATE

-- IF 존재하지 않는 주민등록번호라면
INSERT INTO 사원(사번, 주민등록번호, ...)
VALUES(1, 'xxx', ...);

위와 같은 프로세스는 동시성 제어에서 중복 데이터가 생성될 수 있습니다. 왜냐하면 SELECT … FOR UPDATE에서 조건절과 만족하는 결과 행이 없는 경우에는 잠금이 발생하지 않기 때문에 결과적으로 주민등록번호 = ‘xxx’는 잠금되지 않습니다. 따라서 다른 트랜잭션에서 동시에 등록이 가능한 부분인거죠.

그럼 어떻게 처리해야 되나?

  1. 테이블 잠금 후 처리 : 중복 방지는 되지만 동시성이 떨어지는 문제 발생.

  2. 주민등록번호 컬럼에 유니크 제약조건 설정

  3. insert select not exists

    INSERT INTO test_r1(uid, amount)
select 100, 10000
from dual
where not exists (select *
from test_r1 
where uid = 100);

잠금 에스컬레이션(Lock Escalation)

많은 행 잠금 을 단일 테이블 잠금 으로 변환 하여 메모리 공간을 절약하지만 테이블에 대한 동시 액세스를 줄이는 일부 데이터베이스 시스템에서 사용되는 작업 입니다. InnoDB는 행 잠금에 공간 효율적인 표현을 사용하므로 잠금 에스컬레이션이 필요하지 않습니다.

잠금 모니터링

InnoDB의 경우도 잠금 상태를 확인할 수 있는 방법들을 제공해주지만 Oracle( V$LOCK)이나 SQL Server(sp_lock)에 비해 잠금 정보를 확인하는데 불편함이 있었습니다.

  • Lock 경합 상태가 아닐때는 트랜잭션이 잠금 상태인지는 파악할 수 있으나 잠금모드 등의 상세 정보는 알 수 없습니다.
    • MySQL 8.0에서는 performance_schema.data_locks로 현재 잠금 및 lock 상세 정보를 좀 더 쉽게 파악할 수 있습니다.
    • MariaDB에서는 performance_schema가 빠져 있습니다. (MySQL 8과 MariaDB 10.4 차이점)

  • 실행된 연산에 따라 확인하는 방법이 다를 수 있습니다.

  • 잠금 확인 방법

    • SHOW ENGINE INNODB STATUS : InnoDB의 상태에 대한 표준 모니터의 광범위한 정보를 제공합니다.

      SHOW ENGINE INNODB STATUS;

    • PROCESSLIST : 실행 중인 스레드 집합에서 현재 수행 중인 작업 정보를 제공합니다.

      SHOW [FULL] PROCESSLIST;
또는
SELECT * FROM information_schema.`PROCESSLIST`;

    • information_schema.INNODB_LOCKS : LOCK 경합이 발생할 때 각 트랜잭션이 보유하고 있는 각 잠금에 대한 정보를 제공 합니다.

      SELECT * FROM information_schema.innodb_locks;

    • information_schema.INNODB_LOCK_WAITS : 블로킹 상태의 트랜잭션과 차단하고 있는 트랜잭션 정보를 제공합니다.

      SELECT * FROM information_schema.INNODB_LOCK_WAITS;

    • information_schema.INNODB_TRX : 실행 중인 트랜잭션 정보와 트랜잭션 잠금 상태 정보를 제공합니다.

      SELECT trx_id, trx_state , trx_mysql_thread_id, trx_tables_locked, trx_lock_structs , trx_rows_locked 
FROM information_schema.INNODB_TRX;

    • performance_schema.data_locks : MySQL 8.0이상 지원하며 상세한 잠금 상태를 알 수 있습니다.

      SELECT * FROM performance_schema.data_locks;

  • Gap Lock 잠금 모니터링 예시

    T1 트랜잭션

    SELECT * FROM <table_name> WHERE ID > 5 FOR UPDATE;

    T2 트랜잭션

    INSERT INTO <table_name> (...) VALUES(10, ...);

    • PROCESSLIST

    • information_schema.innodb_locks

    • information_schema.innodb_lock_waits

    • information_schema.INNODB_TRX

    • SHOW ENGINE INNODB STATUS

      ---TRANSACTION 1339, ACTIVE 7245 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1128, 11 row lock(s), undo log entries 7 MariaDB thread id 54, OS thread handle 13408, query id 4821 localhost 127.0.0.1 root Update insert into gablock(id, name, value, idxValue) values(11, 'zzzzz', 101, 101) ------- TRX HAS BEEN WAITING 2835685 ns FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 18 page no 3 n bits 32 index PRIMARY of table `test`.`gablock` trx id 1339 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 15 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 8000000000000064; asc d;; 1: len 6; hex 000000000000; asc ;; 2: len 7; hex 80000000000000; asc ;; 3: len 5; hex 6161616161; asc aaaaa;; 4: SQL NULL; 5: SQL NULL; ------------------ ---TRANSACTION 1343, ACTIVE 7095 sec 2 lock struct(s), heap size 1128, 4 row lock(s) MariaDB thread id 53, OS thread handle 17664, query id 4806 localhost 127.0.0.1 root Trx read view will not see trx with id >= 1343, sees < 1339

  • FOR UPDATE, LOCK IN SHARE MODE 잠금 모니터링 예시

    T1 트랜잭션

    SELECT * FROM <table_name> WHERE ... FOR UPDATE
SELECT * FROM <table_name> WHERE ... FOR SHARE
SELECT * FROM <table_name> WHERE ... LOCK IN SHARE MODE

    T2 트랜잭션

    UPDATE <table_name> 
SET ...
WHERE ...

    • PROCESSLIST

      • 실행중이거나 블로킹 상태면 COMMAND가 Query로 표시되며 블로킹 당한 SQL도 확인 가능합니다.

      • T1 -> T2 순으로 실행하면 T2의 상태가 Updating으로 표시.

      • T2 -> T1 순으로 실행하면 T1의 상태가 Statistics로 표시.

    • information_schema.innodb_locks

      • 잠금 모드가 공유잠금(S) 또는 배타적잠금(X)로 표시 됨.

      • T1 트랜잭션이 FOR UPDATE일 경우 TRX_ID가 모두 표시 됨.

        image-20211222180823799

      • T1 트랜잭션이 LOCK IN SHARE MODE로 공유 잠금(S)을 획득한 상태라면 T1의 TRX_ID가 0으로 표시됨.

        image-20211222181223269

    • information_schema.innodb_lock_waits

      • request_trx_id는 블로킹 상태의 트랜잭션을 나타내고 blocking_lock_id는 현재 잠금을 획득 중인 트랜잭션을 나타냄.

    • SHOW ENGINE INNODB STATUS

      ---TRANSACTION 463, ACTIVE 7 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s) MariaDB thread id 7, OS thread handle 17884, query id 1106 localhost 127.0.0.1 root Updating update test.test_r1 set NAME = 'bbbb' where uid=1000 ------- TRX HAS BEEN WAITING 7341822 ns FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 10 page no 3 n bits 16 index PRIMARY of table `test`.`test_r1` trx id 463 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 80000000000003e8; asc ;; 1: len 6; hex 000000000175; asc u;; 2: len 7; hex 0b0000013d0391; asc = ;; 3: len 3; hex 616161; asc aaa;; 4: len 5; hex 8000001194; asc ;; 5: SQL NULL; ---TRANSACTION 462, ACTIVE 10 sec 2 lock struct(s), heap size 1128, 1 row lock(s) MariaDB thread id 6, OS thread handle 17884, query id 1105 localhost 127.0.0.1 root

  • LOCK TABLES … READ 잠금 모니터링 예시

    T1 트랜잭션

    LOCK TABLES <table_name> READ;

    T2 트랜잭션

    UPDATE <table_name> 
SET ...
WHERE ...

    • PROCESSLIST

    • information_schema.innodb_locks과 information_schema.innodb_lock_waits에는 잠금 정보가 표시되지 않음.

    • information_schema.INNODB_TRX

    • SHOW ENGINE INNODB STATUS

      ---TRANSACTION (000001D4C495C300), ACTIVE 507 sec mysql tables in use 1, locked 1 1 lock struct(s), heap size 1128, 0 row lock(s) MariaDB thread id 6, OS thread handle 1252, query id 875 localhost 127.0.0.1 root ...

댓글남기기