Deadlock 분석: LATEST DETECTED DEADLOCK을 읽고 재현하는 방법
MySQL InnoDB deadlock의 발생 조건과 희생자 선택을 이해하고 LATEST DETECTED DEADLOCK 보고서를 읽어 재현·예방하는 절차를 정리한다.
1. Deadlock은 단순한 lock wait가 아니다
두 트랜잭션이 서로 상대방의 lock 해제를 기다려 어느 쪽도 진행할 수 없는 상태를 deadlock이라고 한다. 일반적인 lock wait는 blocker가 COMMIT 또는 ROLLBACK하면 끝나지만, deadlock은 외부 개입이 없으면 대기 관계가 스스로 해소되지 않는다. InnoDB는 wait-for graph에서 순환을 발견하면 한 트랜잭션을 희생자로 선택해 rollback하고, 다른 트랜잭션이 계속 진행하도록 한다.
운영에서 보이는 대표 증상은 다음 오류다.
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction
이 오류는 서버 전체가 망가졌다는 뜻이 아니다. 동시성 제어가 교착 상태를 감지하고 안전하게 한쪽을 중단했다는 뜻이다. 그러나 같은 deadlock이 지속적으로 반복되면 transaction 설계, index, 접근 순서 또는 처리 단위에 구조적인 문제가 있다는 신호다. 애플리케이션이 1213을 처리하지 못하면 정상적인 동시성 충돌이 사용자 오류로 확대되고, 무제한 즉시 재시도는 부하와 충돌을 더 키울 수 있다.
이 글에서는 MySQL 8.0 이상을 기준으로 다음 질문에 답한다.
- 어떤 lock 관계가 실제 순환을 만들었는가?
LATEST DETECTED DEADLOCK에서 어느 SQL이 무엇을 보유하고 기다렸는가?- InnoDB는 왜 특정 트랜잭션을 rollback했는가?
- 운영 중인 대기와 이미 끝난 deadlock을 각각 어디서 관측하는가?
- 재현 결과를 application transaction 경계와 index 설계로 어떻게 연결하는가?
2. 발생 조건: wait-for graph의 순환
Deadlock을 이해할 때 SQL 실행 순서만 나열하는 것보다 트랜잭션을 정점, 대기 관계를 방향 간선으로 표현한 wait-for graph를 그리는 편이 정확하다.
sequenceDiagram
participant A as 트랜잭션 A
participant I as InnoDB lock manager
participant B as 트랜잭션 B
A->>I: id=1에 X record lock
I-->>A: GRANTED
B->>I: id=2에 X record lock
I-->>B: GRANTED
A->>I: id=2의 X lock 요청
I-->>A: B를 기다림
B->>I: id=1의 X lock 요청
I-->>B: A를 기다림
Note over A,B: A → B → A 순환 형성
I-->>B: 희생자 선택, ERROR 1213과 rollback
I-->>A: id=2 lock GRANTED, 실행 계속
이 예제의 핵심은 “두 세션이 같은 행을 갱신했다”가 아니라 다음 네 조건이 동시에 성립했다는 점이다.
- A가 B와 양립할 수 없는 lock을 보유한다.
- B도 A와 양립할 수 없는 lock을 보유한다.
- A가 B의 lock을 기다린다.
- B가 A의 lock을 기다려 순환이 닫힌다.
같은 행을 두 세션이 갱신해도 한쪽만 기다리고 blocker가 정상 종료할 수 있다면 그것은 lock wait이지 deadlock이 아니다. 반대로 서로 다른 행을 갱신하더라도 secondary index scan, range lock, foreign key 검사, unique key 중복 검사 때문에 예상하지 못한 순환이 생길 수 있다.
2.1 InnoDB가 감지하고 희생자를 고르는 방식
innodb_deadlock_detect=ON이면 InnoDB는 lock 대기가 생길 때 wait-for graph의 순환을 검사한다. 순환을 발견하면 모든 transaction을 멈추는 대신 하나를 rollback한다. 일반적으로 변경하거나 잠근 row가 적은, 즉 rollback 비용이 작다고 추정되는 transaction이 희생될 가능성이 높다. 이것은 업무 중요도를 이해한 선택이 아니다.
따라서 다음을 보장할 수 없다.
- 먼저 시작한 transaction이 항상 살아남는다.
- 나중에 요청한 SQL이 항상 희생된다.
- 읽기보다 쓰기가 우선한다.
- application에서 중요한 주문 transaction이 batch transaction보다 보호된다.
대규모 고경합 시스템에서는 deadlock detection 자체가 CPU 비용을 만들 수 있어 innodb_deadlock_detect=OFF와 짧은 innodb_lock_wait_timeout을 검토하는 특수한 운영도 있다. 그러나 이는 일반적인 해결책이 아니다. 감지를 끄면 순환이 즉시 해소되지 않고 timeout까지 자원을 점유하므로, 충분한 부하 검증과 application retry 설계 없이 적용해서는 안 된다.
3. 먼저 확인할 관측 객체와 설정
MySQL 8.0에서는 현재 lock과 대기를 performance_schema.data_locks, performance_schema.data_lock_waits에서 관측한다. 이미 감지되어 끝난 deadlock의 상세 보고서는 SHOW ENGINE INNODB STATUS의 LATEST DETECTED DEADLOCK에서 확인한다. 다음 쿼리는 대상 버전, 관측 테이블, 핵심 설정을 함께 점검한다.
SELECT VERSION() AS mysql_version;
SHOW TABLES FROM performance_schema LIKE 'data_lock%';
SELECT @@GLOBAL.innodb_deadlock_detect AS deadlock_detect,
@@GLOBAL.innodb_print_all_deadlocks AS print_all_deadlocks,
@@SESSION.innodb_lock_wait_timeout AS lock_wait_timeout;
실행 결과(MySQL 8.0.x):
mysql> SELECT VERSION() AS mysql_version;
+---------------+
| mysql_version |
+---------------+
| 8.0.46 |
+---------------+
1 row in set (0.00 sec)
mysql> SHOW TABLES FROM performance_schema LIKE 'data_lock%';
+-------------------------------------------+
| Tables_in_performance_schema (data_lock%) |
+-------------------------------------------+
| data_lock_waits |
| data_locks |
+-------------------------------------------+
2 rows in set (0.03 sec)
mysql> SELECT @@GLOBAL.innodb_deadlock_detect AS deadlock_detect,
-> @@GLOBAL.innodb_print_all_deadlocks AS print_all_deadlocks,
-> @@SESSION.innodb_lock_wait_timeout AS lock_wait_timeout;
+-----------------+---------------------+-------------------+
| deadlock_detect | print_all_deadlocks | lock_wait_timeout |
+-----------------+---------------------+-------------------+
| 1 | 0 | 50 |
+-----------------+---------------------+-------------------+
1 row in set (0.00 sec)
각 관측점의 수명은 서로 다르다.
| 관측점 | 무엇을 보여 주는가 | 중요한 한계 |
|---|---|---|
performance_schema.data_lock_waits |
현재 존재하는 요청자와 blocker의 관계 | deadlock이 해소되면 관련 행이 빠르게 사라짐 |
performance_schema.data_locks |
현재 보유·대기 중인 engine lock | 원인 SQL과 transaction 문맥을 별도로 연결해야 함 |
SHOW ENGINE INNODB STATUS |
가장 최근에 감지한 deadlock 상세 | LATEST 한 건만 유지되므로 다음 deadlock이 덮어씀 |
INNODB_METRICS.lock_deadlocks |
InnoDB가 집계한 감지 횟수 | SQL·테이블·lock 관계는 제공하지 않음 |
| MySQL error log | innodb_print_all_deadlocks=ON일 때 모든 보고서 |
로그량과 민감한 SQL 노출을 관리해야 함 |
information_schema.INNODB_LOCKS와 INNODB_LOCK_WAITS는 MySQL 8.0에서 제거된 구식 관측점이므로 최신 운영 쿼리의 기준으로 사용하지 않는다.
4. 재현: 반대 순서의 갱신으로 순환 만들기
다음 예제는 MySQL Event Scheduler를 별도 서버 세션처럼 사용해 실제 deadlock을 만든다. worker transaction은 id=2를 먼저 잠그고 id=1을 요청한다. 현재 client transaction은 id=1과 여러 행을 먼저 변경한 뒤 id=2를 요청한다. 변경량이 작은 worker가 희생되도록 유도했지만, 희생자 선택을 application 계약으로 간주해서는 안 된다.
이 예제는 폐기 가능한 학습·검증 인스턴스 전용이다. 운영 서버에서 Event Scheduler를 임의로 활성화하거나 업무 테이블에 적용하지 않는다.
SET GLOBAL event_scheduler = ON;
DROP EVENT IF EXISTS deadlock_note_worker;
DROP PROCEDURE IF EXISTS deadlock_note_worker_proc;
DROP TABLE IF EXISTS deadlock_note_demo;
CREATE TABLE deadlock_note_demo (
id INT NOT NULL PRIMARY KEY,
note VARCHAR(80) NOT NULL
) ENGINE = InnoDB;
INSERT INTO deadlock_note_demo (id, note)
WITH RECURSIVE seq AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM seq WHERE n < 40
)
SELECT n, CONCAT('seed-', n) FROM seq;
DELIMITER $$
CREATE PROCEDURE deadlock_note_worker_proc()
BEGIN
START TRANSACTION;
UPDATE deadlock_note_demo
SET note = 'worker-holds-id-2'
WHERE id = 2;
DO SLEEP(4);
UPDATE deadlock_note_demo
SET note = 'worker-needs-id-1'
WHERE id = 1;
COMMIT;
END$$
DELIMITER ;
CREATE EVENT deadlock_note_worker
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND
DO CALL deadlock_note_worker_proc();
SELECT SLEEP(2) AS wait_for_worker_lock;
START TRANSACTION;
UPDATE deadlock_note_demo
SET note = 'client-holds-id-1'
WHERE id = 1;
UPDATE deadlock_note_demo
SET note = CONCAT('client-weight-', id)
WHERE id BETWEEN 3 AND 40;
DO SLEEP(4);
UPDATE deadlock_note_demo
SET note = 'client-after-deadlock'
WHERE id = 2;
COMMIT;
SELECT id, note
FROM deadlock_note_demo
WHERE id IN (1, 2, 3)
ORDER BY id;
SELECT NAME, COUNT, STATUS
FROM information_schema.INNODB_METRICS
WHERE NAME = 'lock_deadlocks';
SELECT COUNT(*) AS remaining_lock_waits
FROM performance_schema.data_lock_waits;
DROP EVENT IF EXISTS deadlock_note_worker;
DROP PROCEDURE IF EXISTS deadlock_note_worker_proc;
DROP TABLE deadlock_note_demo;
실행 결과(MySQL 8.0.x):
다음은 준비·정리 문장과 반복되는 성공 메시지를 줄이고, transaction별 변경량과 deadlock 발생 증거를 중심으로 발췌한 실제 검증 결과다.
mysql> CREATE TABLE deadlock_note_demo (...);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO deadlock_note_demo (id, note)
-> WITH RECURSIVE seq AS (...)
-> SELECT n, CONCAT('seed-', n) FROM seq;
Query OK, 40 rows affected (0.01 sec)
Records: 40 Duplicates: 0 Warnings: 0
mysql> CREATE PROCEDURE deadlock_note_worker_proc() ...;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE EVENT deadlock_note_worker ...;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE deadlock_note_demo
-> SET note = 'client-holds-id-1'
-> WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE deadlock_note_demo
-> SET note = CONCAT('client-weight-', id)
-> WHERE id BETWEEN 3 AND 40;
Query OK, 38 rows affected (0.00 sec)
Rows matched: 38 Changed: 38 Warnings: 0
mysql> UPDATE deadlock_note_demo
-> SET note = 'client-after-deadlock'
-> WHERE id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT id, note
-> FROM deadlock_note_demo
-> WHERE id IN (1, 2, 3)
-> ORDER BY id;
+----+-----------------------+
| id | note |
+----+-----------------------+
| 1 | client-holds-id-1 |
| 2 | client-after-deadlock |
| 3 | client-weight-3 |
+----+-----------------------+
3 rows in set (0.00 sec)
mysql> SELECT NAME, COUNT, STATUS
-> FROM information_schema.INNODB_METRICS
-> WHERE NAME = 'lock_deadlocks';
+----------------+-------+---------+
| NAME | COUNT | STATUS |
+----------------+-------+---------+
| lock_deadlocks | 1 | enabled |
+----------------+-------+---------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) AS remaining_lock_waits
-> FROM performance_schema.data_lock_waits;
+----------------------+
| remaining_lock_waits |
+----------------------+
| 0 |
+----------------------+
1 row in set (0.00 sec)
mysql> DROP TABLE deadlock_note_demo;
Query OK, 0 rows affected (0.01 sec)
재현이 성공하면 INNODB_METRICS.lock_deadlocks가 증가하고, 희생된 worker의 id=2 변경은 rollback된다. 살아남은 client가 이어서 id=2를 변경하고 commit하므로 최종 값은 client-after-deadlock이다. 재현 직후 data_lock_waits가 0인 점도 중요하다. Performance Schema의 현재 상태만 조회하면 이미 끝난 deadlock의 관계는 보이지 않는다.
5. LATEST DETECTED DEADLOCK을 읽는 순서
상세 보고서는 다음 명령으로 확인한다. 출력 전체가 길고 서버의 다른 내부 상태도 포함하므로, 운영 문서나 incident ticket에는 LATEST DETECTED DEADLOCK 구간만 안전하게 발췌한다.
SHOW ENGINE INNODB STATUS;
아래는 위 재현에서 생성된 보고서의 핵심 구조다. transaction ID, thread ID, space/page 번호, 경과 시간은 실행마다 달라지는 관측값이다.
------------------------
LATEST DETECTED DEADLOCK
------------------------
2026-08-06 00:04:55 140691534005824
*** (1) TRANSACTION:
TRANSACTION 1823, ACTIVE 5 sec starting index read
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 13, query id 36 event_scheduler updating
UPDATE deadlock_note_demo
SET note = 'worker-needs-id-1'
WHERE id = 1
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS ... index PRIMARY of table `mysql_tech_note`.`deadlock_note_demo`
trx id 1823 lock_mode X locks rec but not gap
Record lock ... hex 80000002 ... worker-holds-id-2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index PRIMARY of table `mysql_tech_note`.`deadlock_note_demo`
trx id 1823 lock_mode X locks rec but not gap waiting
Record lock ... hex 80000001 ... client-holds-id-1
*** (2) TRANSACTION:
TRANSACTION 1824, ACTIVE 4 sec starting index read
LOCK WAIT 4 lock struct(s), heap size 1128, 41 row lock(s), undo log entries 39
MySQL thread id 12, query id 37 localhost root updating
UPDATE deadlock_note_demo
SET note = 'client-after-deadlock'
WHERE id = 2
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS ... index PRIMARY of table `mysql_tech_note`.`deadlock_note_demo`
trx id 1824 lock_mode X locks rec but not gap
Record lock ... hex 80000001 ... client-holds-id-1
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index PRIMARY of table `mysql_tech_note`.`deadlock_note_demo`
trx id 1824 lock_mode X locks rec but not gap waiting
Record lock ... hex 80000002 ... worker-holds-id-2
*** WE ROLL BACK TRANSACTION (1)
위 출력은 MySQL 8.0.46 검증 컨테이너에서 실제로 생성한 보고서에서 page 번호, OS thread handle, 물리 record의 일부 필드를 줄인 발췌본이다. hex 80000001과 80000002는 이 예제의 양수 INT primary key 1과 2에 대응한다. 운영 보고서에서는 schema/table/index와 업무 key를 확인하되, 물리 record에 포함된 문자열이나 SQL literal을 외부 문서에 그대로 노출하지 않는다.
보고서는 transaction 번호가 아니라 다음 순서로 읽어야 한다.
5.1 각 transaction의 현재 SQL과 상태를 찾는다
*** (1) TRANSACTION과 *** (2) TRANSACTION 아래에서 다음 정보를 표시한다.
ACTIVE ... starting index read: transaction 상태와 실행 단계mysql tables in use,locked: 사용·잠금 중인 table 수LOCK WAIT: 현재 lock을 기다리는 transaction인지 여부MySQL thread id: application connection과 연결할 수 있는 server thread ID- 바로 뒤 SQL text: deadlock 탐지 시점에 실행 중이던 문장
보고서의 SQL은 transaction 전체 업무를 보여 주지 않는다. 현재 충돌한 statement만 보이는 경우가 많으므로 application trace, transaction log, 앞선 statement history와 함께 읽어야 한다.
5.2 HOLDS THE LOCK(S)와 WAITING FOR THIS LOCK을 한 쌍으로 읽는다
각 transaction에서 보유 lock과 요청 lock을 따로 메모한다.
트랜잭션 1: PRIMARY(id=2) X lock 보유 → PRIMARY(id=1) X lock 대기
트랜잭션 2: PRIMARY(id=1) X lock 보유 → PRIMARY(id=2) X lock 대기
그다음 1 → 2 → 1 순환을 그린다. page 번호나 hex record를 먼저 해독하기보다 index 이름, lock mode, 보유/대기 방향을 우선 확인해야 한다.
index PRIMARY: 충돌이 발생한 indexlock_mode X locks rec but not gap: record에 대한 배타 lock이며 gap은 포함하지 않음waiting: 아직 획득하지 못한 요청heap no와 physical record: page 안의 record 위치와 key 값을 확인하는 단서
PRIMARY가 아닌 secondary index가 표시되면 해당 index key로 실제 row를 좁힌다. 하나의 UPDATE가 secondary index entry와 clustered record를 모두 잠글 수 있으므로, SQL의 WHERE 절만 보고 lock 대상을 단정하면 안 된다.
5.3 마지막 rollback 문장을 확인한다
보고서 끝의 *** WE ROLL BACK TRANSACTION (N)은 InnoDB가 선택한 희생자다. 여기서 rollback된 transaction과 application이 1213을 받은 connection을 연결한다. 희생자가 항상 보고서의 (1) 또는 (2)라고 외워서는 안 된다.
6. 실행 중 대기를 포착하는 진단 쿼리
Deadlock 감지 전의 일반 lock wait나 복잡한 순환 후보를 조사할 때는 다음처럼 요청자와 blocker를 연결한다. SQL text가 NULL일 수 있으며 instrumentation과 statement 수명에 따라 값이 달라질 수 있다.
SELECT w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx_id,
rt.PROCESSLIST_ID AS waiting_thread,
LEFT(rs.SQL_TEXT, 120) AS waiting_sql,
w.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx_id,
bt.PROCESSLIST_ID AS blocking_thread,
LEFT(bs.SQL_TEXT, 120) AS blocking_sql
FROM performance_schema.data_lock_waits AS w
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
LEFT JOIN performance_schema.events_statements_current AS rs
ON rs.THREAD_ID = w.REQUESTING_THREAD_ID
LEFT JOIN performance_schema.events_statements_current AS bs
ON bs.THREAD_ID = w.BLOCKING_THREAD_ID
ORDER BY waiting_trx_id, blocking_trx_id;
실행 결과(MySQL 8.0.x):
mysql> SELECT w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx_id,
-> rt.PROCESSLIST_ID AS waiting_thread,
-> LEFT(rs.SQL_TEXT, 120) AS waiting_sql,
-> w.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx_id,
-> bt.PROCESSLIST_ID AS blocking_thread,
-> LEFT(bs.SQL_TEXT, 120) AS blocking_sql
-> FROM performance_schema.data_lock_waits AS w
-> 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
-> LEFT JOIN performance_schema.events_statements_current AS rs
-> ON rs.THREAD_ID = w.REQUESTING_THREAD_ID
-> LEFT JOIN performance_schema.events_statements_current AS bs
-> ON bs.THREAD_ID = w.BLOCKING_THREAD_ID
-> ORDER BY waiting_trx_id, blocking_trx_id;
Empty set (0.01 sec)
이 쿼리는 현재 대기 관계를 보여 줄 뿐 deadlock history table은 아니다. 결과가 비었다고 deadlock이 발생하지 않았다고 결론 내리면 안 된다. 1213 발생 시각의 SHOW ENGINE INNODB STATUS, INNODB_METRICS.lock_deadlocks, error log, application trace를 함께 수집해야 한다.
실무에서는 data_locks도 결합해 OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA를 확인한다. 다만 high-contention 상황에서 진단 결과를 무제한 덤프하면 관측 비용과 분석 소음이 커진다. incident 시각, 대상 schema/table, thread ID로 범위를 좁히고 필요한 열만 보존한다.
7. 자주 발생하는 패턴과 수정 방향
7.1 같은 row 집합을 반대 순서로 갱신
가장 직접적인 패턴이다.
경로 A: account 10 → account 20
경로 B: account 20 → account 10
모든 코드 경로가 key를 같은 정렬 순서로 잠그도록 통일한다. 여러 ID를 처리한다면 application에서 정렬한 뒤 동일 순서로 SELECT ... FOR UPDATE 또는 UPDATE를 수행한다. 단, SQL optimizer의 실제 접근 순서가 의도와 같은지는 EXPLAIN과 재현으로 확인한다.
7.2 적절한 index가 없어 scan 범위가 커짐
조건에 맞는 index가 없으면 더 많은 record와 gap을 방문하고 잠금 수명도 길어진다. index를 추가하면 충돌 면적을 줄일 수 있지만, 쓰기 비용과 secondary index lock도 늘어난다. “deadlock이니 index 추가”가 아니라 다음을 검증한다.
- 실제 선택된 index와 access type
- locking read 또는 DML이 방문한 row 범위
- equality 조건인지 range 조건인지
- isolation level에 따른 gap/next-key lock 범위
- index 추가 후 write amplification과 실행 계획 변화
7.3 secondary index와 clustered index 접근이 교차
한 transaction은 secondary index에서 후보를 찾고 clustered record를 갱신하는 동안, 다른 transaction은 primary key로 먼저 record를 잠근 뒤 secondary index entry를 변경할 수 있다. 보고서에 서로 다른 index 이름이 나타나면 업무 row뿐 아니라 index entry 접근 순서를 그려야 한다.
7.4 Foreign key 검사와 unique key 충돌
child insert/update는 parent 존재 확인을 위한 lock을 얻을 수 있고, parent delete/update와 충돌할 수 있다. unique key 중복 검사도 insert 경합에서 예상하지 못한 대기를 만든다. 보고서의 table/index 이름을 기준으로 parent·child DML과 constraint를 함께 확인한다.
7.5 너무 큰 transaction
큰 transaction은 더 많은 lock을 오래 보유하고 rollback 비용도 크다. batch를 줄이면 충돌 면적과 복구 비용을 낮출 수 있다. 그러나 업무 원자성을 깨거나 commit 횟수와 redo/binlog 부하를 지나치게 늘릴 수 있으므로, 임의 분할이 아니라 재처리 가능 단위와 일관성 경계를 설계해야 한다.
8. 재시도는 필요하지만 무제한이면 안 된다
1213의 SQLSTATE는 40001이며 transaction 재시도를 전제로 한다. 중요한 점은 실패한 statement 하나가 아니라 transaction 전체를 처음부터 다시 실행하는 것이다. transaction 안의 앞선 변경도 희생자 rollback으로 취소되기 때문이다.
안전한 재시도 정책에는 다음 요소가 필요하다.
- 오류 코드 1213과 lock wait timeout 1205를 구분한다.
- transaction 전체 작업을 다시 생성한다.
- 최대 시도 횟수를 제한한다.
- exponential backoff와 jitter를 적용해 동시 재충돌을 줄인다.
- 외부 API 호출, message 발행, 결제 요청 같은 side effect를 idempotent하게 만든다.
- 최종 실패를 숨기지 않고 metric·trace·log로 남긴다.
즉시 같은 순서로 재시도하는 worker 수가 많으면 모든 worker가 다시 동시에 충돌할 수 있다. retry는 설계상의 deadlock을 가리는 수단이 아니라 일시적 경쟁을 흡수하는 안전장치다.
9. innodb_print_all_deadlocks와 로그 운영
SHOW ENGINE INNODB STATUS는 가장 최근 한 건만 보존하므로 빈도가 높은 장애에서는 이전 사례가 빠르게 덮어써진다. 조사 기간에 innodb_print_all_deadlocks=ON을 사용하면 감지된 모든 deadlock 보고서가 MySQL error log에 기록된다.
운영 적용 전에는 다음을 검토한다.
- deadlock 빈도가 높을 때 error log 증가량과 보존 기간
- SQL text에 개인 정보나 민감한 literal이 포함될 가능성
- 중앙 로그 수집 비용과 접근 권한
- 설정을 켜고 끄는 변경 절차와 종료 조건
- timestamp, instance, application trace ID를 연결하는 방법
설정을 켠 채 방치하기보다 조사 목적, 관찰 기간, 수집량 상한을 정한다. 근본 원인 분석에는 대표 report만 보존하는 것이 아니라 signature를 정규화해 table/index/SQL 경로별 빈도를 집계하는 방법이 유용하다.
10. Aurora MySQL에서의 운영 해석
Aurora MySQL의 SQL 계층도 InnoDB 호환 locking과 deadlock detection을 사용하므로 wait-for graph, 1213 처리, transaction 전체 재시도라는 원리는 같다. 분산 storage가 row-level deadlock을 제거하지는 않는다.
다만 운영 경로는 다음 차이를 고려한다.
- 쓰기 deadlock은 writer에서 발생하므로 writer instance의 error log, Performance Schema, Database Insights/Performance Insights를 우선 확인한다.
innodb_print_all_deadlocks같은 설정은 사용하는 Aurora MySQL 버전과 DB cluster/instance parameter group의 지원·적용 범위를 확인한다.- failover는 connection과 transaction을 중단하지만 deadlock의 정상적인 해결책이 아니다. retry 폭증과 중복 side effect를 만들 수 있다.
- reader에서 수행한 일반 read와 writer의 locking read를 같은 실행 경로로 해석하지 않는다.
SELECT ... FOR UPDATE와 DML은 writer endpoint를 기준으로 조사한다. - 관리형 error log의 CloudWatch export, 보존 기간, 민감 정보 통제를 함께 설계한다.
Aurora에서 Performance Insights의 wait 차원은 경합 시점을 찾는 출발점이다. 그러나 wait category만으로 순환의 두 SQL과 index를 확정할 수 없으므로 InnoDB report와 application transaction trace를 결합한다.
11. 장애 분석 절차
즉시 수집
- 가능한 한 빨리
SHOW ENGINE INNODB STATUS -
INNODB_METRICS의lock_deadlocks - 현재 대기가 남아 있다면
data_lock_waits,data_locks,threads,INNODB_TRX
보고서 해석
-
index,lock_mode,waiting
수정과 검증
12. 흔한 오해와 주의사항
12.1 deadlock은 항상 application 버그다
동시 transaction에서 deadlock 가능성을 완전히 제거하기 어려운 경우도 있다. 중요한 것은 빈도를 낮추는 transaction/index 설계와 안전한 재시도다. 다만 지속적으로 같은 signature가 반복되면 “정상 현상”이라는 말로 덮지 말고 구조적 원인을 수정해야 한다.
12.2 보고서에 나온 마지막 SQL만 고치면 된다
현재 SQL이 기다리는 lock은 같은 transaction의 앞선 SQL이 보유한 lock과 순환한다. 마지막 두 SQL만 떼어 보면 원인을 놓칠 수 있다. transaction 전체 statement 순서를 재구성해야 한다.
12.3 isolation level을 낮추면 모두 해결된다
READ COMMITTED는 일부 gap/next-key lock 범위를 줄여 특정 deadlock 빈도를 낮출 수 있지만 record lock, unique/FK 검사, 반대 순서 갱신 deadlock은 여전히 발생한다. 일관성 의미와 실행 계획까지 검증하지 않고 isolation level을 바꾸면 안 된다.
12.4 innodb_lock_wait_timeout을 늘리면 해결된다
deadlock detection이 켜져 있으면 순환은 timeout을 기다리지 않고 희생자를 선택한다. timeout 증가는 일반 lock wait를 더 오래 유지할 수 있을 뿐, 접근 순서의 순환을 제거하지 않는다.
12.5 KILL로 blocker를 제거하면 분석이 끝난다
이미 감지된 deadlock은 InnoDB가 한쪽을 rollback해 해소한다. 반복 원인은 application 경로와 schema에 남아 있다. 긴급 복구와 재발 방지 분석을 분리해야 한다.
13. 결론
Deadlock 분석의 핵심은 오류가 난 SQL 한 줄을 보는 것이 아니라, 두 transaction이 무엇을 보유한 채 무엇을 기다렸는지 wait-for graph로 복원하는 것이다. performance_schema.data_lock_waits는 현재 대기를, LATEST DETECTED DEADLOCK은 가장 최근에 끝난 순환의 사후 증거를 제공한다. 두 관측점의 수명 차이를 이해해야 “지금은 대기가 없으니 문제도 없었다”는 잘못된 결론을 피할 수 있다.
보고서에서는 transaction별 현재 SQL, HOLDS THE LOCK(S), WAITING FOR THIS LOCK, index와 lock mode, 마지막 희생자 선택을 순서대로 읽는다. 그 결과를 application의 전체 transaction 경계, 실제 index 접근, 일관된 잠금 순서, 제한된 재시도 정책으로 연결해야 분석이 운영 개선으로 완성된다.
다음 단계에서는 deadlock 보고서에서 record의 hex key와 secondary index 정보를 실제 업무 row로 역추적하고, 반복 signature를 자동 분류하는 방법을 더 깊게 다룰 수 있다.