복잡한 MySQL Lock의 세계

MySQL Lock이라는 키워드로 구글링을 하면 너무 많은 종류가 있고 결론적으로 어떤 방식을 사용한다는 것인지 이해가 되지 않았다. 본 글은 Real MySQL 8.0 1권을 읽고 정리한 글이다. 

 

MySQL에서는 InnoDB에서 제공하는 잠금과 MySQL에서 제공하는 잠금 두 종류가 있다. 또 각각에서 제공하는 잠금에는 범위에 따라 여러 종류로 구분된다. 각 잠금들은 무엇이고 언제 사용되는 지 정리해보자.

MySQL의 잠금

MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다. MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치고, 스토리지 엔진 레벨의 잠금은 다른 스토리지에는 영향을 미치지 않는다.

MySQL 8.0부터는 InnoDB가 기본 스토리지 엔진으로 채택되었다. 일반적으로 InnoDB 스토리지 레벨의 잠금이 트랜잭션을 위해 사용된다. 하지만 MySQL 엔진 레벨 잠금도 특정한 상황에서 사용된다. (쿼리 캐시 기능은 MySQL 엔진 레벨 잠금을 사용하여 캐시의 일관성을 보장한다.)

MySQL 엔진 레벨 잠금

1. 글로벌 락

• FLUSH TABLES WITH READ LOCK

글로벌 락은 MySQL 서버 전체에 영향을 미치며, MySQL에서 제공하는 잠금 중에서 가장 범위가 크다.

하나의 세션에서 글로벌 락을 휙득하면 다른 세션에서 SELECT를 제외한 DDL 혹은 DML 쿼리를 실행하는 경우 글로벌 락이 해제될 때까지 대기 상태에 들어간다.

2. 테이블 락

• LOCK TABLES table_name [READ | WRITE]

개별 테이블 단위로 설정되는 잠금이다. 명시적으로 휙득한 잠금은 UNLOCK TABLES 명령으로 잠금을 해제할 수 있다.

글로벌 락과 마찬가지로 온라인 작업에 큰 영향을 줄 수 있으므로 일반적으로 잘 사용되지 않는다.

3. 네임드 락

• GET_LOCK()

네임드 락은 임의의 문자열에 대하여 잠금을 설정할 수 있다. 테이블, 레코드, AUTO_INCREMENT 등의 데이터베이스 객체가 아닌 말 그대로 사용자가 지정한 “문자열”에 대하여 잠금을 휙득하고 해제한다.

배치 프로그래밍에서 한번에 많은 레코드를 변경하는 쿼리는 자주 데드락이 발생한다. 각 프로그램의 실행 시간을 분산하거나 코드를 수정할 수 있지만 쉬운 방법이 아니다. 이러한 경우에 동일한 데이터를 변경하는 프로그램끼리 분류하여 네임드 락을 걸고 쿼리를 실행할 수 있다.

4. 메타데이터 락

데이터베이스 객체의 이름이나 구조를 변경하기 위해 사용하는 잠금이다. 명시적으로 획득하고 해제할 수 있는 잠금이 아니다.

• RENAME TABLE table_name TO new_name

위와 같이 테이블의 이름을 변경하는 경우 자동으로 휙득되는 잠금이다. 이 때, 원본 이름과 변경될 이름 두 개에 모두 잠금을 설정한다.

InnoDB 스토리지 엔진 레벨 잠금

InnoDB 스토리지 엔진은 MySQL 엔진의 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 제공한다. (레코드 = 행 = 튜플)

1. 레코드 락

일반적인 DBMS의 레코드 락과 같이 레코드 자체만을 잠그는 것을 의미한다. 다만 InnoDB 스토리지 엔진은 레코드 자체가 아닌 인덱스의 레코드를 잠근다.

만약 인덱스가 하나도 없는 테이블이라면, 내부적으로 자동 생성된 클러스터 인덱스를 이용한다.

InnoDB에서는 대부분 보조 인덱스를 이용한 변경 작업은 넥스트 키 락 혹은 갭 락을 사용한다. 프라이머리 키 혹은 유니크 인덱스에 의한 변경 작업에서는 레코드 락만을 사용하게 된다.

2. 갭 락

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

레코드 사이에 새로운 레코드가 생성되는 것을 제어한다. 일반적으로 넥스트 키 락의 일부로 사용된다.

3. 넥스트 키 락

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

아래와 같은 경우에 넥스트 키 락 방식으로 잠금이 걸린다.

• MySQL 서버에서 REPEATABLE READ 격리 수준을 사용해야 한다.
• innodb_locks_unsafe_for_binding 시스템 변수가 비활성화(0)

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

데드락이 발생할 수 있으므로 바이너리 로그 포맷을 ROW 형태로 바꾸어 넥스트 키락이나 갭 락을 줄이는 것이 좋다. (?) - Real MySQL 8.0 p168

4. 자동 증가 락

AUTO_INCREMENT 컬럼이 사용된 테이블에 동시에 여러 레코드가 INSERT 되는 경우에 순서대로 증가하는 숫자를 각 레코드가 가질 수 있어야 한다. InnoDB 스토리지 엔진 내부에서는 이를 위해 테이블 수준의 AUTO_INCREMET 락을 사용한다.

AUTO_INCREMENT 락은 INSERT와 REPLACE 쿼리 문장과 같이 새로운 레코드를 저장하는 쿼리에서만 필요하다. UPDATE와 DELETE 등의 쿼리에서는 사용되지 않는다.

다른 잠금과는 다르게 트랜잭션과 관계없이 INSERT 혹은 REPLACE 문장에서 AUTO_INCREMENT 값에 접근하는 순간에만 락이 걸린 후 즉시 해제된다. AUTO_INCREMENT 락은 테이블에 단 하나만 존재하므로 두 개의 INSERT 쿼리가 동시에 실행되면 하나의 쿼리는 대기를 해야 한다.

명시적으로 AUTO_INCREMENT 락을 휙득하고 해제하는 방법은 없다. (MySQL 5.1 부터는 innodb_autoinc_lock_mode 시스템 변수를 통해 작동 방식을 변경할 수 있다.)

인덱스와 잠금

InnoDB의 잠금이 인덱스를 잠그는 것이라고 설명했는데 인덱스를 잠그는 방식은 레코드를 직접 잠그는 방식 다르다.

인덱스 잠금은 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 하는 방식이다.

---

예시를 들어보자,

first_name 컬럼에 인덱스가 걸려있고, first_name=’Lee’ 인 사람이 300명, 그 중에서 last_name=”Gaegool” 인 사람은 1명이 있는 경우이다.

UPDATE students SET graduate_date=NOW() WHERE first_name='Lee' AND last_name='Gaegool'

위와 같은 쿼리를 실행한다면 InnoDB의 레코드 락은 인덱스에 잠금을 걸기 때문에 해당 쿼리는(세션) first_name=’Lee’인 300개의 레코드에 모두 잠금을 휙득하게 된다.

만약 적절한 인덱스를 설정하지 못하면 하나의 세션을 기다리기 위해 다른 클라이언트는 같은 테이블을 업데이트 하지 못하고 계속해서 기다려야 할 것이다.

만약에 인덱스가 하나도 없는 경우라면, 테이블을 FULL SCAN 하면서 모든 레코드에 잠금을 휙득하게 될 것이다. MySQL의 InnoDB에서 인덱스 설계는 매우 중요한 이유이다.

잠금 상태 확인

• MySQL 8.0부터는 performance_schema의 data_locks와 data_lock_waits 테이블을 이용해 잠금과 잠금 대기 순서를 확인할 수 있다.
• SELECT … FROM perfomance_schema.data_lock_waits [ w | r ]