왜 많은 회사들은 READ COMMITTED를 사용할까?

한 줄 요약: 여러가지 이유가 더 있겠지만, Gap Lock으로 인한 Dead Lock 발생 문제가 크다. 

 

사내에서 왜 READ COMMITTED를 쓰는지에 대한 질문과 답변을 보고, 공부하여 정리한 글입니다.

 

1. 왜 READ-COMMITTED를 사용할까 ?

대부분의 서비스 회사는 MySQL을 쓰는 경우 READ COMMITTED를 사용한다고 합니다.

MySQL은 default isolation-level이 REAPEATABLE-READ를 사용하고 있는데, 왜 READ-COMMITTED로 조정하여서 사용하고 있을까요?

2. Gap lock 이란 무엇인가?

참고: https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-gap-locks

gap lock 은 인덱스 레코드 사이 간격에 대한 잠금을 말합니다.

아래 트랜잭션이 있는 경우, 다른 트랜잭션에서는 c1 에 15 값을 삽입할 수 없게 됩니다.

SELECT c1 FROM t WHERE c1 BETWEEN 10 AND 20 FOR UPDATE;

즉, table에 insert 될 수 있는 곳에 insert를 하지 못하도록 잠굴 수 있는데, 이를 잠구는 것을 gap lock이라고 합니다.

그림으로 보자면, 아래 테이블에서 1과 3사이 그리고 3과 5사이에 갭 락을 거는 경우 데이터가 들어올 수 없습니다.

id name

1	foo
3	bar
5	baz

2.1. 그렇다면 gap lock을 왜 사용하는가?

• Reapeatable Read 격리 수준 보장
  • MySQL의 경우 innoDB를 사용하면, Repeatable Read에서도 phantom read가 발생하지 않음.
• Replication 일관성 보장
• Foreign Key 일관성 보장

2.2 READ-COMMITTED 사용시 문제

아래와 같은 transaction이 있는 경우, READ-COMMITTED에서는 phantom read가 발생할 수 있다.

즉, 하나의 트랜잭션에서 범위 쿼리를 하는 경우, 결과가 동등하지 못하다.

SELECT … SELECT … FOR UPDATE 의 경우

// session 1
SELECT * 
FROM some_table
WHERE id BETWEEN 1 AND 3 FOR UPDATE;
// 1, 3 만 나옴 

<--- session 2 (INSERT INTO some_table values (2, 'kim');

SELECT * 
FROM some_table
WHERE id BETWEEN 1 AND 3 FOR UPDATE;
// 1, 2, 3 모두 나옴  

2.3 gap lock은 언제 발생하는가?

• Primary Key와 Unique Index 인데, 결과가 있는 경우

이 경우는 갭 락이 걸리지 않는다. 위 인덱스의 경우에는 결과가 1개임을 보장하는 경우이다. 따라서, Record Lock 만 걸리고 Gap Lock이 걸리지 않는다. 결과가 없는 경우는 갭락이 걸린다. 

• Non-unique Index

unique 하지 않은 경우는 그 결과의 개수와 관련없이 항상 Record Lock + Gap Lock이 사용된다.

• 쿼리가 1개의 결과를 보장하지 않는 경우

예를들어, 복합 인덱스 중, 일부 컬럼만 WHERE절로 사용하거나, BETWEEN과 같은 연산자를 사용하는 경우 Record Lock과 Gap Lock이 같이 사용된다.

2.4 gap-S-lock ? gap-X-lock?

공유 갭 잠금과 배타적 갭 잠금은 본질적으로 차이가 없습니다. 즉, 갭 락의 경우 아래 테이블과 다르게 서로 충돌하지 않고, 동일한 기능을 수행합니다.

InnoDB의 Gap Lock은 “pure inhibitive”로 다른 트랜잭션이 gap 에 insert를 막는 것이 목적입니다.

예를들어, A 트랜잭션이 특정 갭에 공유 락을 걸고, B 트랜잭션이 동일 갭에 배타적 잠금도 걸 수 있습니다.

즉, UPDATE, DELETE, SELECT … FOR UPDATE 를 통해 gap lock을 획득한다고 하여도, 이는 shared gap lock과 다르지 않고, 잠금 경합이 발생하지 않습니다.

충돌하는 갭 잠금이 허용되는 이유는 인덱스에서 레코드가 제거될 경우, 서로 다른 트랜잭션이 해당 레코드에 보유한 갭 잠금을 병합해야 하기 때문입니다.

READ COMMITTED를 사용하여 비활성화 할 수 있습니다. 이 경우 갭 잠금은 검색 및 인덱스 스캔에 대해 비활성화되며 외래 키 제약 조건 검사 및 중복 키 검사에만 사용됩니다.

3. Gap Lock의 특징과 주의사항

3.1 Next Key Lock

Next Key Lock 은 Record Lock과 Gap Lock 을 합친 것이다.

아래 sql을 실행하면, 아래가 모두 잠기게 된다.

• 1번 레코드 (레코드)
• 3번 레코드 (레코드)
• 1번 레코드와 3번 레코드 사이 (gap)

UPDATE some_table
SET ...
WHERE id BETWEEN 1 AND 3;

3.2 Dead Lock이란?

일반적으로 dead lock은 서로 다른 트랜잭션에서 각자가 변경할 레코드를 각자 변경한 상황에서 발생한다. 즉, 자원을 획득하려고 서로 경합하다가 서로 대기하는 현상을 말한다.

아래는 간단한 예시.

// session 1
START TRANSACTION 

UPDATE some_table SET ...      UPDATE some_table SET...
WHERE id=1;                    WHERE id=3;

UPDATE some_table SET ...
WHERE id=3; 

                               UPDATE some_table SET...
															 WHERE id=1;

 

 Gap Lock 이 트랜잭션간 영향도는 데이터가 많은 경우, 크지 않을 수 있습니다.

 하지만, 동시성은 높고 데이터가 적을수록, 문제가 발생할 가능성이 높다. 왜냐하면, 레코드가 적을 수록 전체 레코드 수 대비 상대적으로 gap 이 넓어질 수 있기 때문입니다.

 

 예를들어, 빈 테이블에 id가 100인 레코드 레코드를 업데이트 하는 트랜잭션이 있을 때, 레코드가 존재하지 않기 때문에 갭락이 걸리게 된다. 즉, id 가 100 까지의 갭이 모두 잠기게 됩니다.

데이터가 적을 수록 상대적으로 갭이 넓게 보일 수 있습니다.

// session 1
UPDATE some_table
SET name = 'kim'
WEHERE id = 100; 

// session 2
INSERT INTO some_table VALUES (5, 'lee'); 

 

3.3 Insert Intention Lock

 아래 예시를 보기 전에, insert intention lock에 대해 알아봅시다.

 

insert intetntion lock이란, insert 연산시 설정되는 갭 락입니다.

만약 여러 트랜잭션이 insert 를 하는데, 일반 갭 락을 걸면 서로 다른 곳에 insert를 함에도 불구하고 갭 락 때문에 기다려야하는 현상이 생길 수 있습니다. 즉, 겹치지 않는 곳에 insert 를 함에도 불구하고 동시성 처리가 떨어지는 현상을 겪을 수 있습니다.

 

 그렇기 때문에 insert intention lock 이라는 별도의 갭 락을 걸고, 서로 충돌하지 않는다면 같은 갭에 데이터를 insert 할 수 있게 해주는 것입니다.

 

일반적으로 insert 연산시

1. insert intention lock을 걸고
2. 배타적 잠금을 얻게 됩니다.

insert intention lock 도 갭 락의 일종으로 해당 락 끼리는 충돌하지 않습니다.
하지만, 이 insert intention lock은 gap lock 과는 충돌합니다.

 

 

3.3 Gap Lock으로 인한 Dead Lock?

 빈 테이블에, 아래의 트랜잭션이 실행된다고 가정해봅시다.

빈 테이블이기 때문에, SELECT와 DELETE 모두 갭락을 걸게 됩니다. 배타적 락이 아니라 갭 락이기 때문에, 동시 수행되는데 문제가 없습니다.

 

 그런데 4번에서 1번 세션이 insert 를 거는 순간 insert intention lock 을 걸어야 합니다. 하지만, 2번 세션에서, gap lock을 걸고 있기 때문에 session 1 은 2번 세션의 갭락이 풀릴 때까지 기다려야합니다. 5번 차례가 되는 경우 2번 세션의 경우도, insert를 하려고 할때 1번 세션의 갭락을 기다리기 때문에, 데드락이 발생합니다.

출처:   https://medium.com/daangn/mysql-gap-lock-%EB%8B%A4%EC%8B%9C%EB%B3%B4%EA%B8%B0-7f47ea3f68bc

 

 

 

모두가 이를 인지하고 인덱스를 잘 설계하거나, transaction을 짧게만 가져가면 문제가 덜 발생할 수 있으나, 
이 두가지를 모두 지키는 것이 쉽지 않기 때문에 READ COMMITTED를 사용한다고 합니다. 

 

 

 

 

참고자료 

- https://medium.com/daangn/mysql-gap-lock-%EB%8B%A4%EC%8B%9C%EB%B3%B4%EA%B8%B0-7f47ea3f68bc

MySQL Gap Lock 다시보기

- https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-gap-locks