InnoDB 스토리지 엔진 잠금
InnoDB는 MySQL에서 제공하는 잠금과 별개로 스토리지 엔진 내부에서 레코드 기반 잠금 방식을 탑재하고 있다. InnoDB 레코드 기반의 잠금 방식은 MyISAM보다 뛰어난 동시성 처리를 제공할 수 있다.
최근 버전의 InnoDB에서는 트랜잭션과 잠금, 그리고 잠금 대기 중인 트랜잭션의 목록을 조회할 수 있는 방법이 도입됐다.
MySQL 서버의 information_schema DB에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS라는 테이블을 조인해서 조회하면 어떤 트랜잭션이 어떤 잠금을 대기하고 있고 해당 잠금을 어느 트랜잭션이 가지고 있는ㄴ지 확인할 수 있으며, 장시간 잠금을 가지고 있는 클라이언트를 찾아서 종료시킬 수도 있다. 모니터링도 강화되면서 Perfomance Schema를 이용해 InnoDB 스토리지 엔진의 내부 잠금(세마포어)에 대한 모니터링 방법도 추가됐다.
InnoDB 스토리지 엔진에서는 일반 상용 DBMS와 다르게 레코드 락뿐 아니라 다른 종류의 락이 존재한다.
레코드 락
갭 락
넥스트 키 락
자동 증가 락
레코드 락(Record Lock)
레코드 락은 레코드 자체를 잠그는 것을 의미하며, InnoDB 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 것이다. 인덱스가 하나도 없는 테이블에는 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다.
레코드 자체를 잠그느냐, 인덱스를 잠그느냐는 상당히 중요한 차이를 만들어낸다.
InnoDB에서는 대부분 보조 인덱스를 이용한 변경 작업은 넥스트 키 락, 갭 락을 사용한다.
PK, 유니크 인덱스에 의한 변경 작업은 Gap에 대해서는 잠그지 않고 레코드 자체에 락을 건다.
레코드락은 실제 존재하는 레코드에 직접 락을 걸고 수정/삭제를 제어하면서 트랜잭션 간 동시성 관리에 중요한 역할을 한다.
갭 락(Gap Lock)
갭 락은 레코드 자체가 아니라 레코드와 인접한 사이의 간격만을 잠그는 것을 의미한다.
갭 락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(insert)되는 것을 제어하는 것이다. 갭 락은 넥스트 키 락의 일부로 자주 사용된다.
갭 락은 주로 새로운 레코드의 삽입을 제어하는 데 사용된다.
팬텀 리드 방지에 핵심 메커니즘이다.
Gap Lock 동작
REPEATABLE READ격리수준에서 기본으로 작동한다.단순히 범위 조건을 사용한다고 자동으로 Gap Lock이 걸리지 않고 트랜잭션 내에서
select … for update구문을 사용할 때 걸린다.인덱스가 있는 컬럼에 대해서만 Gap Lock이 걸린다.
쿼리와 for update
일반 select는 갭 락이 걸리지 않고 MVCC를 통해 일관성을 보장한다. (consistence read를 사용해 트랜잭션 시작 시점의 스냅샷을 읽는다.)
update/delete 쿼리의 경우 for update가 없어도 자동으로 갭 락이 걸린다. (Phantom Read 방지)
인덱스 컬럼에 대해 갭 락이 걸리는데, 인덱스가 없는 경우 테이블 전체 스캔을 하면서 모든 레코드에 대해 락을 걸게 된다.
READ COMMITED에서는 select … for update 명령이 갭 락을 걸지 않는다. 따라서 READ COMMITED 격리 수준에서는 동일 트랜잭션내에서도 다른 결과가 나올 수 있다.
Read commited에서는 Phantom Read를 허용하기 위해서이다.
record lock만 사용하고 gap lock은 사용하지 않음
넥스트 키 락(Next-Key Lock)
Next-Key Lock = Record Lock + Gap Lock이다.
Statement 포맷의 binary log를 사용하는 MySQL 서버에서는 REPEATABLE READ 격리 수준을 사용해야 한다.
InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가
replica server에서 실행될 때source server에서 만들어 낸 결과와동일한 결과를 만들어내도록 보장하는 것을 목적으로 한다.
의외로 넥스트 키 락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생하는데, 가능하다면 바이너리 로그 포맷을 ROW 형태로 바꿔 넥스트 키 락이나 갭 락을 줄이는 것이 좋다고 한다.
8.0에서는 ROW 포맷의 바이너리 로그가 기본 설정으로 변경됐다.
자동 증가 락(Auto Increment Lock)
MySQL에서는 자동 증가하는 숫자 값을 추출(채번)하기 위해 autu_increment라는 속성을 제공한다.
auto_increment 속성을 사용하는 컬럼은 테이블에 동시에 여러 레코드가 insert 되는 경우, 저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 갖는다.
InnoDB 스토리지 엔진에서는 내부적으로 auto_increment lock이라고 하는 테이블 수준의 잠금을 사용한다.
자동 증가 락은 레코드를 저장하는 쿼리에서만 발생하고 update/delete 등의 쿼리에서는 걸리지 않는다. insert/replace 문장에서 auto_increment 값을 가져오는 순간만 락이 걸렸다가 즉시 해제된다.
자동 증가 락은 테이블에 단 하나만 존재하기 때문에 두 개의 insert 쿼리가 동시에 실행되는 경우 하나의 쿼리가 auto_increment lock을 걸면 나머지 쿼리는 락을 기다린다.
명시적으로 락을 획득하고 해제하는 방법은 없으며 아주 짧은 시간 동안 걸렸다가 해제되는 잠그이라 대부분 문제가 발생되지 않는다.
인덱스 잠금
InnoDB 잠금은 인덱스와 중요한 관계가 있다. InnoDB는 변경해야 할 레코드를 찾기 위해 검색 조건에 해당하는 인덱스 엔트리들에 대해 잠금을 설정한다. 이는 실제 테이블의 레코드가 아닌, 해당 레코드를 가리키는 인덱스 엔트리에 잠금을 거는 것이다.
🤔 레코드가 아닌 ‘인덱스 레코드를 잠근다.’가 어떤 의미일까.
그림과 같은 테이블이 존재하고, name을 index로 가진다. 6개의 레코드에서 name=’A’, value는 서로 다른 값을 갖는다.
아래 쿼리를 실행하면, InnoDB 스토리지 엔진에서는 Lock이 어떻게 설정될까?
InnoDB는 인덱스 레코드에 Lock이 걸린다고 했었다. 모든 레코드는 name=’A’가 설정되어 있기 때문에 모든 레코드에 Lock이 걸릴 것이다.
lock이 걸린 상황에서 delete from test_lock where value=10 쿼리를 실행해도 락이 해제될 때까지 대기해야 한다.
테이블에는 총 6개의 레코드가 존재하고, update query를 실행했지만 commit되지 않은 상황을 살펴보자.
실제 레코드 6개에 대한 X락 (X,REC_NOT_GAP)이 걸려있다.
그럼 X락이 7개인 것은 무엇일까?
Supremum pseudo-record에 대한 1개의 X락이다.
Supremum pseudo-record는 테이블의 모든 레코보다 더 큰 값을 가진다고 가정되는 가상의 레코드이다.
예시에서는 name=’A’ 값 다음을 위한 Supremum 레코드를 생성해서 인덱스 범위 이후를 관리한다.
실제로 수많은 레코드가 존재할 때, 테이블에 인덱스가 하나도 없다면 풀 스캔을 하면서 테이블의 모든 레코드를 잠그게 된다. 이는 동시성이 매우 떨어지게 될 것이기 때문에 MySQL의 InnoDB에서 인덱스 설계가 중요한 이유이기도 하다.
Last updated