MySQL의 잠금(Lock)

안녕하세요, 오늘은 MySQL의 Lock에 대해 살펴보겠습니다.

 

잠금(Lock)이란 동시성을 제어하는 기능입니다. 예를 들어 여러 커넥션에서 하나의 레코드를 수정할 수 있게 되면 그 결과를 예측하기 어려울 것입니다. 잠금은 여러 커넥션에서 변경을 시도할 경우 특정한 단위 (레코드, 테이블)에 대해 하나의 커넥션만 변경할 수 있게끔 만들어 주는 역할을 합니다. 

 

Lock을 이해하려면 트랜잭션에 관해서도 이해가 필요합니다. Lock은 위에 서술하였듯 동시성을 제어하는 기능이고, 트랜잭션은 하나의 논리적인 작업 단위 안에서 데이터 정합성을 보장해 주는 역할을 합니다. 트랜잭션의 격리 수준에 따라서 여러 트랜잭션의 작업 내용을 어떻게 공유하고, 차단할지 Lock이 결정하게 됩니다.

 

트랜잭션

MySQL에서 트랜잭션은 하나의 논리적인 작업 셋 (여러개의 쿼리)가 100% 실행되거나 100% 실패함을 보장해 주는 것을 말합니다. 가령 Transaction 안에서 세 개의 update 쿼리가 발생할 경우 3개 모두 반영되거나, 모두 반영되지 않아야 합니다.

 

create table t1 (id int not null primary key);

insert into t1 (id) values (3);

... 

insert into t1 (id) values (1), (2), (3);
>> ERROR 1062 (23000): Duplicate entry '3' for key 't1.PRIMARY'

select * from t1;
>>
+----+
| id |
+----+
|  3 |
+----+

 

t1 테이블의 id가 primary key이므로 중복해서 값이 삽입될 수 없습니다. 이 예시에서는 auto-commit 모드가 활성화되어 있다고 가정합니다.

 

같은 트랜잭션에서 1, 2, 3을 모두 삽입하려 하면 3이 중복된 키이기 때문에 error가 발생하고, 1, 2, 3 모두 반영되지 않습니다. 

 

MySQL 엔진의 잠금 (Lock)

Lock은 크게 MySQL 엔진이 제공하는 Lock과 스토리지 엔진이 제공하는 Lock으로 구분할 수 있습니다. MySQL이 제공하는 Lock은 영향이 스토리지 엔진까지 미치지만, 스토리지 엔진이 제공하는 Lock은 MySQL 엔진에 영향을 미치지 않습니다.

 

1. 글로벌 락 (GLOBAL LOCK)

글로벌 락은 MySQL에서 제공하는 Lock 중에 가장 강력한 Lock입니다. 하나의 세션에서 글로벌 락을 획득하면 다른 세션에서 모든 ddl 및 dml 문장이 대기상태로 남게 됩니다.

왼쪽 세션에서 

flush tables with read lock;

 

명령어를 통해 global lock을 획득하고, 오른쪽 세션에서 update를 시도하게 되면 화면에서 보시는 바와 같이 대기상태가 됩니다.

이 상태에서 왼쪽 세션에서 

unlock tables;

명령어를 통해 락을 해제하게 되면 바로 오른쪽 세션의 쿼리가 실행되고 마무리됩니다.

 

글로벌 락은 MySQL의 모든 테이블에 관해서 Lock을 수행하게 됩니다. 따라서 실제 서비스가 중단될 수도 있을 만큼 강력한 lock이기 때문에 웹 서비스용 MySQL에서는 가급적 사용을 하지 않는 것이 좋습니다.

 

2. 테이블 락 (Table Lock)

테이블 락은 개별 테이블 단위로 설정되는 Lock입니다. table lock의 경우 명시적, 묵시적으로 획득이 되는데 명시적으로는 위 예시와 동일한 결과를 얻을 수 있습니다.

세션 1 >> 
lock tables t1 READ;
Query OK, 0 rows affected (0.00 sec)

세션 2 >> 
update t1 set id = 2 where id = 1;
대기...

세션 1 >> 
unlock tables;
Query OK, 0 rows affected (0.00 sec)

세션 2 >> 
Query OK, 1 row affected (6.22 sec)
Rows matched: 1  Changed: 1  Warnings: 0

 

묵시적으로는 스토리지 엔진에 따라 다른데요, MyISAM이나 Memory 테이블의 경우 데이터를 변경하면 묵시적으로 테이블 락이 획득됩니다. 저희가 일반적으로 많이 사용하는 InnoDB의 경우 DDL의 경우에만 묵시적으로 락이 획득됩니다. 이러한 차이가 발생하는 이유는 InnoDB의 경우 레코드 단위의 락 기능이 있기 때문에 데이터 변경 시 전체 테이블에 대한 락을 획득할 필요가 없습니다.

 

3. 네임드 락

네임드 락(Named Lock)은 임의의 문자열에 대해 락을 획득하고 해제할 수 있는 MySQL의 기능입니다. 해당 Lock은 테이블이나 레코드와 관련이 없고 순수한 문자열로 락을 설정합니다.

mysql> select get_lock('hello', 2);
+----------------------+
| get_lock('hello', 2) |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

mysql> select is_free_lock('hello');
+-----------------------+
| is_free_lock('hello') |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (0.00 sec)
>> 0은 이미 잠겨있다는 의미입니다.

mysql> select release_lock('hello');
+-----------------------+
| release_lock('hello') |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select is_free_lock('hello');
+-----------------------+
| is_free_lock('hello') |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)
>> 1은 락을 어느 커넥션도 사용하고 있지 않다는 의미입니다.

• MySQL 8.0 이후부터는 여러 개의 NamedLock을 획득하고 해제할 수 있습니다.

 

4. 메타데이터 락

메타데이터 락은 클라이언트가 명시적으로는 획득할 수 없고, 데이터베이스에서 자체적으로 획득하고 해제하는 Lock입니다. 데이터베이스 객체 (테이블 or 뷰 등)의 이름이나 구조가 변경될 경우 자체적으로 획득 후 작업 완료가 되었을 때 해제합니다.

 

InnoDB 스토리지 엔진 Lock

InnoDB 스토리지 엔진은 레코드 기반의 Lock 기능이 존재합니다. 따라서 MyISAM이나 메모리 엔진에서는 할 수 없는 것들이 가능해집니다. 또한 MySQL 서버는 특수하게도 Gap Lock이라는 개념을 제공합니다. 우리가 실제로 MySQL 서버를 사용하여 어플리케이션을 만날 때 이러한 Gap Lock, Record Lock에 대한 이해가 충분해야 DeadLock과 같은 상황을 피하고, 실제로 만나더라도 해결할 수 있습니다. 

 

부족한 내용은 공식문서를 참고하여 주시길 바랍니다.

 

1. 레코드 락

레코드 자체만을 잠그는 것을 레코드 락이라고 합니다. 여기서 주의할 점은 InnoDB 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 점입니다. 

 

아래의 예시를 보면

SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;

위와 같은 쿼리를 실행하게 된 경우, t 테이블의 c1 = 10인 모든 레코드에 대하여 insert, update, delete가 불가능해집니다.

 

인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정합니다. 

 

2. 갭 락

다른 DBMS와 다르게 InnoDB 엔진은 Gap Lock 기능을 제공합니다. 해당 Lock은 레코드 자체가 아니라 레코드와 레코드 사의의 간격을 잠그는 것을 의미하는데요, 레코드와 레코드 사이의 새로운 레코드가 insert 되는 것을 방지하기 위해 도입되었습니다.

 

아래의 예시를 보면,

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

이렇게 10과 20 사이의 데이터를 조회한다 치면, c1 = 11, 12, ... 19인 데이터는 해당 트랜잭션이 커밋될 때까지 insert할 수 없게 됩니다.

 

Gap Lock은 인덱스에 따라서, (where절에 포함되는 레코드 간격에 따라서)한 값에만 걸릴 수 있고 혹은 아예 존재하지 않을 수도 있습니다. 예를 들어 id가 유일한(unique key) 라면 다음과 같은 쿼리는 gap lock이 필요 없습니다.

SELECT * FROM child WHERE id = 100;

 

이렇게 보면 Gap Lock이 꽤 복잡해 보이기도 하는데, 쉽게 생각하면 InnoDB 엔진의 Record 잠금은 모두 index를 기반으로 해당하는 데이터들을 잠그는 것이고, 이 때 범위조건으로 검색될 경우 앞 키와 뒷 키 사이의 간격을 막아 그 사이의 키에 데이터가 삽입되는 것을 방지한다고 생각해보시면 좋을 것 같습니다.

 

Gap Lock의 또다른 특징으로는 READ-COMMITED 격리수준에서는 Gap Lock이 비활성화 됩니다. 이유는 Read Commited 격리 수준에서는 Non-Repeatable Read 문제가 발생함이 인정이 되기 때문이에요. 

 

 

실제로 테스트를 해보면,

왼쪽 첫번째 select 구문에서는 id 1, 3이 나오고 두번째 select 구문에서는 1, 2, 3이 나오게 됩니다. 즉 다른 세션에서 commit 된 데이터가 추가되어 읽힌다는 이야기이고 이는 gap lock이 설정이 되지 않았다는 것을 의미합니다.

 

 

같은 조건으로 isolation level을 repeatable read로 변경하여 수행해보면 insert문이 실행되지 않고 기다리는 것을 확인할 수 있어요.

 

+ 추가로 Gap Lock과 Transaction Isolation Level 설정에 관한 다른 영향도 있는데요, 앞서 MySQL에서는 모든 Record Lock을 인덱스 기반으로 잠금을 한다고 말씀드렸는데, select ... for update 쿼리는 기본적으로 쿼리가 실행되면 update 쿼리가 실행되고, 커밋될 때 까지 베타 락을 잡게 되는데요. Read-Commited 격리수준에서는 Where절을 판단하여 매칭되지 않는 row에 대해서 잠금을 자동으로 해제해버립니다. 

 

3. 넥스트 키 락

앞서 설명 드린 Record Lock과 Gap Lock을 합쳐놓은 형태의 잠금을 Next Key Lock이라 합니다. 즉 MySQL에서 index 기반으로 탐색 후 잠금을 건다고 말씀을 드렸는데, 그 간격과 실제 레코드를 모두 잠그는 형태로 이해하시면 됩니다. 저희가 운영하면서 아마 묵시적으로 대부분의 경우 이 Next Key Lock을 사용한다고 보시면 좋을 것 같습니다. 

 

MySQL 공식문서에는 아래와 같이 설명이 되어있는데요, 

• That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record.
  
  • 즉, 다음 키 잠금은 인덱스 레코드 잠금에 인덱스 레코드 앞의 간격에 대한 간격 잠금을 더한 것입니다.

해당 인덱스 Record Lock + 해당 인덱스 바로 이전 키 까지의 Gap Lock을 의미한다는 뜻입니다.

 

공식문서의 예제를 보면 이해가 쉬울 것 같습니다.

10, 11, 13, 20 의 키를 갖는 테이블이 가져갈 수 있는 Next Key Lock 조합은 아래와 같다 합니다.

• (- 무한, 10]
• (10, 11]
• (11, 13]
• (13, 20]
• (20, 무한]

'('는 제외, ']'는 포함입니다. 

 

해당 인덱스 Record Lock + 해당 인덱스 바로 이전 키 까지의 Gap Lock을 의미한다는 뜻입니다.

위 말이 조금 이해가 가시면 좋을 것 같습니다.

 

보통 DBMS에서 트랜잭션 격리수준을 이야기할 때 REPEATABLE READ 격리 수준에서는 Phantom Read가 발생할 수 있다 이야기 하는데요, MySQL의 InnoDB 엔진은 위에 설명드린 NextKey Lock을 활용하여 그 사이 구간에 대한 삽입을 막아버리기 때문에  특수하게도 REPEATABLE READ 격리수준에서도 Phantom Read가 발생하지 않습니다.

 

(물론 select..for update 쿼리는 항상 최신의 커밋된 데이터를 가져와야 하기 때문에 Phantom Read가 발생합니다. 다만 이는 예외적인 케이스라 일반적으로는 발생하지 않는다고 이야기하는 것이 맞습니다.)

4. Auto increment Lock

MySQL에서 테이블을 선언할 대 Auto increment로 선언하는 경우가 자주 있는데요, 당연히 이 부분도 여러 커넥션이 동시에 접근하면 안 되기 때문에 InnoDB는 내부적으로 잠금을 설정한 뒤, 작업이 끝나면 잠금을 해제하는 로직을 가지고 있습니다.

 

MySQL 버전 5부터는 시스템 변수 설정을 통해 자동 증가 락을 사용하지 않을 수 도 있는데요, 자세한 사항은 문서를 참고해 보시면 좋을 것 같습니다.