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은 인덱스 레코드 단위 충돌 여부가 결정됨

3. 그런데 왜 이번엔 막혔나? INSERT … SELECT의 차이

이번 장애에서는 단순 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)

7.1 누가 누구를 막는지: data_lock_waits

SELECT * FROM performance_schema.data_lock_waits;

blocking 쪽(held lock)이 어느 테이블/인덱스에 걸려 있는지 확인한다.

7.2 현재 잡힌 락 전체: data_locks

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

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

7.3 요약 뷰: sys.innodb_lock_waits

SELECT * FROM sys.innodb_lock_waits;

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

7.4 미종료 트랜잭션 찾기: innodb_trx

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

장시간 열려있는 트랜잭션이 범인이다.

8. 주의: Timeout ≠ 트랜잭션 종료

9. 실무 결론

1. "미커밋 INSERT 1건"만으로는 Tx2 INSERT가 막히지 않는 것이 정상
2. INSERT … SELECT가 미종료로 남으면 PRIMARY 측 잠금 때문에 단순 INSERT도 대기 가능
3. AUTO_INCREMENT 갭은 정상 동작 - 연속성 보장 X, 유일성만 보장
4. 배치는 에러 시 rollback 보장 + 트랜잭션 분리/청크 커밋이 핵심

예방 체크리스트

모니터링

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

마무리

핵심은 이거다: 트랜잭션은 반드시 닫아라.

배치든 API든 예외가 터지면 반드시 rollback. 특히 INSERT … SELECT는 생각보다 넓은 범위의 락을 잡을 수 있다. 진단할 땐 data_locks, data_lock_waits, innodb_trx 세 가지만 기억하면 된다.