MySQL 은 읽기/쓰기 쿼리에서 어떤 락을 사용할까?

이 글에서는 MySQL 8.0 에서 내부적으로 언제 락을 사용하는지에 대해 알아가는 과정으로 MySQL 엔진 레벨의 락은 아직 학습이 부족하여 해당 글에서 제외하게 되었습니다. 

 

추가로 Real MySQL 을 읽고, 공유 락과 배타 락에 배경 지식이 있는 분들이 쉽게 이해 될 것 같으며, 결론은 '마치며' 에 정리해두었습니다.

 

목차는 다음 순서로 진행됩니다.

• 한 트랜잭션에서 공유 락/배타 락 획득 시, 다른 트랜잭션에서 기다림 없이 실행할 수 있는 쿼리 소개
• 공유 락/배타 락 사용 시, 다른 트랜잭션에서 쓰기 작업이 대기하는 이유
• UPDATE / DELETE 쿼리 시 WHERE 절의 인덱스 여부의 중요성
• MySQL8.0 Repetable Read 에서 Phantom Read 가 발생하는 경우
• UPDATE / DELETE 쿼리를 실행 할 때, MySQL 이 내부적으로 배타 락을 사용하지만 select...for update(JPA의 비관적 락)을 사용하는 이유
• 마치며

 

1. 한 트랜잭션에서 공유 락/배타 락 획득 시, 다른 트랜잭션에서 가능한 쿼리 소개

실험 환경

• DBMS: Mysql 8.0 InnoDB
• 격리 수준: Repeatable Read 

 

실험에 쓰일 테이블과 데이터입니다.

CREATE TABLE example (
	id BININT,
    name VARCHAR(255)
);

INSERT INTO example (id, name) VALUES (1, "a");   // 1번 row
INSERT INTO example (id, name) VALUES (2, "b");   // 2번 row

---

실험 1 : 배타락 & 공유락

 

• 왼쪽 트랜잭션: 1번 행에 대해 배타락(Exclusive Lock)을 획득합니다.
  • select ... for update
• 오른쪽 트랜잭션: 1번 행에 대해 공유락(Shared Lock)을 시도합니다.
  • LOCK IN SHARE MODE (Mysql 5.7버전 이하)
  • select ... for share ( Mysql 8.0버전 이상)

 

결과

• 오른쪽 트랜잭션이 공유락을 획득하지 못하고 대기 상태에 들어갔습니다.
• 배타락이 걸린 상태에서는 공유락도 획득할 수 없으므로, 배타락 역시 당연히 획득할 수 없습니다.

실험 1

 

 

실험 2 : 배타락 & 기본 SELECT

• 왼쪽 트랜잭션: 1번 행에 대해 배타락을 획득합니다.
• 오른쪽 트랜잭션: 1번 행에 대해 SELECT 쿼리를 실행합니다.

결과

• 기본적인 SELECT 쿼리는 성공적으로 실행되었습니다.
• 즉, 배타락이 걸린 상태에서도 읽기 작업(SELECT)은 허용됩니다.
• 배타락에서 읽기가 허용 안된다는 말은 Locking Read 가 안되는 것이지 Non-Locking Read 는 가능합니다. Locking Read 가 공유락, 배타락같이 락을 사용해서 레코드를 직접 읽는 것이고, Non-Locking Read 는 일반적인 select 문을 의미하며 스냅샷에 의한 언두 영역을 읽는 것입니다. 

실험 2

 

실험 3 : 배타락 & UPDATE/DELETE

• 왼쪽 트랜잭션: 1번 행에 대해 배타락을 획득합니다.
• 오른쪽 트랜잭션: 1번 행에 대해 UPDATE 또는 DELETE 쿼리를 실행합니다.

결과

• 오른쪽 트랜잭션은 대기 상태에 들어갔습니다.
• 배타락이 걸린 상태에서는 쓰기 작업(UPDATE, DELETE)이 차단됩니다.

실험 3

 

실험 4 : 공유락 & SELECT/UPDATE/DELETE

• 왼쪽 트랜잭션: 1번 행에 대해 공유락(Shared Lock)을 획득합니다.
• 오른쪽 트랜잭션: 1번 행에 대해 SELECT, UPDATE, 또는 DELETE 쿼리를 실행합니다.

결과

• SELECT 쿼리는 정상 실행되었습니다.
• 하지만 UPDATE와 DELETE 쿼리는 대기 상태에 들어갔습니다.

select 는 정상 실행, update 는 차단

 

실험5 : 공유락 & INSERT

 

INSERT 에서는 실험이 약간 다릅니다.

실험에 사용될 레코드입니다.

// example 테이블에 3개의 레코드 존재, id 는 PK
mysql> select * from example;
+-----+------+
| id  | name |
+-----+------+
|   1 | r    |
|   2 | b    |
| 500 | a    |
+-----+------+

 

실험 5-1. INSERT 가 대기하지 않을 때 입니다.

1번 트랜잭션에서 id가 1부터 2까지인 id 에 공유락을 획득하니, id 가 1과 2인 레코드에 레코드 락이 걸려있습니다.

// 트랜잭션 1에서 id가 1부터 2까지 공유락 획득
mysql> select * from example where id between 1 and 2 for share;
+----+------+
| id | name |
+----+------+
|  1 | r    |
|  2 | b    |
+----+------+

// REC_NOT_GAP은 레코드 자체를 잠그지만, 갭(Gap)은 잠그지 않는 잠금을 의미로 해당 레코드만 보호하고, 새로운 레코드 삽입은 허용합니다.
// id 가 1과 2인 레코드가 공유락인 레코드 락을 획득했습니다.
mysql> select * from performance_schema.data_locks;
+--------+------------------------------------------------------------------------+
| ENGINE | ... | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+--------+------------------------------------------------------------------------+
| INNODB | ... | NULL       | TABLE     | IS            | GRANTED     | NULL      |
| INNODB | ... | PRIMARY    | RECORD    | S             | GRANTED     | 2         |
| INNODB | ... | PRIMARY    | RECORD    | S,REC_NOT_GAP | GRANTED     | 1         |
+--------+------------------------------------------------------------------------+

 

2번 트랜잭션에서 id가 1과 2를 벗어나는 영역에 id가 10과 501인 레코드를 INSERT했고, 성공했습니다.

mysql> insert into example (id, name) values (10, "c");
Query OK, 1 row affected (0.00 sec)

mysql> insert into example (id, name) values (501, "c");
Query OK, 1 row affected (0.00 sec)

 

실험 5-2. INSERT 가 대기할 때 입니다.

트랜잭션 1에서 id 가 2 부터 500 사이인 레코드를 조회하며 공유락을 획득합니다.

// 트랜잭션 1에서 id가 2부터 500까지 공유락 획득
mysql> select * from example where id between 2 and 500 for share;
+----+------+
| id | name |
+----+------+
|   2 |  b  |
| 500 |  a  |
+----+------+

// LOCK이 걸린 데이터에 supremum pseudo-record 가 추가되었습니다.
mysql> select * from performance_schema.data_locks;
+--------+-------------------------------------------------------------------------------------+
| ENGINE | ... | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA              |
+--------+-------------------------------------------------------------------------------------+
| INNODB | ... | NULL       | TABLE     | IS            | GRANTED     | NULL                   |
| INNODB | ... | PRIMARY    | RECORD    | S             | GRANTED     | supremum pseudo-record |
| INNODB | ... | PRIMARY    | RECORD    | S             | GRANTED     | 500                    |
| INNODB | ... | PRIMARY    | RECORD    | S,REC_NOT_GAP | GRANTED     | 2                      |
+--------+-------------------------------------------------------------------------------------+

 

트랜잭션 2에서 실험 5-1과 달리 INSERT 가 실패했습니다. 실험 5-11처럼 레코드 락이 벗어난 영역인 id=501 인 영역에 INSERT 를 시도했지만, 락 타임아웃이 발생했습니다.

실험5-1처럼 공유 락 영역 밖에 INSERT 를 시도했지만, 실험 5-2 만 실패한 이유가 무엇일까요?? 

mysql> insert into example (id, name) values (100, "z");
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into example (id, name) values (501, "z");
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

LOCK_DATE 에 supremum pseudo-record 가 있기 때문입니다. supremum pseudo-record 란 인덱스 페이지의 끝을 나타내는 가상의 레코드로 사실상 "갭 잠금" 역할을 했기 때문입니다.

 

결과

• supremum pseudo-record 값은 사실상 ∞ 와 같다합니다. 그래서, supremum 여부에 따라 INSERT 문이 락에 의해 대기할 수도, 아닐 수도 있게 됩니다.

실험 결론

결과	공유 락	배타 락	기본 select	update / delete	insert
공유 락	O	X	O	X	레코드 락일 경우 O 갭 락이 걸려있을 경우 X
배타 락	X	X	O	X

 

락 사용 시, 다양한 상황에 있어서 어떤 작업이 허용되고, 대기하는지 알아봤습니다.

이를 통해 update / delete 도 락을 사용할 것 같다는 예감이 들며, 이에 대해 찾아봤습니다.

* 참고 - 낙관적 락은 JPA에서 제공하는 기능으로 DB의 공유 락과 무관합니다

 

2. 공유 락/배타 락 사용 시, 다른 트랜잭션에서 쓰기 작업이 대기하는 이유

1. FOREIGN KEY 제약 조건이 테이블에 정의되어 있는 경우, 외래키 있는 컬럼에 INSERT / UPDATE / DELETE 작업을 수행해도 되는지 부모 테이블(PK) 에 레코드 수준의 공유 락(shared record-level locks)을 설정합니다. 

• If a FOREIGN KEY constraint is defined on a table, any insert, update, or delete that requires the constraint condition to be checked sets shared record-level locks on the records that it looks at to check the constraint. InnoDB also sets these locks in the case where the constraint fails.

2. 유니크 키는 INSERT 시 중복된 값을 체크하기 위해 읽기 잠금을, 레코드를 추가할 때는 쓰기 잠금을 사용하며 이 과정에서 데드락이 아주 빈번히 발생합니다.(Real MySQL 8.0 출처)

 

3. UPDATE 와 DELETE 상태에서도 내부적으로 락을 사용합니다. 이 때, 사용되는 락의 종류는 쿼리를 실행하기 위해 사용되는 인덱스와 WHERE 조건에 따라 레코드 락인지, 갭 락인지 넥스트 키 락인지 다르겠지만 이들은 결국 배타 락을 사용합니다. 추가로, UPDATE / DELETE 시 WHERE 조건절에 인덱스가 포함된 컬럼이라면 레코드 락을, 인덱스가 없는 컬럼을 지정할 경우 해당 컬럼이 WHERE 조건을 만족할 때까지 읽은 모든 레코드에 (InnoDB는 레코드의 인덱스에) 락을 걸게됩니다.

• For locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE, and DELETE statements, the locks that are taken depend on whether the statement uses a unique index with a unique search condition or a range-type search condition.
• UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.
• 아래 사진은 트랜잭션을 열고, update 쿼리를 실행 후 락이 걸려있는 데이터를 보는 명령어까지 실행한 사진입니다. LOCK_STATUS 가 GRATED 는 락을 획득한 상태임을 의미하며, LOCK_DATE 는 레코드의 해시값이며, LOCK_MODE 는 X 로 배타락을 의미합니다. 그리고, LOCK_TYPE 을 통해 락 유형은 레코드 락임을 확인했습니다. 즉, update 쿼리는 배타 락이 걸립니다.

 

4. MySQL 8.0 의 InnoDB 스토리지 엔진은 레코드가 자체가 아니라 인덱스의 레코드를 잠그게 됩니다. 인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 레코드를 잠급니다. 

 

출처

- https://dev.mysql.com/doc/refman/8.4/en/innodb-locks-set.html

- https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html#foreign-key-locking

- Real MySQL 1권의 트랜잭션과 잠금

 

즉, MySQL 8.0에서는 외래 키 제약 조건과 UPDATE / DELETE 시에도 락들이 내부적으로 사용됩니다. 이로 인해 위 실험에서 한 트랜잭션이 배타 락/공유 락을 획득했을 때, 다른 트랜잭션에서 단순 INSERT / UPDATE / DELETE 작업을 수행하더라도 MySQL 내부적으로 자동으로 생성된 클러스터 인덱스를 이용해 레코드 락을 획득하고자 했고, 레코드 락은 배타 락이기 때문에 대기 상태에 들어갔던 것입니다.

 

즉, MySQL 8.0 에서 MVCC는 기본 select 쿼리와 같이 잠금 없는 읽기를 지원하기 위함이고, 공유/배타 락은 쓰기를 위함으로 정리할 수 있습니다.

 

* INSERT / UPDATE / DELETE 작업을 하려는 레코드의 범위가 각각의 레코드 수준인지, 특정 범위인지, 테이블 수준인지에 따라 사용되는 MySQL 내부에서 사용되는 락의 종류는 다릅니다.

* 언제 어떤 락이 쓰이는지 자세히 알고 싶다면 출처의 Locking 부분이나 Real MySQL의 Lock 파트를 참고하시길 추천드립니다.

 

3. UPDATE / DELETE 쿼리 시 WHERE 절의 인덱스 여부의 중요성

또한, MySQL은 UPDATE나 DELETE 작업 시 내부적으로 배타 락을 사용하기 때문에 WHERE 조건이 중요합니다.

WHERE 조건에 사용되는 컬럼에 적절한 인덱스가 없는 경우, 쓰기 작업을 할 레코드뿐만 아니라 다른 레코드에도 락이 걸릴 수 있고, 최악의 경우 테이블 단위로 락이 걸릴 수 있습니다. 즉, UPDATE / DELETE 쿼리를 처리 할 때, WHERE 조건에 PK와 같은 인덱스가 있는 컬럼을 사용해야 한다는 의미입니다.

 

WHERE 조건에 인덱스가 없는 UPDATE / DELETE 쿼리가 실행되면 테이블의 모든 행을 스캔하고, 불필요한 레코드에도 락이 설정될 수 있습니다.

 

예를 들어, UPDATE / DELETE ... WHERE last_name = "종혁" and first_name = "안" 이란 쿼리가 있을 때, last_name 이 "종혁"인 레코드가 1000건이라고 가정하고, 그 중에서 first_name 이 "안" 을 만족하는 레코드가 단 1개라고 가정하겠습니다. 이 때, last_name 에만 인덱스가 설정되었다면 last_name 이 "종혁"을 만족하는 1000건의 레코드에 해당하는 인덱스에 락이 걸리게 됩니다.(* MySQL 8.0에서 락은 레코드가 아닌 인덱스에 겁니다.)

 

이 때, 레코드 락이 걸린 1000건의 레코드 중, first_name = "안" 을 만족하는 1건의 데이터를 제외한 999건은 불필요하게 락이 걸리게 되었습니다. 만약, last_name 에도 인덱스가 없다면 last_name="종혁" 을 찾기 위해 MySQL 은 테이블 풀 스캔을 하게 될 것이고, 이 과정에서 테이블의 모든 레코드에도 락을 걸게 됩니다.

 

이러한 이유로 UPDATE / DELETE 시 인덱스가 있는 컬럼을 WHERE 조건절에 줌으로써 불필요한 레코드에 락이 걸리는 것을 방지하는 것이 중요합니다.

 

만약, 예시처럼 인덱스가 없는 컬럼을 WHERE 조건절에 줘야한다면 (last_name , first_name) 두 컬럼의 멀티 컬럼 인덱스를 생성해주며, 더 나아가 first_name 의 카디널리티가 더 높기 때문에 (first_name , last_name) 으로 인덱스를 생성하며 락을 방지할 수 있습니다.

 

* 출처 : Real MySQL 5.3.2 인덱스와 잠금

 

4. MySQL 8.0 Repetable Read 에서 비관적 락 사용 시, Phantom Read 가 발생하는 경우

추가로, Real MySQL 8.0 에서 select + select ... for update 조합으로 쿼리 시, Phantom Read 가 발생 할 수도 있다는 사실 알고 계셨나요? 공식 문서에서는 넥스트 키 락으로 Phantom Read 문제를 막을 수 있다고 나와 있습니다.

하지만, select 후 비관적 락인 select ... for update 쿼리를 진행하면, Phantom Read 가 발생할 수 있습니다.

https://dev.mysql.com/doc/refman/8.4/en/innodb-next-key-locking.html

 

쿼리가 [1]왼쪽 트랜잭션에서 select ... for update → [2]오른쪽 트랜잭션 insert 순서로 진행 될 경우

먼저, 왼쪽 트랜잭션에서 select ... for update 후에 오른쪽 트랜잭션에서 insert 를 하면 오른쪽 트랜잭션은 대기 상태가 되며 공식문서에서 나온 것처럼 넥스트 키 락을 사용해서 레코드 삽입에 의해 발생하는 phatom read 는 발생하지 않습니다. 

next-key 락에 의해 phatom read 를 막는 경우

 

쿼리가 [1]왼쪽 select → [2]오른쪽 insert → [3]오른쪽 commit → [4]왼쪽 select ... for update 순서일 경우

아래 사진처럼 1번부터 4번까지 차례로 순서대로 작성하니 왼쪽 트랜잭션이 같은 트랜잭션임에도 불구하고, phantom read 가 발생한 것을 확인할 수 있습니다. 이는 select ... for update 가 언두 영역을 읽지 않고, 기본 select 와 달리 테이블 레코드를 바로 읽기 때문인데요. MVCC기법에 따라 기본 select 쿼리인 1번에서는 언두영역을 읽고, 비관적 락이 걸린 4번에서는 언두 영역이 아닌 테이블 레코드를 읽게 됩니다.

 

이런 복잡한 상황이 같은 테이블(엔티티)에서 발생할 수 있나 싶기도 하지만, 그렇다고 또 100%라는 보장은 없으니까 어떻게 해야할지 참 어렵습니다.

++ 추가. 이러한 경우는 잘 발생하지 않는다고 합니다.

 

정리하자면, MySQL 8.0 InnoDB는 MVCC 를 활용해 REPEATABLE READ 를 가능하게 하고, select...for update 같이 레코드에 락을 걸어야 할 때는(MVCC를 활용하지 못할 때 는) 넥스트 키 락을 활용해 Phantom Read 를 방지한다. 단, 한 트랜잭션에서 select 후, select...for update 시에는 Phantom Read 가 발생할 수도 있다로 정리할 수 있겠습니다.

MVCC를 위한 언두영역과 select ... for update 의 특징으로 인해 phantom read 가 발생하는 경우

 

5. UPDATE / DELETE 쿼리를 실행 할 때, MySQL 이 내부적으로 배타 락을 사용하지만 select...for update(JPA의 비관적 락)을 사용해 동시성 제어를 하는 이유

현대 DBMS 는 MVCC를 통해 잠금 없는 읽기를 지원합니다. MySQL 8.0은 Repeatable Read 가 기본 격리 수준이며 MVCC를 통해 트랜잭션은 다른 트랜잭션의 결과를 반영하지 않고 있는데요.

 

다른 트랜잭션의 결과를 반영하지 않다보니 두 트랜잭션에서 한 레코드에 UPDATE 쿼리를 각각 실행하면 하나의 트랜잭션 결과만 레코드에 반영하게 됩니다. 즉, 하나의 UPDATE 결과만 레코드에 반영되고 다른 트랜잭션의 UPDATE 결과는 레코드에 반영되지 않습니다. 값이 누적되어야 하는데, 누적되지 않는 것이죠. 이를 Lost Update 문제라 합시다.

 

그래서, 이럴 경우는 select ... for update 를 통해 UPDATE 하려는 트랜잭션들을 순차적으로 만들어서 2개의 변경사항을 누적시킬 수 있습니다. select ... for update 는 JPA의 비관적 락을 통해 쉽게 사용할 수 있습니다.

 

Lost Update 문제에 대한 예시를 누적되어야 하는 돈으로 가져왔습니다.

 

- update 문만 사용해서 Lost Update 문제 발생하는 경우

id=1 인 레코드의 money 컬럼의 값(1000)을 왼쪽 트랜잭션에서는 500을 빼고, 오른쪽 트랜잭션에는 100을 빼고, 두 트랜잭션을 커밋했습니다. 그러나, 왼쪽 트랜잭션에 Query OK, 1 row affected (4.28 sec) 을 통해 update 시 배타 락이 걸려 대기했지만 결과는 id=1인 레코드의 money 컬럼의 값은 500 으로, 오른쪽 트랜잭션의 결과가 레코드에 반영되지 않았습니다.

lost update 문제 발생

 

- select ... for update 사용해서 Lost Update 문제 발생하지 않는 경우 

배타 락을 사용하니  id=1 인 행의 money 컬럼의 값(1000)을 오른쪽 트랜잭션에서는 100을 빼자마자, 왼쪽 트랜잭션에는 id=1 인 행의 money 컬럼의 값을 조회하니 1000에서 100을 뺀 값인 900이 나오게 되었습니다.

 

 

6. 마치며

공유 락/배타 락/MVCC 를 공부하고, Real MySQL을 공부하면서 락을 사용하면 어떤 쿼리 작업까지 영향을 미치는지 항상 헷갈렸고, 배타 락과 MySQL 에서 지원하는 레코드 락 / 갭 락 / 넥스트 키 락은 차이가 무엇인지, update 에도 배타 락이 걸리는데 select ... for update 는 왜 필요한지? 직접 실험하고 정리해보았습니다.

 

이번 실험을 통해 UPDATE / DELETE 작업도 배타 락을 사용하고, 외래키/유니크 키 제약 조건들은 어떻게 데이터의 무결성을 보장했는지 궁금했는데 이번 기회에 정리할 수 있었습니다. 

 

• MySQL 내부적으로 데이터의 무결성(외래키, 유니크 키)을 지키기 위해 락을 사용합니다.
• MySQL InnoDB는 UPDATE / DELETE 쿼리 시 인덱스가 없는 컬럼을 WHERE 절에 넣으면 조건에 맞는 레코드를 UPDATE / DELETE 하기 위해 많은 레코드를 읽게 되며, 읽은 레코드에는 배타 락을 겁니다.
• 비관적 락은 배타 락을 사용하며 읽기 락과 배타 락과의 공유가 불가합니다.
• MySQL 8.0 InnoDB의 기본 격리 수준에서 select...for update 시, 발생하는 Phantom Read 를 막기 위해 배타 락인 넥스트 키 락(갭 락 + 레코드 락)을 사용합니다.