Mariadb 시퀀스 채번 지연

🔍 MariaDB 10.6 OLTP 환경 - 시퀀스 채번 지연 문제 분석 정리

1️⃣ SHOW ENGINE INNODB STATUS\G 분석 방법

1. TRANSACTIONS 섹션
   • LOCK WAIT가 있으면 해당 트랜잭션이 다른 트랜잭션을 기다리고 있음.
   • ACTIVE N sec → 실행 시간이 길면 지연 가능성이 높음.
   • undo log entries 값이 크면 롤백 시 영향을 미칠 수 있음.
2. WAITING FOR LOCKS 섹션
   • lock_mode X waiting → 해당 행이 잠겨 있고, 다른 트랜잭션이 대기 중.
   • table test.sequence_table → 어느 테이블에서 잠금이 발생하는지 확인.
3. LATEST DETECTED DEADLOCK 섹션
   • 데드락 발생 시 충돌한 트랜잭션이 표시됨.
   • TRANSACTION ID를 확인하여 원인 분석 가능.

---

2️⃣ SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%'; 해석

✅ 현재 조회된 값 분석

변수값의미

Innodb_row_lock_current_waits	0	현재 대기 중인 트랜잭션 없음
Innodb_row_lock_time	1,836,770	총 잠금 대기 시간 (ms)
Innodb_row_lock_time_avg	15,834	평균 대기 시간 (ms) (약 15초)
Innodb_row_lock_time_max	1,200,000	최대 대기 시간 (ms) (20분)
Innodb_row_lock_waits	116	총 116번의 잠금 충돌 발생

🚨 문제점

• 트랜잭션이 평균 15초 이상 대기하고 있음.
• 최대 20분 동안 대기한 트랜잭션이 있음.
• 116번의 충돌 발생 → 경쟁이 심한 테이블일 가능성 높음.

🔧 해결책

1. SHOW ENGINE INNODB STATUS\G 실행하여 대기하는 트랜잭션 확인.
2. innodb_lock_wait_timeout을 120초에서 30초 이하로 줄여서 테스트.
3. INSERT ... ON DUPLICATE KEY UPDATE 방식 최적화 (필요 시 REPLACE INTO 또는 UUID 사용 고려).

---

3️⃣ innodb_lock_wait_timeout을 줄이면?

• 현재 값: 120초 → 대기 시간이 길어 트랜잭션이 지연됨.
• 예를 들어 30초로 설정하면:
  • 30초 동안 잠금을 얻지 못한 트랜잭션은 자동 롤백됨.
  • 롤백된 트랜잭션이 다시 시도할 수 있도록 애플리케이션 로직 필요.

🚨 세션을 새로 맺는 것이 아니라, 대기 시간이 초과된 트랜잭션이 롤백됨.

---

4️⃣ MariaDB는 AUTO COMMIT인가?

✅ 기본적으로 AUTO COMMIT이 활성화되어 있음 (autocommit = 1)

• 명시적으로 BEGIN; 또는 START TRANSACTION;을 사용하지 않으면 자동 커밋됨.
• 하지만, 다음과 같은 경우 트랜잭션이 롤백될 수 있음:
  1. LOCK WAIT TIMEOUT 초과 → 강제 롤백됨.
  2. 데드락 감지 → 자동 롤백됨.
  3. 애플리케이션에서 명시적으로 ROLLBACK; 실행.

🚨 INSERT 후에도 롤백될 수 있는 이유?

• INSERT ... ON DUPLICATE KEY UPDATE가 실행되었지만, 다른 트랜잭션과 충돌하여 대기.
• innodb_lock_wait_timeout 초과 또는 데드락 발생 시 자동 롤백됨.
• SHOW ENGINE INNODB STATUS\G에서 "ROLLING BACK" 메시지 확인 가능.

---

✅ 추천 해결 방법

1. 대기 중인 트랜잭션 분석
   • SHOW ENGINE INNODB STATUS\G 실행 후, LOCK WAIT 상태 확인.
   • SHOW PROCESSLIST; 실행하여 대기 중인 쿼리 확인.
2. innodb_lock_wait_timeout 조정
   • 현재 120초 → 30초로 조정 후 테스트.
3. INSERT 방식 개선
   • INSERT ... ON DUPLICATE KEY UPDATE 대신 REPLACE INTO 또는 UUID 방식 검토.
4. 트랜잭션 길이 최소화
   • BEGIN ... COMMIT 사용 시, 가능한 한 빨리 커밋하도록 개선.
5. 경합이 심한 테이블 분리 또는 샤딩 고려
   • 특정 테이블이 경합이 심하면, 분리하여 부하를 줄이는 것도 방법.