InnoDB 스토리지 엔진 잠금

  MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 장금 방식입니다. 하지만 이원화된 잠금 처리 탓에 InnoDB 스토리지 엔진에서 사용되는 잠금에 대한 정보는 MySQL 명령을 이용해 접근하기가 상당히 까다롭습니다.Performance Schema를 이용해 InnoDB 스토리지 엔진의 내부 장금에 대한 모니터링 방법이 추가되었습니다.

 

  InnoDB 스토리지 엔진의 잠금 기능을 제공하며, 잠금 정보가 상당히 작은 공간으로 관리되기 때문에 레코드  락이 페이지 락으로, 또는 테이블 락으로 레벨업되는 경우는 없습니다. 일반 상용 DBMS와는 조금 다르게 InnoDB 스토리지 엔진에서는 레코드 락뿐 아니라 레코드와 레코드 사이의 간격을 잠그는 갭 락이라는 것이 합니다.

레코드 락

  레코드 자체만을 잠그는 것을 레코드 락이라 하며, 다른 상용 DBMS의 레코드 락과 동일한 역할을 합니다.

한가지 중요한 차이는 InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스 레코드를 잠근다는 점입니다.

레코드 자체를 잠그느냐, 아니면 인덱스를 잠그느냐는 상당히 중요한 차이를 만들어 냅니다.

 

갭 락

  레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미합니다.

레코드와 레코드 사이의 간격에 새로운 레코드가 생셩되는 것을 제어하는 것입니다.

넥스트 키 락의 일부로 자주 사용됩니다.

 

넥스트 키 락

  레코드 락과 갭 락을 합쳐 놓은 형태입니다.

STATEMENT 포맥의 바이너리 로그를 사용하는 MySQL 서버에서는 REPEATABLE READ 격리수준을 하용해야 합니다.

바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주목적입니다.

데드락이나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생하므로 가능하면 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋습니다.

 

자동 증가 락

  AUTO_INCREMENT라는 칼럼 속성을 제공하며 칼럼이 사용된 테이블에 동시에 여러 레코드가 INSERT되는 경우, 저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 하며, auto increment lock이라고 하는 테이블 수준의 잠금을 사용합니다.

INSERT와  REPLACE 쿼리 문장과 같이 저장하는 쿼리에서만 필요합니다.

자동 증가 락은 하나만 존재하기 때문에 동시에 INSERT가 일어나는 경우 하나의 쿼리는 해당 잠금을 기다려야 하며, 자동 증가 락은 명시적으로 해제하거나 획득할 수 없습니다.

 

인덱스와 잠금

  InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리됩니다. 즉, 변경할 레코드를 찾을 때 검색한 인덱스의 레코드를 모두 잠궈야 한다. 이와 같은 특징 때문에 MySQL에서는 인덱스 설계가 굉장히 중요합니다.

 

--// 데이터베이스에 employees 테이블에는 아래와 같이 first_name 컬럼만
--// 멤버로 담긴 ix_firstname이라는 인덱스가 준비돼 있다.
--//key ix_firstname(first_name)
--//employees 테이블에서 first_name='Georgi'인 사원은 전체 253명이 있으며,
--//first_name='Georgi'이고 last_name='kLASSEN'인 사원은 딱 1명만 있는 것을 아래 쿼리로 
--//확인 할수 있다.
mysql>SELECT COUNT(*) FROM employees WHERE first_name='Georgi'
+-----------+
|       256 |
+-----------+
mysql> SELECT COUNT(*) FROM employees WHERE first_name='Georgi' AND last_name='Klassen';
+-----------+
|         1 |
+-----------+
--//employees 테이블에서 first_name='Georgi' 이고 last_name='Klassen' 인 사원의
--//입사 일자를 오늘로 변경하는 쿼리를 실행하면,
mysql> UPDATE employees SET hire_date=NOW() WHERE first_name='Georgi' AND last_name='Klassen'

 

위의 쿼리를 실행하면 1개의 UPDATE 쿼리를 위해 몇 개의 레코드에 락을 걸어야 할까?
first_name에는 인덱스가 존재하지만 last_name에는 인덱스가 없기 때문에 first_name='Georgi' 인 레코드 253건이 모두 잠깁니다.

 

만약 인덱스가 아예 존재하지 않는다면 풀 스캔이 일어나면서 1개의 UPDATE를 위해 모든 레코드가 잠기게 됩니다. MySQL의 InnoDB에서 인덱스 설계가 중요한 이유도 이 때문입니다.

 

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

테이블의 레코드 수준 잠금은 테이블 수준의 잠금보다는 조금 더 복잡합니다. 테이블 잠금에서는 잠금의 대상이 테이블 자체이므로 쉽게 문제의 원인이 발견되고 해결될 수 있지만, 레코드 수준의 잠금은 테이블의 레코드 가가에 잠금이 걸리므로 그 레코드가 자주 사용되지 않는다면 오랜 시간 동안 잠겨진 상태로 남아 있어도 잘 발견되지 않습니다.

MySQL 5.1부터는 레코드 잠금과 잠금 대기에 대한 조회가 가능해져 쿼리 하나로 잠금과 잠금 대기를 바로 확인할 수 있게 되었습니다.

--// 명령이 실행된 상태의 프로세스 목록을 조회
SHOW PROCESSLIST;

--// performance_schema의 data_locks 테이블과 data_lock_waits 테이블을 조인해 
--// 잠금 대기 순서 조회

mysql>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;

만약 특정 스레드가 어떤 잠금을 가지고 있는지 더 상세히 확인하고 싶다면 performance_schema의 data_locks 테이블이 가진 컬럼을 모두 살펴보면 됩니다.

mysql> SELECT* FROM performance_schema.data_locks\G

만약 특정 스레드가 잠금을 가진 상태에서 오랜 시간 멈춰있다면, 다음과 같이 특정 스레드를 강제 종료하여 잠금 경합을 끝낼 수 있습니다.

 

mysql> KILL 17;