[PostgreSQL] 현재 실행 중인 transaction 조회하여 lock 상태 확인하기

실행 중인 트랜잭션 조회

1. 활성 트랜잭션 조회:

SELECT * FROM pg_stat_activity 
WHERE state = 'active' 
  AND xact_start IS NOT NULL;

이 쿼리는 현재 활성 상태이며 트랜잭션이 시작된 모든 세션을 보여줍니다.

2. 장기 실행 트랜잭션 조회:

SELECT pid, 
       usename, 
       application_name,
       state, 
       query,
       age(clock_timestamp(), xact_start) AS xact_age,
       age(clock_timestamp(), query_start) AS query_age
FROM pg_stat_activity 
WHERE state != 'idle' 
  AND xact_start IS NOT NULL 
ORDER BY xact_start;

이 쿼리는 실행 중인 트랜잭션을 나이 순으로 정렬하여 보여줍니다.

3. 특정 데이터베이스의 트랜잭션 조회:

SELECT pid, 
       usename, 
       application_name,
       state, 
       query,
       age(clock_timestamp(), xact_start) AS xact_age
FROM pg_stat_activity 
WHERE datname = 'your_database_name' 
  AND state != 'idle' 
  AND xact_start IS NOT NULL;

이 쿼리는 특정 데이터베이스에서 실행 중인 트랜잭션을 보여줍니다.

4. 대기 중인 트랜잭션 조회:

SELECT pid, 
       usename, 
       application_name,
       wait_event_type, 
       wait_event,
       query,
       age(clock_timestamp(), xact_start) AS xact_age
FROM pg_stat_activity 
WHERE wait_event IS NOT NULL 
  AND state = 'active' 
  AND xact_start IS NOT NULL;

이 쿼리는 현재 대기 중인 트랜잭션을 보여줍니다.

5. 트랜잭션 ID 조회:

SELECT pid, 
       usename, 
       application_name,
       state, 
       backend_xid,
       backend_xmin,
       query
FROM pg_stat_activity 
WHERE state != 'idle' 
  AND (backend_xid IS NOT NULL OR backend_xmin IS NOT NULL);

 

 

트랜잭션 강제 종료 방법

트랜잭션을 강제 종료합니다:

SELECT pg_cancel_backend([프로세스 ID]);

 

 

또는 더 강력한 종료를 위해:

SELECT pg_terminate_backend([프로세스 ID]);