PostgreSQL에서 idle vs idle in transaction 상태 차이

PostgreSQL에서 idle vs idle in transaction 상태 차이

PostgreSQL의 pg_stat_activity에서 세션의 상태(state)는 다음과 같이 나타날 수 있습니다.

상태	의미	잠금(Lock 가능성)	주요 원인
idle	현재 실행 중인 쿼리가 없고, 단순히 연결이 유지됨	❌ 없음	애플리케이션이 DB 연결을 유지하고 있지만, 아무 작업도 하지 않는 경우
idle in transaction	트랜잭션이 시작되었지만 COMMIT 또는 ROLLBACK이 수행되지 않고 대기 중	✅ 있음	BEGIN; 실행 후 쿼리를 실행했지만, COMMIT; 또는 ROLLBACK;을 하지 않음

---

1️⃣ idle 상태

📌 의미

• 현재 세션이 DB에 연결되어 있지만 아무런 작업도 수행하지 않는 상태.
• SELECT, UPDATE, INSERT 등의 쿼리가 실행된 후 종료되었고, 트랜잭션도 닫혀 있음.
• 단순히 연결이 열려 있는 상태일 뿐이므로 다른 트랜잭션에 영향을 주지 않음.

🔍 예제

SELECT * FROM users WHERE id = 1;

• 이 쿼리가 실행된 후 완료되면, 해당 세션의 상태는 idle이 됨.

✅ 해결 필요 여부

• 보통 문제 없음.
• 그러나 너무 많은 idle 상태의 세션이 유지되면 불필요한 연결을 차지할 수 있으므로, 커넥션 풀을 사용하거나 연결을 주기적으로 닫는 것이 좋음.

---

2️⃣ idle in transaction 상태

📌 의미

• 트랜잭션이 열려 있지만, 아무런 작업도 수행하지 않는 상태.
• BEGIN;을 실행한 후 COMMIT; 또는 ROLLBACK;을 하지 않으면 idle in transaction 상태가 유지됨.
• 이 상태가 오래 지속되면 다른 트랜잭션을 블로킹할 가능성이 있음.

🔍 예제

BEGIN;  -- 트랜잭션 시작

UPDATE users SET balance = balance - 100 WHERE id = 1;

-- ⚠️ 여기서 COMMIT 또는 ROLLBACK을 하지 않으면 `idle in transaction` 상태가 됨

• 위 상태에서 트랜잭션을 종료하지 않으면, 해당 세션은 잠금을 유지한 채 idle in transaction 상태가 됨.
• 이후 다른 세션이 같은 테이블의 데이터를 변경하려 하면 대기 상태(Blocking)가 발생할 수 있음.

⚠️ 문제점

• idle in transaction 상태가 오래 지속되면, 해당 트랜잭션이 잠금(Lock)을 유지할 가능성이 높음.
• 다른 트랜잭션이 같은 자원(테이블, 행)을 수정하려고 할 경우 대기 상태(Blocking) 발생.
• 장기간 유지되면 Deadlock(교착 상태) 또는 과도한 리소스 점유 문제가 발생할 수 있음.

✅ 해결 방법

1. 현재 idle in transaction  상태 확인

SELECT pid, usename, state, query, age(clock_timestamp(), query_start)
FROM pg_stat_activity
WHERE state = 'idle in transaction';

• age(clock_timestamp(), query_start) 값이 크다면, 오래된 트랜잭션이므로 문제가 될 수 있음.

2. 문제 트랜잭션 종료 (pg_terminate_backend)

SELECT pg_terminate_backend(12345); -- 예시: PID 12345 강제 종료

• 주의: pg_terminate_backend는 해당 세션을 강제로 종료하므로, 데이터 손실 가능성이 있음.
• 안전한 방법은 ROLLBACK;을 수행하는 것.

3. 애플리케이션 코드 수정

• 트랜잭션을 열었으면 반드시 COMMIT 또는 ROLLBACK을 호출하도록 코드 수정.
• 예제:

BEGIN;
UPDATE users SET balance = balance - 100 WHERE id = 1;
COMMIT;  -- ✅ 트랜잭션 종료 필수

4. PostgreSQL 설정 변경 (idle_in_transaction_session_timeout)

• PostgreSQL에서는 idle in transaction 상태의 세션을 일정 시간 후 자동 종료하도록 설정 가능.

ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';
SELECT pg_reload_conf(); -- 설정 적용

• 위 설정을 하면 10분 이상 idle in transaction 상태인 세션이 자동 종료됨.

5. 적용범위

 

1️⃣ 현재 설정 값 확인

SHOW idle_in_transaction_session_timeout;

2️⃣ 특정 데이터베이스에만 설정

ALTER DATABASE mydb SET idle_in_transaction_session_timeout = '5min';

• mydb 데이터베이스에서만 적용됨.

3️⃣ 전체 PostgreSQL 서버에 설정 (모든 DB에 적용)

ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf(); -- 변경 사항 적용

• PostgreSQL 인스턴스 전체(DB 전체)에 적용됨).

---

✅ idle vs idle in transaction 요약

상태	의미	잠금 발생 가능성	해결 필요 여부
idle	단순히 연결이 유지됨	❌ 없음	보통 문제 없음
idle in transaction	트랜잭션이 열려 있지만 실행 중인 쿼리가 없음	✅ 있음 (잠금 가능)	오래 지속되면 해결 필요

 

📌 idle in transaction 상태가 오래 유지되면, 반드시 트랜잭션을 COMMIT 또는 ROLLBACK 해야 합니다! 🚀