Postgresql Lock 살펴보기

Table-Level Locks

1. ACCESS SHARE (AccessShareLock)
   • 오직 ACCESS EXCLUSIVE lock 모드와 충돌합니다.
   • 일반적으로 테이블을 수정하지 않고 오로지 읽는 쿼리는 이 lock 모드를 획득합니다. SELECT 명령은 참조된 테이블에 이 모드의 lock을 획득합니다.
2. ROW SHARE (RowShareLock)
   • EXCLUSIVE와 ACCESS EXCLUSIVE lock 모드와 충돌합니다.
   • SELECT 명령은 FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, 또는 FOR KEY SHARE 옵션이 지정된 모든 테이블에 이 모드의 lock을 획득합니다(명시적으로 FOR... locking 옵션이 없이 참조된 다른 테이블에는 ACCESS SHARE lock을 추가로 획득).
3. ROW EXCLUSIVE (RowExclusiveLock)
   • SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE lock 모드와 충돌합니다.
   • UPDATE, DELETE, INSERT, MERGE 명령은 타깃 테이블에 이 lock 모드를 획득합니다(참조된 다른 테이블에는 ACCESS SHARE lock을 추가로 획득). 일반적으로 테이블의 데이터를 수정하는 명령에 의해 이 lock 모드가 획득됩니다.
4. SHARE UPDATE EXCLUSIVE (ShareUpdateExclusiveLock)
   • SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE lock 모드와 충돌합니다. 이 모드는 동시에 발생하는 스키마 변경과 VACUUM 작업을 방지합니다.
   • VACUUM (not VACUUM FULL), ANALYZE, CREATE INDEX CONCURRENTLY, CREATE STATISTICS, COMMENT ON, REINDEX CONCURRENTLY 및 특정 ALTER INDEX와 ALTER TABLE 변형에 의해 획득됩니다.
5. SHARE (ShareLock)
   • ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE lock 모드와 충돌합니다. 이 모드는 동시에 발생하는 데이터 변경을 방지합니다.
   • CREATE INDEX (CONCURRENTLY 없이)에 의해 획득됩니다.
6. SHARE ROW EXCLUSIVE (ShareRowExclusiveLock)
   • ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE lock 모드와 충돌합니다. 이 모드는 동시에 발생하는 데이터 변경을 방지하며 한 세션만이 동시에 이를 보유할 수 있습니다.
   • CREATE TRIGGER 및 일부 ALTER TABLE 형태에 의해 획득됩니다.
7. EXCLUSIVE (ExclusiveLock)
   • ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE lock 모드와 충돌합니다. 이 모드는 테이블을 읽는 것만이 이 lock 모드를 보유하는 트랜잭션과 병렬로 진행될 수 있습니다.
   • REFRESH MATERIALIZED VIEW CONCURRENTLY에 의해 획득됩니다.
8. ACCESS EXCLUSIVE (AccessExclusiveLock)
   • 모든 lock 모드(ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE)와 충돌합니다. 이 모드는 보유자가 유일하게 테이블에 접근하는 것을 보장합니다.
   • DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, REFRESH MATERIALIZED VIEW (CONCURRENTLY 없이) 명령과 많은 형태의 ALTER INDEX와 ALTER TABLE에 의해 획득됩니다. LOCK TABLE 문에서 명시적으로 모드를 지정하지 않을 경우 기본 lock 모드로 사용됩니다.

Conflict Lock Modes

 

Row-Level Locks

1. FOR UPDATE
   • FOR UPDATE는 SELECT문에 의해 검색된 행들을 잠그므로, 현재 트랜잭션이 종료될 때까지 다른 트랜잭션들이 해당 행을 잠그거나 수정하거나 삭제하는 것을 방지합니다.
   • 이 잠금은 UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE, 또는 SELECT FOR KEY SHARE를 시도하는 다른 트랜잭션을 차단합니다. SELECT FOR UPDATE는 동일한 행에 대해 이러한 명령 중 하나를 실행한 동시 트랜잭션이 있을 경우 기다렸다가 해당 행을 잠그고 업데이트된 행을 반환하거나, 행이 삭제된 경우에는 행을 반환하지 않습니다.
   • SELECT * FROM 테이블명 FOR UPDATE;
2. FOR NO KEY UPDATE
   • FOR NO KEY UPDATE는 FOR UPDATE와 비슷하게 작동하지만, 획득하는 잠금이 약합니다. 이 잠금은 동일한 행에 잠금을 시도하는 SELECT FOR KEY SHARE 명령을 차단하지 않습니다.
   • FOR UPDATE 잠금을 획득하지 않는 UPDATE에 의해 이 잠금 모드가 획득됩니다.
   • SELECT * FROM 테이블명 FOR NO KEY UPDATE;
3. FOR SHARE
   • FOR SHARE는 FOR NO KEY UPDATE와 비슷하게 작동하지만, 각 검색된 행에 공유 잠금을 획득합니다. 공유 잠금은 다른 트랜잭션이 해당 행에 대해 UPDATE, DELETE, SELECT FOR UPDATE, 또는 SELECT FOR NO KEY UPDATE를 수행하는 것을 차단합니다.
   • 그러나 SELECT FOR SHARE 또는 SELECT FOR KEY SHARE의 수행을 방지하지 않습니다.
   • SELECT * FROM 테이블명 FOR SHARE;
4. FOR KEY SHARE
   • FOR KEY SHARE는 FOR SHARE와 비슷하지만 잠금이 더 약합니다. SELECT FOR UPDATE는 차단되지만 SELECT FOR NO KEY UPDATE는 차단되지 않습니다.
   • 키-공유 잠금은 다른 트랜잭션이 해당 행을 삭제하거나 키 값을 변경하는 UPDATE를 수행하는 것을 차단하지만, 다른 UPDATE나 SELECT FOR NO KEY UPDATE, SELECT FOR SHARE, 또는 SELECT FOR KEY SHARE의 수행을 방지하지 않습니다.
   • SELECT * FROM 테이블명 FOR KEY SHARE;

PostgreSQL은 수정된 행에 대한 정보를 메모리에 저장하지 않으므로 한 번에 잠금할 수 있는 행의 수에 제한이 없습니다. 하지만, 행을 잠그는 것은 디스크 쓰기를 유발할 수 있습니다. 예를 들어, SELECT FOR UPDATE는 선택된 행을 잠금 상태로 표시하므로 디스크 쓰기가 발생할 수 있습니다.

Conflicting Row-Level Locks

데드락

데드락(Deadlock)은 두 개 이상의 트랜잭션이 서로의 진행을 막는 잠금을 보유하면서 발생하는 데이터베이스 시스템의 상태입니다. 이러한 상태에서는 외부 개입 없이는 서로의 의존성 사이클에서 벗어날 수 없습니다. PostgreSQL에서는 데드락이 발생하고 처리하는 방법은 다음과 같습니다

1. 데드락 발생 상황

데드락은row-level locks의 결과로도 발생할 수 있습니다(따라서 명시적 잠금이 사용되지 않더라도 발생할 수 있습니다). 예를 들어, 두 개의 동시 트랜잭션이 테이블을 수정하는 경우를 생각해 보겠습니다. 첫 번째 트랜잭션이 다음과 같이 실행합니다:

UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 11111;

이 명령은 지정된 계정 번호의 행에 대한 행 수준 잠금을 획득합니다. 그런 다음 두 번째 트랜잭션이 다음과 같이 실행합니다:

UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;

첫 번째 UPDATE 문은 지정된 행에 대한 행 수준 잠금을 성공적으로 획득하므로 해당 행을 업데이트할 수 있습니다. 하지만 두 번째 UPDATE 문은 업데이트하려는 행이 이미 잠겨 있음을 발견하고, 잠금을 획득한 트랜잭션이 완료될 때까지 기다립니다. 이제 두 번째 트랜잭션이 첫 번째 트랜잭션이 완료될 때까지 실행을 계속할 수 없습니다. 그리고 첫 번째 트랜잭션이 다음을 실행합니다:

UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;

첫 번째 트랜잭션은 지정된 행에 대한row-level locks을 획득하려고 시도하지만, 두 번째 트랜잭션이 이미 그런 잠금을 보유하고 있습니다.

따라서 첫 번째 트랜잭션은 두 번째 트랜잭션이 완료될 때까지 기다립니다. 이로 인해 첫 번째 트랜잭션은 두 번째 트랜잭션에 막히고, 두 번째 트랜잭션은 첫 번째 트랜잭션에 막혀 데드락 상태가 됩니다.

요약

• 트랜잭션 1이 테이블 A에 대해 Exclusive lock을 획득한 후 테이블 B에 대해서도 Exclusive lock 시도합니다.
• 동시에 트랜잭션 2는 이미 테이블 B를 Exclusive lock 상태이며 테이블 A에 대한 Exclusive lock을 시도합니다.
• 이 상황에서는 각 트랜잭션이 서로의 잠금을 보유하고 있기 때문에 어느 트랜잭션도 진행할 수 없습니다.

1. PostgreSQL의 대처 방법
   • PostgreSQL은 데드락 상황을 자동으로 감지하고, 해결하기 위해 관련된 트랜잭션 중 하나를 중단시킵니다. 이를 통해 다른 트랜잭션(들)이 완료될 수 있도록 합니다. 어떤 트랜잭션이 중단될지 예측하기는 어렵습니다.
2. 데드락 방지 전략
   • 데드락을 피하는 가장 좋은 방법은 모든 애플리케이션이 데이터베이스를 사용할 때 여러 객체에 대한 잠금을 일관된 순서로 획득하도록 하는 것입니다. 예를 들어, 위의 예에서 두 트랜잭션이 행을 같은 순서로 업데이트했다면 데드락은 발생하지 않았을 것입니다.
   • 또한 트랜잭션이 객체에 대해 획득하는 첫 번째 잠금이 그 객체에 필요한 가장 제한적인 모드여야 합니다. 미리 확인이 불가능한 경우, 데드락으로 인해 중단된 트랜잭션은 다시 시도함으로써 처리할 수 있습니다.

따라서 데드락 상황이 감지되지 않는 한, 테이블 수준 또는 행 수준 잠금을 요구하는 트랜잭션은 충돌하는 잠금이 해제될 때까지 무기한 기다릴 것입니다. 이는 애플리케이션이 트랜잭션을 장시간 열어 두는 것이 좋지 않은 이유입니다

Advisory lock

 

PostgreSQL에서는 애플리케이션에서 정의한 잠금을 생성할 수 있는 advisory locks를 제공합니다. 이 잠금은 시스템이 강제하지 않기 때문에 올바르게 사용하는 것이 중요합니다. advisory locks는 MVCC 모델과 맞지 않는 잠금 전략에 유용하며, "플랫 파일" 데이터 관리 시스템의 비관적 잠금(pessimistic locking) 전략을 모방하는 데 자주 사용됩니다. 테이블에 플래그를 사용하는 대신 advisory locks를 사용하면 더 빠르고, 테이블 부풀림을 방지하며, 세션 종료 시 서버가 자동으로 정리합니다. 

 

advisory locks는 세션 수준과 트랜잭션 수준에서 획득할 수 있습니다. 

 

• 세션 수준
  • 명시적으로 해제하거나 세션이 종료될 때까지 유지됩니다.
  • 트랜잭션 의미를 따르지 않으므로, 트랜잭션 롤백 후에도 잠금이 유지됩니다.
  • 잠금을 여러 번 획득할 수 있으며, 각 잠금 요청에 대한 해제 요청이 필요합니다. 

 

• 트랜잭션 수준
  • 트랜잭션 종료 시 자동으로 해제됩니다.
  • 명시적인 해제 작업이 필요하지 않아 단기 사용에 편리합니다. 

 

세션 수준과 트랜잭션 수준 잠금 요청은 서로를 차단하며, 동일한 세션이 이미 잠금을 보유하고 있으면 추가 요청이 항상 성공합니다.

 

 현재 세션에 의해 보유된 모든 advisory locks 목록은 pg_locks 시스템 뷰에서 확인할 수 있습니다. advisory locks와 일반 잠금은 공유 메모리 풀에 저장되며, 이 풀의 크기는 max_locks_per_transaction 및 max_connections 설정 변수에 의해 정의됩니다. 메모리를 다 소모하지 않도록 주의해야 하며, 그렇지 않으면 서버는 어떤 잠금도 부여할 수 없습니다. 특히 정렬 및 LIMIT 절이 포함된 쿼리에서는 advisory locking 방법을 사용할 때 SQL 표현식이 평가되는 순서에 따라 잠금을 제어해야 합니다.

 

 

SELECT pg_advisory_lock(id) FROM foo WHERE id = 12345; -- ok
SELECT pg_advisory_lock(id) FROM foo WHERE id > 12345 LIMIT 100; -- danger!
SELECT pg_advisory_lock(q.id) FROM
(
  SELECT id FROM foo WHERE id > 12345 LIMIT 100
) q; -- ok

 

두 번째 쿼리는 LIMIT이 잠금 함수가 실행되기 전에 적용된다는 보장이 없어 위험할 수 있습니다. 예상하지 못한 잠금이 일부 획득되어 세션이 종료될 때까지 해제되지 않을 수 있습니다. 이러한 잠금은 댕글링(dangling) 잠금이지만 pg_locks에서 여전히 볼 수 있습니다.

 

 

 

 

공식문서

https://www.postgresql.org/docs/current/explicit-locking.html#EXPLICIT-LOCKING

13.3. Explicit Locking