MySQL의 갭 락과 넥스트 키 락에 대해

2024. 8. 1. 13:20ㆍArticle

프로젝트에서 MySQL을 사용하며 락과 트랜잭션 격리 수준에 대한 문제를 여러 번 마주했었다. 이번 기회에 좀 더 제대로 알고자 글을 쓴다.  

InnoDB 스토리지 엔진 잠금

InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 제공한다. 따라서 MyISAM보다 훨씬 뛰어난 동시성 처리를 제공할 수 있다.

 

또한 레코드 락뿐 아니라 레코드와 레코드 사이의 간격을 잠그는 갭 락이라는 것도 제공한다. 

InnoDB 스토리지 엔진은 인덱스 레코드에 락을 건다

InnoDB 스토리지 엔진은 레코드를 잠글 때, 레코드 자체가 아니라 인덱스의 레코드를 잠근다. 인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다. 

 

레코드 자체를 잠그느냐, 아니면 인덱스를 잠그느냐는 상당히 큰 차이를 만들어 내기 때문에 주의해야 한다.

 

예를 들어, member 테이블이 다음과 같다고 가정하자.

  • pk를 제외하고 nickname, address 칼럼이 있다.
  • memer의 address='서울'인 레코드는 100개이다.
  • address가 '서울'인 멤버 중 nickname = '홍길동'인 레코드드는 1개이다.

그리고 다음 쿼리를 실행한다고 가정하자.

UPDATE member SET age=27 WHERE address='서울' AND nickname='홍길동';

 

그럼 이때 락이 어떻게 설정될까? 이때 address는 인덱스가 존재하지만, nickname에는 인덱스가 없다. 따라서 address 인덱스를 통해 address='서울'인 모든 레코드를 찾고, 그 안에서 풀 스캔을 하며 nickname='홍길동'인 레코드를 찾을 것이다. 따라서 이때 address가 서울인 인덱스 레코드 전부에 락이 걸린다. 따라서 그 동안 address='서울'인 레코드에 쓰기 작업을 하려는 트랜잭션은 모두 대기하게 된다.

 

즉, 인덱스 설정이 동시성 처리 성능에 영향을 미칠 수 있다는 것을 고려하며 인덱스를 설정해줘야 한다.

갭 락(Gap Lock)

갭 락은 인덱스 레코드 사이의 간격에 대한 잠금 또는 첫 번째 또는 마지막 인덱스 레코드 앞뒤 간격에 대한 잠금이다.

"A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record."

 

이때 레코드 간 간격뿐만 아니라 첫 번째 또는 마지막 인덱스 레코드 앞뒤 간격에 대해서도 잠금을 걸 수 있다는 것에 주의해야 한다. 따라서 레코드 수가 적은 테이블에 갭 락을 걸 경우, 레코드 수가 적을수록 갭 락의 간격이 넓어지게 되어, 동시 처리 성능에 큰 영향을 줄 수도 있다.

REPEATABLE READ 격리 수준 보장

갭 락은 다음 목적을 위해 사용된다.

  • REPEATABLE READ 격리 수준 보장
  • Replication 일관성 보장
  • Foreign Key 일관성 보장

대표적으로 갭 락은 REPEATABLE READ 격리 수준을 보장한다. 즉, MySQL은 갭 락을 통해 반복 가능한 읽기를 보장하며, 팬텀 리드 문제를 방지한다. 그런데 그만큼 갭 락은 동시 처리 성능을 떨어트린다. 즉, 동시 처리 성능과 격리 수준 간 트레이드 오프를 고려하여, 갭 락을 사용해야 한다.

 

MySQL의 기본 설정인 REPEATABLE READ에서는 갭 락이 사용된다. 그리고 격리 수준을 READ COMMITTED로 설정하면 갭 락이 비활성화된다. 이경우 갭 락은 검색 및 인덱스 스캔에 대해 비활성화되며 외래 키 제약 조건 검사 및 중복 키 검사에만 사용된다.

"Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED. In this case, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking."

 

다음은 READ COMMITTED에서 갭 락이 사용되지 않는 것을 보여주는 예시이다.

  T1 T2
1 SET transaction_isolation='READ-COMMITTED'; SET transaction_isolation='READ-COMMITTED';
2 BEGIN; BEGIN;
3 SELECT * FROM member WHERE id BETWEEN 1 AND 3 FOR UPDATE;  
4   INSERT INTO member VALUES (2, '철수');
5   COMMIT;
6 SELECT * FROM member WHERE id BETWEEN 1 AND 3 FOR UPDATE;  

 

READ COMMITTED 격리 수준에서 위 쿼리들을 수행하면, 3번과 6번의 SELECT 결과가 다르다. 즉, T1이 3번에서 SELECT FOR UPDATE로 배타 락을 걸었지만, T2가 4번에서 id=2 위치에 INSERT를 정상적으로 수행한 것이다. 즉, READ COMMITTED에서는 SELECT FOR UPDATE 명령이 갭 락을 걸지 않는다는 것을 의미한다. 따라서 READ COMMITTED에서는 반복 가능한 읽기가 보장되지 않는다.

MVCC와 갭 락을 통한 REPEATABLE READ 격리 수준 보장

그런데 REPEATABLE READ 격리 수준에서도 항상 동일 결과가 조회되는 것은 아니다. 이 경우에도 팬텀 리드 문제가 발생할 수 있다.

 

REPEATABLE READ에서는 잠금 없이 격리 수준을 보장하기 위해 MVCC를 사용한다. 즉, 언두 로그에 자신의 앞 트랜잭션이 커밋한 데이터만 읽어오기 때문에 트랜잭션 중에 다른 트랜잭션이 실행되고 값을 쓰더라도 기존 트랜잭션은 그 값이 보이지 않는다. 즉, 반복 가능한 읽기가 보장된다.

 

그런데 SELECT FOR SHARE, SELECT FOR UPDATE와 같이 락을 사용하는 조회에서는 언두 로그에 락을 걸 수 없으니 테이블에서 조회해온다. 따라서 이 경우에는 팬텀 리드가 발생할 수 있다. 따라서 MySQL InnoDB는 REPEATABLE READ에서 팬텀 리드 문제를 방지하기 위해 갭 락을 사용한다.  레코드와 갭 락을 합친 넥스트 키 락이라고도 할 수 있다. 즉, T1이 레코드를 조회하면서 해당 레코드와 그 전후에 넥스트 키 락을 걸기 때문에, 그 동안 T2가 해당 범위에 값을 쓰려고 하면 대기하게 된다. 따라서 T1 입장에서는 넥스트 키 락을 건 덕분에 팬텀 리드 문제가 발생하지 않는다.

 

정리하자면, REPEATABLE READ에서 락을 걸지 않는 조회는 MVCC를 사용하여 언두 로그에서 데이터를 조회하고, 락을 거는 조회에서는 원본 테이블에서 데이터를 조회하는데 갭 락을 통해 팬텀 리드 문제를 방지한다.

공유 갭 락 = 배타 갭 락

갭 락은 공유 락 형태로만 존재한다. 즉, 갭 락의 경우 공유 락이든 배타 락이든 동일하다. 따라서 여러 트랜잭션에서 동시에 갭 락을 획득할 수 있다.

"Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function."

 

갭 락은 다른 트랜잭션이 대상 간격에 새로운 레코드를 INSERT하는 것을 막는 것이 주 목적이다. 따라서 UPDATE, DELETE, SELECT FOR UPDATE 등의 문장으로 인해 배타 갭 락을 설정했더라도, 이는 공유 갭 락과 같은 의미이고 여러 트랜잭션에서 갭 락을 동시에 쥐고 있을 수 있다.

넥스트 키 락(Next Key Lock) = 레코드 락 + 갭 락

갭 락은 순수하게 레코드 사이의 간격만 잠그는 것이 아니라, 필요에 따라 레코드와 간격을 동시에 잠그기도 한다. 이렇게 레코드 락과 갭 락을 합친 형태가 넥스트 키 락이다.

갭 락을 거는 상황

MySQL에서 어떤 인덱스를 통해 테이블을 조회하는지에 따라 사용되는 락이 다르다.

  • PK(Primary Key)와 UK(Unique Key)
    • 쿼리 조건이 1건의 결과를 보장하는 경우, 갭 락은 사용되지 않고 레코드 락만 사용된다.
    • 쿼리 조건이 1건의 결과를 보장하지 못하는 경우, 레코드 락 + 갭 락이 사용된다. (레코드가 없거나, 복합 인덱스에서 일부 컬럼만으로 WHERE 조건이 사용된 경우 포함)
  • Non-Unique Seconday Index
    • 쿼리 결과 레코드 건수에 상관없이 항상 레코드 락 + 갭 락이 사용된다.

예를 들어, 다음 쿼리를 실행한다고 가정하자.

SELECT * FROM child WHERE id = 100;

 

만약 id가 인덱싱되어 있지 않거나 nonunique 인덱스라면, 해당 쿼리문은 앞의 갭에 잠금을 건다. 

"If id is not indexed or has a nonunique index, the statement does lock the preceding gap."

 

반면, id가 인덱싱되어 있으면 id가 100인 행에 대해서만 인덱스 레코드 락이 걸리고 갭 락이 걸리지 않는다.

갭 락의 문제점: 동시 처리 성능

MySQL에서 갭 락은 REPEATABLE READ를 보장하기 위해 사용된다. 그 말은 즉, 갭 락이 동시 처리 성능을 낮춘다고 볼 수 있다.

 

1억 건이 있는 테이블에 비해 적은 수의 레코드가 저장된 테이블에서 갭 락이 미치는 영향이 더 크다. 예를 들어, 테이블에 레코드가 한 건도 없다고 가정하자.

  T 1 T 2
1 BEGIN; BEGIN;
2 UPDATE member SET name='철수' WHERER id=5  
3   INSERT INTO member VALUES (1, '영희');
  1. T1에서 UPDATE 쿼리를 실행하면 테이블에 레코드가 한 것도 없기 때문에 T1은 member 테이블의 PK(id) 인덱스의 시작 레코드부터 마지막 레코드까지의 간격에 갭 락을 걸게 된다.
  2. 따라서 T2는 INSERT를 하려는데 해당 간격에 갭 락이 걸려 있으므로 인서트 인텐션 락을 획득하지 못하고, T1의 갭 락이 해제될 때까지 대기하게 된다.

즉, 테이블의 레코드 수가 적으면 적을수록 갭 락의 간격이 넓어지게 된다. 따라서 테이블의 레코드 건수가 적은 상태에서 동시에 실행되는 트랜잭션 수가 많을 경우, 트랜잭션 간 대기하는 경우가 빈번히 발생한다.

인서트 인텐션 락(Insert Intention Lock)

갭 락은 간격을 잠근다. 그리고 레코드 건 수가 적을 수록 이 간격이 커진다. 따라서 갭 락은 간격을 잠금으로써, 여러 쿼리가 서로 충돌되지 않고 INSERT 할 수 있음에도 이들이 동시에 처리되지 못하고 대기하게 된다. MySQL에서는 이 문제를 해결하기 위해 INSERT만을 위한 갭 락인 인서트 인텐션 락을 제공한다. 이는 INSERT시 설정되는 일종의 갭 락이다.

 

인서트 인텐션 락은 행 삽입 전에 INSERT 연산에 의해 설정되는 일종의 갭 락이다. 이는 동일한 간격에 레코드를 삽입하려는 여러 트랜잭션이 있을 때, 각 트랜잭션이 동일 간격 내의 서로 다른 위치에 삽입하는 경우, 서로를 기다릴 필요가 없도록 삽입 의도를 알린다.

"An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap."

 

갭 락과 인서트 인텐션 락은 호환이 불가하지만, 인서트 인텐션 락과 인서트 인텐션 락은 호환이 가능하다. 따라서 갭 락이 걸린 간격에는 인서트 인텐션 락 획득을 대기하기 때문에 INSERT 할 수 없다. 반면, 인서트 인텐션 락을 건 각각의 트랜잭션이 서로 다른 위치에 INSERT하는 것이라면 대기 없이 동시에 INSERT가 수행될 수 있다. 즉, 인서트 인텐션 락들이 호환되는 상황에서는 중복 키 에러만 아니면 동시에 실행될 수 있다.

 

다음 예를 보자. (member 테이블에 레코드가 한 건도 없는 상황이라고 가정)

   T1 T2
1 BEGIN; BEGIN;
2 INSERT INTO member VALUES (2, '철수'); INSERT INTO member VALUES (3, '영희');
  1. member 테이블에 아무 레코드가 없기 때문에 2번 쿼리로 인해 T1과 T2는 PK(id) 인덱스 레코드의 시작부터 끝까지 간격에 인서트 인텐션 락을 건다 (동시에 같은 범위에 대해 인서트 인텐션 락 획득 가능).
  2. 그리고 두 트랜잭션이 서로 다른 위치에 INSERT하므로 INSERT 시 행이 충돌하지 않으므로 서로를 막지 않고 바로 INSERT가 수행된다.

인서트 인텐션 락 + 갭 락으로 인한 데드락

인서트 인텐션 락과 갭 락은 호환이 불가하다고 했다. 따라서 이로 인한 데드락 문제가 발생할 수 있다.

 

다음 예제는 데드락이 발생하는 상황이다. 참고로 member 테이블에는 아무 레코드도 없는 상황이라 가정한다.

   T1 T2
1 BEGIN; BEGIN;
2 SELECT * FROM member WHERE id=2 FOR UPDATE; SELECT * FROM member WHERE id=2 FOR UPDATE;
3 DELETE FROM member WHERE id=2; DELETE FROM member WHERE id=2;
4 INSERT INTO member VALUES (2, '철수');  
5   INSERT INTO member VALUES (2, '철수');
  1. 위 예제에서 3번까지의 쿼리는 실행 시점에 관계없이 2개의 트랜잭션 간 상호 간섭이나 대기없이 즉시 실행된다. member 테이블에 아무 레코드가 없기 때문에 2번 쿼리로 인해 PK(id) 인덱스 레코드의 시작부터 끝까지 갭 락이 걸린다. 그리고 갭 락은 호환이 가능하기 때문에 세션 1과 세션 2는 2번 쿼리에 의해 동시에 갭 락을 갖고, 이어서 3번의 DELETE까지 수행하게 된다.
  2. 그런데 4번과 5번의 INSERT를 실행하기 위해서는 인서트 인텐션 락이 필요하다. 그러나 인서트 인텐션 락은 갭 락과 호환될 수 없다. 따라서 T1은 T2가 갭 락을 내려놓을 때까지 기다리고, T2는 T1이 갭 락을 내려놓을 때까지 기다린다. 즉, T1과 T2는 각자 갭 락을 가지고 있지만, 호환되지 않는 인서트 인텐션 락을 획득하기 위해 상대방의 갭 락이 해제되기를 기다린다. (데드락 발생)

Reference

저작자표시

'Article' 카테고리의 다른 글

좋아요 동시성 제어 과정  (1) 2024.08.20
서비스 계층에 @Transactional 어노테이션을 붙이는 것에 대한 고찰  (3) 2024.07.20

관련글

티스토리툴바