데이터베이스가 트랜잭션 연결 시 오랫동안 유휴 상태로 실행됨 - Amazon Aurora

데이터베이스가 트랜잭션 연결 시 오랫동안 유휴 상태로 실행됨

데이터베이스에 대한 연결이 1,800초 이상 idle in transaction 상태로 지속되었습니다.

지원되는 엔진 버전

이 인사이트 정보는 Aurora PostgreSQL의 모든 버전에서 지원됩니다.

컨텍스트

해당 idle in transaction 상태의 트랜잭션은 다른 쿼리를 차단하는 잠금을 유지할 수 있습니다. 또한 VACUUM(autovacuum 포함)이 잘못된 행을 정리하여 인덱스 또는 테이블 팽창 또는 트랜잭션 ID 랩어라운드로 이어지는 것을 방지할 수 있습니다.

이 문제의 잠재적 원인

BEGIN 또는 START TRANSACTION을 사용하여 대화형 세션에서 시작된 트랜잭션이 COMMIT, ROLLBACK 또는 END 명령을 사용하여 종료되지 않았습니다. 이 경우 트랜잭션이 idle in transaction 상태로 이동합니다.

작업

pg_stat_activity 쿼리를 통해 유휴 트랜잭션을 찾을 수 있습니다.

SQL 클라이언트에서 다음 쿼리를 실행하여 idle in transaction 상태의 모든 연결을 나열하고 기간별로 정렬합니다.

SELECT now() - state_change as idle_in_transaction_duration, now() - xact_start as xact_duration,* FROM pg_stat_activity WHERE state = 'idle in transaction' AND xact_start is not null ORDER BY 1 DESC;

인사이트의 원인에 따라 다른 조치를 취할 것을 권장합니다.

트랜잭션 종료

BEGIN 또는 START TRANSACTION을 사용하여 대화형 세션에서 트랜잭션을 시작하면 트랜잭션이 idle in transaction 상태로 이동합니다. COMMIT, ROLLBACK, END 명령을 실행하여 트랜잭션을 종료하거나 연결을 완전히 끊어 트랜잭션을 롤백할 때까지 이 상태로 유지됩니다.

연결 종료

다음 쿼리를 사용하여 유휴 트랜잭션과의 연결을 종료합니다.

SELECT pg_terminate_backend(pid);

pid는 연결의 프로세스 ID입니다.

idle_in_transaction_session_timeout 파라미터 구성

파라미터 그룹에서 idle_in_transaction_session_timeout 파라미터를 구성합니다. 이 파라미터를 구성하면 트랜잭션의 오랜 유휴 상태를 종료하기 위해 수동 개입이 필요하지 않다는 이점이 있습니다. 이 파라미터에 대한 자세한 내용은 PostgreSQL 설명서를 참조하세요.

트랜잭션이 idle_in_transaction 상태로 지정된 시간보다 오래 열려 있는 경우 연결이 종료된 후 PostgreSQL 로그 파일에 다음 메시지가 보고됩니다.

FATAL: terminating connection due to idle in transaction timeout

AUTOCOMMIT 상태 확인

AUTOCOMMIT은 기본적으로 활성화되어 있습니다. 하지만 클라이언트에서 실수로 비화성화된 경우에는 반드시 다시 활성화해야 합니다.

  • psql 클라이언트에서 다음 명령을 실행합니다.

    postgres=> \set AUTOCOMMIT on
  • pgadmin에서 아래쪽 화살표에서 AUTOCOMMIT 옵션을 선택하여 활성화합니다.

    pgadmin에서 AUTOCOMMIT을 선택하여 활성화합니다.

애플리케이션 코드에서 트랜잭션 로직 확인

애플리케이션 로직에 문제가 있는지 조사합니다. 다음 조치를 고려해 보세요.

  • JDBC 자동 커밋이 애플리케이션에서 참으로 설정되어 있는지 확인합니다. 또한 코드에 명시적 COMMIT 명령을 사용하는 것도 고려해 보세요.

  • 오류 처리 로직을 확인하여 오류 발생 후 트랜잭션이 종료되는지 확인합니다.

  • 트랜잭션이 열려 있는 동안 애플리케이션이 쿼리에서 반환된 행을 처리하는 데 시간이 오래 걸리는지 확인합니다. 그렇다면 행을 처리하기 전에 트랜잭션을 닫도록 애플리케이션을 코딩하는 것을 고려해 보세요.

  • 트랜잭션에 장기 실행 작업이 많이 포함되어 있는지 확인합니다. 그렇다면 단일 트랜잭션을 여러 트랜잭션으로 나누세요.

관련 지표

이 인사이트와 관련된 PI 지표는 다음과 같습니다.

  • idle_in_transaction_count - idle in transaction 상태에 있는 세션 수입니다.

  • idle_in_transaction_max_time - idle in transaction 상태에서 가장 오래 실행되는 트랜잭션의 지속 시간입니다.