[정리] InnoDB 스토리지 엔진 잠금
서론
MySQL의 잠금 분류 기준은 "누가 잠금을 관리하는가?" 즉, MySQL의 아키텍처에 따라 나눈다. 이 글에서는 InnoDB 스토리지 엔진 계층에서의 잠금에 관해 정리해본다.
InnoDB 스토리지 엔진
InnoDB 엔진은 내부에서 레코드 기반 잠금 방식을 탑재하고있다. 이 레코드 기반 잠금 덕에 MyISAM 보다 훨씬 뛰어난 동시성 처리를 제공한다.
하지만 이원화된 잠금 처리 때문에 InnoDB의 잠금에 관한 정보는 MySQL 명령어를 통해 접근하기가 까다롭다.
다행히 최신 버전에서는 InnoDB의 트랜잭션, 잠금, 잠금 대기 중인 트랜잭션 목록 조회 방법이 도입되었다.
informatino_schema 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 라는 테이블 조인해서 조회하면 다음과 같은 정보와 기능을 쓸 수 있다.
- 현재 어떤 트랜잭션이 어떤 잠금 대기?
- 해당 잠금을 어느 트랜잭션이 가짐?
- 장시간 잠금 가지고있는 클라이언트를 찾아서 종료 가능
점점 InnoDB가 중요해지고 모니터링도 강화되면서 Performance Schema를 이용해 InnoDB 엔징 내부 잠금(세마포어)에 대한 모니터링 방법도 추가되었다.
InnoDB 스토리지 엔진 잠금
InnoDB는 레코드 기반의 잠금을 지원한다.
잠금 정보가 상당히 작은 공간으로 관리되기에 레코드 락이 페이지 락으로 또는 테이블 락으로 레벨업 되는 경우는 없다.
다른 DBMS와는 다르게 InnoDB에서는 레코드 락 뿐만 아니라 레코드와 레코드 사이의 간격을 잠그는 갭 락이 존재한다.
레코드 락
InnoDB는 레코드 자체가 아닌 인덱스의 레코드를 잠근다.
인덱스가 설정 안된 테이블이라도 clustered index를 이용해 잠금을 설정한다. 인덱스가 있다면 secondary index와 그게 가리키는 clustered index 둘 다 잠근다.
InnoDB에서는 변경 쿼리가 보조 인덱스를 사용하는 경우, 일반적으로 넥스트 키 락(Record Lock + Gap Lock)을 사용하여 레코드와 그 앞의 간격까지 함께 잠근다.
반면, PK 또는 UNIQUE 인덱스를 이용한 변경 작업에서 동등 조건(=)으로 단일 레코드를 정확히 식별할 수 있는 경우에는, 갭 락 없이 해당 레코드에 대해서만 레코드 락만 획득한다
갭 락
InnoDB의 갭 락은 다른 DBMS와 차이가 크다. 레코드 자체가 아닌 레코드와 바로 인접한 레코드 사이의 간격만 잠근다.
레코드와 레코드 사이 간격에 새로운 레코드가 insert 되는 것을 제어하는 역할을 한다.
갭 락은 그 자체로 쓰이기 보다는 넥스트 키 락의 일부로써 자주 사용된다.
넥스트 키 락
레코드 락 + 갭 락을 합친 형태의 잠금. (STATEMENT 포맷의 바이너리 로그 쓰는 MySQL은 REPEATABLE READ 격리 수준을 써야됨)
innodb_locks_unsafe_for_binlog 변수가 비활성화 상태면, 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식으로 잠금이 걸린다.
넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행 시, 소스 서버에서 만들어낸 결과와 동일한 결과를 만들도록 보장해주는게 주 목적이다.
자동 증가 락
자동 증가 하는 숫자 값을 채번하기 위해 AUTO_INCREMENT라는 컬럼 속성을 제공한다.
동시에 여러 레코드가 insert되는 경우 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련의 번호가 필요하다.
이를 위해 내부적으로 자동 증가(AUTO_INCREMENT) 락 이라하는 테이블 수준의 잠금이 사용된다.
다른 InnoDB 잠금들과 달리 자동 증가 락은 트랜잭션과 관계없이 insert, replace 문장에서 AUTO_INCREMENT를 가져오는 순간만 락 걸렸다 즉시 해제된다.
자동 증가 락은 테이블 당 단 한개만 존재한다.
여러 레코드가 insert시도 시 자동 증가 락을 얻을 때 까지 대기해야한다.
AUTO_INCREMENT 칼럼에 명시적으로 값 세팅해도 자동 증가 락이 걸린다.
자동 증가 락을 명시적으로 획득하고 해제할 수 있는 방법은 없다. 대부분의 자동 증가 락은 아주 짧게 락이 걸리고 해제되기에 문제가 없다.
여기까지 설명은 5.0 이하의 버전에서 쓰던 방식에 대한 설명이었다. 5.1 부터는 innodb_autoinc_lock_mode라는 시스템 변수로 자동 증가 락 작동 방식을 변경 가능하다.
innodb_autoinc_lock_mode = 1 경우
insert 레코드 건수를 정확히 예측 가능할 때는 자동 증가 락 안쓰고, 더 가볍고 빠른 래치(뮤택스)를 이용해 처리한다. 더 짧게 락 걸고 푼다.
하지만 insert 레코드 건수 예측이 불가할 때는 5.0 이전 처럼 자동 증가 락을 쓴다.
대량 insert일 때 InnoDB는 여러 개의 자동 증가 값을 한번에 할당 받아서 insert 대상 레코드에 사용한다.
그런데 만약 한 번에 할당된 자동 증가 값이 남아 못쓰면 폐기되어 대량 insert 후에 레코드 자동 증가 값은 연속되지 않고 누락이 발생할 수도 있다.
이 설정에서는 최소 하나의 insert 로 삽입된 레코드는 연속된 자동 증가 값을 가지게 된다.(미리 할당받은걸 쓰니까...) 그래서 이 모드를 "연속 모드" 라고도 지칭한다.
innodb_autoinc_lock_mode = 2 경우
절대 자동 증가 락을 쓰지 않는다. 경량화된 래치(뮤택스)만 사용한다. innodb_autoinc_lock_mode = 1 에서는 자동 증가 락도 특정 경우에 쓴 것과 대비된다.
이 모드에서는 하나의 insert로 저장되는 레코드라 하더라도 연속된 자동 증가 값을 보장 못한다. 그래서 이 모드를 "interleaved 모드"라고도 한다.
insert, ... selelct 같은 대량 insert에서도 다른 커넥션에서 insert가 가능해서 동시 처리 성능이 좋아진다. 하지만 이 모드에서 작동하는 자동 증가 기능은 UNIQUE한 값이 생성된다는 것만 보장된다.
STATEMENT 포맷의 바이너리 로그 쓰는 복제에선 소스 서버와 레플리카 서버의 자동 증가 값이 달라 질 수 있기에 주의해야된다.
자동 증가 값이 한번 증가하면 절대 줄어들지 안는 이유는?
- AUTO_INCREMENT 잠금 최소화 위함
- 설령 인서트 실패하더라도 한번 증가된건 줄어들지않는다.
인덱스와 잠금
InnoDB 잠금과 인덱스는 상당히 밀접히 연관되어 있다. InnoDB 잠금은 레코드를 잠그는게 아닌 인덱스를 잠근다.
즉, 변경해야할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락 걸어야된다.
예시
예제 디비의 employees 테이블에는 아래와 같이 first_name 칼럼만 멤버로 담긴 ix_firstname이란 인덱스가 준비되있다.
이 테이블에서
first_name = 'Georgi인 사원은 253명first_name='Georgi 이고 last_name = 'Klassen인 사원 1명
이 상황에서 first_name='Georgi' 이고 last_name = 'Klassen'인 사원의 입사 일자를 오늘로 변경하는 쿼리 실행해 보자.
UPDATE employees
SET hire_date=NOW()
WHERE first_name='Georgi' AND last_name='Klassen'위 쿼리 실행하면 1개의 레코드가 업데이트된다. 하지만 이 1개 위해 몇 개의 레코드 락 걸어야할까?
인덱스 활용 가능한 조건은 first_name = 'Georgi 뿐이고, first_name = 'Georgi인 레코드 253 건의 레코드 모두가 잠긴다.
MySQL이 낯설면 이건 이상하게 생각할것이다. 그러나 이거 모르고 개발하면 제대로 모르고 MySQL을 다루고 있는것이다.
이렇게 UPDATE를 위한 적절한 인덱스가 준비돼 있지 않다면 각 클라이어트간의 동시성이 상당히 떨어져서, 한 세션에서 UPDATE 작업 도중에는 다른 클라이언트는 그 테이블을 UPDATE 못하고 기다려야 할 것이다.
또한, 만약 이 테이블에 인덱스가 하나도 없다면?
그렇다면 테이블은 FULL SCAN 하면서 UPDATE를 진행하는데 이 과정에서 테이블에 있는 약 300,000 건의 모든 레코드를 잠그게 된다.
이게 MySQL의 방식이며 이런 FULL SCAN과 불필요한 락을 방지하기위해 인덱스 설계가 중요하다.
레코드 수준의 잠금 확인과 해제
InnoDB가 쓰는 테이블의 레코드 수준 잠금은 테이블 수준보다 조금 더 복잡하다.
테이블 수준의 잠금에서는 잠금 대상이 테이블 자체이기 때문에 쉽게 문제가 발견되어 해결 시도가 가능하다.
하지만, 레코드 수준의 락은 테이블의 레코드 각각에 락이 걸려서 그 레코드가 자주 사용되는게 아니면 오랜 시간 잠겨져있더라도 발견이 잘 안된다.
더욱이 옛날 버전은 레코드 잠금에 대한 메타 정보를 제공안했기에 더욱 어려웠던 부분이 있다.
하지만 5.1 부터는 레코드 잠금과 잠금 대기에 대한 정보를 조회 가능해서 쿼리만 실행해보면 관련 내용을 파악할 수 있다.
잠금을 강제로 해제하려면 KILL 명령어로 MySQL의 프로세스를 강제 종료하는 방법이 있다.