동시성 제어 시 JPA의 비관적 락(select ... for update)에 대해 회의적인 이유

이 글에서는 MySQL을 사용해, 특정 레코드에 락이 걸린 상태에서 다른 트랜잭션이 해당 레코드에 어느 작업까지 허용하는지 실험해보고, MySQL 내부에서 언제 락을 사용하는지 알아보며 비관적 락에 대해 회의적인 이유를 얘기하는 개인적인 글입니다.

 

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

 

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

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

 

 

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

실험 환경

• DBMS: Mysql 8.0
• 격리 수준: 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 값은 DB내부에서 정하게 되는데 이에 따라 INSERT 문이 락에 의해 대기할 수도, 아닐 수도 있게 됩니다.

실험 결론

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

 

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

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

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

 

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

1. FOREIGN KEY 제약 조건이 테이블에 정의되어 있는 경우, INSERT / UPDATE / DELETE 작업을 수행해도 되는지 제약 조건을 확인하기 위해 조회하는 레코드들에 대해 공유 레코드 수준 락(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. 유니크 키도 중복된 값을 체크할 때는 읽기 잠금을, 쓰기 할 때는 쓰기 잠금을 사용하며 이 과정에서 데드락이 아주 빈번히 발생합니다.(Real MySQL 8.0 출처)

 

3. UPDATE 와 DELETE 상태에서도 내부적으로 락을 사용합니다. 이 때, 사용되는 락의 종류는 쿼리를 실행하기 위해 사용되는 인덱스와 WHERE 조건에 따라 레코드 락인지, 갭 락인지 넥스트 키 락인지 다르겠지만 이들은 결국 배타 락을 사용합니다.

• 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 와 락을 지원합니다. 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 ... for update 시, Phantom Read 문제가 발생 할 수도 있다는 사실 알고 계셨나요? 공식 문서에서는 Non-Locking Read 시, 넥스트 키 락으로 Phantom Read 문제를 막을 수 있다고 나와 있습니다.

하지만, 비관적 락인 select ... for update 같은 Locking Read 시, Phantom Read 가 발생할 경우가 있습니다.

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

 

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

먼저, 왼쪽 트랜잭션에서 select ... for update 후에 오른쪽 트랜잭션에서 insert 를 하면 오른쪽 트랜잭션은 대기 상태가 되며 레코드 삽입에 의해 발생하는 phatom read 는 발생하지 않습니다. 

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

 

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

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

 

이런 복잡한 상황이 같은 테이블(엔티티)에서 발생할 수 있나 싶기도 하지만, 그렇다고 또 100%라는 보장은 없으니까 어떻게 해야할지 참 어렵습니다. 이러한 이유로 글 마지막에 설명드릴텐데, 제게 있어서 이는 앞으로 비관적 락을 사용하지 않을 이유 중 하나가 됩니다.

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

 

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

MySQL은 내부적으로 UPDATE/DELETE 쿼리를 실행할 때 행(테이블)에 배타 락을 설정하지만, 이 락은 쿼리 실행 시점에만 설정됩니다. 이 때문에 데이터 조회 시점부터 데이터를 보호하기 위해 select...for update와 같은 명령을 사용해 배타락을 직접 설정하는 것이 필요합니다. 데이터 조회 후 UPDATE를 실행할 때 조회 시점부터 다른 트랜잭션이 데이터를 수정하지 못하도록 배타락을 설정하여 데이터 충돌을 막아야 합니다. 

 

그렇지 않으면 아래처럼 Lost Update 문제가 발생합니다. 

Lost Update 란 두 트랜잭션이 같은 row 에 대해 쓰기 작업(insert, update, delete)을 수행할 때, 한 트랜잭션의 결과가 반영되지 않는 것을 의미합니다. 만약, 돈과 관련된 트랜잭션이라면 큰일나겠죠. 그래서 예제도 돈과 관련된 것으로 가져왔습니다.

 

- 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 내부적으로 데이터의 무결성(외래키, 유니크 키)을 지키기 위해 락을 걸고,
• UPDATE / DELETE 쿼리 시 인덱스가 없는 컬럼을 WHERE 절에 넣으면 예상치 못하게 수많은 레코드에 락이 걸릴 수도 있고,
• Repetable Read 에서 비관적 락을 사용할 때, phantom read 도 발생하는 경우도 있다보니(대체적으로는 발생하지 않지만..)

여기에 동시성 제어를 위해 비관적 락을 추가로 사용해서 데드락 위험을 더 증가 시켜야 할까? 란 생각이 들었습니다. 이러한 이유로 비관적 락에 대해 회의적이게 되었습니다.  또한, 누군가 제게 '비관적 락은 왜 사용 안하셨나요?' 라 물어본다면 이 경험을 토대로 말씀 드릴 것 같습니다.

 

이에 관련해서, 인프런 질의 응답에서 영한님께서 동시성 제어를 위해 DB 락을 거는 방법을 피하고, 원자적 연산이나 로직의 입구에서 막는 방법을 선호한다고 말씀을 해주신 것을 보고 이번 경험을 통해 조금은 이해하게 되었습니다.