2026-07-30
MySQL

유니크 인덱스와 INSERT의 데드락

북마크 토글 API 동시성 문제 해결 중에 InnoDB 유니크 인덱스 INSERT 데드락을 로그와 소스 코드로 분석하기

최근 서비스에서 퀴즈 북마크 기능을 구현하면서 간헐적으로 500 응답이 발생하는 문제를 겪었습니다.
북마크 API는 현재 상태를 조회한 뒤, 이미 북마크되어 있으면 삭제하고 그렇지 않으면 추가하는 토글 방식의 API였습니다.

QuizService.kt
@Transactional
fun markQuiz(userId: UUID, command: MarkQuizCommand) {
    if (!quizRepository.existsById(command.quizId)) {
        throw QuizNotFoundException()
    }
 
    if (quizBookmarkRepository.existsByQuizIdAndUserId(command.quizId, userId)) {
        quizBookmarkRepository.deleteByQuizIdAndUserId(command.quizId, userId)
    } else {
        quizBookmarkRepository.saveIgnore(
            QuizBookmark(
                quizId = command.quizId,
                userId = userId
            )
        )
    }
}
V1__init_schema.sql
CREATE TABLE quiz_bookmark (
    id BINARY(16) PRIMARY KEY,
    quiz_id BINARY(16) NOT NULL,
    user_id BINARY(16) NOT NULL,
    UNIQUE INDEX uk_quiz_bookmark_quiz_user (quiz_id, user_id)
);

같은 사용자가 같은 퀴즈를 중복으로 북마크하지 못하도록 (quiz_id, user_id) 유니크 인덱스도 설정했습니다.
단일 요청에서는 문제가 없었지만, 같은 북마크를 짧은 시간에 여러 번 토글하면 문제가 나타났습니다.

문제 상황

같은 사용자와 같은 퀴즈에 북마크 요청 100개를 동시에 보내 문제를 재현했습니다.

결과는 다음과 같았습니다.

항목값
총 요청 수100
성공 응답87
실패 응답13

100개 중 13개 요청이 500으로 실패했고, 서버에는 다음과 같은 로그가 남았습니다.

org.springframework.dao.DeadlockLoserDataAccessException:
jOOQ; SQL [insert ignore into `quiz_bookmark` (`id`, `quiz_id`, `user_id`) values (?, ?, ?)];
Deadlock found when trying to get lock; try restarting transaction

예외가 발생한 위치는 INSERT였으며 예외는 데드락 전용 예외인 DeadlockLoserDataAccessException이 발생했습니다.
왜 INSERT가 데드락을 일으켰는지 확인하기 위해 InnoDB의 데드락 로그를 조회했습니다.

SHOW ENGINE INNODB STATUS
LATEST DETECTED DEADLOCK
------------------------
2026-08-14 20:20:48 281473010351872
*** (1) TRANSACTION:
TRANSACTION 124053, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 10 row lock(s), undo log entries 1
MySQL thread id 40618, OS thread handle 281473304682240, query id 230080 172.19.0.1 root update
insert ignore into `quiz_bookmark` (`id`, `quiz_id`, `user_id`) values (x'a37e8fb9981d11f1b64c4bf5a1a60da9', x'55555555555555555555555555555555', x'11111111111111111111111111111111')
 
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 81 page no 5 n bits 80 index uk_quiz_bookmark_quiz_user of table `quizit`.`quiz_bookmark` trx id 124053 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
...
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
...
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
...
 
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 81 page no 5 n bits 80 index uk_quiz_bookmark_quiz_user of table `quizit`.`quiz_bookmark` trx id 124053 lock_mode X insert intention waiting
Record lock, heap no 1
 
*** (2) TRANSACTION:
TRANSACTION 124054, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 10 row lock(s), undo log entries 1
MySQL thread id 40616, OS thread handle 281472567598848, query id 230081 172.19.0.1 root update
insert ignore into `quiz_bookmark` (`id`, `quiz_id`, `user_id`) values (x'a37e8fba981d11f1b64ccf532829ca93', x'55555555555555555555555555555555', x'11111111111111111111111111111111')
 
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 81 page no 5 n bits 80 index uk_quiz_bookmark_quiz_user of table `quizit`.`quiz_bookmark` trx id 124054 lock mode S
Record lock, heap no 1
Record lock, heap no 2
Record lock, heap no 3
...
 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 81 page no 5 n bits 80 index uk_quiz_bookmark_quiz_user of table `quizit`.`quiz_bookmark` trx id 124054 lock_mode X insert intention waiting
Record lock, heap no 1
*** WE ROLL BACK TRANSACTION (2)

두 INSERT 트랜잭션은 같은 유니크 인덱스 범위에 공유 락을 보유한 채 배타 락인 삽입 의도 락(Insert Intention Lock)을 기다리고 있었습니다.
서로의 공유 락 때문에 삽입에 필요한 락을 얻지 못한 것이 직접적인 데드락 원인이었습니다.

여기서 먼저 확인할 것은 INSERT 트랜잭션이 공유 락을 얻게 된 이유였습니다.

INSERT에서의 공유 락

INSERT는 일반적으로 배타 락만을 필요로 한다고 생각하기 쉽습니다.
하지만 유니크 인덱스에서는 중복 여부를 확인하는 과정에서 공유 락도 사용합니다.

If a duplicate-key error occurs, a shared lock on the duplicate index record is set.

공식 문서는 중복 키 에러가 발생하면 해당 인덱스 레코드에 공유 락이 설정된다고 설명합니다.
하지만 로그에는 공유 락이 걸린 레코드가 여러 개 출력되어 있었고, 중복 검사 범위를 문서만으로는 알기 어려웠습니다.
그래서 mysql-server의 실제 INSERT 관련 코드를 확인했습니다.

row0ins.cc
[[nodiscard]] static dberr_t row_ins_scan_sec_index_for_duplicate(...) {
  do {
    const rec_t *rec = pcur.get_rec();
    ...
    const bool is_next = !is_supremum && (cmp_dtuple_rec(entry, rec, index, offsets) < 0);
 
    if (flags & BTR_NO_LOCKING_FLAG) {
      ...
    } else {
      ...
      err = row_ins_set_rec_lock(LOCK_S, lock_type, block, rec, index, offsets, thr);
    }
 
    if (!is_next && !index->allow_duplicates) {
      ...
    } else {
      ut_a(is_next || index->allow_duplicates);
      goto end_scan;
    }
  } while (pcur.move_to_next(mtr));
  ...
}

row_ins_scan_sec_index_for_duplicate()는 레코드를 삽입하기 전에 유니크 인덱스의 중복 후보를 스캔하고, row_ins_set_rec_lock()으로 해당 범위에 공유 락을 설정합니다.
스캔은 B-Tree 정렬 기준으로 삽입하려는 엔트리보다 큰 첫 번째 레코드를 만날 때까지 이어집니다.
즉, 공유 락은 임의의 위치가 아니라 중복 가능성이 있는 인덱스 범위를 검사하는 과정에서 획득한 것이었습니다.

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 81 page no 5 n bits 80 index uk_quiz_bookmark_quiz_user of table `quizit`.`quiz_bookmark` trx id 124053 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
...

소스 코드로 공유 락을 획득하는 경로는 확인했지만, 같은 유니크 키의 중복 후보가 로그에 여러 개 나타난 이유는 아직 설명되지 않았습니다.
다음 단서는 각 레코드에 표시된 info bits 32였습니다.

삭제 표시 레코드

로그에 출력된 레코드 대부분은 info bits 값이 32였습니다.
info bits는 InnoDB 레코드 헤더에 저장되는 상태 플래그이며, 비트 값에 따라 의미가 달라집니다.

rec.h
/* Info bit denoting the predefined minimum record: this bit is set
if and only if the record is the first user record on a non-leaf
B-tree page that is the leftmost page on its level
(PAGE_LEVEL is nonzero and FIL_PAGE_PREV is FIL_NULL). */
constexpr uint32_t REC_INFO_MIN_REC_FLAG = 0x10UL;
 
/** The deleted flag in info bits; when bit is set to 1, it means the record has
 been delete marked */
constexpr uint32_t REC_INFO_DELETED_FLAG = 0x20UL;
 
/* Use this bit to indicate record has version */
constexpr uint32_t REC_INFO_VERSION_FLAG = 0x40UL;
 
/** The instant ADD COLUMN flag. When it is set to 1, it means this record
was inserted/updated after an instant ADD COLUMN. */
constexpr uint32_t REC_INFO_INSTANT_FLAG = 0x80UL;

REC_INFO_DELETED_FLAG의 값은 0x20, 10진수로 32입니다.
따라서 해당 레코드들은 DELETE로 인해 논리적으로 삭제되었지만, purge 스레드가 아직 물리적으로 제거하지 않은 삭제 표시(delete-marked) 상태였습니다.
반복된 토글 과정에서 삭제된 유니크 인덱스 레코드들이 인덱스에 남아 있었던 것입니다.

다음 의문은 삭제 표시 레코드를 중복 후보로 스캔하면서도 INSERT가 배타 락을 거는 실제 삽입 단계로 넘어갈 수 있는 이유였습니다.
답은 실제 중복 여부를 판단하는 row_ins_dupl_error_with_rec()에서 찾을 수 있었습니다.

row0ins.cc
static bool row_ins_dupl_error_with_rec(
    const rec_t *rec,
    const dtuple_t *entry,
    dict_index_t *index,
    const ulint *offsets
) {
    ulint matched_fields;
    ulint n_unique;
    ulint i;
 
    n_unique = dict_index_get_n_unique(index);
    matched_fields = 0;
 
    entry->compare(rec, index, offsets, &matched_fields);
 
    if (matched_fields < n_unique) {
        return false;
    }
 
    if (!index->is_clustered() && !index->nulls_equal) {
        for (i = 0; i < n_unique; i++) {
            if (dfield_is_null(dtuple_get_nth_field(entry, i))) {
                return false;
            }
        }
    }
 
    return rec_get_deleted_flag(rec, rec_offs_comp(offsets)) == 0;
}

이 함수는 유니크 키 컬럼이 모두 일치하더라도 마지막에 rec_get_deleted_flag()를 확인해 삭제 표시 상태가 아닌 레코드만 실제 중복으로 판단합니다.
결국 INSERT는 삭제 표시 레코드를 중복 후보로 스캔하며 공유 락을 획득하지만, 살아 있는 중복 레코드는 아니라고 판단해 실제 삽입 단계로 넘어갈 수 있습니다.

데드락 발생 흐름

지금까지 확인한 데드락 로그와 소스 코드를 토대로 애플리케이션에서 발생한 흐름을 정리하면 다음과 같습니다.

  1. 북마크 토글 요청이 동시에 들어옵니다.
  2. 삭제 분기로 들어간 요청이 기존 인덱스 레코드를 삭제 표시 상태로 만듭니다.
  3. purge가 완료되기 전에 같은 유니크 키에 대한 INSERT가 동시에 실행됩니다.
  4. 각 INSERT는 유니크 인덱스 중복 검사를 위해 삭제 표시 레코드들에 공유 락을 획득합니다.
  5. 살아 있는 중복 레코드는 없다고 판단한 뒤 실제 삽입을 위해 배타 락을 요청합니다.
  6. 두 트랜잭션이 서로의 공유 락을 기다리며 순환 대기에 빠집니다.

이를 시퀀스 다이어그램으로 표현하면 다음과 같습니다.

Transaction 3Transaction 2InnoDBTransaction 1Transaction 3Transaction 2InnoDBTransaction 1DELETE삭제 표시 처리배타 락 획득INSERT IGNORETransaction 1의 배타 락으로 인해 공유 락 대기INSERT IGNORETransaction 1의 배타 락으로 인해 공유 락 대기COMMIT공유 락 획득공유 락 획득배타 락 요청Transaction 3의 공유 락으로 인해 대기배타 락 요청Transaction 2의 공유 락으로 인해 대기데드락 감지ROLLBACK

물론 데드락 로그에서 두 트랜잭션이 삽입을 위해 기다린 대상은 삭제 표시 레코드가 아닌 다음과 같은 일반 레코드였습니다.

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

해당 레코드는 슈프리멈(Supremum) 레코드로, 실제 테이블 레코드가 아니라 InnoDB 인덱스 페이지의 끝을 나타내는 가상 레코드입니다.
InnoDB는 갭(gap)을 오른쪽 경계 레코드에 붙여 표현하므로, 슈프리멈 레코드에 대한 배타 락은 페이지의 마지막 갭에 새 인덱스 레코드를 삽입하려다 대기했다는 의미입니다.

이로써 INSERT가 공유 락을 보유한 이유와 실제 삽입 단계에서 순환 대기가 만들어진 과정을 연결할 수 있었습니다.

해결

해결 방법으로는 같은 (quiz_id, user_id) 요청을 락으로 직렬화하는 방법과, 데드락이 발생한 트랜잭션을 재시도하는 방법을 비교했습니다.

MySQL named lock이나 별도의 락 테이블을 사용하면 같은 키의 요청을 순서대로 처리할 수 있습니다.
하지만 북마크 토글은 트랜잭션이 짧고 재시도 비용이 작은 반면, 별도 락은 획득과 해제, 타임아웃과 장애 복구까지 관리해야 합니다.

데드락은 InnoDB가 순환 대기를 끊기 위해 한 트랜잭션을 롤백한 일시적인 실패이므로, 같은 작업을 새 트랜잭션에서 다시 실행해 복구할 수 있습니다.
따라서 별도 락을 추가하는 대신 데드락이 발생한 트랜잭션 전체를 재시도하기로 했습니다.

실제 구현에서는 기존에 사용하던 transaction() 확장 함수에 재시도 옵션을 추가했습니다.
retryOn을 DeadlockLoserDataAccessException으로 제한해 모든 예외를 무작정 반복하지 않도록 했습니다.

TransactionExtensions.kt
data class TransactionRetry(
    val count: Int,
    val backoff: Duration,
    val retryOn: (Throwable) -> Boolean = { it is TransientDataAccessException }
)
 
fun <T> transaction(
    readOnly: Boolean = false,
    propagation: Propagation = Propagation.REQUIRED,
    retry: TransactionRetry? = null,
    func: () -> T
): T {
    if (retry == null) {
        return TransactionWrapper(
            readOnly = readOnly,
            propagation = propagation,
            func = func
        )
    }
 
    var count = 0
 
    while (true) {
        try {
            return TransactionWrapper(
                readOnly = readOnly,
                propagation = propagation,
                func = func
            )
        } catch (exception: Throwable) {
            if (!retry.retryOn(exception)) {
                throw exception
            }
 
            count += 1
            if (count >= retry.count) {
                throw exception
            }
 
            Thread.sleep(retry.backoff.inWholeMilliseconds)
        }
    }
}
QuizService.kt
@Transactional(propagation = Propagation.NOT_SUPPORTED)
fun markQuiz(
    userId: UUID,
    command: MarkQuizCommand
) = transaction(
    retry =
        TransactionRetry(
            count = 3,
            backoff = 100.milliseconds,
            retryOn = { it is DeadlockLoserDataAccessException }
        )
) {
    if (!quizRepository.existsById(command.quizId)) {
        throw QuizNotFoundException()
    }
 
    if (quizBookmarkRepository.existsByQuizIdAndUserId(command.quizId, userId)) {
        quizBookmarkRepository.deleteByQuizIdAndUserId(command.quizId, userId)
    } else {
        quizBookmarkRepository.saveIgnore(
            QuizBookmark(
                quizId = command.quizId,
                userId = userId
            )
        )
    }
}

트랜잭션 경계는 transaction() 헬퍼가 만들고, 데드락이 발생하면 다음 시도에서 새로운 트랜잭션을 다시 시작하도록 구성했습니다.

검증

재시도 적용 후 같은 사용자가 같은 퀴즈에 요청 100개를 동시에 보내는 테스트를 100번 수행했습니다.

항목결과
전체 요청10,000개
성공 응답10,000개
실패 응답0개
정확히 한 번 재시도한 요청1,104개
정확히 두 번 재시도한 요청10개

1,114개 요청에서 실제 재시도가 발생했지만 모두 최대 세 번째 시도 안에 성공해 사용자에게 노출되는 실패는 없었습니다.
이를 통해 롤백된 작업이 새로운 트랜잭션에서 정상적으로 복구되는 것을 확인했습니다.