우리가 일반적으로 알고 있는 데이터베이스 서버의 잠금(Lock)은 레코드 자체에 대한 잠금(Record Lock)이에요. 어떤 트랜잭션에서 레코드를 변경하기 위해서는 그 레코드를 잠그고, 그 동안은 다른 트랜잭션은 변경중인 레코드의 잠금이 해제되어야 변경을 할 수 있게 됩니다. 이는 우리 모두가 이미 잘 알고 있는 데이터베이스 잠금이죠.
하지만 모든 데이터베이스 서버는 매우 복잡한 업무들을 처리하는 소프트웨어이며, 이를 위해서 단순 레코드 수준의 잠금뿐만 아니라 훨씬 더 복잡한 잠금 메커니즘을 가지고 있어요. 오늘은 MySQL 서버에서만 볼 수 있는 특별한 형태의 잠금인 Gap Lock에 대해서 살펴보려고 해요. 아마도 MySQL을 포함한 데이터베이스 서버를 사용하면서 데드락(Dead Lock)은 누구나 경험해보는 고민거리인데, MySQL 서버에서 이런 잠금 관련 문제들의 대부분은 지금부터 살펴 볼 Gap Lock으로 인한 것들일 가능성이 매우 높아요.
MySQL 서버의 잠금 그리고 관련 기능들에 대한 더 자세한 내용은 “Real MySQL 8.0”을 참고해주세요.
여기에서부터는 문장의 간결함을 위해서 높임말은 생략할게요.
Gap Lock 이란 ?
Gap Lock의 이해를 위해서, 간단한 예제 데이터를 살펴보자.
CREATE TABLE tb_gaplock (
id INT NOT NULL,
name VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (id)
);INSERT INTO tb_gaplock
VALUES (1, ‘Matt’), (3, ‘Esther’), (6, ‘Peter’);
tb_gaplock 테이블에 실제 저장된 레코드는 3건 (Matt, Esther, Peter)인데, 위의 그림에서는 존재하지 않는 id 컬럼 값 3건을 같이 표시했다. 즉, tb_gaplock 테이블의 id 컬럼은 Primary Key이므로 1과 3 그리고 6을 제외한 값은 언제든지 INSERT될 수 있는 상태이다.
MySQL 서버에서는 이런 상태의 테이블에서 id=1인 레코드와 3인 레코드 사이의 간격과 id=3과 6 사이의 간격을 잠글 수 있다. 이렇게 실제 존재하지 않는 레코드 공간(간격)을 잠그는 것을 MySQL 서버에서는 Gap Lock이라고 한다. Gap Lock은 Primary Key뿐만 아니라 Secondary Index에도 동일하게 사용된다는 것도 기억해 두도록 하자.
Gap Lock이 사용되었다는 것은 아주 간단히 확인할 수 있다. MySQL 클라이언트 프로그램으로 다음과 같이 트랜잭션을 실행후 COMMIT하지 않은 상태에서,
/* MySQL Client 1 */
mysql> BEGIN;
mysql> UPDATE tb_gaplock SET name=’Matt2' WHERE id=3 /* Not-exist id */;
다른 MySQL 클라이언트 프로그램에서 아래 SQL을 실행해서 결과를 확인해보면 된다.
/* MySQL Client 2 */
mysql> SELECT * FROM performance_schema.data_locks \G
*************************** 1. row ***************************
ENGINE_LOCK_ID: 140244814860136:1150:140245786187056
ENGINE_TRANSACTION_ID: 4016304
THREAD_ID: 166
OBJECT_SCHEMA: test
OBJECT_NAME: tb_gaplock
INDEX_NAME: NULL
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
*************************** 2. row ***************************
ENGINE_LOCK_ID: 140244814860136:89:4:9:140245790489632
ENGINE_TRANSACTION_ID: 4016304
THREAD_ID: 166
OBJECT_SCHEMA: test
OBJECT_NAME: tb_gaplock
INDEX_NAME: PRIMARY
LOCK_TYPE: RECORD
LOCK_MODE: X,GAP
LOCK_STATUS: GRANTED
performance_schema.data_locks 테이블의 결과에서 첫번째는 테이블 잠금(LOCK_TYPE=TABLE)이며, 두번째는 LOCK_MODE 컬럼의 “X, GAP”은 Exclusive Gap Lock이 걸린 것을 의미한다. 두번째 정보의 LOCK_TYPE=RECORD로 표시된 것은 테이블 수준의 잠금이 아니라 레코드 수준의 잠금을 의미하는 것일 뿐, 테이블에 존재하는 레코드 자체의 잠금으로 해석하면 안된다.
이번 예제에서는 이해를 돕기 위해서 간단한 정수 타입 컬럼을 예시로 했지만, DOUBLE이나 DATETIME 와 같은 타입에서는 2개의 값 사이에 중복되지 않게 저장될 수 있는 값의 개수는 거의 무한대에 가깝다는 것과 Unique하지 않은 인덱스(Secondary Index)에서는 동일한 값이 중복해서 많이 저장될 수 있다는 것도 기억해두자.
Gap Lock의 필요성
MySQL 서버의 Gap Lock은 크게 아래 3가지 목적을 위해서 사용된다.
- Repeatable Read 격리 수준 보장
- Replication 일관성 보장 (Binary Log Format = Statement 또는 Mixed)
- Foreign Key 일관성 보장
MySQL 클라이언트 프로그램을 이용해서 2개의 컨넥션을 준비해서, 아래와 같은 순서대로 SQL을 실행해보자. 이 예제에서는 Read Committed 격리 수준에서 발생하는 문제점을 살펴보면서, 역으로 Repeatable Read에서 Gap Lock이 필요한 이유를 이해해보자.
이 예제를 직접 실행해보면, 3번 6번 SELECT의 결과가 다르다는 것을 확인할 수 있다. 여기 예제에서는 Repeatable Read가 아닌 Read Committed 격리 수준에서는 1번 세션에서 id 컬럼이 1보다 크거나 같고 3보다 작거나 같은 범위에 대해서 배타적 잠금을 걸었는데, 2번 세션에서는 id=2인 레코드를 INSERT 할 수 있다는 것을 확인할 수 있다. 즉 이는 세션 1번의 SELECT .. FOR UPDATE 명령이 Gap Lock을 걸지 않았다는 것을 의미하며, 그와 동시에 Read Committed 격리 수준에서는 동일 트랜잭션내에서도 다른 결과가 나올 수 있다는 것도 보여주고 있다.
MySQL 서버의 Replication은 데이터의 복제를 위해서 바이너리 로그(Binary Log) 파일을 사용하는데, 바이너리 로그는 3가지 포맷(STATEMENT, ROW, MIXED)중 하나를 선택할 수 있다. 여기에서 STATEMENT와 MIXED는 일반적인 경우, 실행된 SQL 문장을 바이너리 로그 파일로 기록하기 때문에 거의 유사한 포맷이라고 볼 수 있다. 이제 격리 수준 예제와 같이 2개의 컨넥션을 준비해서, 아래와 같이 SQL을 실행해보자.
이 예제에서는 4번 INSERT는 즉시 완료되지 않고 3번 UPDATE를 실행한 세션 1번의 트랜잭션이 COMMIT 또는 ROLLBACK되어야 완료된다. 이는 세션 1번의 트랜잭션이 id=1과 3 레코드뿐만 아니라 id=1과 3 사이의 간격을 잠그고 있기 때문이다. 그런데 이 예제에서 만약 Gap Lock이 없고 세션 2번의 트랜잭션이 대기없이 INSERT가 실행되었다고 가정하면, 세션 1번보다 세션 2번이 먼저 COMMIT을 실행할 수 있게 된다. 만약 세션 2번이 먼저 COMMIT되고 그 이후 세션 1번이 COMMIT된다면, 복제 Source DB와 Replica DB에서의 트랜잭션 실행 순서가 거꾸로 되기 때문에, 데이터는 서로 달라지는 결과를 만들게 된다. 이렇게 바이너리 로그 포맷이 STATEMENT일 때 간격의 잠금이 필요한 이유는, 이런 복제 Source와 Replica간의 데이터 부정합을 막기 위함이다.
Gap Lock의 특징과 주의 사항
MySQL 서버의 Gap Lock은 아래와 같은 2가지 큰 특징을 가진다.
- Shared Gap Lock = Exclusive Gap Lock
- Next Key Lock = Record Lock + Gap Lock
Gap Lock은 MySQL 서버 내부적으로 Shared Lock 형태만 존재(Exclusive Gap Lock은 없음)하며, 순수하게 다른 트랜잭션이 대상 간격(Gap)에 새로운 레코드가 INSERT되는 것을 막는 것이 주 목적이다. 그래서 UPDATE나 DELETE 그리고 SELECT .. FOR UPDATE 문장에 의한 Gap Lock이라 하더라도 (설령 performance_schema.data_locks에 보여지는 정보가 “LOCK_MODE: X,GAP”로 Exclusive Gap Lock이라 하더라도), 여러 트랜잭션의 Gap Lock은 서로 호환된다. 즉, 2개 트랜잭션에서 동시에 “UPDATE tb_gaplock SET .. WHERE id=2 /* Not-existed record */" 명령을 실행해도 잠금 경합은 발생하지 않는다.
또한 Gap Lock은 순수하게 레코드 사이의 간격만 잠그는 것이 아니라, 필요에 따라 레코드와 간격을 동시에 잠그기도 한다. 예를 들어서 “UPDATE tb_gaplock SET .. WHERE id BETWEEN 1 AND 3” 명령을 실행하면, 이 때 MySQL 서버는 id=1과 3 그리고 그 사이의 간격을 동시에 잠그게 된다. 이렇게 Record와 Gap을 동시에 잠그는 형태를 MySQL 서버에서는 Next Key Lock이라고 하며, 실제 업무 프로그램에서는 이런 Next Key Lock이 더 일반적으로 사용 된다.
Gap Lock은 레코드와 레코드 사이의 간격을 잠그기 때문에 서로 다른 트랜잭션간 영향도가 그다지 크지 않을거라 생각하기도 한다. 실제 1억건 레코드를 테이블에 각 트랜잭션에서 서로 다른 인덱스 키 값을 접근해서 데이터를 변경하는 프로그램의 경우, Gap Lock으로 인한 성능 저하 현상은 거의 발생하지 않는 것처럼 보일 수 있다. 하지만 위 예제의 tb_gaplock 테이블에 레코드가 한 건도 없는 상태에서 다음 예제를 한번 생각해보자.
1번 세션의 트랜잭션에서 UPDATE가 실행되면, (테이블의 레코드가 한 건도 없기 때문에) 1번 트랜잭은 tb_gaplock 테이블의 Primary Key 시작 지점(Pseudo Infimum Record)부터 마지막 지점(Pseudo Supremum Record)까지의 간격을 잠그게 된다. 그래서 2번 트랜잭션에서는 어떠한 값이라 하더라도 INSERT를 완료하지 못하고 1번 트랜잭션이 완료될 때까지 대기하게 된다. 즉, 테이블의 레코드 건수가 적으면 적을수록 Gap Lock의 간격이 넓어지는 역효과를 내게 된다는 것이다. 그래서 테이블의 레코드 건수가 적은 상태에서 동시 실행되는 트랜잭션이 많을 경우, 트랜잭션간 상호 간섭과 대기를 더 고려해야 할 때도 있다.
Gap Lock으로 인한 Dead Lock
일반적으로 우리가 알고 있는 Dead Lock은 다음과 같은 형태일 것이다. 트랜잭션 1번은 id=1 레코드를 변경하고 트랜잭션 2번은 id=3 레코드를 변경(Exclusive Lock 획득)한 상태에서, 각 트랜잭션이 다른 트랜잭션에서 변경한 레코드에 대해서 잠금을 대기하는 상태이다.
하지만 실제 업무 프로그램에서 발생하는 Dead Lock은 이렇게 단순하지 않다. 만약 이런 단순한 형태라면 레코드의 업데이트 순서(Primary Key 순서대로 UPDATE 하도록)만 바꿔주면 쉽게 해결된다. 우리가 개발하는 응용 프로그램에서 발생하는 대부분의 Dead Lock은 지금까지 살펴본 Gap Lock으로 인해서 복잡한 형태로 발생한다.
Gap Lock으로 인한 Dead Lock중에서 조금 더 복잡한 예제를 한번 살펴보자. 우선 이 예제를 이해하기 위해서는 MySQL 서버에서 동등 비교(Equal 비교) 조건이 인덱스 종류별로 어떤 잠금이 사용되는지를 알고 있어야 한다.
- Primary Key와 Unique Index
- 쿼리의 조건이 1건의 결과를 보장하는 경우, Gap Lock은 사용되지 않고 Record Lock만 사용됨
- 쿼리의 조건이 1건의 결과를 보장하지 못하는 경우, Record Lock + Gap Lock이 동시에 사용됨 (레코드가 없거나, 여러 컬럼으로 구성된 복합 인덱스를 일부 컬럼만으로 WHERE 조건이 사용된 경우 포함) - Non-Unique Secondary Index
- 쿼리의 결과 대상 레코드 건수에 관계없이 항상 Record Lock + Gap Lock이 사용됨
이제 Dead Lock 발생 예제를 한번 살펴보자. 이 예제는 id=2인 레코드가 있다면, 먼저 잠금을 걸어서 삭제하고 다시 INSERT하고자 하는 예제이다. 이 예제에서 SELECT .. FOR UPDATE 문장은 없어져도 똑같이 Dead Lock 이 발생하지만, 일반적으로 개발시에 많이 사용되는 시나리오를 그대로 나열해 보았다.
위의 예제에서 3번까지의 SQL 명령은 실행 시점이나 순간에 관계없이, 2개 트랜잭션간 상호 간섭이나 대기없이 즉시 실행된다. 실제 tb_gaplock 테이블에 id=2인 레코드가 존재하지 않으므로 SELECT .. FOR UPDATE 문장과 DELETE 문장은 Record Lock 없이 Gap Lock만 획득하게 되는데, Gap Lock은 항상 Shared 모드이므로 서로 충돌하지 않고 잠금 획득이 허용된다. 그래서 서로 같은 id=2 레코드에 대해서 SELECT .. FOR UPDATE 와 DELETE 문장이 잠금 대기없이 실행될 수 있는 것이다.
그런데 4번과 5번의 INSERT 문장은 다른 형태의 잠금인 “INSERT Intention Gap Lock”을 필요로 한다. 하지만 “INSERT Intention Gap Lock”은 Gap Lock과 호환되지 않기 때문에, 1번 트랜잭션의 INSERT는 2번 트랜잭션이 가진 Gap Lock을 기다리게 되고, 2번 트랜잭션의 INSERT는 1번 트랜잭션이 가진 Gap Lock을 기다리게 되어서 Dead Lock 상황이 발생하게 되는 것이다. 여기에서 1번과 2번 트랜잭션은 각자 이미 Gap Lock을 가지고 있지만, 호환되지 않는 잠금(INSERT Intention Gap Lock)을 획득하기 위해서 상대방 트랜잭션의 Gap Lock이 해제되기를 기다려야 한다.
MySQL 서버에서 Primary Key나 Unique Index를 이용하는 경우에는 Gap Lock 없이 Record Lock만 사용된다고 많이들 이해하고 있어, 이런 형태의 Dead Lock이 MySQL 서버의 오류(Bug)로 인한 것이라고 오해하기도 한다. 그리고 여기에서는 간단히 Primary Key에 대한 예시만 보였지만, 복합 컬럼으로 구성된 Primary Key나 Unique Index 또는 Secondary Index 등에서 비슷하지만 조금씩은 다른 잠금 이슈들이 발생하게 된다. Dead Lock이 발생할 때마다 대충 넘기지 않고 정확한 원인을 하나씩 해결하다 보면, 이런 패턴들이 더 쉽게 이해될 수 있을 것이다.
Gap Lock 사용 최소화 & Concurrency 향상
이미 살펴 보았던 것처럼, MySQL 서버에서 Gap Lock은 바이너리 로그 포맷과 격리 수준에 의해서 사용될 수도 사용되지 않을 수도 있다. MySQL 서버에서 아래 2개 파라미터(System variables)를 변경하면 Gap Lock의 사용을 최소화할 수 있다.
- binlog_format = ROW
- transaction_isolation = READ-COMMITTED
하지만 이 2 개 파라미터는 MySQL 서버의 물리적인 특성(하드웨어 사양)과 업무적인 특성(REPEATABLE-READ 격리 수준의 필요 여부)에 따라서 적절히 선택되어야 한다. 또한 이 2개 파라미터가 변경되면, 위의 Dead Lock 상황은 발생하지 않게 된다. Dead Lock이 발생하지 않는다고 해서 좋은 것만은 아니라는 것도 기억해야 한다. 예를 들어서, Dead Lock이 발생하면 개발자가 문제를 인지할 수 있지만, Dead Lock이 발생하지 않으면 서로 다른 트랜잭션에서 INSERT한 레코드들이 섞여서 저장되면서 의도하지 않은 버그를 만들어낼 수도 있다.
바이너리 로그 포맷을 변경하는 것은 그다지 큰 변화는 아니지만, 격리 수준을 변경하는 것은 금전 관련 서비스에서는 매우 중요한 변화이기 때문에 주의해야 한다.
INSERT Intention Gap Lock
MySQL 서버는 Gap Lock의 자매품으로 INSERT Intention Gap Lock이라는 잠금 형태도 있다. 이미 살펴본 바와 같이 Gap Lock은 매우 넓은 범위의 잠금 효과를 만들게 될 수 있다. 그래서 Gap Lock으로 인해서 서로 충돌 없이 동시에 처리될 수 있는 INSERT 문장임에도 불구하고, 동시 처리 성능이 심각하게 훼손되었다. MySQL 에서는 이 문제를 해결하기 위해서 INSERT만을 위한 Gap Lock인 INSERT Intention Gap Lock을 도입하여, INSERT 문장들은 Duplicate Key 에러만 아니면 동시에 실행될 수 있도록 구현한 것이다.
MySQL 서버의 잠금 이야기를 재미있게 읽으셨다면 그리고 더 많은 것들이 궁금하고 배우고 싶다면 당근 마켓 개발자와 DBA로 지원해주세요.
