데이터베이스 락(Lock)

김명일·2022년 5월 31일

락(Lock)

테이블, 행과 같은 리소스에 접근을 제어하는 객체의 개념을 말합니다.

Locking

Locking이란 읽거나 변경중인 데이터가 다른 트랜잭션에 의해 변경되거나 읽혀지는 것을 제어하기 위한 시스템입니다. 이러한 Locking 전략은 동시성과 성능에 맞게 적절하게 선택해야 합니다. 잘못된 전략에 의해 Locking으로 인해 Dead Lock이 발생할 수 있습니다.

Locking의 종류

Mysql InnoDB(8.0)의 Locking 종류

Rock Mode

Shared and Exclusive Locks

InnoDB에서 기본적으로 실행되는 행수준의 잠금으로 Shared Lock(s lock), Exclusive Lock(x lock)이 있다.

Shared Lock이란 잠근 행을 다른 트랜잭션이 읽을 수 있도록 허용하고 Shared Lock을 얻을 수 있지만 쓰기는 불가능하도록 하는 잠금이다. 즉, 해당 행에 대해 다른 트랜잭션이 읽는 것은 가능하지만 변경은 불가능하도록하는 잠금 수준입니다.

Exclusive Lock이란 다른 트랜잭션이 같은 오브젝트를 잠그지 못하도록 하는 잠금입니다.

A트랜잭션이 R에 대해 Shared Lock을 획득한 상태에서 B트랜잭션이 R에 대해
i) Shared Lock을 요청하는 경우: A, B트랜잭션 모두 Shared Lock을 획득 합니다.
ii) Exclusive Lock을 요청하는 경우, 대기하게 됩니다.

A트랜잭션이 R에 대해 Exclusive Lock을 획득한 상태에서 B트랜잭션이 R에 대해 Shared Lock, Exclusive Lock을 요청하는 경우 모두 대기하게 됩니다.

Shared Lock은 일반적인 SELECT에는 걸리지 않는다.SELECT ... FOR SHARE(SELECT ... LOCK IN SHARE MODE(하위버전))에서 걸리게 된다. Exclusive Lock은 SELECT ... FOR UPDATE시 걸리게 됩니다.

실제로 SELECT ... FOR UPDATE 실행 후, SHOW ENGINE INNODB STATUS 명령어를 실행해보면 어떤 트랜잭션이 몇개의 row를 lock하고 있는지 확인할 수 있습니다.

transaction

Intention Locks

테이블 수준의 잠금으로, 추후에 테이블 내 행에 걸리게 될 잠금 수준(row-level lock)을 나타내는 잠금입니다. Intention Lock에도 Intention Shared Lock(is lock), Intention Exclusive Lock(ix lock)이 존재하며 각각은 테이블 내에 행들이 s lock이나 x lock이 걸릴 것이라는 것을 의미합니다.

아래에 표에서 conflict에 해당하는 경우, 충돌한 락이 처리될 때까지 대기하게 됩니다.

Rock Type

Record Locks

Record Lock은 index record 잠금 입니다. 예를 들어, SELECT c1 FROM t1 WHERE t1.c1 = 10 FOR UPDATE;인 쿼리를 실행하는 경우, t1.c1 값이 10인 인덱스 레코드에 잠금이 걸립니다. 테이블에서 따로 인덱스를 정의해주지 않더라도 mysql에는 clustered index를 생성해, record lock을 위해 해당 인덱스를 사용합니다.

Gap Locks

Gap Lock은 인덱스 사이의 범위에 잠금을 거는것을 말합니다. 예를 들어 SELECT c1 FROM t1 WHERE c1 BETWEEN 3 AND 7 FOR UPDATE;를 A트랜잭션에서 실행하고, B트랜잭션에서 INSERT INTO t1(c1) VALUES(6);을 실행하면 c1이 3과 7사이인 인덱스에 락이 걸려있으므로 INSERT문은 대기하게됩니다. 이외에 UPDATE, DELETE문도 대기하게 됩니다.
그렇기 때문에 phantom read를 방지할 수 있습니다.

SELECT c1 FROM t1 WHERE c1 BETWEEN 3 AND 7 AND c1 = 6 FOR UPDATE;의 결과가 하나의 row만 나오더라도 c1이 3에서 7사이인 인덱스에 락이 걸리게 됩니다.

격리 수준을 READ_COMMITED로 변경하여, gap lock을 비활성화 할 수 있습니다.

왜 필요할까??
A트랜잭션에서 UPDATE t1 SET c1 = 77 WHERE id BETWEEN 1 AND 3;을 실행하고 커밋되기 이전에 B트랜잭션에서 INSERT INTO t1(id,c1) VALUES(2,6);을 실행하고 먼저 커밋된다면, 복제되는 데이터베이스에서는 INSERT가 일어나고 UPDATE가 발생하기 때문에 원하는 변경이 일어나지 않을 수 있다.

index와 gap lock

Primary Key와 Unique Key가 적용된 컬럼에서 쿼리의 결과가 1건인 경우, Record Lock만 걸리게된다. 그러나 레코드가 없거나, 여러 컬럼으로 구성된 복합 인덱스를 일부 컬럼만으로 WHERE 조건이 사용된 경우 gap lock과 record lock이 함께 사용된다.
non-unique secondary index인 경우, gap lock + record lock이 적용된다.

Next-Key Locks

record lock과 gap lock의 조합이다. UPDATE t1 SET c1 = 77 WHERE id BETWEEN 1 AND 3;을 실행하는 경우, 1과 3사이 그리고 1과 3까지 락이 걸리게 된다.

위와 같은 테이블 상태에서 A트랜잭션을 실행한 후 아래 와 같이 B트랜잭션을 실행하게면 B트랜잭션은 대기하게 된다.

그러나 INSERT INTO t1 VALUES(2,10);을 수행하는 경우 대기 없이 실행된다.

현재 테이블을 기준으로 (-무한대, 1], (1,3], (3, +무한대) 3가지 구간으로 나눠질 수 있는데, UPDATE t1 SET c1 = 100 WHERE id = 10;을 수행하게 되면 (3, +무한대) 구간에 락이 걸리게된다. 따라서 id가 4인 값을 INSERT할 경우 막히게 되지만 id가 2인 값을 INSERT하는 경우 잘 실행되게 된다.