[MySQL] 잠금(Lock)에 관하여

kyle·2024년 5월 9일

MySQL의 InnoDB는 데이터에 대한 동시 요청이 발생한 경우, 일종의 잠금(Lock)을 통해 무결성을 보장한다. 잠금을 구현하는 방식에 따라 공유락과 배타락으로 나눌 수 있으며, 잠금을 어느 범위로 거는지에 따라 레코드락과 갭락, 넥스트 키락으로 나눌 수 있다.

1. 공유락과 배타락

1-1. 공유락(Shared Lock, s-lock)

공유락은 트랜잭션이 특정 행에 대한 읽기 권한만을 가지도록 허용하는 잠금이다.

SELECT * FROM t WHERE id = 1 FOR SHARE;

FOR SHARE 키워드를 통해 id가 1번인 행에 대한 공유락을 획득 할 수 있다.
공유락을 획득한 데이터는 해당 트랜잭션 내에서 조회(SELECT) 될 수는 있지만, 수정(UPDATE) 혹은 삭제(DELETE) 될 수는 없다.

단순 SELECT 문 만으로도 데이터를 조회 할 수 있는데, 굳이 공유락을 획득하는 이유는 무엇일까?

이는 공유락이 쓰기 연산을 허용하지 않는다는 특성 때문이다. 공유락을 획득한 데이터는 트랜잭션이 진행되는 동안 다른 트랜잭션에 의해 수정되지 않음이 보장되므로 무결성이 지켜진다.
따라서 공유락을 획득한 행에 대해서는 다른 트랜잭션에서 공유락을 추가적으로 획득 할 수는 있으나, 배타락을 획득 할 수는 없다.

1-2. 배타락(Exclusive Lock, x-lock)

배타락은 트랜잭션이 특정 행에 대한 읽기 및 쓰기 권한을 가지도록 허용하는 잠금이다.

SELECT * FROM t WHERE id = 1 FOR UPDATE;

FOR UPDATE 키워드를 통해 id가 1번인 행에 대한 배타락을 획득 할 수 있다.
배타락을 획득한 데이터는 해당 트랜잭션 내에서 조회, 수정, 삭제 될 수 있다.
배타락을 획득한 데이터에 대해서, 다른 트랜잭션에서는 공유락과 배타락 모두 추가적으로 획득 할 수 없다. 따라서 배타락은 해당 데이터에 대한 독점 권한을 가지는 잠금이라고 보면된다.

1-3. 공유락과 배타락 실습

트랜잭션 1이 공유락을 획득한 후, 트랜잭션 2가 추가적으로 공유락을 획득하려는 경우 (가능)
- 서로 다른 트랜잭션에서 하나의 데이터에 대한 공유락을 동시에 획득 할 수 있다.

트랜잭션 1이 공유락을 획득한 후, 트랜잭션 2가 추가적으로 배타락을 획득하려는 경우 (불가능)
- 공유락을 획득한 데이터에 대해서, 배타락을 획득하려는 경우에는 앞서 걸린 공유락이 풀릴 때까지 대기한다.
- 앞서 걸린 공유락이 일정 시간 이상 풀리지 않는 경우, Lock wait timeout이 발생한다.

트랜잭션 1이 배타락을 획득한 후, 트랜잭션 2가 추가적으로 공유락을 획득하려는 경우 (불가능)
- 배타락을 획득한 데이터에 대해서, 공유락을 획득하려는 경우에는 앞서 걸린 배타락이 풀릴 때까지 대기한다.
- 앞서 걸린 배타락이 일정 시간 이상 풀리지 않는 경우, Lock wait timeout이 발생한다.

1-4. 데드락 주의하기

공유락과 배타락을 사용할 때, 데드락 발생 가능성에 대해 주의해야 한다.

데드락(Dead Lock)이란, 서로가 점유하는 자원에 대한 무한정 대기 상태를 의미한다.

예를 들면 아래와 같은 상황에서 데드락이 발생 할 수 있다.

트랜잭션 1은 1번 데이터에 대한 공유락을 획득한다.
트랜잭션 2는 2번 데이터에 대한 공유락을 획득한다.
트랜잭션 1은 2번 데이터에 대한 배타락을 획득하려 하지만, 이미 트랜잭션 2에 의해 공유락이 걸려있으므로 대기한다.
트랜잭션 2는 1번 데이터에 대한 배타락을 획득하려 하지만, 이미 트랜잭션 1에 의해 공유락이 걸려있으므로 대기한다.
서로 배타락 획득 과정에서 대기하며 데드락이 발생한다.

아래는 해당 과정을 직접 실행한 결과이다.

2. 레코드락(Record Lock)

일반적으로 레코드락은 테이블의 레코드 자체에 대한 잠금을 의미한다.

MySQL의 InnoDB는 테이블의 레코드가 아닌 인덱스의 레코드를 잠근다는 것이 특징이다.

예를 들어, 아래와 같은 user 테이블이 있다고 해보자.

그리고 name이 “kyle”인 레코드를 대상으로 공유락을 획득한다.

SELECT * FROM user WHERE name = 'kyle' FOR SHARE;

이제 아래의 명령어를 통해 어떤 레코드에 어떤 잠금이 걸렸는지 확인해보자.

SELECT * FROM performance_schema.data_locks;

놀랍게도 name이 “kyle”인 레코드 뿐만 아니라, 전체 레코드에 공유락(S)이 걸린 것을 확인할 수 있다.

MySQL의 InnoDB는 레코드가 아니라 인덱스를 기준으로 레코드락을 건다고 하였다.
하지만 user 테이블에 인덱스를 생성하지 않았으므로, InnoDB는 자체적으로 클러스터 인덱스(PK)를 만들어 잠금을 설정한다. 따라서 모든 레코드가 해당 인덱스에 속한다고 간주하여 전체를 잠근다.
이렇듯 InnoDB에서는 인덱스 설계를 어떻게 하느냐에 따라서, 일부 레코드만 잠그려 했을 뿐인데 테이블 전체가 잠길 수도 있다. 인덱스 설계가 굉장히 중요함을 알 수 있는 부분이다.

그렇다면 이제 name 컬럼에 인덱스를 생성하고 같은 작업을 반복해보면 어떻게 될까?

CREATE INDEX name_index ON user (name);

아까와는 다르게 name이 “kyle”인 세 개의 레코드에만 공유락(S)이 걸리는 것을 확인할 수 있다.

여기서 또 재밌는 점이 있는데, SELECT 문을 아래와 같이 살짝 바꿔보면 어떻게 될까?

SELECT * FROM user WHERE name = 'kyle' and age = 30 FOR SHARE;

WHERE 절에 age가 30이라는 조건을 추가했다.
name이 “kyle”이면서 age가 30인 레코드는 한 개 뿐이므로, 레코드락도 한 개의 레코드에만 걸릴 것이라 예상 할 것이다.

하지만 실제로 결과를 확인해보면 이전과 같이 세 개의 레코드에 모두 공유락(S)이 걸리는 것을 확인할 수 있다.

InnoDB는 레코드가 아닌 인덱스를 기준으로 잠금을 걸기 때문에, 인덱스에 포함되지 않은 컬럼을 조건으로 추가하여도 여전히 인덱스를 기준으로 레코드락을 걸게 된다.
따라서 항상 이러한 점을 주의해서 인덱스를 설계할 필요가 있다.

3. 갭락과 넥스트 키락

3-1. 갭락(Gap Lock)

갭락은 단일 레코드가 아닌 레코드와 레코드 사이 혹은 인덱스의 맨 앞 혹은 끝의 바깥 범위를 잠그는 것을 의미한다.

레코드락처럼 특정 데이터에 대한 잠금 용도로 사용하는 것이 아니라, 데이터의 생성, 수정, 삭제를 방지하는데 사용되는 잠금 방식이다.

InnoDB에서는 갭락만 따로 사용하지 않고, 레코드락과 함께 넥스트 키락이라는 개념으로 사용한다.

따라서 갭락이 어떻게 적용되는지 아래에서 자세하게 살펴보자.

3-2. 넥스트 키락(Next Key Lock)

넥스트 키락은 레코드락과 갭락을 함께 사용하는 것을 의미한다.

실제로 InnoDB에서 불필요한 데이터의 생성, 수정, 삭제를 방지하는데 사용되며, REAPEATABLE READ 격리수준에서 적용된다.

예를 들어 다음과 같은 user 테이블이 있다고 하자.

name 컬럼에 대한 인덱스를 적용한다.

CREATE INDEX name_index ON user (name);

트랜잭션 1에서 h로 시작하는 name을 가진 user들에 대해 배타락을 획득한다.

SELECT * FROM user WHERE name LIKE 'h%' FOR UPDATE;

h로 시작하는 이름인 haley, harry라는 두 개의 레코드에 대해 배타락을 획득하였다.

트랜잭션 2에서 “howard”라는 이름의 user를 생성(INSERT)한다.

INSERT INTO user (name, age) VALUE ('howard', 40);

잠금으로 인해 시간 초과가 되어 Lock wait timeout이 발생하였다.
트랜잭션 1에서는 단순히 haley, harry라는 레코드에 잠금을 걸었을 뿐인데, 왜 새로운 데이터를 생성할 수 없을까?

이는 갭락 때문이다.

트랜잭션 1에서 인덱스의 범위 검색을 통해 조회한 레코드들에 배타락을 걸면, 해당 인덱스의 앞뒤로 데이터를 생성하지 못하도록 잠금을 거는데, 이를 갭락(Gap Lock)이라고 한다.

종합하자면, 트랜잭션 1과 트랜잭션 2의 결과에 따른 잠금이 아래와 같이 나타나게 된다.

여기서 데이터의 순서는 데이터베이스 테이블의 레코드 순서가 아니라, B-Tree의 인덱스 순서임에 주의한다.

트랜잭션 1로 인해 haley, harry라는 인덱스에 레코드락이 걸리고, 해당 인덱스 전, 후, 사이로 데이터가 생성, 수정, 삭제되지 못하도록 갭락이 걸린다. 이런 방식을 넥스트 키락이라고 한다. 따라서, howard가 생성 될 경우, 인덱스 정렬에 의해 harry의 다음에 위치하게 될 것이다. 하지만 갭락이 존재하므로 생성할 수 없어, 잠금이 풀릴 때까지 기다려야 한다. 이전의 경우 기다리는 시간이 너무 길기 때문에 timeout이 발생한 것이다.

그렇다면 만약 h로 시작하는 이름이 아니면, 갭락에 걸리지 않고 정상적으로 생성될까?

INSERT INTO user (name, age) VALUE ('steve', 10);

정상적으로 생성된다.

이는 h로 시작하는 name의 인덱스와 그 사이에만 넥스트 키락이 걸린 것이기 때문에, steve라는 인덱스는 넥스트 키락이 걸린 위치와 연관되어 있지 않아서 정상적으로 생성이 된다고 볼 수 있다.

여기서 더 재밌는 사실을 알 수 있는데, 그럼 h로 시작하지는 않지만 갭락이 있는 위치에 해당할만한 인덱스라면 어떨까?

INSERT INTO user (name, age) VALUE ('isaac', 15);

역시나 갭락을 대기하며 timeout이 걸리게 된다.

isaac은 인덱스에서 정렬될 때, harry 다음으로 들어가게 되므로 갭락에 의해 생성이 불가능하다.

3-3. 갭락을 사용하는 이유

지금까지 MySQL의 InnoDB는 레코드락과 갭락을 모두 사용하는 넥스트 키락 방식으로 잠금을 적용한다고 보았다. 그렇다면 왜 갭락이라는 것을 사용할까? 단순히 레코드락만으로도 동시 요청에 대한 데이터 무결성을 지킬 수 있지 않을까?

갭락의 필요성을 알기 위해, 일단 갭락을 사용하지 않는 환경에서 하나의 실험을 해보자.

MySQL에서 갭락을 사용하지 않으려면 트랜잭션 격리수준을 REPEATABLE READ에서 READ COMMITTED로 내리면 된다.

다시 아까와 같은 user 테이블이 있다고 해보자.

아래의 시나리오 대로 쿼리문을 실행해보자.

트랜잭션 1을 시작한다.
트랜잭션 2를 시작한다.
트랜잭션 1은 h로 시작하는 이름에 해당하는 레코드들에 배타락을 획득한다. → 결과 1
트랜잭션 2는 howard라는 이름의 user를 생성한다.
트랜잭션 2를 커밋하고 종료한다.
트랜잭션 1은 h로 시작하는 이름에 해당하는 레코드들에 배타락을 획득한다. → 결과 2

3번과 6번 항목 사이에는 트랜잭션 1에서 어떠한 쿼리문도 실행하지 않는다.
따라서 3번의 결과 1과 6번의 결과 2는 트랜잭션 1에서 동일한 모습으로 나타나야 한다.

일단 트랜잭션 1에서 3번을 실행하면 아래와 같은 결과 1이 나타난다.

h로 시작하는 이름인 haley, harry라는 레코드가 잠금 조회된다.

이후 트랜잭션 2에서 howard라는 이름의 user를 생성한 후 커밋한다.

-- 트랜잭션 2
INSERT INTO user (name, age) VALUE ('howard', 20);
COMMIT;

현재 트랜잭션 격리수준이 READ COMMITTED이므로 갭락이 존재하지 않아, 정상적으로 생성된다.

마지막으로 트랜잭션 1에서 6번을 실행하면 아래와 같은 결과 2가 나타난다.

이상하다! 분명 트랜잭션 1의 결과 1에서는 haley, harry만 나왔는데, 결과 2에서는 트랜잭션 2에서 추가한 howard까지 잠금 조회가 된다.
트랜잭션 1에서는 결과 1과 결과 2 사이에 어떠한 작업도 하지 않았는데, 동일한 쿼리문에 다른 결과가 나타나는 것이다.

이렇게 하나의 트랜잭션 내에서 특정 레코드가 보였다 안보였다 하는 현상을 유령 읽기(Phantom Read)라고 한다. 갭락이 존재하지 않는 READ COMMITTED 격리수준에서는 다른 트랜잭션에서 데이터를 생성하는 것을 막을 수 없다. 따라서 h로 시작하는 이름의 새 user를 생성할 수 있다. 잠금 조회를 할 때는 MySQL의 언두 로그(Undo Log)가 아닌 데이터베이스에서 직접 조회를 하므로(언두 로그는 잠금이 없기 때문) 새롭게 생성된 데이터까지 조회되는 것이다.

MySQL의 InnoDB 엔진은 기본 격리수준으로 REPEATABLE READ를 채택하고 있으며, 여기에는 갭락을 포함한 넥스트 키락을 이용해 위와 같은 유령 읽기(Phantom Read) 현상을 방지하고 있다.

kyle

공유를 기반으로 선한 영향력을 주는 개발자가 되고 싶습니다.

이전 포스트