DevOps 도구 및 베스트 프랙티스 마스터 - DevOps 지식 허브 DevOps 도구 및 베스트 프랙티스 마스터 - DevOps 지식 허브

PostgreSQL 데드락 및 잠금 경합 식별 및 해결 방법

PostgreSQL 잠금 경합과 데드락을 마스터하세요. `pg_locks`를 사용하여 차단 세션을 식별하고, 일반적인 데드락 시나리오를 분석하며, 일관된 트랜잭션 순서 지정 및 최적화된 쿼리와 같은 실용적인 기술을 통해 이러한 중요한 데이터베이스 문제를 예방하고 해결하는 방법을 알아보세요. 더 원활하고 효율적인 PostgreSQL 운영을 보장합니다.

PostgreSQL 데드락 및 잠금 경합 식별 및 해결 방법

PostgreSQL 데드락과 잠금 경합은 일반적으로 갑자기 멈춘 것처럼 느껴지는 애플리케이션으로 나타납니다. 요청이 쌓이고, 작업자는 active 또는 idle in transaction 상태에 머물며, 데이터베이스 자체에는 여전히 충분한 CPU가 남아 있을 수 있습니다. 문제는 원시적인 용량이 아닙니다. 한 세션이 다른 세션이 잠금을 해제하기를 기다리고 있으며, 때로는 그 뒤에 전체 줄이 형성됩니다.

이를 처리하는 가장 빠른 방법은 두 가지 경우를 분리하는 것입니다. 잠금 경합은 세션이 기다리고 있지만 결국 계속될 수 있음을 의미합니다. 데드락은 두 개 이상의 세션이 서로를 기다리는 순환 상태에 있어 PostgreSQL이 하나의 트랜잭션을 취소해야 함을 의미합니다. 두 경우 모두 동일한 기본 도구로 디버깅하지만, 수정 방법은 종종 다릅니다.

PostgreSQL 잠금 기본 사항

PostgreSQL은 많은 세션이 동시에 작업하는 동안 테이블, 행, 트랜잭션 및 기타 내부 객체를 보호하기 위해 잠금을 사용합니다. 또한 MVCC를 사용하므로 일반적인 읽기와 쓰기 작업은 종종 서로를 차단하지 않습니다. 이것이 PostgreSQL이 높은 동시성을 잘 처리할 수 있는 이유이지만, 잠금 문제가 혼란스러울 수 있는 이유이기도 합니다. 문제는 일반적으로 "너무 많은 사용자"가 아니라 특정 명령문 패턴입니다.

잠금 유형

PostgreSQL은 다양한 잠금 수준을 활용하며, 각 수준은 서로 다른 수준의 보호를 제공합니다. 이러한 잠금을 이해하는 것은 문제 진단에 중요합니다.

  • AccessShareLock: 일반 SELECT에서 사용됩니다. 주로 ACCESS EXCLUSIVE와 충돌하며, 이것이 쓰기가 발생하는 동안 많은 읽기가 실행될 수 있는 이유입니다.
  • RowExclusiveLock: 테이블에 대한 INSERT, UPDATE 및 DELETE에서 일반적입니다. 이름을 오해하기 쉽습니다. 테이블의 모든 행이 독점적으로 잠겨 있다는 의미는 아닙니다.
  • ShareUpdateExclusiveLock: FULL 없이 VACUUM, ANALYZE 및 일부 인덱스 작업과 같은 작업에서 사용됩니다. 일반적인 읽기 및 쓰기를 허용하지만 여러 유지 관리 작업과 충돌합니다.
  • ShareLock / ShareRowExclusiveLock / ExclusiveLock: 특정 DDL 및 제약 조건 관련 작업에서 사용되는 더 강력한 테이블 수준 모드입니다.
  • AccessExclusiveLock: 가장 제한적인 테이블 잠금입니다. ALTER TABLE, DROP TABLE, TRUNCATE 및 VACUUM FULL이 이러한 종류의 잠금을 사용할 수 있습니다. 일반적인 읽기와 쓰기를 모두 차단합니다.

행 수준 잠금은 테이블 수준 잠금 모드와 별개입니다. UPDATE는 테이블 수준 RowExclusiveLock과 변경하는 행에 대한 행 잠금을 사용합니다. 사람들이 "이 행이 잠겨 있습니다"라고 말할 때, 일반적으로 다른 트랜잭션이 해당 행을 FOR UPDATE로 수정하거나 선택했으며 아직 커밋하지 않았음을 의미합니다.

잠금 모드

잠금 모드는 트랜잭션에 필요한 액세스 유형을 나타냅니다. 일반적으로 RowExclusiveLock, ShareLock, ExclusiveLock 등과 같은 이름으로 표시됩니다.

잠금 경합 및 차단 세션 식별

잠금 경합은 여러 트랜잭션이 다른 트랜잭션이 보유한 잠금을 기다릴 때 발생합니다. 이로 인해 애플리케이션 속도가 크게 느려질 수 있습니다. pg_locks 시스템 뷰는 이러한 문제를 진단하는 주요 도구입니다.

pg_locks 사용

pg_locks 뷰는 데이터베이스 시스템의 모든 활성 잠금에 대한 정보를 제공합니다. 어떤 세션이 잠금을 보유하고 있고 어떤 세션이 기다리고 있는지 이해하는 데 매우 중요합니다.

다음은 차단 세션을 식별하는 일반적인 쿼리입니다.

SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocked_locks.locktype AS blocked_locktype,
    blocked_locks.virtualtransaction AS blocked_vtx,
    blocked_locks.mode AS blocked_mode,
    blocked_activity.query AS blocked_statement,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocking_locks.locktype AS blocking_locktype,
    blocking_locks.virtualtransaction AS blocking_vtx,
    blocking_locks.mode AS blocking_mode,
    blocking_activity.query AS blocking_statement
FROM
    pg_catalog.pg_locks blocked_locks
JOIN
    pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN
    pg_catalog.pg_locks blocking_locks
ON
    blocking_locks.locktype = blocked_locks.locktype AND
    blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND
    blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND
    blocking_locks.offset IS NOT DISTINCT FROM blocked_locks.offset AND
    blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND
    blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
JOIN
    pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE
    NOT blocked_locks.granted
    AND blocking_locks.pid != blocked_locks.pid;

쿼리 설명:

  • 차단된 프로세스와 차단 프로세스 각각에 대해 pg_locks를 pg_stat_activity와 두 번 조인합니다.
  • WHERE NOT blocked_locks.granted 절은 현재 대기 중인 잠금만 필터링합니다.
  • blocking_locks.pid != blocked_locks.pid는 세션이 자체를 차단하는 것을 보고하지 않도록 합니다.
  • pg_locks의 조인 조건은 동일한 리소스의 잠금을 일치시킵니다.

출력 해석

  • blocked_pid / blocking_pid: 관련 세션의 프로세스 ID(PID)입니다.
  • blocked_user / blocking_user: 이러한 PID와 연결된 사용자입니다.
  • blocked_statement / blocking_statement: 현재 실행 중이거나 대기 중인 SQL 쿼리입니다.
  • blocked_mode / blocking_mode: 요청 및 보유된 잠금 모드입니다.

이 쿼리가 행을 반환하면 잠금 경합이 있는 것입니다. blocking_pid는 blocked_pid가 기다리고 있는 잠금을 보유하고 있습니다.

데드락 이해 및 해결

데드락은 두 개 이상의 트랜잭션이 각각 순환 구조의 다른 트랜잭션이 보유한 잠금을 기다리며, 어느 쪽도 스스로 해결할 수 없는 순환 종속성을 생성할 때 발생합니다. PostgreSQL은 데드락을 감지하고 일반적으로 데드락을 유발하고 가장 적은 작업을 수행한 트랜잭션을 중단하여 자동으로 해결합니다.

일반적인 데드락 시나리오

  1. 두 트랜잭션이 서로 다른 테이블의 다른 행을 역순으로 업데이트하는 경우:

    • 트랜잭션 A: 테이블 1의 행 X를 업데이트한 다음 테이블 2의 행 Y를 업데이트하려고 시도합니다.
    • 트랜잭션 B: 테이블 2의 행 Y를 업데이트한 다음 테이블 1의 행 X를 업데이트하려고 시도합니다. 트랜잭션 A가 행 X를 잠그고 트랜잭션 B가 행 Y를 잠그면 다른 쪽이 보유한 잠금을 획득하려고 할 때 데드락이 발생합니다.

  2. UPDATE 후 SELECT ... FOR UPDATE:

    • 트랜잭션 A: 행을 업데이트합니다.
    • 트랜잭션 B: 동일한 행에 대해 SELECT ... FOR UPDATE를 실행합니다. UPDATE가 여전히 행 배타적 잠금을 보유하고 있는 상태에서 SELECT FOR UPDATE가 공유 잠금을 획득하려고 하고 다른 종속성이 존재하는 경우 데드락이 발생할 수 있습니다.

데드락 감지

PostgreSQL은 데드락 정보를 서버 로그에 기록합니다. 일반적으로 다음과 같은 메시지가 표시됩니다.

ERROR:  deadlock detected
DETAIL:  Process 1234 waits for ShareLock on transaction 5678; blocked by process 5679.
Process 5679 waits for ExclusiveLock on tuple (0,1) of relation 12345; blocked by process 1234.
HINT:  See server log for detail.

PostgreSQL은 자동으로 하나의 트랜잭션을 중단하여 다른 트랜잭션이 계속될 수 있도록 합니다. 사후에 pg_stat_activity에만 의존하지 마십시오. 확인할 때쯤이면 취소된 명령문이 사라졌을 수 있습니다. 서버 로그가 일반적으로 순환 구조에 대한 가장 좋은 기록입니다.

데드락 해결

데드락이 감지되고 PostgreSQL이 트랜잭션을 중단하여 해결하는 경우:

  1. 희생자 식별: PostgreSQL 로그에서 deadlock detected 메시지를 확인합니다. 중단된 프로세스를 지정합니다.
  2. 중단된 트랜잭션 재시도: SQLSTATE 40P01(deadlock_detected)을 수신하는 애플리케이션은 작업을 다시 시도해도 안전할 때 전체 트랜잭션을 재시도해야 합니다. 마지막 명령문만 재시도하면 애플리케이션 상태가 일관되지 않을 수 있습니다.
  3. 원인 분석: 해결의 핵심은 향후 데드락을 방지하는 것입니다. 여기에는 데드락이 발생한 이유(일반적인 시나리오에 설명된 대로)를 이해하고 애플리케이션 로직 또는 데이터베이스 설계를 조정하는 것이 포함됩니다.

잠금 경합 및 데드락 방지 기술

예방이 치료보다 항상 낫습니다. 잠금 경합을 최소화하고 데드락 상황을 피하기 위한 전략을 구현하는 것은 고성능 PostgreSQL 데이터베이스에 매우 중요합니다.

1. 일관된 트랜잭션 순서 지정

  • 규칙: 모든 트랜잭션에서 리소스(테이블, 행)에 항상 동일한 순서로 액세스하고 수정합니다. 여러 트랜잭션이 TableA와 TableB를 업데이트해야 하는 경우 항상 TableA를 TableB보다 먼저 업데이트하거나 그 반대로 일관된 방식으로 업데이트해야 합니다.
  • 예시: 트랜잭션이 users와 orders의 레코드를 업데이트해야 하는 경우 항상 users에 대한 작업을 먼저 수행한 다음 orders에 대한 작업을 수행합니다. 한 트랜잭션은 users 다음 orders를 업데이트하고 다른 트랜잭션은 orders 다음 users를 업데이트하는 시나리오를 피하십시오.

2. 트랜잭션 기간 최소화

  • 규칙: 트랜잭션을 가능한 한 짧게 유지하십시오. 트랜잭션이 열려 있는 시간이 길수록 보유하는 잠금이 많아져 경합 가능성이 높아집니다.
  • 조치: 트랜잭션 내에서 필요한 데이터베이스 작업만 수행하십시오. 데이터베이스 외부 작업(예: 외부 API 호출, 트랜잭션 상태에 의존하지 않는 복잡한 계산)은 트랜잭션 경계 밖으로 이동하십시오.

3. 적절한 격리 수준 사용

  • 규칙: 올바른 트랜잭션 격리 수준을 이해하고 선택하십시오. PostgreSQL은 다음을 제공합니다.
    • READ UNCOMMITTED (PostgreSQL에서 READ COMMITTED로 시뮬레이션)
    • READ COMMITTED (기본값)
    • REPEATABLE READ
    • SERIALIZABLE
  • 조치: 기본 READ COMMITTED는 더티 읽기를 방지하면서 우수한 성능을 제공합니다. REPEATABLE READ 및 SERIALIZABLE은 더 강력한 일관성을 제공하지만 더 많은 serialization_failure 오류(스냅샷 격리의 데드락)와 잠재적으로 더 많은 잠금 경합을 초래할 수 있습니다. 반드시 필요한 경우에만 사용하십시오.

4. 쿼리 및 인덱스 최적화

  • 규칙: 느린 쿼리는 잠금을 더 오래 보유합니다. 쿼리가 효율적이고 인덱스가 잘 생성되었는지 확인하십시오.
  • 조치: EXPLAIN ANALYZE를 사용하여 느린 쿼리를 식별하십시오. 특히 WHERE 절 및 JOIN 조건에 대해 데이터 검색 속도를 높이기 위해 적절한 인덱스를 추가하십시오.

5. SELECT ... FOR UPDATE 절제하여 사용

  • 규칙: SELECT ... FOR UPDATE는 트랜잭션 기간 동안 행을 잠급니다. 이는 경합 조건을 방지하는 데 강력하지만 주요 경합 원인이 될 수도 있습니다.
  • 조치: 트랜잭션이 작업을 완료하기 전에 다른 트랜잭션이 행을 수정하는 것을 방지하기 위해 실제로 행을 잠가야 하는 경우에만 사용하십시오. 특정 시나리오에 어드바이저리 잠금이 더 적합할 수 있는지 고려하십시오.

6. 어드바이저리 잠금

  • 규칙: 데이터베이스 객체 잠금에 직접 매핑되지 않는 애플리케이션 수준 잠금 또는 더 복잡한 동기화 요구 사항의 경우 PostgreSQL의 어드바이저리 잠금이 강력한 도구가 될 수 있습니다.
  • 조치: pg_advisory_lock(), pg_advisory_lock_shared() 및 pg_advisory_unlock()과 같은 함수를 사용하여 사용자 정의 잠금 메커니즘을 구현하십시오. 이러한 잠금은 데드락 감지 메커니즘에 의해 자동으로 감지되지 않으므로 애플리케이션 로직이 이를 신중하게 관리해야 합니다.

7. 작업 일괄 처리

  • 규칙: 많은 개별 UPDATE 또는 DELETE 문을 실행하는 대신 단일 문으로 일괄 처리하거나 가능한 경우 대량 로드/업데이트에 COPY를 사용하는 것을 고려하십시오.
  • 조치: 단일 UPDATE 문은 개별 UPDATE 루프보다 잠금을 더 효율적으로 획득할 수 있습니다. 일괄 작업의 잠금 동작을 분석하십시오.

실용적인 트라이지 흐름

인시던트가 활성화되면 머릿속의 가장 오래된 이론부터 시작하지 말고 대기 중인 세션부터 시작하십시오.

SELECT
    now() - a.query_start AS waiting_for,
    a.pid,
    a.usename,
    a.state,
    a.wait_event_type,
    a.wait_event,
    pg_blocking_pids(a.pid) AS blocked_by,
    a.query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY waiting_for DESC;

하나의 차단 PID가 반복해서 나타나면 해당 PID를 검사하십시오.

SELECT
    pid,
    usename,
    state,
    now() - xact_start AS transaction_age,
    now() - query_start AS query_age,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE pid = 12345;

주목해야 할 문구는 idle in transaction입니다. 해당 세션은 유용한 데이터베이스 작업을 적극적으로 수행하지 않지만 여전히 잠금을 보유하고 있을 수 있습니다. 이는 종종 트랜잭션을 열고, 쿼리를 수행하고, 외부 API를 호출하고, API가 반환된 후에만 커밋하는 애플리케이션 코드에서 발생합니다. 가능하면 트랜잭션 외부로 외부 호출을 이동하십시오.

신중하게 취소하십시오. SELECT pg_cancel_backend(pid)는 현재 쿼리를 중지하도록 요청합니다. SELECT pg_terminate_backend(pid)는 세션을 종료하고 열려 있는 트랜잭션을 롤백합니다. 프로덕션 인시던트에서 차단기를 종료하는 것이 올바른 결정일 수 있지만, 먼저 쿼리와 트랜잭션 기간을 캡처하여 나중에 코드 경로를 수정할 수 있도록 하십시오.

인시던트 후에 한 가지 습관을 더 들이면 도움이 됩니다. 차단 쿼리, 차단된 쿼리 및 애플리케이션 로그의 트랜잭션 경계를 저장하십시오. SQL 문만으로는 충분하지 않은 경우가 많습니다. 무해해 보이는 UPDATE users SET last_seen_at = now()도 결제 API를 기다리는 트랜잭션 내에 있으면 차단기가 될 수 있습니다. 데드락 수정은 일반적으로 하나의 격리된 문 내부가 아닌 트랜잭션 흐름 수준에서 이루어집니다.

지속적인 수정은 일반적으로 간단합니다. 트랜잭션을 짧게 유지하고, 공유 리소스에 일관된 순서로 액세스하고, 업데이트가 너무 많이 스캔하지 않도록 인덱스를 추가하고, 애플리케이션 재시도가 40P01 및 직렬화 실패를 깔끔하게 처리하도록 만드십시오. PostgreSQL은 데드락을 감지할 수 있지만 트랜잭션 패턴을 재설계할 수는 없습니다. 그 부분은 인시던트가 진정된 후 애플리케이션 및 스키마 검토에 속합니다.