Lock에는 MySQL 엔진이 관리하는 Lock과 InnoDB 스토리지 엔진이 관리하는 락이 있다.
이번에는 InnoDB 스토리지 엔진이 관리하는 Lock에 대해 알아보자.
레코드 락
InnoDB 엔진이 관리하는 락 중에서 핵심이다.
다른 DBMS의 레코드 락과 동일하지만 한 가지 중요한 차이는 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 것이다.
따로 생성한 세컨더리 인덱스 뿐만 아니라 PK 인덱스도 마찬가지다.
다만, PK 인덱스는 갭 락을 걸지 않고 레코드 락만 걸지만 세컨더리 인덱스에서는 넥스트 키 락 또는 갭 락을 이용한다.
갭 락
갭 락은 레코드와 해당 레코드와 바로 인접한 레코드 사이 간격을 잠그는 것을 의미한다.
갭 락의 역할은 레코드와 레코드 사이에 새로운 레코드가 INSERT 되는 것을 막기 위함이다.
갭 락 그 자체보다는 넥스트 키 락의 일부로 자주 사용된다.
넥스트 키 락
레코드 락과 갭 락을 합쳐놓은 형태의 잠금이다.
갭 락이나 넥스트 키 락의 주목적은, 바이너리 로그에 기록되는 쿼리가 Slave 서버에서 실행될 때 Master 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이다.
Auto Increment 락
MySQL에는 테이블에 INSERT 할 때, 자동 증가하는 숫자를 넣어주는 AUTO_INCREMENT 칼럼 속성이 존재한다.
보통 ID 칼럼에 사용하곤 하는데, 테이블에 동시에 여러 건의 INSERT가 발생하는 경우 저장되는 각 레코드의 ID 값은 중복되지 않고 순서대로 증가하는 값이 자동으로 저장된다.
InnoDB 스토리지 엔진에서는 이 작업을 위해 내부적으로 AUTO_INCREMENT 락 이라고 하는 테이블 수준의 잠금을 사용한다.
어쩌면 당연하게 새로운 레코드를 저장하는 쿼리, INSERT, REPLACE 쿼리에서만 작동한다.
해당 락은, 트랜잭션과 상관없이 INSERT 혹은 REPLACE 쿼리에서 AUTO_INCREMENT 값을 가져올 때만 락이 걸렸다가 즉시 해제한다.
AUTOINCREMENT 락은 테이블 당 하나만 존재한다.
따라서 2개 이상의 INSERT 쿼리가 동시에 실행되는 경우, 하나의 쿼리가 해당 테이블에 AUTOINCREMENT 락을 걸면 나머지 쿼리는 락이 해제될 때까지 기다려야 한다.
참고로 AUTOINCREMENT 칼럼에 값을 명시적으로 넣어준 쿼리가 실행되어도 AUTOINCREMENT 락은 수행된다. 그 이유는, AUTOINCREMENT 속성이 걸린 칼럼에 값이 명시적으로 담겨져 INSERT 되면, AUTOINCREMENT 값을 재설정하는 작업이 수행되기 떄문이다.
AUTO_INCREMENT 락은 명시적으로 획득하고 해제하는 방법은 없다.
그리고 아주 짧은 시간동안 존재하는 락이기 때문에 대부분 문제가 잘 되지 않는다.
지금까지는 MySQL 5.0 이하 버전에서 사용되던 방식이다.
MySQL 5.1 이상부터는 innodb_autoinc_lock_mode 시스템 변수를 이용해 작동 방식을 변경할 수 있다.
innodbautoinclock_mode = 0
위에서 설명된 방식 그대로 사용한다.
innodbautoinclock_mode = 1
MySQL 서버가 INSERT 되는 레코드의 수를 정확히 예측할 수 있을 때에는 AUTO_INCREMENT 락을 걸지 않고, 훨씬 빠른 래치(뮤텍스)를 이용한다.
락이 존재하는 타이밍이 존재하긴 하지만 AUTO_INCREMNET 락보다는 훨씬 빠르게 테이블에 락을 걸고 해제하게 된다.
다만, INSERT 되는 레코드의 수를 정확히 예측할 수 있어야 한다는 전제 조건이 있기 때문에 이 조건을 만족하지 못하면 AUTO_INCREMENT를 사용한다.
대량의 INSERT 쿼리가 처리될 때는, InnoDB 스토리지 엔진이 AUTOINCREMENT 값을 한 번에 여러 값을 할당받아서 사용한다.
하지만 이때 할당받은 값을 모두 사용하지 못하게 되면 폐기하게 되어, 그 다음 INSERT 쿼리에서 AUTOINCREMENT 값은 중간 값이 누락된 값이 저장될 수 있다.
위 설정에서는 하나의 INSERT 쿼리로 저장되는 레코드는 연속된 자동 증가 값을 가진다는 특징을 가진다.
따라서 이 설정 모드를 연속 모드 라고도 한다.
innodbautoinclock_mode = 2
해당 설정은 AUTO_INCREMENT 락을 걸지 않고 가벼운 래치(뮤텍스)를 사용한다.
이 설정에서는 대량 INSERT 문장이 실행되는 중에도 다른 커넥션에서 INSERT를 수행할 수 있다.
결과적으로 동시 처리 성능이 높아진다.
하지만 자동 증가 값이 연속되는 것은 보장할 수 없다. 단지 유니크한 값만 생성한다는 것만 보장한다.
하나의 INSERT 쿼리로 저장할 때에도 순서는 보장할 수 없다. 따라서 인터리빙 모드 라고도 한다.
위 설정에서 STATEMENT 포맷의 바이너리 로그를 사용하는 Replication 에서는 Master와 Slave의 AUTO_INCREMENT 값이 달라질 수 있다.
STATEMENT 포맷의 바이너리 로그를 사용한다면 mode 값을 1로 설정하자.
인덱스와 InnoDB의 잠금
레코드 락에서 언급한대로 InnoDB는 레코드 자체가 아니라 인덱스의 레코드를 잠근다.
이 말은 즉슨, SELECT나 UPDATE, DELETE 처럼 변경해야 할 레코드를 찾기 위해 인덱스를 타고 검색하게 되는데, 이 과정에서 조건에 해당하는 레코드를 전부 잠근다.
MySQL의 InnoDB에서 인덱스 설계가 중요한 이유 또한 이것이다.
레코드 수준의 잠금 확인 및 해제
MySQL 8.0부터는 performanceschema의 datalocks와 datalockwaits 테이블을 통해 확인할 수 있다.
# 현재 프로세스의 목록을 조회한다.
mysql> 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_watis w
JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_engine_transaction_id
JOIN infromation_schema.innodb_trx r
ON r.trx_id = w.requesting_engine_transaction_id;
# 각 스레드가 어떤 잠금을 가지는지 상세하게 조회한다.
mysql> SELECT * FROM performance_schema.data_locks\G문제가 되는 잠금이 있다면 mysql> KILL {thread_id} 를 통해 스레드를 강제 종료하자.