PostgreSQL 교착 상태 및 잠금 경합 식별 및 해결 방법

PostgreSQL 교착 상태 및 잠금 경합 이해 및 해결

강력하고 널리 사용되는 오픈 소스 관계형 데이터베이스인 PostgreSQL은 여러 사용자와 애플리케이션이 데이터를 동시에 액세스하고 수정할 수 있도록 강력한 동시성 제어 메커니즘을 제공합니다. 그러나 이러한 동시 작업이 복잡하게 상호 작용할 때 잠금 경합(lock contention) 및 더 심각한 경우에는 교착 상태(deadlock)가 발생할 수 있습니다. PostgreSQL에서 잠금이 작동하는 방식을 이해하고, 경합의 근본 원인을 파악하며, 효과적인 해결 전략을 구현하는 것은 데이터베이스 성능과 가용성을 유지하는 데 매우 중요합니다.

이 문서는 PostgreSQL 잠금의 복잡한 내용을 안내합니다. 다양한 잠금 유형을 살펴보고, pg_locks 시스템 뷰를 활용하여 잠금 문제를 진단하고, 차단 세션을 식별하며, 일반적인 교착 상태 시나리오를 분석하고, 가장 중요하게는 이러한 성능 병목 현상을 예방하고 해결하기 위한 실용적인 기술을 논의할 것입니다. 이러한 개념을 숙달하면 PostgreSQL 환경 내에서 보다 원활하고 효율적인 작업을 보장할 수 있습니다.

PostgreSQL 잠금 기본 사항

PostgreSQL은 테이블, 행, 심지어 특정 열과 같은 데이터베이스 객체에 대한 동시 액세스를 관리하기 위해 정교한 잠금 메커니즘을 사용합니다. 주요 목표는 충돌하는 작업을 방지하여 데이터 무결성을 보장하는 것입니다. 그러나 이 메커니즘은 신중하게 관리되지 않으면 성능 문제의 원인이 될 수도 있습니다.

잠금 유형

PostgreSQL은 각기 다른 수준의 보호를 제공하는 다양한 잠금 수준을 사용합니다. 이를 이해하는 것이 문제 진단의 핵심입니다.

Access Exclusive Lock (액세스 배타적 잠금): 리소스에 대한 배타적 액세스. 다른 트랜잭션은 해당 리소스에 대해 어떤 잠금도 획득할 수 없습니다. 이것이 가장 제한적인 잠금입니다.
Exclusive Lock (배타적 잠금): 단 하나의 트랜잭션만 이 잠금을 보유할 수 있습니다. 다른 트랜잭션은 리소스를 읽을 수 있지만 수정할 수는 없습니다.
Share Update Exclusive Lock (공유 업데이트 배타적 잠금): 다른 사용자가 읽을 수는 있지만 쓸 수는 없도록 허용하며, 다른 사용자가 특정 다른 잠금을 취하는 것을 방지합니다.
Share Row Exclusive Lock (공유 행 배타적 잠금): 여러 트랜잭션이 Share Row Exclusive 잠금 또는 Share 잠금을 보유할 수 있도록 허용하지만, 단 하나의 트랜잭션만 Exclusive, Share Update Exclusive 또는 Row Exclusive 잠금을 보유할 수 있습니다.
Share Lock (공유 잠금): 여러 트랜잭션이 Share 잠금을 동시에 보유할 수 있도록 허용합니다. 그러나 Exclusive, Access Exclusive 또는 Share Update Exclusive 잠금을 획득하려는 트랜잭션을 차단합니다.
Row Exclusive Lock (행 배타적 잠금): 여러 트랜잭션이 Row Exclusive 잠금을 동시에 보유할 수 있도록 허용합니다. 이는 트랜잭션이 Exclusive, Access Exclusive 또는 Share Update Exclusive 잠금을 획득하는 것을 방지합니다. 이것은 UPDATE 및 DELETE 작업에 일반적인 잠금 유형입니다.
Exclusive Lock (배타적 잠금): 특정 행에 대한 트랜잭션의 배타적 액세스를 부여합니다. 다른 트랜잭션은 해당 행을 읽을 수 있지만 해당 행에 대한 어떤 행 수준 잠금도 획득할 수 없습니다. (참고: 앞서 언급된 Exclusive Lock 설명과 내용이 겹치며, 일반적으로 PostgreSQL에서는 행 수준 잠금 컨텍스트에서 'RowExclusiveLock'이 더 흔하게 사용되나, 원문 목록을 유지합니다.)
Access Exclusive Lock (액세스 배타적 잠금): 가장 제한적인 잠금으로, 다른 모든 트랜잭션이 모든 수준에서 해당 리소스에 액세스하는 것을 방지합니다.

잠금 모드

잠금 모드는 트랜잭션이 요구하는 액세스 유형을 나타냅니다. 이들은 종종 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은 교착 상태를 감지하고 일반적으로 교착 상태를 유발하고 가장 적은 작업을 수행한 트랜잭션 중 하나를 중단하여 자동으로 해결합니다.

일반적인 교착 상태 시나리오

서로 다른 테이블의 다른 행을 역순으로 업데이트하는 두 트랜잭션:
- 트랜잭션 A: 테이블 1의 행 X를 업데이트한 다음 테이블 2의 행 Y를 업데이트하려고 시도합니다.
- 트랜잭션 B: 테이블 2의 행 Y를 업데이트한 다음 테이블 1의 행 X를 업데이트하려고 시도합니다.
  트랜잭션 A가 행 X를 잠그고 트랜잭션 B가 행 Y를 잠그면, 서로가 보유한 잠금을 획득하려고 할 때 교착 상태에 빠지게 됩니다.
UPDATE 후 SELECT ... FOR UPDATE:
- 트랜잭션 A: 행을 업데이트합니다.
- 트랜잭션 B: 동일한 행에 대해 SELECT ... FOR UPDATE를 실행합니다.
  UPDATE가 행 배타적 잠금(row-exclusive lock)을 계속 보유하고 있는 상태에서 SELECT FOR UPDATE가 공유 잠금(share lock)을 획득하려고 하고 다른 종속성이 있는 경우 교착 상태가 발생할 수 있습니다.

교착 상태 감지

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이 트랜잭션을 중단하여 해결하는 경우:

희생자 식별: PostgreSQL 로그에서 deadlock detected 메시지를 확인합니다. 중단된 프로세스가 지정되어 있습니다.
중단된 트랜잭션 재시도: 교착 상태 오류를 수신하는 애플리케이션은 이 특정 오류(예: deadlock_detected 오류 코드)를 포착하고 트랜잭션을 재시도하도록 설계되어야 합니다. 이것이 애플리케이션 관점에서 교착 상태를 처리하는 가장 일반적이고 효과적인 방법입니다.
원인 분석: 해결의 핵심은 향후 교착 상태를 방지하는 것입니다. 여기에는 교착 상태가 발생한 이유(일반적인 시나리오에서 설명된 대로)를 이해하고 애플리케이션 논리 또는 데이터베이스 설계를 조정하는 것이 포함됩니다.

잠금 경합 및 교착 상태 방지 기술

예방이 치료보다 항상 낫습니다. 잠금 경합을 최소화하고 교착 상태 상황을 피하기 위한 전략을 구현하는 것은 고성능 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는 트랜잭션 기간 동안 행을 잠급니다. 이는 경쟁 상태(race conditions)를 방지하는 데 강력하지만 경합의 주요 원인이 될 수도 있습니다.
조치: 트랜잭션이 작업을 완료하기 전에 다른 트랜잭션이 수정하는 것을 막기 위해 행을 잠가야 할 때만 사용하십시오. 특정 시나리오에 어드바이저리 잠금(advisory locks)이 더 적합할 수 있는지 고려하십시오.

6. 어드바이저리 잠금 (Advisory Locks)

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

7. 작업 일괄 처리

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

결론

잠금 경합과 교착 상태는 높은 동시성을 가진 데이터베이스 환경에서 흔히 발생하는 과제입니다. PostgreSQL 잠금의 기본 개념을 이해하고, pg_locks 및 pg_stat_activity와 같은 도구를 사용하여 문제를 진단하며, 일관된 트랜잭션 순서 지정, 트랜잭션 기간 최소화, 쿼리 최적화와 같은 예방 전략을 구현함으로써 PostgreSQL 데이터베이스의 안정성과 성능을 크게 향상시킬 수 있습니다. 애플리케이션에서 교착 상태 트랜잭션 재시도를 포함한 강력한 오류 처리가 상황을 효과적으로 관리하는 데 중요한 부분임을 기억하십시오.