PostgreSQL의 idle in transaction이란?

PostgreSQL을 사용하다 보면 간혹 세션의 상태가 idle in transaction 인 경우를 발견하였다. 이번 글에서는 이 상태가 무엇인지, 그리고 어떤 경우에 이 상태가 되는지 정리하고자 한다.

DB에서의 세션이란?

세션이란 단위는 DB에 접속하는 클라이언트의 연결 그 자체를 말한다. 여기서 말하는 클라이언트의 종류는 다음과 같다.

• DBeaver 같은 SQL 도구
• 우리가 쓰는 서비스의 백엔드 프레임워크 등

세션은 한 번 열리면 클라이언트 쪽에서 끊지 않는 한 살아 있다.

PostgreSQL에서는 이 세션 정보를 다음의 쿼리로 확인할 수 있다.

sql
SELECT * FROM pg_stat_activity;

pg_stat_activity은 DB 서버에 접속한 클라이언트 프로세스를 확인할 수 있는 뷰이며, 자세한 컬럼 정보는 여기에서 확인 가능하다.

이 중에서 이번 글에서 특히 다룰 컬럼은 state 이다.

세션의 상태?

세션이 하나의 열린 통로라고 하자. 그러면 클라이언트로부터 쿼리를 받아 DB에서는 이를 처리한다. 이런 쿼리 작업을 여러 번 보낼 때, DB서버에서 하나로 묶어 전부 반영하거나 전부 취소하는 단위를 트랜잭션이라고 한다. 트랜잭션은 클라이언트가 날린 작업을 수행하면서 생겼다 사라졌다 한다.

세션은 이렇게 작업을 하는 데 있어서 상태를 가질 수 있다. 상태는 총 4가지 경우로 나뉜다.

active 상태는 세션에서 쿼리를 처리하고 있는 것을 의미하며, 나머지 idle 과 관계있는 상태는 DB 서버가 클라이언트의 다음 명령을 기다리고 있다는 중이라는 것을 뜻한다.

idle in transaction이 되는 건 언제인가?

DB의 쿼리는 BEGIN 예약어를 시작으로 COMMIT 예약어가 날아가기 전까지 계속 트랜잭션을 살려둘 수 있다.

sql
BEGIN;
SELECT * FROM my_table1;
SELECT * FROM my_table2;
SELECT * FROM my_table3;
COMMIT; -- 이 명령어 이전까지는 한 트랜잭션 안에서 쿼리를 처리

반면에 commit이 되지 않은 경우에는 idle in transaction 상태가 될 수 있다.

sql
BEGIN;
SELECT * FROM my_table1;
SELECT * FROM my_table2;
SELECT * FROM my_table3;
-- 세션 입장에서는 COMMIT이 되지 않으므로 계속 다음 쿼리를 기다리게 됨

aborted는 무엇인가?

트랜잭션 안에서 쿼리 자체에 에러가 날 때를 의미한다. 다음의 쿼리를 보면 이해할 수 있다.

sql
BEGIN;
SELECT 1/0;  -- ERROR: division by zero가 출력된다.

이 상태가 되면 롤백하기 전까지 이후에 보내는 쿼리가 모두 거부된다. 따라서 클라이언트에서 ROLLBACK 을 해야 빠져나올 수 있으며, 해당 트랜잭션에서 받은 모든 쿼리가 취소되며 트랜잭션 시작 시점으로 돌아간다.

백엔드 개발 실무에서

만약 idle in transaction 상태를 자주 마주하게 된다면, 해당 쿼리를 실행하는 측에서 commit을 지속적으로 안 하고 있는 건 아닌지 확인할 필요가 있다.

백엔드 개발 실무에서는 다음과 같은 상황일 때 이 상태를 마주쳤다.

• raw query를 사용했는데 commit 명령어로 마무리 짓지 않았을 때
• sqlalchemy를 사용했는데 teardown에서 commit(db.session.commit()) 명령어를 작성하지 않았을 때

자동으로 커밋이 되는 기능인 autocommit에 익숙했던 터라, 위의 상황에선 commit을 놓쳤던 사례였다. 이럴 경우에는 commit을 명시적으로 하는 편이 좋다.

python
from sqlalchemy import text

try:
    db.session.execute(text("SELECT * FROM my_table1"))
    db.session.execute(text("SELECT * FROM my_table2"))
    db.session.commit()  # commit은 try 절 내에 작성
except Exception:
    db.session.rollback()  # rollback은 except절 내에 작성
    raise