PostgreSQL 데이터베이스 잠금 관리 및 교착 상태 해결 전략

PostgreSQL 교착 상태(Deadlock) 현상 및 대응 방법

교착 상태의 개념과 발생 원인

교착 상태는 두 개 이상의 트랜잭션이 서로의 자원 해방을 무한정 기다리면서 모든 트랜잭션이 진행되지 않는 상황을 말합니다. PostgreSQL에서 교착 상태는 주로 여러 트랜잭션이 동일한 자원을 다른 순서로 요청할 때 발생합니다. 예를 들어: - 트랜잭션 T1이 자원 A의 잠금을 보유하고 자원 B의 잠금을 요청합니다. - 트랜잭션 T2가 자원 B의 잠금을 보유하고 자원 A의 잠금을 요청합니다. 이 경우 T1과 T2는 서로를 기다리는 순환 상태에 빠져 교착 상태가 형성됩니다.

PostgreSQL의 교착 상태 자동 감지 및 해제

PostgreSQL은 내장된 교착 상태 감지 메커니즘을 통해 그래프 기반 알고리즘으로 트랜잭션 간의 잠금 의존 관계를 주기적으로 스캔합니다. 교착 상태 루프가 감지되면 시스템은 자동으로 한 트랜잭션을 희생자로 선택(보통 실행 시간이 가장 짧거나 최근 시작된 트랜잭션)하여 롤백하고 해당 트랜잭션이 보유한 잠금을 해제함으로써 교착 상태를 해결합니다. 이 과정은 사용자에게 투명하지만, 트랜잭션 롤백으로 인해 비즈니스 로직이 중단될 수 있습니다.

교착 상수 수동 식별 및 해제 방법

자동 감지가 제때 발생하지 않거나 능동적인 개입이 필요한 경우 다음 단계로 수동으로 처리할 수 있습니다: 1. 활성 트랜잭션 및 잠금 상태 조회


   SELECT spid, 상태, 실행쿼리, 시작시간
   FROM pg_활동세션
   WHERE 상태 = '활성' AND 대기이벤트유형 = 'Lock';

이 쿼리는 잠금을 기다리는 프로세스 ID(`spid`), 상태, 실행 중인 SQL 문장 및 시작 시간을 반환합니다. 2. 차단 프로세스 종료 - 트랜잭션 취소(정상 종료 시도):


     SELECT pg_취소_백엔드(spid);

트랜잭션이 응답하지 않을 경우 강제 종료:


     SELECT pg_강제종료_백엔드(spid);

- 종료 후: 종료된 트랜잭션의 잠금이 즉시 해제되며 다른 트랜잭션이 계속 실행될 수 있습니다. 3. 교착 상태 근본 원인 분석


   SELECT l.잠금종류, l.데이터베이스, l.spid, l.모드, t.테이블이름
   FROM pg_잠금 l
   JOIN pg_테이블 t ON l.관계 = t.oid
   WHERE t.테이블이름 = '대상테이블명';

이 쿼리는 구체적으로 잠금이 걸린 테이블 및 잠금 유형(예: 행 잠금, 테이블 잠금)을 식별하여 트랜잭션 설계를 최적화하는 데 도움을 줍니다.

교착 상태 예방을 위한 최적의 방법

1. 대형 트랜잭션 분할 긴 실행 시간을 가진 트랜잭션을 여러 개의 소규모 트랜잭션으로 분할하여 잠금 보유 시간을 줄입니다. 예를 들어:


   -- 원본 대형 트랜잭션
   BEGIN;
   테이블1 UPDATE SET 컬럼1 = 값1 WHERE 조건;
   테이블2 UPDATE SET 컬럼2 = 값2 WHERE 조건;
   COMMIT;

   -- 최적화된 소규모 트랜잭션
   BEGIN; 테이블1 UPDATE SET 컬럼1 = 값1 WHERE 조건; COMMIT;
   BEGIN; 테이블2 UPDATE SET 컬럼2 = 값2 WHERE 조건; COMMIT;

2. 잠금 획득 순일화 모든 트랜잭션이 동일한 순서로 자원에 접근하도록 보장합니다(예: 먼저 테이블 A를 조작한 후 테이블 B를 조작). 3. 인덱스 설계 최적화 고빈도 쿼리 조건에 대해 합리적인 인덱스를 생성하여 전체 테이블 스캔으로 인한 테이블 레벨 잠금 증가를 방지합니다. 예를 들어:


   CREATE INDEX idx_계정번호 ON 계정테이블(계정번호);

4. 격리 수준 조정 비즈니스 허용 시 격리 수준을 `REPEATABLE READ`(RR)에서 `READ COMMITTED`(RC)로 낮춰 간격 잠금(Gap Lock)으로 인한 교착 상태를 줄입니다. 5. 트랜잭션 보유 시간 최소화 트랜잭션 내에서 시간이 많이 소요되는 작업(네트워크 요청, 파일 I/O 등)을 수행하지 않고, 가능한 한 빨리 트랜잭션을 커밋하거나 롤백합니다.

PostgreSQL 테이블 잠금 관리

PostgreSQL에서 테이블 잠금은 일반적으로 트랜잭션이 자동으로 관리하지만(트랜잭션 종료 시 해제), 필요에 따라 수동으로 잠금을 해제해야 할 수도 있습니다(예: 차단 트랜잭션 종료). 다음은 구체적인 조작 방법입니다:

1. 테이블 잠금 보유자 확인

먼저 현재 어떤 세션(트랜잭션)이 대상 테이블의 잠금을 보유하고 있는지 쿼리합니다:


SELECT
 잠금.잠금종류,
 잠금.관계::regclass AS 테이블이름,
 잠금.모드 AS 잠금모드,
 활동세션.spid AS 프로세스ID,
 활동세션.쿼리 AS 실행중인쿼리,
 활동세션.상태 AS 트랜잭션상태
FROM
 pg_잠금 잠금
JOIN
 pg_활동세션 활동세션 ON 잠금.spid = 활동세션.spid
WHERE
 잠금.관계::regclass = '대상테이블명'::regclass;

주요 필드 설명: - 잠금모드: 잠금 유형(예: AccessExclusiveLock, RowExclusiveLock 등) - 프로세스ID: 잠금을 보유한 세션의 PID - 실행중인쿼리: 현재 세션에서 실행 중인 SQL(비어 있을 수 있음, 유휴 트랜잭션을 의미)

2. 잠금 보유 트랜잭션 종료

쿼리에서 얻은 프로세스ID를 기반으로 해당 세션을 종료하여 잠금을 해제합니다:

방법 1: 정상 취소 권장


SELECT pg_취소_백엔드(실제_PID); -- 실제 프로세스ID로 교체

기능: 세션을 종료하려고 시도하며 트랜잭션이 롤백되도록 허용(실행 중인 쿼리를 즉시 중단하지 않음). 적용 시나리오: 세션이 시간이 많이 걸리는 작업을 실행 중이지만 완전히 차단되지 않은 경우.

방법 2: 강제 종료(즉시 적용)


SELECT pg_강제종료_백엔드(실제_PID); -- 실제 프로세스ID로 교체

기능: 세션을 강제로 종료하며 트랜잭션이 즉시 롤백됩니다. 위험: 일부 작업이 완료되지 않을 수 있음(예: 커밋되지 않은 데이터 손실).

3. 잠금 해제 여부 확인

다시 1단계의 쿼리를 실행하여 대상 테이블에 활성 잠금이 없는지 확인합니다:


SELECT * FROM pg_잠금 WHERE 관계::regclass = '대상테이블명'::regclass;

결과가 비어 있으면 잠금이 해제된 것입니다.

4>테이블 잠금 장기 보유 방지

향후 테이블 잠금 차단이 다시 발생하는 것을 방지하기 위해 다음 조치를 취할 수 있습니다: 1. 트랜잭션 시간 단축: 트랜잭션 내에서 시간이 많이 소요되는 작업(외부 API 호출 등)을 수행하지 않습니다. 2. 잠금 수준 조정: 낮은 격리 수준(예: READ COMMITTED) 사용 또는 명시적으로 잠금 유형 지정:


   BEGIN;
   LOCK TABLE 대상테이블명 IN ROW EXCLUSIVE MODE; -- 동시 읽기/쓰기 허용
   -- 작업 수행...
   COMMIT;

3. 모니터링 및 경고: 도구(예: pg_활동세션 또는 외부 모니터링)를 통해 장기 트랜잭션을 실시간으로 감지합니다.

주의사항

- 권한 요구사항: pg_취소_백엔드 또는 pg_강제종료_백엔드 실행에는 슈퍼 사용자 권한 또는 동일한 역할 권한이 필요합니다. - 데이터 일관성: 강제 종료로 인해 트랜잭션이 롤백될 수 있으므로 비즈니스가 이러한 예외를 처리할 수 있도록 해야 합니다. - 시스템 테이블 잠금: 잠금이 PostgreSQL 내부 작업(예: VACUUM)에 의해 보유된 경우 작업이 완료될 때까지 기다려야 하며 강제 종료를 피해야 합니다. 위 단계를 통해 PostgreSQL의 테이블 잠금을 안전하게 해제하고 잠금 관리 전략을 최적화할 수 있습니다.

태그: PostgreSQL 데이터베이스 잠금 교착 상태 트랜잭션 관리

6월 10일 20:25에 게시됨

괴물 클럽