존재하지 않는 레코드에 비관적 락으로 동시성 제어가 가능할까

서론

이전에 프로젝트를 진행하면서 동시성 제어를 해야할 필요가 있었습니다. 일반적으로 알려진 동시성 제어 예제들은 테이블에 특정 레코드에 대한 수정에 대해 락을 걸고 동시성 문제를 해결하는 것이었습니다. 하지만 저희의 상황은 달랐습니다. 저희의 동시성 문제는 테이블에 존재하지 않는 레코드를 동시 생성을 제어했어야 했습니다.

 

당시에는 "SELECT ... FOR UPDATE" 은 이미 존재하는 레코드에만 적용할 수 있을거라고 판단해서 MySQL의 네임드 락과 Redis 분산락을 이용해서 문제를 해결했습니다. 하지만 'Real MySQL' 도서를 읽다보니 InnoDB의 레코드 잠금은 레코드를 잠그는 것이 아닌 인덱스를 잠그는 방식 방식으로 처리됨을 알 수 있었습니다. 그리고 문득 "레코드가 존재하지 않더라도 인덱스만 적절하게 있다면 레코드 락으로도 구현할 수 있지 않을까?"란 의문이 들어 시작하게 되었습니다.

 

이번 포스팅에서는 테이블에 존재하지 않는 레코드에 "SELECT ... FOR UPDATE" 수행 시 락 동작 방식을 이해하고 동시성 제어 가능 여부를 확인합니다.

 

테이블 및 데이터 준비

실험을 위한 아래의 간단한 테이블과 데이터를 생성하겠습니다.

mysql> CREATE TABLE test_tbl (
    -> id BIGINT AUTO_INCREMENT PRIMARY key,
    -> data VARCHAR(10));

mysql> INSERT test_tbl (data)
    -> VALUES (1), (2), (3), (5), (6);
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from test_tbl;
+----+------+
| id | data |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
|  4 | 5    |
|  5 | 6    |
+----+------+
5 rows in set (0.00 sec)

 

MySQL은 테이블 생성 기본적으로 InnoDB 스토리지 엔진을 사용하고 InnoDB 테이블은 자동으로 프라이머리 키에 대해 클러스터링 인덱스를 만들기 때문에 이를 이용해서 테스트해보겠습니다.

 

테이블에 생성한 데이터는 data 컬럼에 4를 제외한 1에서 6까지 숫자가 연속해서 들어가있습니다. 이때 두 트랜잭션이 테이블에 존재하지 않는 레코드에 대해 비관적 락으로 동시에 테이블에 data컬럼에 4를 넣을려고 할 때 락 동작 방식과 동시성 제어 여부를 확인해보겠습니다.

 

SELECT ... FOR UPDATE

위 테이블에 data 컬럼 값이 4인 데이터를 넣는 시나리오는 다음과 같습니다.

1. test_tbl 테이블에 data 컬럼 값이 4인 레코드가 있는지 확인
2. 레코드가 없다면 data 컬럼 값이 4인 값 생성

1번을 수행하기 위해 다음 SQL을 실행하겠습니다.

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_tbl where data='4' for update;
Empty set (0.00 sec)

 

위 SQL에서 data 컬럼값 4를 찾으면서 값이 없다면 2번을 수행하기 위해 FOR UPDATE도 함께 수행했습니다.

 

다음 2번 수행하기 전 현재 위 SQL 수행 후 트랜잭션의 락이 어떻게 걸려있는지 확인해보겠습니다.

mysql> SELECT * FROM performance_schema.data_locks;
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+
| ENGINE | ENGINE_LOCK_ID                       | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+
| INNODB | 2233154021944:4765:2233121448168     |                188929 |        81 |       12 | simple        | test_tbl    | NULL           | NULL              | NULL       |         2233121448168 | TABLE     | IX        | GRANTED     | NULL                   |
| INNODB | 2233154021944:3708:4:1:2233121445384 |                188929 |        81 |       12 | simple        | test_tbl    | NULL           | NULL              | PRIMARY    |         2233121445384 | RECORD    | X         | GRANTED     | supremum pseudo-record |
| INNODB | 2233154021944:3708:4:2:2233121445384 |                188929 |        81 |       12 | simple        | test_tbl    | NULL           | NULL              | PRIMARY    |         2233121445384 | RECORD    | X         | GRANTED     | 1                      |
| INNODB | 2233154021944:3708:4:3:2233121445384 |                188929 |        81 |       12 | simple        | test_tbl    | NULL           | NULL              | PRIMARY    |         2233121445384 | RECORD    | X         | GRANTED     | 2                      |
| INNODB | 2233154021944:3708:4:4:2233121445384 |                188929 |        81 |       12 | simple        | test_tbl    | NULL           | NULL              | PRIMARY    |         2233121445384 | RECORD    | X         | GRANTED     | 3                      |
| INNODB | 2233154021944:3708:4:5:2233121445384 |                188929 |        81 |       12 | simple        | test_tbl    | NULL           | NULL              | PRIMARY    |         2233121445384 | RECORD    | X         | GRANTED     | 4                      |
| INNODB | 2233154021944:3708:4:6:2233121445384 |                188929 |        81 |       12 | simple        | test_tbl    | NULL           | NULL              | PRIMARY    |         2233121445384 | RECORD    | X         | GRANTED     | 5                      |
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+
7 rows in set (0.00 sec)

 

위 결과의 전체 컬럼 정보는 다음을 참고해주세요. https://dev.mysql.com/doc/refman/8.4/en/performance-schema-data-locks-table.html

MySQL :: MySQL 8.4 Reference Manual :: 29.12.13.1 The data_locks Table

 

여기서 봐야할 컬럼은 뒤쪽에 위치한 INDEX_NAME, LOCK_MODE, LOCK_DATE 컬럼입니다. 현재 락을 걸고 있는 인덱스는 클러스터링 인덱스로 프라이머리 키에 대한 인덱스입니다. 클러스터링 인덱스에 락이 걸리는 이유는 WHERE data='4' 를 수행하기 위해 데이터베이스는 풀테이블 스캔을 진행할 것입니다. 하지만 MySQL에서는 풀테이블 스캔이 클러스터링 인덱스 스캔으로 수행되기 때문에 위의 인덱스를 사용하게 됩니다.

 

LOCK_MODE는 모두 X(Exclusive)로 걸려있습니다. LOCK_DATA의 값은 id 컬럼 값을 나타냅니다. LOCK_DATA는 WHERE data='4'를 수행하면서 테이블의 모든 레코드에 잠금을 걸었습니다. 그러면 다른 트랜잭션에서 동일한 'select * from test_tbl where data='4' for update;' SQL 명령문이 수행될 때 잠금 대기가 걸릴 것이라고 예상할 수 있습니다. 아래 SQL을 통해 확인해보겠습니다.

mysql> SELECT * FROM performance_schema.data_locks;
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+
| ENGINE | ENGINE_LOCK_ID                       | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+
| INNODB | 2233154024464:4765:2233121467208     |                188930 |        83 |        8 | simple        | test_tbl    | NULL           | NULL              | NULL       |         2233121467208 | TABLE     | IX        | GRANTED     | NULL                   |
| INNODB | 2233154024464:3708:4:2:2233121464424 |                188930 |        83 |        8 | simple        | test_tbl    | NULL           | NULL              | PRIMARY    |         2233121464424 | RECORD    | X         | WAITING     | 1                      |
| INNODB | 2233154021944:4765:2233121448168     |                188929 |        81 |       12 | simple        | test_tbl    | NULL           | NULL              | NULL       |         2233121448168 | TABLE     | IX        | GRANTED     | NULL                   |
| INNODB | 2233154021944:3708:4:1:2233121445384 |                188929 |        81 |       12 | simple        | test_tbl    | NULL           | NULL              | PRIMARY    |         2233121445384 | RECORD    | X         | GRANTED     | supremum pseudo-record |
| INNODB | 2233154021944:3708:4:2:2233121445384 |                188929 |        81 |       12 | simple        | test_tbl    | NULL           | NULL              | PRIMARY    |         2233121445384 | RECORD    | X         | GRANTED     | 1                      |
| INNODB | 2233154021944:3708:4:3:2233121445384 |                188929 |        81 |       12 | simple        | test_tbl    | NULL           | NULL              | PRIMARY    |         2233121445384 | RECORD    | X         | GRANTED     | 2                      |
| INNODB | 2233154021944:3708:4:4:2233121445384 |                188929 |        81 |       12 | simple        | test_tbl    | NULL           | NULL              | PRIMARY    |         2233121445384 | RECORD    | X         | GRANTED     | 3                      |
| INNODB | 2233154021944:3708:4:5:2233121445384 |                188929 |        81 |       12 | simple        | test_tbl    | NULL           | NULL              | PRIMARY    |         2233121445384 | RECORD    | X         | GRANTED     | 4                      |
| INNODB | 2233154021944:3708:4:6:2233121445384 |                188929 |        81 |       12 | simple        | test_tbl    | NULL           | NULL              | PRIMARY    |         2233121445384 | RECORD    | X         | GRANTED     | 5                      |
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+
9 rows in set (0.00 sec)

 

다른 트랜잭션으로 'select * from test_tbl where data='4' for update;' SQL을 수행한 후 데이터 락에 대해 조회한 결과입니다.

ENGINE_TRANSACTION_ID 컬럼에 '18930'과 '188929' 두 개의 트랜잭션이 있음을 볼 수 있습니다. 그리고 이후 생성된 트랜잭션은 1번 레코드의 락 점유를 대기하고 있음을 확인할 수 있습니다. 따라서, 테이블에 해당 레코드가 없어도 SELECT ... FOR UPDATE를 통해 동시성 제어를 할 수 있습니다. 

 

하지만 하나의 레코드 생성을 위해 테이블 전체 레코드에 잠금을 거는 것은 심각한 성능 저하를 야기할 것이며 또 "그럼 data 컬럼에 인덱스가 있으면 어떻게 될까? 이런 의문점이 들기도 합니다. 다음 절은 세컨더리 인덱스가 존재할 때에 대해 알아보겠습니다.

 

세컨더리 인덱스에서 SELECT ... FOR UPDATE

이제 세컨더리 인덱스 존재 시 락 동작에 대해 알아보겠습니다. 이를 위해 data 컬럼에 대한 인덱스를 생성해주겠습니다.

이후 과정은 위와 동일합니다. 과정은 일부 생략하고 하나의 트랜잭션의 락에 대해 알아보겠습니다.

mysql> SELECT * FROM performance_schema.data_locks;
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-------------------+-----------------------+-----------+-----------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID                       | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME        | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-------------------+-----------------------+-----------+-----------+-------------+-----------+
| INNODB | 2233154021944:4765:2233121448168     |                188940 |        81 |       17 | simple        | test_tbl    | NULL           | NULL              | NULL              |         2233121448168 | TABLE     | IX        | GRANTED     | NULL      |
| INNODB | 2233154021944:3708:5:5:2233121445384 |                188940 |        81 |       17 | simple        | test_tbl    | NULL           | NULL              | idx_test_tbl_data |         2233121445384 | RECORD    | X,GAP     | GRANTED     | '5', 4    |
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-------------------+-----------------------+-----------+-----------+-------------+-----------+
2 rows in set (0.00 sec)

 

세컨더리 인덱스를 사용했을 때 결과는 클러스터링 인덱스와 확연한 차이가 있습니다. 세컨더리 인덱스를 사용하면 모든 레코드에 잠금을 걸지않는 것을 볼 수 있습니다. 현재 id 컬럼값 4, data 컬럼값 '5' 인 레코드를 LOCK_MODE : X,GAP으로 잠금 것을 확인할 수 있습니다. LOCK_MODE 컬럼값 X는 위에서 확인했지만 GAP은 처음 등장했습니다. GAP 락은 이 레코드 “앞뒤의 간격(gap)”에 대해 삽입을 막는 잠금으로 즉, 다른 트랜잭션이 이 위치에 새로운 레코드 삽입 못 하게 막습니다. GAP 락에 대해 더 알고싶으시다면 다음을 참고해주세요.

https://medium.com/daangn/mysql-gap-lock-%EB%8B%A4%EC%8B%9C%EB%B3%B4%EA%B8%B0-7f47ea3f68bc

MySQL Gap Lock 다시보기

 

위 락 결과를 예상하면 세컨더리 인덱스로 '4' 인 값을 찾아가게 되고 '4'가 존재하지 않으니 다음 레코드인 data 컬럼값 '5'인 레코드에 대한 배타락과 GAP 락을 확인할 수 있습니다. 이 경우에도 다른 트랜잭션 또한 'select * from test_tbl where data='4' for update;' 수행 시 해당 세컨터리 인덱스의 data 컬럼값 '5'의 잠금을 얻을려고 할 것이고 따라서 테이블에 레코드 값이 존재하지 않더라도 동시성 제어가 가능하게 됩니다.

 

결국 꼭 레코드가 테이블에 존재해야만 레코드 락이 걸리는 것이 아니고 갭락과 레코드락을 사용하면 앞선 문제를 해결할 수 있음을 확인할 수 있습니다.