MySQL 유니크 키 중복 입력이 대기하는 이유: 미커밋 INSERT 충돌 실험

유니크 키가 있는 테이블에 이미 있는 값을 넣으면 곧바로 중복 오류가 날 것이라고 예상하기 쉽습니다. 그런데 같은 값을 먼저 넣은 트랜잭션이 아직 끝나지 않았다면, 뒤따르는 INSERT는 오류도 성공도 아닌 대기 상태에 빠집니다. 다른 세션에서 조회하면 그 값은 보이지 않는데도 말입니다.

이 글에서는 MySQL 8.4.11에서 두 연결이 같은 request_key를 입력하도록 만들어 이 현상을 재현했습니다. 확인한 내용은 다음 세 가지입니다.

• 조회 결과가 0건인데 INSERT가 기다리는 이유
• 대기 중인 잠금이 어느 인덱스의 어떤 모드인지
• 선행 트랜잭션이 COMMIT할 때와 ROLLBACK할 때 결과가 어떻게 갈리는지

실제 서비스 장애 이력이 아니라, 별도 스키마에서 수행한 재현 실험입니다.

1. 장애처럼 보이는 현상: 조회는 0건인데 INSERT가 끝나지 않는다

결제나 주문처럼 같은 요청이 두 번 처리되면 안 되는 업무에서는 요청 식별자에 유니크 인덱스를 둡니다. 이런 환경에서 다음과 같은 문의가 들어오곤 합니다. “먼저 SELECT로 확인했을 때는 0건이었는데, INSERT가 응답 없이 멈춰 있다.”

조회 결과만 보고 “충돌할 데이터가 없으니 잠금 문제는 아니다”라고 판단하면 원인을 놓칩니다. 일반 SELECT가 보여 주는 데이터와, 유니크 제약을 검사할 때 InnoDB가 고려하는 다른 트랜잭션의 미확정 데이터는 서로 다르기 때문입니다.

테스트 환경과 측정 조건

항목	실험 조건
MySQL 버전	8.4.11 (Community, Windows)
테스트 스키마·테이블	lab_unique_wait_33.unique_insert_lab
스토리지 엔진	InnoDB
초기 데이터	2행
기본키	PRIMARY KEY (id)
유니크 인덱스	UNIQUE KEY uk_request_key (request_key)
격리 수준	REPEATABLE-READ
자동 커밋	1 (Workbench에서는 true로 표시)
잠금 대기 제한	innodb_lock_wait_timeout 세션 값 300초
연결 구성	INSERT용 A·B, 진단용 C
비교 조건	A가 COMMIT하는 경우와 ROLLBACK하는 경우

아래는 같은 구조를 만드는 재현용 SQL입니다. 초기 2행의 값은 실험 결과에 영향을 주지 않는 범위에서 예시로 적었습니다.

CREATE DATABASE lab_unique_wait_33 CHARACTER SET utf8mb4;
USE lab_unique_wait_33;

CREATE TABLE unique_insert_lab (
    id INT NOT NULL,
    request_key VARCHAR(40) NOT NULL,
    writer VARCHAR(20) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uk_request_key (request_key)
) ENGINE = InnoDB;

INSERT INTO unique_insert_lab VALUES
    (1, 'REQ-INIT-001', 'INIT'),
    (2, 'REQ-INIT-002', 'INIT');

COMMIT;

실험 전에 버전, 격리 수준, 자동 커밋, 잠금 대기 제한, 초기 행 수를 확인했습니다.

SELECT
    VERSION() AS mysql_version,
    @@session.transaction_isolation AS isolation_level,
    @@session.autocommit AS autocommit,
    @@session.innodb_lock_wait_timeout AS lock_wait_timeout_sec,
    (SELECT COUNT(*)
       FROM lab_unique_wait_33.unique_insert_lab) AS initial_rows,
    'PRIMARY KEY(id), UNIQUE KEY uk_request_key(request_key)'
        AS index_definition;

결과의 index_definition 열은 테이블 정의를 기록해 두려고 직접 적은 문자열이며, 시스템에서 읽어 온 값은 아닙니다. 실제 인덱스 구성은 SHOW INDEX FROM unique_insert_lab으로 확인할 수 있습니다. 뒤에서 살펴볼 잠금 진단 결과에 uk_request_key가 나타나는 것으로도 유니크 인덱스가 있다는 사실을 확인할 수 있습니다.

잠금 대기 제한 300초는 캡처할 시간을 벌려고 정한 실험값입니다. 기본값은 50초입니다. 이 값은 기다리는 쪽 세션에 적용되므로, 실험에서는 B 세션에 설정되어 있어야 의미가 있습니다.

2. 원인 가설: 선행 INSERT가 확정되기를 기다린다

세션 A와 B가 입력하는 값은 다음과 같이 정했습니다.

• 세션 A: id 101, request_key REQ-COMMIT-001
• 세션 B: id 201, 같은 request_key

두 세션의 기본키를 다르게 둔 이유는, 충돌 원인이 기본키가 아니라 유니크 키라는 점을 분명히 하기 위해서입니다.

세션 A에서는 INSERT 후 트랜잭션을 종료하지 않았습니다.

-- 세션 A
START TRANSACTION;

INSERT INTO unique_insert_lab
    (id, request_key, writer)
VALUES
    (101, 'REQ-COMMIT-001', 'SESSION_A');

-- COMMIT / ROLLBACK을 실행하지 않고 대기

세션 B에는 미리 SET SESSION innodb_lock_wait_timeout = 300;으로 대기 제한을 늘려 두었습니다. 이어서 같은 request_key를 입력하자, 이 INSERT는 결과를 돌려주지 않고 멈췄습니다.

-- 세션 B
START TRANSACTION;

INSERT INTO unique_insert_lab
    (id, request_key, writer)
VALUES
    (201, 'REQ-COMMIT-001', 'SESSION_B');

세운 가설은 “B가 같은 키를 가진 A의 미확정 행과 부딪혀, A가 끝나기를 기다린다”입니다. 가설이 맞다면 결과는 A의 선택에 따라 갈려야 합니다.

• A가 COMMIT하면 키가 확정되므로 B는 중복 오류를 받습니다.
• A가 ROLLBACK하면 선행 입력이 사라지므로 B는 입력에 성공합니다.

먼저 A와 B가 모두 진행 중인 상태에서 진단용 세션 C로 같은 키를 조회했습니다. 결과 열의 test_phase는 실험 단계를 기록하려고 넣은 고정 문자열입니다.

-- 세션 C
SELECT
    'A has not committed; B INSERT is waiting' AS test_phase,
    COUNT(*) AS visible_rows_from_c
FROM unique_insert_lab
WHERE request_key = 'REQ-COMMIT-001';

C의 결과는 0건입니다. 일반 SELECT는 잠금 없는 일관된 읽기라서, 커밋되지 않은 A의 행을 보지 않습니다. 그러나 B의 INSERT는 유니크 키를 검사하는 과정에서 그 미확정 행을 발견합니다. 같은 순간에 같은 키를 두고, 조회는 “없다”고 하고 INSERT는 “있을 수도 있다”고 판단하는 것이 이 현상의 핵심입니다.

따라서 “SELECT로 0건을 확인했으니 INSERT는 바로 성공한다”는 애플리케이션의 가정은 동시 요청이 있을 때 성립하지 않습니다.

3. 잠금 진단: 유니크 인덱스에서 S 잠금이 대기 중

대기 원인을 확인하려고 세션 C에서 performance_schema.data_lock_waits와 data_locks를 묶어 조회했습니다.

• data_lock_waits: 누가 누구를 기다리는지(요청 잠금과 차단 잠금의 관계)
• data_locks: 각 잠금의 인덱스, 모드, 상태
• threads: 잠금을 쥔 스레드를 연결 번호로 변환

SELECT
    rt.PROCESSLIST_ID AS waiting_session,
    bt.PROCESSLIST_ID AS blocking_session,
    r.INDEX_NAME AS index_name,
    r.LOCK_TYPE AS lock_type,
    r.LOCK_MODE AS waiting_mode,
    r.LOCK_STATUS AS waiting_status,
    b.LOCK_MODE AS blocking_mode,
    b.LOCK_STATUS AS blocking_status,
    r.LOCK_DATA AS lock_data
FROM performance_schema.data_lock_waits AS w
JOIN performance_schema.data_locks AS r
  ON r.ENGINE = w.ENGINE
 AND r.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks AS b
  ON b.ENGINE = w.ENGINE
 AND b.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID
LEFT JOIN performance_schema.threads AS rt
  ON rt.THREAD_ID = w.REQUESTING_THREAD_ID
LEFT JOIN performance_schema.threads AS bt
  ON bt.THREAD_ID = w.BLOCKING_THREAD_ID
WHERE r.OBJECT_SCHEMA = 'lab_unique_wait_33'
  AND r.OBJECT_NAME = 'unique_insert_lab';

항목	관찰 결과	의미
waiting_session	12	기다리는 연결 (세션 B)
blocking_session	8	막고 있는 연결 (세션 A)
index_name	uk_request_key	충돌 위치는 기본키가 아닌 유니크 인덱스
lock_type	RECORD	인덱스 레코드 단위 잠금
waiting_mode	S	B가 요청한 공유 잠금
waiting_status	WAITING	아직 획득하지 못함
blocking_mode	X,REC_NOT_GAP	A가 보유한 레코드 배타 잠금
blocking_status	GRANTED	A가 이미 획득함
lock_data	'REQ-COMMIT-001', 101	유니크 키 값과 A 행의 기본키

진단 결과는 가설과 일치합니다. 연결 12(B)가 uk_request_key의 'REQ-COMMIT-001' 레코드에 공유(S) 잠금을 요청했습니다. 그런데 연결 8(A)이 같은 레코드에 배타 잠금을 쥐고 있어서 대기하고 있습니다.

INSERT인데 왜 S 잠금을 요청할까

INSERT라면 배타 잠금만 쓸 것 같지만, 유니크 인덱스에서 같은 값을 발견하면 InnoDB는 먼저 그 레코드에 공유 잠금을 요청합니다. “이 값이 정말 확정된 중복인지”를 판정하려면 기존 레코드의 주인이 끝날 때까지 기다려야 하기 때문입니다. MySQL 공식 문서에도 중복 키가 발생하면 해당 인덱스 레코드에 공유 잠금이 설정된다고 설명되어 있습니다.

캡처의 waiting_mode가 S,REC_NOT_GAP이 아니라 S로만 표시된 점도 눈여겨볼 만합니다. data_locks에서 접미어 없는 S는 레코드와 그 앞 간격을 함께 잠그는 next-key 잠금입니다. B가 이 잠금을 얻은 뒤 트랜잭션을 끝내지 않으면, 같은 값뿐 아니라 그 앞 간격으로 들어오려는 다른 입력도 영향을 받을 수 있습니다.

A의 배타 잠금은 언제 생겼나

InnoDB는 새로 INSERT한 행에 처음부터 잠금 항목을 만들지 않습니다. 트랜잭션 ID를 기록해 두는 암묵적 잠금으로만 보호하다가, 다른 트랜잭션이 그 행과 충돌하는 순간 명시적 잠금으로 바꿉니다. 캡처의 X,REC_NOT_GAP은 B가 충돌을 일으키면서 A 몫으로 만들어진 잠금으로 해석할 수 있습니다.

이번 실험에서는 B가 들어오기 전의 data_locks를 따로 캡처하지 않았습니다. 다만 실무에서는 “INSERT만 하고 멈춘 트랜잭션은 충돌이 생기기 전까지 data_locks에 행 잠금이 보이지 않을 수 있다”는 점을 기억해 두면 좋습니다.

lock_data의 101은 A가 넣은 행의 기본키입니다. InnoDB 보조 인덱스는 각 항목에 기본키 값을 함께 저장하므로, 유니크 키 문자열과 기본키가 나란히 표시됩니다. 이 값으로 어떤 행이 충돌 대상인지 바로 특정할 수 있습니다.

4. COMMIT과 ROLLBACK에 따라 갈리는 결과

A가 COMMIT한 경우: B는 1062 오류

세션 A에서 COMMIT을 실행하자, 기다리던 B의 INSERT가 다음 오류로 끝났습니다.

MySQL Error (1062):
Duplicate entry 'REQ-COMMIT-001'
for key 'unique_insert_lab.uk_request_key'

출력의 01:02는 START TRANSACTION과 INSERT 두 문장을 실행한 스크립트 전체 소요 시간입니다. 실험자가 A를 종료할 때까지 일부러 기다린 시간이 그대로 들어 있으므로, INSERT의 처리 성능으로 읽으면 안 됩니다.

약 1분을 기다렸지만 대기 제한(300초) 안이었기 때문에, 오류는 잠금 시간 초과(1205)가 아니라 중복 키(1062)였습니다. 같은 상황이라도 대기 제한이 기본값 50초였다면 B는 1205를 먼저 받았을 것입니다.

여기서 놓치기 쉬운 점이 있습니다. 1062 오류는 실패한 INSERT 문장 하나만 되돌리고, B의 트랜잭션은 계속 열려 있습니다. 중복 검사 중에 얻은 S 잠금도 트랜잭션이 끝날 때까지 유지됩니다. 그래서 오류를 확인한 뒤 B에서 ROLLBACK을 실행해 실험 트랜잭션을 정리했습니다.

애플리케이션이 1062를 잡아 “이미 처리된 요청”으로 응답만 하고 트랜잭션을 닫지 않으면, 그 연결이 잠금을 쥔 채 커넥션 풀로 돌아갈 수 있습니다.

A가 ROLLBACK한 경우: B의 행이 저장됨

두 번째 실험에서는 키를 REQ-ROLLBACK-001로 바꾸고 같은 절차를 반복했습니다.

1. A가 id 102, B가 id 202로 같은 키를 입력해 B가 대기하도록 만들었습니다.
2. A에서 ROLLBACK을 실행했습니다.
3. B의 INSERT가 완료된 것을 확인하고 B에서 COMMIT했습니다.

두 실험의 최종 상태는 세션 C에서 한 번에 확인했습니다. test_result 열은 실험 내용을 설명하려고 CASE로 붙인 문구입니다. 실제 판단 근거는 id, request_key, writer 세 열입니다.

SELECT
    CASE request_key
        WHEN 'REQ-COMMIT-001' THEN 'A COMMIT -> B duplicate error'
        WHEN 'REQ-ROLLBACK-001' THEN 'A ROLLBACK -> B insert success'
    END AS test_result,
    id,
    request_key,
    writer
FROM unique_insert_lab
WHERE request_key IN (
    'REQ-COMMIT-001',
    'REQ-ROLLBACK-001'
)
ORDER BY request_key;

선행 트랜잭션 종료 방식	최종 id	request_key	writer	남지 않은 행
A COMMIT	101	REQ-COMMIT-001	SESSION_A	B의 201
A ROLLBACK	202	REQ-ROLLBACK-001	SESSION_B	A의 102

키마다 정확히 한 행씩 남았습니다. COMMIT 실험에서는 먼저 들어온 A의 행이, ROLLBACK 실험에서는 기다리던 B의 행이 남았습니다. 인덱스나 서버 설정은 그대로 두고 A의 종료 방식만 바꿨으므로, B의 결과가 A의 확정 여부에 따라 결정된다는 가설이 확인되었습니다.

5. 운영에서의 판단 기준

“먼저 조회하고 없으면 입력”은 중복을 막지 못함

앞에서 세션 C의 조회 결과가 0건이었던 것처럼, 조회 시점에 행이 보이지 않는다고 이후 INSERT가 성공한다는 보장은 없습니다. 다른 트랜잭션이 같은 키를 입력하는 중일 수도 있고, 조회와 입력 사이에 먼저 넣을 수도 있습니다.

중복 방지의 최종 책임은 유니크 제약에 맡기고, 애플리케이션은 1062 오류를 정상적인 업무 분기로 처리하는 편이 확실합니다. 주문 재시도처럼 같은 키가 같은 작업을 뜻한다면, 오류를 받았을 때 기존 처리 결과를 조회해 돌려주는 방식을 검토할 수 있습니다.

INSERT IGNORE나 INSERT ... ON DUPLICATE KEY UPDATE로 바꿔도 이 대기는 사라지지 않습니다. 이 구문들도 선행 트랜잭션의 미확정 행이 확정되어야 “무시할지, 갱신할지”를 판단할 수 있기 때문입니다. 이번 실험에서 직접 측정하지는 않았습니다.

대기 중인 INSERT가 아니라 막고 있는 트랜잭션을 찾음

이번 실험에서 대기를 끝낸 것은 B 쪽 조치가 아니라 A의 COMMIT 또는 ROLLBACK이었습니다. 운영에서도 3장의 잠금 진단 SQL로 blocking_session을 찾은 뒤, 다음을 확인하는 것이 순서입니다.

• 그 트랜잭션이 언제 시작되었는지
• 왜 아직 끝나지 않았는지

유니크 키를 입력한 뒤 외부 API 응답이나 불필요한 후속 작업을 기다리는 구조라면 잠금 유지 시간이 길어집니다. 업무 원자성은 지키되, 트랜잭션 안에는 꼭 필요한 작업만 넣도록 경계를 점검하는 것이 근본 대책입니다.

같은 키를 기다리는 세션이 여럿이면 데드락이 생길 수 있음

이번 실험은 후행 INSERT가 B 하나뿐이었고, 잠금 대기와 1062만 관찰했습니다. 그러나 같은 키를 B와 또 다른 세션 D가 동시에 기다리는 상황은 다릅니다. MySQL 공식 문서는 이때 A가 ROLLBACK하면 데드락이 발생할 수 있다고 설명합니다.

1. A가 ROLLBACK하면 B와 D가 둘 다 S 잠금을 얻습니다.
2. 두 세션이 각자 INSERT를 진행하려고 X 잠금을 요청합니다.
3. 서로의 S 잠금 때문에 어느 쪽도 X 잠금을 얻지 못해 데드락이 됩니다.

이번 실험에서 재현한 상황은 아니므로 데드락 사례로 분류하지 않습니다. 다만 같은 요청 키로 재시도가 몰리는 서비스라면, 1062뿐 아니라 데드락 오류(1213)도 대비해 둘 필요가 있습니다.

대기 제한 확대는 원인 해결이 아님

300초는 캡처 시간을 확보하기 위한 실험값이며 운영 권장값이 아닙니다. 대기 제한을 늘려도 A의 긴 트랜잭션이나 같은 키 충돌은 사라지지 않고, 오히려 요청 스레드와 커넥션 풀이 더 오래 묶입니다.

또 1205 시간 초과는 기본 설정(innodb_rollback_on_timeout = OFF)에서 문장만 되돌리고 트랜잭션은 남겨 둡니다. 따라서 애플리케이션은 1062와 마찬가지로 1205 이후에도 트랜잭션을 명시적으로 정리해야 합니다.

어떤 결과를 받았는지가 알려 주는 것

같은 유니크 키로 들어온 INSERT가 받는 결과는 선행 트랜잭션의 상태와 대기 시간에 따라 달라집니다. 결과를 보면 선행 트랜잭션에서 무슨 일이 있었는지 거꾸로 추정할 수 있습니다.

후행 INSERT의 결과	선행 트랜잭션에서 일어난 일	이번 실험
즉시 1062	같은 키가 이미 커밋되어 있었음	해당 없음
대기 후 1062	대기 중에 선행 트랜잭션이 COMMIT	REQ-COMMIT-001
대기 후 성공	대기 중에 선행 트랜잭션이 ROLLBACK	REQ-ROLLBACK-001
대기 후 1205	제한 시간 안에 선행 트랜잭션이 끝나지 않음	관찰하지 않음
1213 데드락	여러 세션이 같은 키를 기다리다 선행이 ROLLBACK	관찰하지 않음

제가 운영에서 겪은 중복 키 오류는 표의 첫 번째 경우였습니다. 기존 데이터를 지우지 않은 상태에서 같은 데이터를 다시 입력하면서 1062 오류가 발생했습니다. 이렇게 즉시 나는 1062는 이미 커밋된 데이터와 부딪힌 것이라 원인을 찾기가 비교적 쉽습니다. 반면 이번 실험처럼 한참 기다린 뒤에 나는 1062는 다른 트랜잭션이 같은 키를 입력하고 있었다는 뜻이므로, 막고 있던 트랜잭션까지 확인해야 합니다.

이번 실험에서는 네 가지 관찰을 차례로 연결해 미커밋 INSERT 충돌을 설명했습니다.

1. 세션 C의 조회 결과 0건
2. uk_request_key에서 S 잠금이 WAITING
3. A가 COMMIT한 뒤 B는 1062 오류
4. A가 ROLLBACK한 뒤 B의 행이 저장됨

운영에서 유니크 키 INSERT가 멈췄다면 일반 SELECT 결과로 판단을 끝내지 마세요. data_lock_waits로 막고 있는 트랜잭션부터 찾는 것이 출발점입니다.