InnoDB 스토리지 엔진 잠금

InnoDB 스토리지 엔진에서 제공하는 잠금

• InnoDB 스토리지 엔진은 레코드 기반의 잠금 기능을 제공한다.
  • 레코드 기반의 잠금 방식 때문에 MyISAM 보다는 훨씬 뛰어난 동시성 처리를 제공할 수 있다.
• 이원화된 잠금 처리 탓에 InnoDB 스토리지 엔진에서 사용되는 잠금에 대한 정보는 MySQL 명령을 이용해 접근하기 상당히 까다롭다.
• 예전 버전의 MySQL 서버에서는 InnoDB의 잠금 정보를 진단할 수 있는 도구가 적고, 내용도 이해하기가 상당히 어려웠다.
  • 1) lock_monitor: innodb_lock_monitor 라는 이름의 InnoDB 테이블을 생성 후, InnoDB의 잠금 정보를 덤프하는 방법
  • 2) SHOW ENGIN INNODB STATUS 명령
• 최근 버전에서는 InnoDB의 트랜잭션과 잠금, 그리고 잠금 대기 중인 트랜잭션의 목록을 조회할 수 있는 방법이 도입되었다.
  • information_schema 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 라는 테이블을 조인해서 조회하는 방법
  • 장시간 잠금을 가지고 있는 클라이언트를 찾아 종료시킬 수도 있다.
• 조금씩 업그레이드되면서 Performance Schema를 이용해 InnoDB 스토리지 엔진 내부 잠금(세마포어)에 대한 모니터링 방법도 추가되었다.

InnoDB 스토리지 엔진의 잠금

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

Record lock (Record only lock)

• 레코드 자체만을 잠그는 것을 말하며, 다른 상용 DBMS의 레코드 락과 동일한 역할을 한다.
  • 한 가지 중요한 차이점은 InnoDB 스토리지 엔진은 레코드 자체가 아닌 인덱스의 레코드를 잠근다는 점이다.
  • 인덱스가 하나도 없는 테이블도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다.
• 프라이머리 키 또는 유니크 인덱스에 의한 변경 작업에서는 레코드 자체에 대해서만 락을 건다.
  • 보조 인덱스를 이용한 변경 작업은 넥스트 키 락 또는 갭 락을 사용

GAP Lock

• 다른 DBMS와의 차이
• 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다.
• 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(INSERT)되는 것을 제어하는 역할을 한다.
• 갭 락은 그 자체 보다는 넥스트 키 락의 일부로 자주 사용된다.

Next key lock

• 레코드 락과 갭 락을 합쳐 놓은 형태의 잠금이다.
• STATEMENT 포맷의 바이너리 로그를 사용하는 MySQL 서버에서는 REPREATABLE READ 격리 수준을 사용해야 한다.
• innodb_locks_unsafe_for_binlog 시스템 변수가 비활성화(0)되면 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식으로 잠금이 걸린다.
• 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주목적이다.
• 가능하다면 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋다.
  • 의외로 넥스트 키 락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생한다.
  • MySQL 8.0에서는 ROW 포맷의 바이너리 로그가 기본 설정이다.

자동 증가 락

• MySQL에서는 자동 증가하는 숫자 값을 추출(채번)하기 위해 AUTO_INCREMENT 라는 칼럼 속성을 제공한다.
  • 해당 컬럼이 사용된 테이블에선 여러 레코드가 INSERT되는 경우, 저장되는 각 레코드는 저장된 순서대로 증가하는 일련번호 값(중복되지 않은)을 가져야 한다.
  • InnoDB 스토리지 엔진에선 이를 위해 내부적으로 AUTO_INCREMENT 락 이라고 하는 테이블 수준의 잠금을 사용한다.
• AUTO_INCREMENT 락은 새로운 레코드를 저장하는 쿼리에서만 필요하다.
  • 필요: INSERT와 REPLACE 쿼리 문장
  • UPDATE나 DELETE 등의 쿼리에선 걸리지 않는다.
• InnoDB의 다른 잠금과는 달리, 트랜잭션과 관계없이 AUTO_INCREMENT 값을 가져오는 순간만 락이 걸렸다가 즉시 해제된다.
• AUTO_INCREMENT 락은 테이블에 단 하나만 존재하기 때문에, 두 개의 INSERT 쿼리가 동시에 실행되는 경우 하나의 쿼리가 락을 걸면 다른 쿼리는 락을 기다려야 한다.
  • AUTO_INCREMENT 칼럼에 명시적으로 값을 설정하더라고 락을 건다.
• MySQL 5.0 이하 버전에서 AUTO_INCREMENT 락을 명시적으로 획득하고 해제하는 방법은 없다.
  • AUTO_INCREMENT 락은 아주 짧은 시간 동안 결렸다가 해제되는 잠금이라서 대부분의 경우 문제가 되지 않는다.
• MySQL 5.1 이상부터는 innodb_autoinc_lock_mode 라는 시스템 변수를 이용해 자동 증가 락의 작동 방식을 변경할 수 있다.
  • 값이 0일 경우: MySQL 5.0과 동일한 잠금 방식. 모든 INSERT 문장은 자동 증가 락을 사용
  • 값이 1일 경우: 연속 모드. MySQL 서버가 쿼리가 실행되기 전에 INSERT되는 레코드의 건수를 정확히 예측할 수 있을 때는 훨씬 가볍고 빠른 래치(뮤텍스)를 이용해 처리한다.
  • 값이 2일 경우: 인터리빙 모드. 절대 자동 증가 락을 걸지 않고 경량화된 래치(뮤텍스)를 사용한다. 연속된 자동 증가 값을 보장하지는 않는다.

인덱스와 잠금

• InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리된다.
• 즉, 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 한다.
• 테이블에 인덱스가 하나도 없다면 테이블을 풀 스캔하기 때문에, 모든 레코드를 잠그게 된다.

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

• InnoDB 스토리지 엔진을 사용하는 테이블의 레코드 수준 잠금은 테이블 수준의 잠금보다는 조금 더 복잡하다.
  • 테이블 잠금: 잠금의 대상이 테이블 자체. 쉽게 문제 원인이 발견되고 해결될 수 있다.
  • 레코드 수준의 잠금: 테이블의 레코드 각각에 잠금이 걸린다. 레코드가 자주 사용되지 않는다면 오랜 시간 동안 남겨진 상태로 남아 있어도 잘 발견되지 않는다.
• MySQL 5.1 부터는 레코드 잠금과 잠금 대기에 대한 조회가 가능하므로, 쿼리 하나를 실행하면 잠금과 잠금 대기를 바로 확인할 수 있다.
  • information_schema 라는 DB에 INNODB_TRX , INNODB_LOCKS , INNODB_LOCK_WAITS 테이블을 통해 확인이 가능하다.
• MySQL 8.0 부터는 information_schema 의 정보들은 조금씩 제거(Deprecated)되고 있으며, performance_schema 의 data_locks 와 data_lock_waits 테이블로 대체되고 있다.
• 강제로 잠금을 해제하려면 KILL 명령을 이용해 MySQL 서버의 프로세스를 강제 종료하면 된다.

-- 프로세스 목록 조회
-- // 락이 걸린 쿼리들을 확인
SHOW PROCESSLIST;

-- 잠금 대기 순서 확인
SELECT
	r.trx_id AS waiting_trx_id,
	r.trx_mysql_thread AS waiting_thread,
	r.trx_query AS waiting_query,
	b.trx_id AS blocking_trx_id,
	b.trx_mysql_thread_id AS blocking_thread,
	b.trx_query AS 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 informaion_schema.innodb r
	ON r.trx_id = w.requesting_engine_transaction_id

-- 특정 스레드의 잠금을 상세 확인
SELECT * FROM performance_schema.data_locks\G

-- 특정 스레드(17번) 강제 종료
KILL 17;

Reference

참고 서적

📔 Real MySQL 8.0