DB Lock

DB Lock

여러 사용자가 재고 1개인 상품을 동시에 구매하려고 하면, 재고를 확인하고 차감하는 사이에 서로의 작업이 겹칠 수 있습니다. 이러한 상황을 Race Condition 이 발생했다 라고 합니다. Lock은 이런 상황에서 데이터가 틀어지지 않도록 접근 순서를 지키게 하는 장치입니다.

이 글은 MySQL 8.0 InnoDB, 격리 수준은 기본값인 REPEATABLE READ를 전제로 합니다. 격리 수준이 READ COMMITTED이면 뒤에서 설명할 Gap Lock의 동작이 달라지므로, 재현이 되지 않는다면 먼저 격리 수준을 확인하는 것을 추천드립니다.

-- 격리수준을 확인할수 있는 명령어
SELECT @@transaction_isolation;

 

Shared Lock (S Lock)

Shared Lock은 여러 트랜잭션이 함께 읽을 수 있게 하는 잠금입니다. S Lock을 사용하는 이유는, 읽은 값이 트랜잭션이 끝날 때까지 다른 트랜잭션에 의해 바뀌지 않도록 고정하기 위함입니다.

잠금 없는 일반 SELECT와의 차이는 아래 쿼리에서 확인할수 있습니다.

-- [A]
START TRANSACTION;
SELECT * FROM child WHERE id = 10;        -- name 확인, 스냅샷 생성

-- [B]
START TRANSACTION;
UPDATE child SET name = 'changed' WHERE id = 10;
COMMIT;

-- [A]
SELECT * FROM child WHERE id = 10;            -- 예전 값
SELECT * FROM child WHERE id = 10 FOR SHARE;  -- 'changed'

 

마지막 A 트랜잭션의 두 SELECT가 서로 다른 값을 반환하는 것을 확인할 수 있습니다.

MySQL InnoDB 기준, 하나의 트랜잭션 안에서 일반 SELECT은 MVCC를 통해 최초 스냅샷의 정보를 그대로 가져옵니다. 이는 REPEATABLE READ (Mysql InnoDB 의 기본값 격리수준), 즉 하나의 트랜잭션에서 한 번 읽은 값은 계속 같은 값으로 보인다는 격리 수준의 특성입니다.

그러나 S Lock을 사용해 SELECT 하는 순간, 스냅샷이 아닌 미커밋 변경이 있으면 기다린 뒤 가장 최근 커밋 상태를 읽어, 트랜잭션 B가 변경한 changed를 가져오게 됩니다. 그리고 그 시점부터 다른 트랜잭션의 X Lock을 막으므로, 방금 읽은 값은 커밋할 때까지 유효하다는 것이 보장됩니다.

결론적으로 S Lock으로 읽은 데이터는 읽는 시점의 최신값이며, 해당 트랜잭션이 커밋될 때까지 그 값이 유지됩니다.

 

-- [A] S Lock 획득
START TRANSACTION;

SELECT * FROM child
WHERE id = 10
FOR SHARE;


-- [B] 다른 세션에서 실행

START TRANSACTION;

SELECT * FROM child
WHERE id = 10; -- 즉시 성공: 일반 SELECT는 잠금을 요청하지 않음

SELECT * FROM child
WHERE id = 10
FOR SHARE; -- 즉시 성공: S Lock끼리는 함께 획득 가능

SELECT * FROM child
WHERE id = 10
FOR UPDATE; -- 대기: X Lock은 S Lock과 충돌

UPDATE child
SET name = 'blocked'
WHERE id = 10; -- 대기: UPDATE는 X Lock이 필요

위 쿼리를 통해 다른 트랜잭션도 같은 데이터에 S Lock을 획득해 함께 읽을 수 있지만, X Lock을 획득해 수정하려 하면 대기하는것을 확인할 수 있습니다.

 

Exclusive Lock (X Lock)

Exclusive Lock은 한 트랜잭션이 행을 수정할 수 있게 하는 배타 잠금입니다. SELECT ... FOR UPDATE로 직접 요청할 수도 있고, UPDATE와 DELETE를 실행하면 자동으로 획득됩니다.

📌 S Lock과 X Lock의 차이는 읽기 보호인지, 수정 독점인지에 있습니다.
S Lock은 읽은 데이터를 기준으로 작업하는 동안 값이 변경되지 않도록 보호하는 잠금입니다. 따라서 다른 트랜잭션도 함께 읽을 수 있지만, 수정은 할 수 없습니다.
X Lock은 해당 데이터를 직접 수정하기 위해 획득하는 잠금입니다. 수정 중인 데이터에 다른 트랜잭션이 잠금 읽기하거나 수정하는 것까지 막아, 수정 작업을 한 트랜잭션만 수행하도록 보장합니다.

 

S Lock이 여러 트랜잭션에 공유되는 것과 달리, X Lock은 한 트랜잭션만 가질 수 있습니다. 다만 모든 접근을 막는 것은 아니고, 무엇이 막히고 무엇이 통과하는지는 아래 쿼리에서 확인할 수 있습니다.

-- [A] X Lock 획득

START TRANSACTION;
SELECT * FROM child WHERE id = 10 FOR UPDATE;


-- [B]

START TRANSACTION;
SELECT * FROM child WHERE id = 10; -- 즉시 성공
SELECT * FROM child WHERE id = 10 FOR SHARE; -- 대기
SELECT * FROM child WHERE id = 10 FOR UPDATE; -- 대기
UPDATE child SET name = 'blocked' WHERE id = 10; -- 대기

-- dbeaver 의 서로다른 스크립트 작성 페이지를 열어 테스트하면 대기 하는것을 확인할 수 있습니다.

 

B의 네 문장 중 첫 번째만 통과하고 나머지 세 개는 A가 커밋할 때까지 대기합니다. 즉 X Lock이 걸린 행에 대해 다른 트랜잭션의 S Lock과 X Lock은 모두 불가능하지만, 잠금 없는 일반 SELECT는 영향을 받지 않습니다.

잠금 없는 SELECT가 통과하는 것은 MVCC 덕분입니다. 잠긴 행을 기다리는 대신 최초 스냅샷의 값을 읽기 때문에, A가 무엇을 하고 있든 상관없이 즉시 결과를 반환합니다. 앞서 S Lock에서 본 것과 같은 원리입니다.

결론적으로 X Lock은 수정할 행을 다른 트랜잭션이 수정하거나, S Lock, X Lock 하는 것을 커밋 시점까지 차단합니다.

 

Intention Lock (IS Lock, IX Lock)

Intention Lock은 행에 S Lock 또는 X Lock을 잡으려는 의도를 테이블 수준에 표시하는 잠금입니다. 쿼리를 따로 직접 요청하는 것이 아니라 InnoDB가 자동으로 획득합니다. SELECT ... FOR SHARE는 IS Lock을, SELECT ... FOR UPDATE는 IX Lock을 설정합니다.

 

행에 S Lock을 얻으려면 먼저 테이블에 IS Lock 을 획득해야 하고, 행에 X Lock을 얻으려면 먼저 IX Lock을 획득해야 합니다. 즉 모든 행 잠금 앞에는 반드시 테이블 수준의 의도 표시가 먼저 붙습니다.

 

Intention Lock 이 필요한 이유는 테이블 전체를 잠그려는 트랜잭션 때문입니다. Intention Lock이 없다면, 테이블을 잠그려는 트랜잭션은 그 테이블의 모든 행을 하나씩 확인해서 잠긴 행이 있는지 검사해야 합니다. 테이블 수준에 IS, IX 표시를 남겨두면 이 검사가 한 번으로 끝납니다.

 

주의할 점은 Intention Lock 자체가 무언가를 막는 잠금이 아니라는 것입니다. Intention lock은 LOCK TABLES ... WRITE 같은 테이블 전체 요청 외에는 아무것도 막지 않습니다. 따라서 IS Lock끼리는 서로 충돌하지 않고, IX Lock끼리도 서로 충돌하지 않습니다. IX는 이 테이블의 어떤 행을 잠글 것이라는 사실만 담고 있을 뿐, 그것이 어느 행인지에 대한 정보는 없기 때문입니다. 만약 IX끼리 충돌한다면 서로 다른 행을 수정하는 트랜잭션들이 전부 대기하게 되어(락 전에 IX Lock 을 하므로), 행 수준 잠금이 존재할 이유가 사라집니다. 실제 충돌 판정은 다음 단계인 행 수준 잠금에서 일어납니다.

결과적으로 Intention Lock은 평소에 아무것도 막지 않고 기록만 남기다가, 테이블 전체를 잠그려는 요청이 들어왔을 때 빠르게 판정하는 역할을 합니다.

 

다음과 같은 쿼리를 통해 IS , IX Lock 을 확인해볼수있습니다.

START TRANSACTION;
SELECT * FROM child WHERE id = 10 FOR SHARE;

-- 서로다른 세션에서 (dbeaver 라면 서로 다른 스크립트 창에서 진행하면 됩니다.)

SELECT OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA
FROM performance_schema.data_locks
WHERE OBJECT_NAME = 'child';

 

해당 결과를 통해 TABLE, RECORD 에 락이 걸렸고 저희는 FOR SHARE 즉 S LOCK 을 획득했기에 IS Lock 이 걸린것을 확인할수 있습니다.

 

Record Lock

Record Lock은 인덱스 레코드 하나를 잠그는 잠금입니다.

SELECT id FROM product WHERE id = 10 FOR UPDATE;

이 쿼리는 id = 10인 행에 대해 다른 트랜잭션의 수정, 삭제, lock 을 막습니다.

여기서 중요한 점은 Record Lock이 행이 아니라 인덱스 레코드를 잠근다는 것입니다. InnoDB는 내부적으로는 항상 클러스터드 인덱스를 사용해 행을 관리합니다.

Record locks always lock index records, even if a table is defined with no indexes.

 

기본 키가 있으면 기본 키가 클러스터드 인덱스가 됩니다. 기본 키와 적절한 유니크 인덱스도 없다면, InnoDB는 행을 관리하기 위한 숨겨진 클러스터드 인덱스를 내부적으로 만듭니다. 따라서 사용자가 만든 인덱스가 없어도 Record Lock이 잠글 인덱스 레코드는 항상 존재합니다.

위의 인덱스를 만든다는 말이 WHERE 조건에 사용한 컬럼의 인덱스를 자동으로 만든다는 말은 아닙니다. 예를 들어 name 컬럼에 인덱스가 없다면, WHERE name = 'A'를 실행해도 InnoDB는 name 인덱스를 새로 만들지 않습니다. 대신 기존 클러스터드 인덱스를 처음부터 스캔하며 조건을 확인합니다.

REPEATABLE READ에서 이런 잠금 읽기나 수정 쿼리를 실행하면, 스캔한 인덱스 레코드와 그 사이의 gap까지 넓게 잠길 수 있습니다. 겉으로는 테이블 전체가 막힌 것처럼 보이지만, 많은 Record Lock과 Gap Lock, 즉 Next-Key Lock이 잡힌 것입니다.

If you have no indexes suitable for your statement and MySQL must scan the entire table to process the statement, every row of the table becomes locked, which in turn blocks all inserts by other users to the table."

 

-- name 컬럼에 인덱스가 없다면
UPDATE product SET stock = stock - 1 WHERE name = 'A';

의도는 한 행을 수정하는 것이지만, 인덱스가 없으면 스캔 과정에서 다른 행들까지 잠기게 됩니다.

 

Gap Lock

Gap Lock은 이미 존재하는 행이 아니라, 인덱스 레코드 사이의 아직 없는 값이 들어올 자리를 잠급니다.

SELECT * FROM child WHERE id BETWEEN 10 AND 20 FOR UPDATE;

이 잠금은 다른 트랜잭션이 10과 20 사이에 새 값을 INSERT 하는 것을 막습니다. 같은 조건으로 두 번 조회했을 때 없던 행이 생기는 팬텀 현상을 막기 위한 장치입니다.

Gap Lock은 X Lock, S Lock 에서만 걸립니다. SELECT ... FOR UPDATE, SELECT ... FOR SHARE, UPDATE, DELETE가 대상이며, 일반 SELECT는 애초에 Gap Lock도 없습니다.

 

Gap Lock이 걸리는 기준

Gap Lock 이 걸리는 기준은 같은 조건의 결과가 나중에 늘어날 수 있는가입니다. 늘어날 수 있으면 그 자리를 막아야 하므로 Gap Lock이 걸리고, 늘어날 수 없으면 걸리지 않습니다.

늘어날 수 없는 경우는 하나뿐입니다. 유니크 인덱스를 = 로 조회했고, 그 값의 행이 실제로 존재할 때입니다.

 

SELECT * FROM child WHERE id = 11 FOR UPDATE;

id가 기본 키이고 값이 11인 행이 존재한다면, 유니크 제약 때문에 11이 하나 더 생길 수 없습니다. 결과가 이미 확정되어 있으므로 레코드 하나에만 잠금이 걸립니다.

그외의 대부분 경우 Gap Lock이 걸립니다. 범위 조회, 논유니크 인덱스, 인덱스를 타지 못하는 조건, 기본 키로 조회했더라도 해당 행이 없으면 걸립니다. 이 섹션 첫 쿼리 WHERE id BETWEEN 10 AND 20 FOR UPDATE 예시가 기본 키인데도 Gap Lock이 걸리는 이유도 범위 조회이기 때문입니다.

여기서 기준이 되는 것은 테이블에 인덱스가 있는지가 아니라 WHERE 조건이 어떤 인덱스를 타는지입니다. 기본 키가 있는 테이블이라도 인덱스 없는 컬럼으로 조회하면 전체 스캔이 발생하고, 그 과정에서 만난 레코드와 gap이 모두 잠깁니다. 인덱스가 있더라도 옵티마이저가 전체 스캔을 선택하면 마찬가지입니다. 실제 잠금 범위가 궁금하다면 EXPLAIN으로 실행 계획을 확인하는 것이 명확한 락의 범위를 파악할수 있습니다.

READ COMMITTED로 격리 수준을 낮추면 검색과 인덱스 스캔에서 Gap Lock이 비활성화됩니다. 이 경우 Gap Lock은 외래 키 제약 검사와 중복 키 검사에만 사용됩니다.

 

Next-Key Lock

Next-Key Lock은 인덱스 레코드에 대한 Record Lock과, 그 레코드 앞의 Gap Lock을 합친 잠금입니다. REPEATABLE READ에서 InnoDB가 검색과 인덱스 스캔에 사용하는 기본 잠금 방식이며, 이를 통해 팬텀 현상을 방지합니다.

 

공식 문서의 예시를 그대로 가져오면, 인덱스에 10, 11, 13, 20 값이 있을 때 잠금 구간은 다음과 같이 나뉩니다.

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

구간 표기가 (이전 값, 현재 값] 형태인데, 즉 왼쪽은 열려 있고 오른쪽은 닫혀 있습니다. ((10, 11]은 10은 포함하지 않고 11은 포함한다는 뜻입니다.) 이것이 "레코드 + 그 앞의 gap"이라는 정의를 그대로 나타낸 것입니다. ( 인덱스 11 을 기준으로 보면 10 초과 11이하를 막으므로 레코드 + 그앞 gap 을 막는걸 확인할수 있습니다.)

공식 문서는 이를 다음과 같이 설명합니다.

If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.

 

마지막 구간에는 supremum이라는 개념이 등장합니다. 인덱스의 가장 큰 값보다 위쪽 구간을 잠글 때 사용하는 가상의 레코드입니다. supremum은 실제 인덱스 레코드가 아니므로, 이 next-key lock은 사실상 가장 큰 값 이후의 gap만 잠그는 효과를 냅니다. 위 구간에서 마지막 줄만 양쪽이 열려 있는 이유입니다 (20, positive infinity).

 

기본은 Next-Key Lock이고, 나머지는 축소된 형태입니다

공식 문서는 REPEATABLE READ에서 InnoDB가 검색과 인덱스 스캔에 next-key lock을 사용한다고 설명합니다. 즉 이것이 기본값이고, 앞서 본 Record Lock만 걸리는 경우가 오히려 예외입니다.

문서에 명시된 축소 조건은 두 가지입니다.

• 유니크 인덱스로 단일 행을 검색해 잠그는 경우에는 gap locking이 필요하지 않습니다
• 격리 수준을 READ COMMITTED로 낮추면 검색과 인덱스 스캔에서 gap locking이 비활성화됩니다

정리하면 InnoDB는 일단 레코드와 그 앞의 gap을 함께 잠그고, 팬텀이 발생할 수 없다는 것이 보장될 때만 gap을 떼어냅니다. 앞의 Gap Lock 섹션에서 정리한 기준이 곧 이 축소 조건입니다.

 

기본                          → Next-Key Lock (레코드 + 앞 gap)
  ├─ 유니크 인덱스 = 조회로 단일 행 특정  → Record Lock (gap lock 생략)
  └─ READ COMMITTED                    → Record Lock (gap lock 생략)

 

Insert Intention Lock

Insert Intention Lock은 INSERT가 행을 넣기 직전에 잡는 특수한 형태의 Gap Lock입니다. 같은 gap에 INSERT 하려는 여러 트랜잭션이, 서로 다른 위치에 넣는 것이라면 굳이 기다리지 않아도 되도록 의도를 표시하는 역할을 합니다.

먼저 이 잠금이 왜 필요한지 보겠습니다. 레코드가 10과 20만 있고 그 사이가 비어 있다고 하겠습니다.

 

-- [A]
START TRANSACTION;
INSERT INTO child (id) VALUES (12);

-- [B]
START TRANSACTION;
INSERT INTO child (id) VALUES (15);   -- 대기 없이 성공

둘 다 같은 gap에 INSERT 하지만 넣으려는 위치가 다르므로 서로 기다릴 이유가 없습니다. Insert Intention Lock끼리는 충돌하지 않기 때문에 이렇게 동시에 통과할 수 있습니다. 만약 INSERT가 해당 구간을 배타적으로 잡아버렸다면 뒤에 오는 INSERT가 모두 대기하게 됩니다.

반대로 이미 다른 트랜잭션이 그 구간에 Gap Lock을 잡고 있다면 대기합니다. 공식 문서의 예시를 보면, 먼저 클라이언트 A가 범위 조회로 gap에 잠금을 잡습니다.

 

-- child 테이블에 id 값 90, 102가 존재
-- 클라이언트 A
START TRANSACTION;
SELECT * FROM child WHERE id > 100 FOR UPDATE;

이 상태에서 클라이언트 B가 그 구간에 INSERT를 시도하면, INSERT 전 Insert Intention Gap Lock을 요청, A의 Gap Lock과 충돌해서 대기하게 됩니다.

 

-- 클라이언트 B
START TRANSACTION;
INSERT INTO child (id) VALUES (101);

정리하면 Insert Intention Lock끼리는 서로 막지 않고, 다른 트랜잭션이 잡아둔 Gap Lock에 의해서만 막힙니다. Gap Lock의 목적이 해당 구간으로의 INSERT를 막는 것이므로, 사실상 Insert Intention Lock이 Gap Lock에 걸리는 유일한 대상입니다.

이 잠금을 알아둬야 하는 실질적인 이유는 데드락 로그에서 보일수 있기 때문입니다.

 

-- 데드락 예시

-- [A]
SELECT * FROM child WHERE id BETWEEN 10 AND 20 FOR UPDATE;

-- [B]
SELECT * FROM child WHERE id BETWEEN 10 AND 20 FOR UPDATE; -- 통과! gap lock끼리 충돌 안 함

-- [A]
INSERT INTO child (id) VALUES (12); -- B의 gap lock에 막혀 대기

-- [B]
INSERT INTO child (id) VALUES (15); -- A의 gap lock에 막혀 대기 → 데드락

SHOW ENGINE INNODB STATUS의 출력에 아래와 같은 문구가 있다면, 다른 트랜잭션이 잡고 있는 gap에 INSERT를 시도하다 대기에 걸린 상황입니다.

lock_mode X locks gap before rec insert intention waiting

 

참조

Mysql 공식문서