[POSTGRESQL] 서비스 멈춤의 주범, PostgreSQL 데드락 방지를 위한 3가지 쿼리 설계 원칙

분명 쿼리는 완벽한데, 특정 상황에서 서비스가 응답하지 않고 멈춘 적이 있나요? 대규모 트래픽이 몰릴 때 가장 무서운 적은 바로 **데드락(Deadlock)**입니다. 오늘은 PostgreSQL에서 왜 데드락이 발생하는지, 그리고 pg_stat_activity를 통해 범인을 찾아내고 해결하는 실무 노하우를 공유합니다.

---

1. 데드락(Deadlock)의 이해와 발생 원인

데드락은 두 개 이상의 트랜잭션이 서로가 가진 자원(Lock)을 기다리며 무한 대기에 빠지는 현상입니다.

🏗️ 시나리오 예시: 도서 대출 및 회원 관리 시스템

• Table A (books): 도서 정보 및 대출 상태 관리
• Table B (members): 회원 정보 및 누적 대출 횟수 관리

순서	트랜잭션 A (사원 1의 대출 처리)	트랜잭션 B (사원 2의 회원 등급 조정)
1	books 테이블의 '개미' 도서 락 점유	members 테이블의 '홍길동' 회원 락 점유
2	'홍길동'의 대출 횟수 증가를 위해 members 접근 시도 (B가 점유 중이라 대기)	'개미' 도서의 추천 점수 수정을 위해 books 접근 시도 (A가 점유 중이라 대기)

결과: 두 트랜잭션은 서로의 락이 풀리기만을 기다리며 멈춰버립니다. PostgreSQL은 이를 감지하면 한쪽을 강제로 롤백시키며 ERROR: deadlock detected 메시지를 던집니다.

---

2. [응급처치] 이미 걸린 데드락 강제 해제하기

시스템이 이미 멈췄다면, 원인이 되는 세션을 찾아 강제로 종료시켜야 합니다.

① 범인(Blocking PID) 찾기

어떤 쿼리가 어떤 쿼리를 막고 있는지 아래 쿼리로 확인합니다.

SQL

SELECT
    a.pid AS blocked_pid,
    a.query AS blocked_query,
    b.pid AS blocking_pid,
    b.query AS blocking_query
FROM pg_stat_activity a
JOIN pg_stat_activity b ON b.pid = ANY(pg_blocking_pids(a.pid));

② 프로세스 킬(Kill)

문제가 되는 blocking_pid를 종료시켜 다른 쿼리들이 흐를 수 있게 합니다.

• SELECT pg_cancel_backend(PID); : 실행 중인 쿼리만 취소 (연결은 유지)
• SELECT pg_terminate_backend(PID); : 세션 자체를 강제 종료 (확실한 해결)

---

3. [근본 해결] 데드락 예방을 위한 실무 전략

① 자원 접근 순서의 표준화 (Ordering)

가장 확실한 방법입니다. 모든 로직에서 테이블에 접근하는 순서를 동일하게 맞춥니다.

• 수정 전: 로직A(도서→회원), 로직B(회원→도서) (위험)
• 수정 후: 모든 로직이 반드시 도서 테이블을 먼저 수정 후 회원 테이블을 수정하도록 고정.

② MyBatis Bulk 처리 시 데이터 정렬

리스트 형태로 대량 업데이트를 할 때, 리스트 안의 데이터 순서가 무작위라면 데드락이 발생하기 쉽습니다. 반드시 정렬 후 DB에 전달하세요.

Java

// Java 서비스 로직에서 ID 기준으로 정렬하여 락 순서 고정
public void updateProcess(List<BookEntity> list) {
    list.sort(Comparator.comparing(BookEntity::getBookId)); 
    mapper.updateBooksBulk(list);
}

③ FOR UPDATE NOWAIT으로 무한 대기 방지

무작위로 대기하는 대신, 락이 걸려 있으면 즉시 에러를 발생시켜 트랜잭션을 종료하는 방식입니다.

SQL

-- 락을 획득할 수 없으면 즉시 에러를 던져 데드락 형성을 차단
SELECT * FROM books WHERE book_id = 100 FOR UPDATE NOWAIT;

④ 인덱스 최적화와 Big-O

• O(N) (Full Scan): 인덱스가 없으면 한 행을 고치려 해도 전체 테이블에 락 영향이 갈 수 있고, 시간이 오래 걸려 데드락 확률이 높아집니다.
• O(\log N) (Index Scan): 인덱스를 통해 필요한 행만 빠르게 수정하면 락 점유 시간이 극도로 짧아져 데드락 발생 가능성이 거의 사라집니다.

---

4. 요약: 이것만은 꼭 기억하세요!

1. 순서 고정: 테이블 접근 순서를 팀 내에서 약속하세요.
2. 정렬: 대량 데이터(Bulk) 처리 시 반드시 정렬하세요.
3. 인덱스: 쿼리 실행 속도를 높여 락 유지 시간을 최소화하세요.

---

4. 마무리: 이것만은 꼭 기억하세요!

데이터베이스의 데드락이 시스템을 멈추게 하듯, 투자에서도 한 자산에 과도하게 묶여 유동성 데드락에 빠지면 기회를 놓치게 됩니다. 시스템도, 포트폴리오도 언제나 원활하게 흐를 수 있도록 설계(Design)하는 습관이 중요합니다.

예외 처리만큼 중요한 것이 DB 트랜잭션 관리입니다. 아래 게시글도 한번 읽어주시면 감사하겠습니다.

https://run-code-rich.tistory.com/entry/java-exception-handling-comprehensive-guide

[Java] 자바 예외 처리(Exception) 총정리: try-catch부터 Custom Exception까지