PostgreSQL / PPAS Lock 종류

• 해당 페이지에서는 PostgreSQL / PPAS 의 Lock 유형과 특징을 기재한다.
  
  
• ( 출처 : https://www.postgresql.org/docs/11/explicit-locking.html - 공식 DOC )
  
  ( 출처 : https://www.kostolansky.sk/posts/postgresql-advisory-locks/ - 다른분 개인 블로그)
  
  
• Lock 은 기본 SQL 문 이외에 LOCK 과 같은 명시적인 명령문에도 적용된다.
•  
• PostgreSQL 에서 지원하는 Table Level 까지 적용되는 Lock 은 아래와 같다.
  
  
  • ACCESS SHARE
    
    
    • ACCESS EXCLUSIVE Lock 과 충돌한다.
      
      
    • 읽기만 하는 SELECT 문 수행시 획득 되는 Lock 이다
      
      
  • ROW SHARE
    
    
    • EXCLUSIVE, ACCESS EXCLUSIVE Lock 과 충돌한다.
      
      
    • SELECT FOR UPDATE, SELECT FOR SHARE 구문을 수행할때 획득되는 Lock 이다. (이를 수행시에 참조되는 다른 테이블에는 ACCESS SHARE Lock 을 획득한다.)
  • ROW EXCLUSIVE
    
    
    • SHARE, SHARE ROW EXCLUSIVE,  EXCLUSIVE, ACCESS EXCLUSIVE Lock 과 충돌한다.
      
      
    • UPDATE,  DELETE, INSERT 등과 같은 데이터 변경을 수행하는 구문을 수행 시, 획득하는 Lock 이다. (이를 수행시에 참조되는 다른 테이블에는 ACCESS SHARE Lock 을 획득한다.)
      
      
  • SHARE UPDATE EXCLUSIVE
    
    
    • ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE,  SHARE,  SHARE ROW EXCLUSIVE,  EXCLUSIVE, ACCESS EXCLUSIVE Lock 과 충돌한다.
      
      
    • VACUUM (단, FULL 제외), ANALYZE,  CREATE INDEX CONCURRENTLY,  CREATE STATISTICS, ALTER TABLE VALIDATE, ALTER TABLE 류 구문들을 수행시 획득하는 Lock 이다.
      
      
  • SHARE
    
    
    • ROW EXCLUSIVE,  SHARE UPDATE EXCLUSIVE,  SHARE ROW EXCLUSIVE,  EXCLUSIVE, ACCESS EXCLUSIVE Lock 과 충돌한다. 또한 테이블 단위내에 동시 데이터 변경을 막는다.
      
      
    • CREATE INDEX 구문을 ( CONCURRENTLY 구문 없이 ) 수행 시 획득하는 Lock 이다.
      
      
  • SHARE ROW EXCLUSIVE
    
    
    • ROW EXCLUSIVE,  SHARE UPDATE EXCLUSIVE,  SHARE,  SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE Lock 과 충돌한다.
      위의 SHARE 와 유사하게 동시 데이터 변경을 막으면서도 Exclusive (베타성) 으로 인하여 단 하나의 세션만 해당 Lock 을 부여 받을 수 있다.
      
      
    • CREATE COLLATION,  CREATE TRIGGER, 기타 ALTER TABLE 류 구문을 수행시에 획득하는 Lock 이다.
      
      
  • EXCLUSIVE
    
    
    • ROW EXCLUSIVE,  SHARE UPDATE EXCLUSIVE,  SHARE,  SHARE ROW EXCLUSIVE,  EXCLUSIVE, ACCESS EXCLUSIVE Lock 과 충돌한다. 이 형태의 Lock 만 유일하게 동시 ACCESS SHARE 을 허용한다.
      
      
    • REFRESH MATERIALIZED VIEW CONCURRENTLY 구문을 수행 할 때 획득한다.
      
      
  • ACCESS EXCLUSIVE
    
    
    • 모든 형태의 Lock 과 충돌한다 (select 구문도 막을 수 있는 유일한 Lock 유형이다). 그렇기 때문에 해당 Lock 이 발생하면, 이 Lock 을 획득한 트랜잭션만 수행 중임을 예상 할 수 있는 형태기도 하다.
      
      
    • DROP TABLE,  TRUNCATE,  REINDEX,  CLUSTER,  VACUUM FULL, REFRESH MATERIALIZED VIEW (부가 명령어CONCURRENTLY 없이) 구문을 수행 할 때 획득하며
      부가적으로 다수의 Alter 구문을 수생 할 떄 획득 할 수 있다. 또한 Lock 명령어로 명시적인 Lock 을 수행 시 내부적으로 작동하는 Lock 형태기도 하다.
      
      
  • 위의 Lock 유형들에 대한 호환 및 충돌 가능 여부는 아래 표와 같다.
    
    

Requested Lock Mode	Current Lock Mode
	ACCESS SHARE	ROW SHARE	ROW EXCLUSIVE	SHARE UPDATE	EXCLUSIVESHARE	SHARE ROW EXCLUSIVE	EXCLUSIVEACCESS	EXCLUSIVE
ACCESS SHARE								X
ROW SHARE							X	X
ROW EXCLUSIVE					X	X	X	X
SHARE UPDATE EXCLUSIVE				X	X	X	X	X
SHARE			X	X		X	X	X
SHARE ROW EXCLUSIVE			X	X	X	X	X	X
EXCLUSIVE		X	X	X	X	X	X	X
ACCESS EXCLUSIVE	X	X	X	X	X	X	X	X

 

• PostgreSQL 에서 지원하는 Row Level Lock 은 아래와 같다.
  
  
  • FOR UPDATE
    
    
    • Select 구문와 같이 쓰이게 되면 다른 트랜잭션들의 데이터 변경을 방지하게 되어
      UPDATE,  DELETE,  SELECT FOR UPDATE,  SELECT FOR NO KEY UPDATE,  SELECT FOR SHARE,  SELECT FOR KEY SHARE 등의 작업 및 Lock 획득을 방지한다.
      
      
    • 반대로 이 작업들이 선 수행 되게 되면 대기상태가 되며, Isolation Level 이 REPEATABLE READ 거나 SERIALIZABLE 인 상태에서
      해당 구문을 트랜잭션에서 실행 중, 다른 트랜잭션으로 인해 작업대상 데이터가 변경이 되고, 그 후 트랜잭션을 완료하려고 하면 에러가 발생한다.
      
      
    • 해당 Lock 은 Delete 구문 및 특정 컬럼에서 수행되는 update 에 의해서도 발생 할 수 있다. (update 대상 컬럼이 외래키 및 unique index 로 걸려 있을 때)
      
      
  • FOR NO KEY UPDATE
    
    
    • FOR UPDATE 와 기능은 같으나 SELECT FOR KEY SHARE 는 허용하는 Lock 이다.
      
      
    • FOR UPDATE Lock 을 획득하지 않은 Update 구문이 획득 하는 Lock 이다.
      
      
  • FOR SHARE
    
    
    • FOR NO KEY UPDATE 와 기능은 같으나, 이로 인해 획득 되는 exclusive lock 대신에 shared lock 을 획득한다.
      이를 통해 UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE 수행을 막으나 SELECT FOR SHARE, SELECT FOR KEY SHARE 는 허용한다.
      
      
  • FOR KEY SHARE
    
    
    • FOR SHARE 와 기능은 같으나 여기서 SELECT FOR NO KEY UPDATE 를 허용하게 되어 키값으로 해당되지 않는 컬럼 값의 update 를 허용한다.
      
      
  • 위의 Lock 유형들에 대한 호환 및 충돌 가능 여부는 아래 표와 같다.
    
    

Requested Lock Mode	Current Lock Mode
	FOR KEY SHARE	FOR SHARE	FOR NO KEY UPDATE	FOR UPDATE
FOR KEY SHARE				X
FOR SHARE			X	X
FOR NO KEY UPDATE		X	X	X
FOR UPDATE	X	X	X	X

 

• Page-level Locks
  
  
  • PostgreSQL / PPAS 는 Shared Buffer Pool 내의 페이지 (데이터 블록) 단위의 읽기, 쓰기를 조절하기 위하여 페이지 내부적으로 해당 Lock 을 수행 할 수도 있다.
    
    
• Advisory Locks
  
  
  • 단순 트랜잭션 이외의 다수의 어플리케이션 (혹은 시스템) 에서 수행되는 트랜잭션들에 대한 Lock 을 조절하기 위하여 도입된 Lock 형태다.
    (어플리케이션 및 시스템 자체적으로 이에대한 Lock 을 통한 트랜잭션 통제를 할 수 없기 때문)
    
    
  • 크게 세션 단위 및 트랜잭션 단위에서 획득이 가능하며, 각 단위 에서의 Lock 은 아래의 특징을 가진다.
    
    
    • 세션 레벨의 Advisory Locks 는 특정 트랜잭션이 rollback 시 해소되는 일반적인 Lock 과는 다르게 rollback 후에도 어느정도 Lock 이 유지가 된다. 또한 나중에 실패하는 트랜잭션에 대해서도 unlock 이 유효하다.
      특정 세션에서 계속 호출되는 트랜잭션에 대해서도 처리 중 여러번 lock 을 획득 할 수 있다. 단, 이를 위해서는 각 Lock 획득 구간에 Lock 이 해소되는 요청이 있도록 해야 한다.
      
      
    • 트랜잭션 레벨의 Advisory Locks 는 세션 레벨과는 다르게 일반적은 Lock 규칙을 따른다.
      
      
  • 이를 탐지하기 위해서 아래의 함수를 사용 할 수 있다.
    
    
    • 세션 레벨 Lock 탐지 함수
      
      pg_advisory_lock(key bigint)
      
      pg_try_advisory_lock(key bigint)
      
      pg_advisory_unlock(key bigint)
      
      
    • 트랜잭션 레벨 Lock 탐지 함수
      
      pg_advisory_xact_lock(key bigint)
      
      pg_try_advisory_xact_lock(key bigint)