목차
1. 환경 및 문제 상황
1-1. 환경
1-2. 문제 상황
2. UPDATE / DELETE가 실행되지 않았던 원인
3. 락(Lock) 확인 방법
3-1. idle in transaction 상태란?
3-2. 해결 방법
4. 의문점
4-1. select만 했는데 왜 락(lock)이 걸렸을까?
5. 정리 및 실무에서의 주의점
1. 환경 및 문제 상황</ㅁ>
1-1. 환경
- DBeaver
- PostgreSQL
1-2. 문제 상황
DBeaver에서 UPDATE 또는 DELETE 쿼리를 실행하면 쿼리가 끝나지 않고 무한 로딩 상태에 빠진다.
⚠️ 특이점 : 내 PC뿐만 아니라 다른 사람의 PC에서도 동일한 증상 발생
이를 통해 PC나 디비버 설정 문제가 아니라
DB 자체에서 누군가 락을 잡고 있는 상황일 가능성이 높다는 판단을 내렸다.
2. UPDATE / DELETE가 실행되지 않았던 원인
PostgreSQL에서 UPDATE나 DELETE는 대상 테이블과 로우에 쓰기 락(Write Lock) 을 필요로 한다.
그런데 이미 다른 세션이 해당 테이블에 락을 잡고 있으면
이 쿼리들은 실행되지 못하고 대기 상태로 들어간다.
DBeaver에서는 이 상태가 쿼리가 멈춘 것처럼 보이는 무한 로딩으로 나타난다.
3. 락(lock) 확인 방법
락이 걸려 있는지 확인하는 방법은 디비버에서 아래 쿼리를 실행하는 것입니다.
SELECT
pid, usename, state, query, now() - query_start AS running_time
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY running_time DESC;
이 쿼리는 현재 PostgreSQL에서 돌아가고 있는 모든 세션 상태를 보는 쿼리입니다.
✔ pid : 세션 ID
✔ usename : 어떤 사용자가 실행했는지
✔ state : 세션 상태
→ active : 쿼리 실행 중
→ idle in transaction : 쿼리는 끝났지만 트랜잭션은 열린 상태 (위험 ⚠️ )
✔ query : 마지막으로 실행한 SQL
✔ running_time : 해당 쿼리가 얼마나 오래 유지되고 있는지
3-1. idle in transaction 상태란?
쿼리는 끝났지만 COMMIT / ROLLBACK 이 안 된 상태를 의미합니다.
즉, 세션 상태를 봤을 때, 상태값이 idle in transaction 이면 락이 걸린 상태임을 말합니다.
3-2. 해결 방법
이를 해결하기 위해서는 강제로 해당 세션을 종료하는 방법이 있습니다.
위의 쿼리에서 상태가 idle in transaction 인 row의 pid 값을 확인합니다.
그 후, 해당 pid 값을 넣어서 아래 쿼리를 실행하여 세션을 강제로 종료합니다.
SELECT pg_terminate_backend(pid);
4. 의문점
4-1. SELECT만 했는데 왜 락이 걸렸을까?
PostgreSQL은 디비버 설정에서 Auto-commit 이 꺼져 있으면
SELECT 에도 ACCESS SHARE LOCK 을 겁니다.
그렇기에 트랜잭션이 열린 채로 남아 있으면 다른 사람의 UPDATE / DELETE 가 막힙니다.
❌ 하지만 무작정 Auto-commit 을 키면 안 됩니다.
특히 실무에서는 주요 데이터일수록 신중하게 데이터를 처리해야 하기에 이를 꺼놓는 경우가 대부분입니다.
5. 마무리하며
이번 문제는 트랜잭션이 종료되지 않아 발생한 문제였습니다.
이를 방지하기 위해서는 SELECT 쿼리 수행 후에도 COMMIT 혹은 ROLLBACK을 실행하는 것이 좋습니다.