DBAtlas · 데이터베이스 운영 아틀라스

장기 실행 / 열린 트랜잭션

장기 실행 중이거나 열린 트랜잭션이 락, undo, bloat를 축적하고 있다. 문제의 트랜잭션을 찾아 유형(idle in transaction, 긴 배치, 영향)을 파악하고 안전하게 해결한다.

가장 오래된 열린 트랜잭션은 무엇인가?

열린 트랜잭션을 시작 시각 순으로 조회한다. 가장 오래된 것이 대개 문제의 원인이다. 상태를 확인한다: 실제로 실행 중인가, 아니면 커밋을 기다리는 'idle in transaction'인가?

SELECT s.sid, s.serial#, s.username, s.status, t.start_time, t.used_ublk*8/1024 AS undo_mb
FROM v$transaction t JOIN v$session s ON s.taddr=t.addr
ORDER BY t.start_time;
  • idle in transaction(커밋을 잊고 유휴 상태)
  • 활성 상태이지만 매우 오래 실행되는 배치/DML
  • 트랜잭션이 계속되는 동안 락/bloat/undo가 축적됨

idle in transaction은 왜 발생하는가?

세션이 트랜잭션을 열었지만 쿼리를 실행하지 않고 유휴 상태로 있다. 즉 commit/rollback을 잊었다. 이로 인해 락과 undo가 불필요하게 유지된다. 애플리케이션 수정으로 충분한가, 아니면 긴급히 종료해야 하는가?

SELECT pid, now()-state_change AS idle_age, left(query,80) AS last_query
FROM pg_stat_activity WHERE state='idle in transaction' ORDER BY state_change;
  • 애플리케이션/커넥션 풀이 커밋하지 않음(영구 해결)
  • 지금 바로 종료해야 함

활성 상태의 긴 트랜잭션은 무엇을 하고 있는가?

세션은 실제로 실행 중(active)이다. 단일 거대 DML/보고서인가, 아니면 다른 세션을 기다리게 해서 긴급히 끊어야 하는 블로커인가?

SELECT s.sid, s.serial#, sq.sql_text, s.last_call_et AS running_secs
FROM v$session s JOIN v$sql sq ON sq.sql_id=s.sql_id
WHERE s.status='ACTIVE' AND s.type='USER' ORDER BY s.last_call_et DESC;
  • 단일 거대 DML/보고서, 분할이 필요함
  • 다른 세션을 기다리게 함, 긴급히 종료

긴 트랜잭션은 무엇을 축적하고 있는가?

긴 트랜잭션의 부작용을 파악한다: 주로 다른 세션을 기다리게 하는 락 적체인가, 아니면 오래된 행 버전을 유지해 정리(VACUUM/undo)를 막아 생기는 bloat인가?

SELECT pid, pg_blocking_pids(pid) AS blocked_by FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid))>0;
SELECT max(age(backend_xmin)) AS max_xmin_age FROM pg_stat_activity WHERE backend_xmin IS NOT NULL;
  • 락 적체로 다른 세션이 대기 중
  • 오래된 버전 누적이 정리를 막음(undo/bloat)

근본 원인: 애플리케이션이 커밋하지 않음(idle in transaction)

커넥션 풀이나 애플리케이션이 트랜잭션을 연 뒤 commit/rollback 없이 연결을 유휴 상태로 유지한다. 이로 인해 락과 undo가 불필요하게 유지된다.

해결: 문제 세션을 안전하게 종료

블로킹/장기 세션이 다른 세션을 기다리게 한다면 통제된 방식으로 종료해야 한다. 종료는 롤백을 유발하며 공간/시간이 필요할 수 있으므로 주의한다.

근본 원인: 단일 거대 DML/보고서

단일 트랜잭션에서 수백만 행을 처리하는 DML이나 매우 긴 보고서는 실행 시간 내내 락과 undo/bloat를 축적한다.

근본 원인: 락 적체 대기열

보유한 락 때문에 긴 트랜잭션 뒤에 대기열이 생겼다. 다수의 세션이 같은 리소스를 기다리고 있다.

근본 원인: 오래된 버전 누적(undo/bloat)

장기 실행 트랜잭션이 그보다 앞선 오래된 행 버전의 정리를 막는다. Postgres에서는 테이블/인덱스 bloat로, Oracle에서는 undo 압박으로 나타난다.

DBAtlas에서 열기 →