[MySQL 장애] 트랜잭션 미종료로 인한 INSERT 대기 현상과 대응

팀원이 버그 보상으로 유저들에게 상품을 메일로 지급해야 하는 상황이 있었다.
대량의 유저에게 보상 메일을 보내는 배치 작업이었는데, 작업 중 에러가 발생했다.
환경은 Aurora MySQL이다.

문제는 예외 처리가 미흡해서 COMMIT도 ROLLBACK도 안 된 채 세션이 살아있었다는 점이다.
그 결과 메일 관련 테이블에 INSERT하는 다른 API들이 전부 lock wait 상태로 대기하다가 timeout으로 실패했다.

빠르게 상황을 파악한 뒤 innodb_trx로 미종료 트랜잭션을 찾아 rollback을 수행했고, 장애는 약 5분 만에 마무리됐다.

#1. 문제 상황

#관측된 증상

1. 보상 메일 발송 배치가 START TRANSACTION 후 INSERT … SELECT 수행
2. 다음 단계에서 에러 발생
3. 예외 처리 미흡으로 COMMIT/ROLLBACK이 실행되지 않음
4. 다른 트랜잭션들이 같은 테이블에 INSERT 시도 → 전부 대기하다가 timeout

#왜 SELECT는 되고 INSERT만 막혔나

일반 SELECT는 MVCC 스냅샷 읽기로 동작해서 락 요청 없이 진행된다.

반면 INSERT/UPDATE/DELETE는:

• 인덱스 엔트리 추가/변경
• FK가 있으면 부모키 검증

이 과정에서 미종료 트랜잭션이 보유 중인 잠금과 충돌하면 대기한다.

#2. 단순 INSERT는 왜 막히지 않았나

여기서 한 가지 의문이 있었다.

"Tx1이 INSERT 하고 커밋 안 했으면, Tx2 INSERT도 막히는 거 아냐?"

실제로 테스트해보면 막히지 않는다.

-- Tx1: INSERT 후 COMMIT 안 함
START TRANSACTION;
INSERT INTO inbox_mails (UserId, Note, CreatedAt) VALUES (10, 'tx1', NOW(6));
-- (커밋 안 함)
 
-- Tx2: 동일 테이블에 INSERT
INSERT INTO inbox_mails (UserId, Note, CreatedAt) VALUES (10, 'tx2', NOW(6));
-- 결과: 성공! 대기 없음

#이유

• Id가 AUTO_INCREMENT라서 Tx1과 Tx2는 서로 다른 PK 값을 받는다
• UserId 보조 인덱스가 UNIQUE가 아니라면 동일 UserId로 여러 row를 삽입할 수 있다
• InnoDB의 row-level locking은 충돌 여부가 인덱스 레코드 단위로 결정된다

"Tx1이 미커밋 INSERT를 했으니 Tx2 INSERT가 무조건 막힌다"는 가정은 InnoDB에서 기본적으로 성립하지 않는다.

#3. 이번 장애에서는 왜 막혔나

이번 장애에서는 단순 INSERT가 아니라 INSERT … SELECT가 미종료 상태로 남아있었다.

#재현된 상황

-- Tx1: INSERT … SELECT 후 에러로 미종료
START TRANSACTION;
INSERT INTO inbox_mails (UserId, Note, CreatedAt)
SELECT user_id, 'batch', NOW(6) FROM some_source_table;
 
-- 여기서 에러 발생 (예: 없는 테이블 참조)
INSERT INTO mail_rewards_does_not_exist (MailId) VALUES (1);
-- COMMIT/ROLLBACK 없이 세션 유지...
 
-- Tx2: 단순 INSERT 시도
INSERT INTO inbox_mails (UserId, Note, CreatedAt) VALUES (10, 'other', NOW(6));
-- 결과: lock wait → timeout

#data_locks 스냅샷에서 확인된 것

inbox_mails, PRIMARY, RECORD, "X, INSERT_INTENTION" (대기자)
inbox_mails, PRIMARY, RECORD, X (차단자)
inbox_mails, IX_inbox_mails_UserId, RECORD, X (추가로 잡힌 락)

Tx2를 막은 것은 AUTO_INCREMENT lock이 아니라, PRIMARY 인덱스에서의 INSERT_INTENTION vs X 충돌이다.

INSERT … SELECT는 SELECT 대상 범위에 따라 여러 레코드에 X 락을 잡을 수 있고, 이게 미종료 상태로 남으면 이후 단순 INSERT도 PK 삽입 단계에서 대기하게 된다.

#4. AUTO_INCREMENT 갭이 생기는 이유

장애 수습 후 데이터를 확인했더니 Id가 28 → 32로 건너뛰어 있었다.

• InnoDB에서 AUTO_INCREMENT 값은 INSERT 시점에 할당·선점된다
• 트랜잭션이 ROLLBACK 되어도 재사용되거나 복구되지 않는다
• 특히 INSERT … SELECT는 결과 row 수를 사전에 확정하기 어려워서 여러 개를 미리 확보(reserve)할 수 있다

AUTO_INCREMENT가 보장하는 것은 연속성이 아니라 유일성이다.

#5. InnoDB 락 구조 정리

#락 종류

#FK가 있으면 더 복잡해진다

INSERT INTO inbox_mails(UserId=10, ...)는 다음을 수행한다:

1. PK(클러스터 인덱스)에 새 레코드 추가
2. 보조 인덱스 IX_inbox_mails_UserId에 (UserId, PK) 엔트리 추가
3. FK 검증: users.Id=10이 존재하는지 확인

미종료 트랜잭션이 1~3 중 하나라도 잠금을 잡고 있으면, 다른 INSERT는 대기한다.

#6. 혼동 포인트 정리

"INSERT가 막혔다"라는 현상 하나에 두 가지 층이 섞여 있어서 혼란스러웠다.

이 둘은 다른 레이어의 문제다.

#7. 진단 방법 (MySQL 8.0)

#data_lock_waits

SELECT * FROM performance_schema.data_lock_waits;

누가 누구를 막는지 확인할 때 가장 먼저 조회한다.
blocking 쪽(held lock)이 어느 테이블/인덱스에 걸려 있는지 확인한다.

#data_locks

SELECT 
  OBJECT_NAME,
  INDEX_NAME,
  LOCK_TYPE,
  LOCK_MODE,
  LOCK_STATUS
FROM performance_schema.data_locks;

대기자가 있든 없든 현재 잡힌 락을 전부 보여준다.

#sys.innodb_lock_waits

SELECT * FROM sys.innodb_lock_waits;

락 대기를 요약해서 보여주는 뷰다.
빠르게 파악할 때 유용하다.

#innodb_trx

SELECT 
  trx_id,
  trx_state,
  trx_started,
  trx_query
FROM information_schema.innodb_trx;

장시간 열려 있는 트랜잭션이 차단의 원인인 경우가 많다.
장기 실행 트랜잭션을 추적해 kill과 롤백 모니터링까지 간 사례는 대용량 UPDATE로 인한 MySQL 장애 대응에 따로 정리했다.

#8. Timeout이 트랜잭션을 종료하지는 않는다

#9. 실무 결론

미커밋 INSERT 한 건이 다른 INSERT를 막는 일은 기본적으로 없다.
이번 장애가 성립한 조건은 INSERT … SELECT가 여러 레코드에 X 락을 잡은 채 미종료로 남았다는 것이고, 그래서 단순 INSERT까지 PRIMARY 삽입 단계에서 대기했다.
AUTO_INCREMENT 갭은 그 과정에서 남은 흔적일 뿐 비정상이 아니다.
결국 예방의 핵심은 배치가 에러로 끝나더라도 rollback이 반드시 실행되게 보장하고, 대량 작업은 트랜잭션을 나눠 청크 단위로 커밋하는 것이다.
진단은 data_locks, data_lock_waits, innodb_trx 세 가지로 충분했다.

#예방 체크리스트

#모니터링

• Aurora Performance Insights에서 synch/cond/innodb/row_lock_wait wait event 감시
• wait event 스파이크 시점에 data_lock_waits 즉시 조회하는 런북 준비