select .. for update 대상 유무에 따른 잠금 상태

개요

InnoDB 엔진은 기본적으로 DDL 쿼리를 제외한 모든 데이터 조작 작업에서 테이블 락을 사용하지 않고 레코드 기반의 잠금 방식을 사용합니다. 더 자세히는 레코드 자체보다는 인덱스에 잠금을 설정하여 이루어지며, 테이블에 명시적인 인덱스가 없는 경우에도 내부적으로 생성된 클러스터 인덱스를 통해 잠금이 이루어집니다. 그리고 Repetable-Read 격리 수준에서 InnoDB는 record lock과 gap lock을 결합한 Next-key lock을 활용하여 Phantom Read를 방지합니다.

조건에 부합하는 특정 행을 찾기 위해 인덱스를 스캔하는 과정에서, InnoDB는 해당 인덱스 레코드뿐만 아니라 그 이전 공간에도 잠금을 설정합니다. 따라서 첫 번째 발견된 레코드와 쿼리가 정의한 범위 내의 빈 공간에서 새로운 insert 작업을 방지합니다. 예를 들어 id 값이 1, 8, 12, 13, 16인 레코드가 포함된 테이블에 대해 아래의 쿼리를 실행했다고 가정해 봅시다.

SELECT * FROM t_user WHERE id > 10 FOR UPDATE;

이 쿼리를 실행하면 id가 10 보다 큰 레코드들에 대해 record lock이 적용됩니다. 또한 존재하지 않는 값(ex: 11)과 쿼리 조건 범위 밖에 있는 값(ex: 9)에 대해서도 실제로 존재하는 레코드 사이의 간격(gap)에 gap lock이 설정되어, 해당 범위 내에서 새로운 레코드의 insert를 방지합니다.

1. id > 10을 만족하는 첫 번째 인덱스 레코드 12를 발견
2. 첫 번째 인덱스 발견 직전의 인덱스 레코드 id 8부터 id 12 사이에 gap lock 적용
3. id> > 10 인 모든 인덱스 레코드들의 사이에도 gap lock 적용
4. id > 10인 모든 인덱스 레코드들에 각각 record lock 적용

위 지식을 기반으로 저는 'select for update 구문을 사용 시 테이블에 이무 값도 없으면 락이 걸리까?'에 대한 의문점이 들었습니다. 왜냐하면 구간을 스캔하여 조건을 만족하는 인덱스 레코드에 대해 Lock을 설정하는 과정을 고려했을 때, 테이블에 단 한 개의 값도 없으면 잠금을 설정할 기준이 되는 인덱스 레코드가 부재하므로 잠금이 설정되지 않을 것이라 예상했기 때문입니다.

이러한 상황을 두 가지 시나리오 나누면 다음과 같습니다.

1. 현재 테이블에 어떤 값(row)이 존재할 때: select * for update를 통해 전체 레코드를 조회하는 경우, 여러 트랜잭션에서 새로운 값(row)의 추가(Insert)가 가능할까?
2. 현재 테이블에 단 한 개의 값(row)도 존재하지 않을 때: select * for update를 통해 전체 레코드를 조회하는 경우, 여러 트랜잭션에서 새로운 값(row)의 추가(Insert)가 가능할까?

실제 예시를 들어, 아래 MemberSerivce에서 findAllWithLock 메서드를 통해 전체 회원을 조회하고, 특정 조건에 따라 새로운 회원을 추가하는 상황을 고려했습니다. Member 테이블에 1개 이상의 값(row)의 존재 유무에 따라, 10개보다 많은 member가 저장이 될지에 대해 이 글을 보시는 분들도 한번 고민해 보시면 좋을 것 같습니다.

@Service
@RequiredArgsConstructor
public class MemberService {

    private final MemberRepository memberRepository;

    /**
     * 데이터베이스에서 현재 멤버 수를 조회하고, 10명 미만인 경우 새로운 멤버를 추가.
     * 이 과정에서 SELECT ... FOR UPDATE를 사용하여 동시성을 관리하며,
     * findAllWithLock() 메서드가 멤버 목록을 잠금 상태로 조회
     */
    @Transactional
    public void registerMember() {
        if (memberRepository.findAllWithLock().size() < 10) {
            memberRepository.save(new Member());
        }
    }
    
}

public interface MemberRepository extends JpaRepository<Member, Long> {
    @Query(value = "SELECT * FROM member for update", nativeQuery = true)
    List<Member> findAllWithLock();
}

먼저 첫 번째 시나리오인 '현재 테이블에 어떤 값(row)이 존재할 때, select * for update를 통해 전체 레코드를 조회하는 경우 여러 트랜잭션에서 새로운 값(row)의 추가(Insert)가 가능할까?'에 대해서는 저는 처음에 Member 테이블에 현재 어떠한 값이라도 존재하는 경우라면 존재하는 값들을 기준으로 삼아 Next-Key Lock을 걸어 10개 보다 많은 Member가 저장되지 않을 것이라고 생각했습니다. 그리고 결론적으로 이는 정답이었습니다.

두 번째 시나리오인 '현재 테이블에 단 한 개의 값(row)도 존재하지 않을 때, select * for update를 통해 전체 레코드를 조회하는 경우 여러 트랜잭션에서 새로운 값(row)의 추가(Insert)가 가능할까?'에 대해서는 저는 현재 테이블에 어떤 값도 존재하지 않으면 기준이 될 인덱스가 없어 Lock이 설정되지 않을 것이라 생각했습니다. 따라서 동시에 여러 스레드에서 if문을 통과하여 10개 이상의 Member가 저장될 수 있는 동시성 문제가 발생할 것이라 생각했습니다. 그리고 이는 결론적으로 틀렸습니다.

 

 

테스트

아래는 select for update 구문을 사용할 때, 조회 대상이 없는 경우에도 잠금이 걸릴 수 있는지에 대한 테스트입니다. InnoDB를 사용하는 MariaDB 환경에서 진행했으며, 트랜잭션의 격리 수준은 별도로 설정하지 않았기 때문에 기본값인 REPETABLE-READ가 적용됩니다.

MariaDB [(none)]> show variables like '%isol%';

+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+

member 테이블을 생성하고, 초기에는 테이블에 어떠한 값도 존재하지 않는 상태로 시작합니다.

CREATE TABLE member (
    id bigint AUTO_INCREMENT PRIMARY KEY
);

MariaDB [test]> select * from member;

Empty set (0.002 sec)

 

1. 조건 없는 조회

테이블에 아무런 조건 없이 select .. for update를 실행하여 전체 테이블에 잠금이 걸리는지 확인해 봅시다.

트랜잭션 A에서는 먼저 select * from member for update를 실행한 후, 새로운 값을 추가하며, 트랜잭션 B에서도 동일하게 select * from member for update를 실행하려고 시도합니다.

-- Transaction A
start transaction;
select * from member for update;
insert into member (id) values(default);
commit;

-- Transaction B
start transaction;
select * from member for update;
insert into member (id) values(default);
commit;

결과를 보면 트랜잭션 A는 성공적으로 INSERT를 수행했지만, 아직 commit이나 rollback을 하지 않았기 때문에 트랜잭션 B는 트랜잭션 A의 작업이 완료될 때까지 select 작업을 수행하지 못하고 대기 상태에 놓이게 됩니다. 따라서 이를 통해 select .. for update 구문이 조회 대상 레코드가 존재하지 않거나 심지어 테이블에 단 하나의 값도 존재하지 않는 경우에도 잠금이 걸린다는 것을 확인할 수 있습니다.

MariaDB [test]> SELECT
    ->     r.trx_id AS waiting_trx_id,
    ->     r.trx_mysql_thread_id AS waiting_thread,
    ->     r.trx_query AS waiting_query,
    ->     b.trx_id AS blocking_trx_id,
    ->     b.trx_mysql_thread_id AS blocking_thread,
    ->     b.trx_query AS blocking_query,
    ->     l.lock_id AS requested_lock_id,
    ->     l.lock_mode AS requested_lock_mode,
    ->     l.lock_table,
    ->     l.lock_index,
    ->     l.lock_type
    -> FROM information_schema.innodb_lock_waits w
    -> INNER JOIN information_schema.innodb_locks l ON w.requested_lock_id = l.lock_id
    -> INNER JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id
    -> INNER JOIN information_schema.innodb_trx b ON w.blocking_trx_id = b.trx_id;
   
+----------------+----------------+-------------------------------------------------------------------------------+
| waiting_trx_id | waiting_thread | waiting_query                                                                 |
+----------------+----------------+-------------------------------------------------------------------------------+
| 9044           | 6              | SET STATEMENT SQL_SELECT_LIMIT=501 FOR select * from member for update        |
+----------------+----------------+-------------------------------------------------------------------------------+
| blocking_trx_id| blocking_thread| blocking_query                                                                |
+----------------+----------------+-------------------------------------------------------------------------------+
| 9041           | 5              | NULL                                                                          |
+----------------+----------------+-------------------------------------------------------------------------------+
| requested_lock_id | requested_lock_mode | lock_table      | lock_index | lock_type                         |
+-------------------+---------------------+-----------------+------------+-----------------------------------+
| 9044:178:3:2      | X                   | `test`.`member` | PRIMARY    | RECORD                            |
+-------------------+---------------------+-----------------+------------+-----------------------------------+

실제로 information_schema 조회를 통해 상세한 결과를 보면 select * from member for update 쿼리는 테이블에 데이터가 없더라도 테이블의 가능한 모든 가상 레코드에 대해 잠금을 시도한다는 것을 lock_type이 record로 표시되어 있는 것에서 알 수 있습니다.

결론적으로 트랜잭션 A와 B는 테이블의 테이블에 값이 없음에도 불구하고 모든 레코드를 대상으로 Exclusive Lock을 시도하기 때문에, 트랜잭션 A가 select * for member for update를 실행하여 잠금을 걸고 있다면 트랜잭션 B는 트랜잭션 A가 완료될 때까지 대기해야 합니다. 그리고 이전의 예시 코드에 대해 아래 테스트를 수행하면 10개 이하의 멤버만 저장되는 것을 확인할 수 있습니다.

@SpringBootTest(classes = TestProjectApplication.class)
class MemberServiceConcurrentTest {

    @Autowired
    private MemberService memberService;

    @Autowired
    private MemberRepository memberRepository;

    @Test
    @DisplayName("10개 이하의 멤버만 등록되어야 한다.")
    void ensure_at_most_ten_members_registered_concurrently() throws InterruptedException {
        final int numberOfThreads = 200;
        CountDownLatch latch = new CountDownLatch(numberOfThreads);
        ExecutorService executor = Executors.newFixedThreadPool(64);

        for (int i = 0; i < numberOfThreads; i++) {
            executor.submit(() -> {
                try {
                    memberService.registerMember();
                }
                finally {
                    latch.countDown();
                }
            });
        }
        latch.await();

        long savedMembersCount = memberRepository.count();
        // 저장된 멤버의 수가 10 이하인지 검증
        assertTrue(savedMembersCount <= 10, "등록된 멤버 수가 10 이하이어야 합니다.");
    }
}

 

2. 서로 다른 범위의 조회

마찬가지로 비어있는 member 테이블을 대상으로 이번에는 서로 다른 범위를 가진 select .. for update 쿼리를 다른 트랜잭션에서 동시에 실행하면 어떻게 되는지 살펴봅시다. 

-- Transaction A
start transaction;
select * from member where id > 20 and id < 30 for update;
insert into member (id) values(default);
commit;

-- Transaction B
start transaction;
select * from member where id > 50 and id < 60 for update;
insert into member (id) values(default);
commit;

저는 조건절에 범위를 지정해 주더라도 테이블에 값이 없으므로 첫 번째 테스트 결과와 같이 테이블의 가능한 모든 가상 레코드에 대해 잠금을 시도하여 트랜잭션 A의 커밋 또는 롤백이 완료되기 전까지는 트랜잭션 B가 select .. for update 실행 부분에서 대기할 것이라 예상했습니다. 하지만 실제로는 서로 다른 범위에 대한 select .. for update 쿼리들은 동시에 실행되어, 각각의 범위에 대해 독립적인 잠금이 설정되며 insert 구문에서 대기하고 있는 것을 확인할 수 있습니다.

MariaDB [test]> SELECT
    ->     r.trx_id AS waiting_trx_id,
    ->     r.trx_mysql_thread_id AS waiting_thread,
    ->     r.trx_query AS waiting_query,
    ->     b.trx_id AS blocking_trx_id,
    ->     b.trx_mysql_thread_id AS blocking_thread,
    ->     b.trx_query AS blocking_query,
    ->     l.lock_id AS requested_lock_id,
    ->     l.lock_mode AS requested_lock_mode,
    ->     l.lock_table,
    ->     l.lock_index,
    ->     l.lock_type
    -> FROM information_schema.innodb_lock_waits w
    -> INNER JOIN information_schema.innodb_locks l ON w.requested_lock_id = l.lock_id
    -> INNER JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id
    -> INNER JOIN information_schema.innodb_trx b ON w.blocking_trx_id = b.trx_id;
   
+----------------+----------------+-----------------------------------------+-----------------+-----------------+----------------+-------------------+---------------------+-----------------+------------+-----------+
| waiting_trx_id | waiting_thread | waiting_query                           | blocking_trx_id | blocking_thread | blocking_query | requested_lock_id | requested_lock_mode | lock_table      | lock_index | lock_type |
+----------------+----------------+-----------------------------------------+-----------------+-----------------+----------------+-------------------+---------------------+-----------------+------------+-----------+
|           9049 |              6 | insert into member (id) values(default) |            9048 |               5 | NULL           | 9049:178:3:1      | X                   | `test`.`member` | PRIMARY    | RECORD    |
+----------------+----------------+-----------------------------------------+-----------------+-----------------+----------------+-------------------+---------------------+-----------------+------------+-----------+

이러한 이유는 각 트랜잭션이 서로 다른 범위를 대상으로 select .. for update 쿼리를 실행하면, InnoDB는 지정된 각 범위에 대해 독립적으로 Gap Lock을 설정하기 때문입니다. 그리고 마찬가지로 테이블에 실제로 존재하는 값뿐만 아니라 존재하지 않는 값에 대해서도 지정된 범위에 따라 별도의 잠금이 걸립니다.

결론적으로 InnoDB는 조회되는 값이 없는 상황에서도 조건절에 주어진 범위에 기반하여 잠금을 설정하며, 서로 다른 범위를 대상으로 하는 트랜잭션들은 각자의 지정된 범위 내에서만 잠금을 받게 됩니다. 이로 인해 동일 테이블에서 조건절에 따라 서로 다른 범위에 대한 쿼리들이 동시에 수행될 수 있습니다.
 

3. 겹치는 범위 조회

마찬가지로 비어있는 member 테이블에서 겹치는 범위를 대상으로 select .. for update 쿼리를 실행하여, 이번에는 겹치는 부분에서 잠금이 어떻게 걸리는지 확인해 봅시다.

-- Transaction A
start transaction;
select * from member where id > 10 and id < 20 for update;
insert into member (id) values(default);
commit;

-- Transaction B
start transaction;
select * from member where id > 15 and id < 25 for update;
insert into member (id) values(default);
commit;

위 결과는 어떨까요? 테스트 1번과 2번과 결과를 봤을 때 이번 테스트는 일단은 범위가 주어지기 때문에 주어진 범위로 락이 걸어질 것으로 예상되며, 범위가 겹치기 때문에 별도로 잠금이 걸리지 않아 트랜잭션 B는 select 부분에서 트랜잭션 A를 기다릴 것으로 기대할 수 있습니다.

그런데 이게 무슨 일일까요? 실제 결과는 트랜잭션 B에서 select .. for update 쿼리가 바로 실행되며, 겹치는 범위에도 불구하고 트랜잭션 A의 완료를 select가 아닌 insert 구문에서 대기하는 것을 확인할 수 있습니다. 이유는 트랜잭션 A와 트랜잭션 B가 겹치는 범위에 대해 잠금을 시도하지만, 실제로 데이터가 존재하지 않기 때문에 InnoDB가 Gap-Lock을 사용하여 조회는 허락하고, 이 범위에 대한 새로운 데이터의 삽입을 방지하기 때문입니다. 따라서 트랜잭션 B가 대기하는 위치는 삽입을 시도하는 지점이 됩니다.

MariaDB [test]> SELECT
    ->     r.trx_id AS waiting_trx_id,
    ->     r.trx_mysql_thread_id AS waiting_thread,
    ->     r.trx_query AS waiting_query,
    ->     b.trx_id AS blocking_trx_id,
    ->     b.trx_mysql_thread_id AS blocking_thread,
    ->     b.trx_query AS blocking_query,
    ->     l.lock_id AS requested_lock_id,
    ->     l.lock_mode AS requested_lock_mode,
    ->     l.lock_table,
    ->     l.lock_index,
    ->     l.lock_type
    -> FROM information_schema.innodb_lock_waits w
    -> INNER JOIN information_schema.innodb_locks l ON w.requested_lock_id = l.lock_id
    -> INNER JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id
    -> INNER JOIN information_schema.innodb_trx b ON w.blocking_trx_id = b.trx_id;
   
+----------------+----------------+-----------------------------------------+-----------------+-----------------+----------------+-------------------+---------------------+-----------------+------------+-----------+
| waiting_trx_id | waiting_thread | waiting_query                           | blocking_trx_id | blocking_thread | blocking_query | requested_lock_id | requested_lock_mode | lock_table      | lock_index | lock_type |
+----------------+----------------+-----------------------------------------+-----------------+-----------------+----------------+-------------------+---------------------+-----------------+------------+-----------+
| 9073           | 6              | insert into member (id) values(default) | 9072            | 5               | NULL           | 9073:179:3:1      | X                   | `test`.`member` | PRIMARY    | RECORD    |
+----------------+----------------+-----------------------------------------+-----------------+-----------------+----------------+-------------------+---------------------+-----------------+------------+-----------+

더 자세히는 당근 기술블로그의 아래 부분을 참고하면 도움이 되는데, 

두 트랜잭션이 서로 대기하지 않고 select .. for update 쿼리를 실행할 수 있는 이유는, Gap Lock이 Shared Lock의 형태로만 존재하기 때문입니다. 즉 비어있는 member 테이블에서 트랜잭션 A와 B가 겹치는 범위에 대해 select .. for update 쿼리를 실행할 때 실제로 데이터가 존재하지 않음에도 불구하고 해당 범위에 대해 Gap Lock이 적용되지만, Gap Lock은 Shared Lock 형태로만 존재하기 때문에 여러 트랜잭션에서 동일한 범위에 대해 select .. for update를 실행해도 잠금 경합이 발생하지 않습니다. 

그렇다면 만약 범위 내인 member id가 16이라는 값이 있는 경우에는 어떻게 될까요?

MariaDB [test]> select * from member;

+----+
| id |
+----+
| 16 |
+----+

1 row in set (0.002 sec)

+----------------+----------------+--------------------------------------------------------------------------------------------------+-----------------+-----------------+----------------+-------------------+---------------------+-----------------+------------+-----------+
| waiting_trx_id | waiting_thread | waiting_query                                                                                    | blocking_trx_id | blocking_thread | blocking_query | requested_lock_id | requested_lock_mode | lock_table      | lock_index | lock_type |
+----------------+----------------+--------------------------------------------------------------------------------------------------+-----------------+-----------------+----------------+-------------------+---------------------+-----------------+------------+-----------+
| 9086           | 6              | SET STATEMENT SQL_SELECT_LIMIT=501 FOR select * from member where id > 15 and id < 25 for update | 9083            | 5               | NULL           | 9086:180:3:2      | X                   | `test`.`member` | PRIMARY    | RECORD    |
+----------------+----------------+--------------------------------------------------------------------------------------------------+-----------------+-----------------+----------------+-------------------+---------------------+-----------------+------------+-----------+

해당 범위에 해당하는 값이 존재한다면 이번에는 트랜잭션 B가 select .. for update  구문에서부터 트랜잭션 A의 작업 완료를 대기하게 됩니다. 이는 select .. for update 구문이 해당 범위 내 존재하는 모든 인덱스 레코드에 대해 잠금을 설정하기 때문이며, 실제 데이터가 있는 경우에는 다른 트랜잭션이 그 데이터에 대한 작업을 시작하기 전에 기존 트랜잭션의 완료를 기다려야 하기 때문입니다.

• 트랜잭션 A 실행: 트랜잭션 A는 select .. for update 쿼리를 사용하여 id 값이 16인 행에 대해 Exclusive Lock을 설정합니다. 이를 통해 해당 행을 수정하거나 접근하는 다른 트랜잭션의 작업을 방지합니다.
• 트랜잭션 B 대기: 트랜잭션 B 역시 겹치는 범위에서 select .. for update를 실행하여 id가 16인 행을 포함하려 합니다. 그러나 이 행은 이미 트랜잭션 A에 의해 잠겨 있기 때문에, 트랜잭션 B는 트랜잭션 A의 커밋 또는 롤백이 완료되어 해당 잠금이 해제될 때까지 대기 상태에 들어갑니다.

결론적으로 겹치는 범위 조회의 경우 비어 있는 테이블 상황에서는 select .. for update 구문이 즉시 실행되고 실제 데이터 삽입 시점에서만 다른 트랜잭션의 완료를 대기합니다. 반면 범위 내에 실제 데이터가 존재하는 경우에는 조회 단계부터 다른 트랜잭션의 완료를 대기하게 됩니다.

 

 

정리

select .. for update를 통해 데이터베이스에 새로운 데이터의 삽입을 효과적으로 방지할 수 있음을 확인했습니다. 하지만 실제 테스트를 통해 확인했듯이 잠금이 발생하는 시점이 주어진 상황에 따라 select 명령어의 실행 단계에서 발생할 수도 있고, insert 명령어의 실행 단계에서도 발생할 수 있다는 점을 고려하여 비즈니스 로직에서 예상치 못한 결과가 발생할 수 있으므로 주의하면 좋을 것 같습니다.

예를 들어 주어진 상황에 따라 select 부분이 정상적으로 실행되어 다음과 같은 코드에서 something()로직이 수행될 경우 예상치 못한 문제가 발생할 수 있습니다.

@Service
@RequiredArgsConstructor
public class MemberService {

    private final MemberRepository memberRepository;

    @Transactional
    public void registerMember() {
        if (memberRepository.findAllWithLock().size() < 10) {
            something();
            memberRepository.save(new Member());
        }
    }
    
}

피드백은 언제든지 환영이며 틀린 부분이 있다면 댓글 달아주시면 빠르게 반영하겠습니다.

 

 

참고

• https://miintto.github.io/docs/mysql-select-for-update
• https://medium.com/daangn/mysql-gap-lock-%EB%8B%A4%EC%8B%9C%EB%B3%B4%EA%B8%B0-7f47ea3f68bc
• https://medium.com/daangn/mysql-gap-lock-%EB%91%90%EB%B2%88%EC%A7%B8-%EC%9D%B4%EC%95%BC%EA%B8%B0-49727c005084
• https://dev.mysql.com/doc/refman/8.3/en/innodb-locking.html