본 문서는 postgresql을 기반으로한 opensql 기술 문서입니다. 

개요

PostgreSQL에서 Lock은 여러 세션 간에 데이터 일관성을 유지하기 위해 사용되는 동기화 메커니즘입니다. 
여러 세션이 동시에 데이터에 접근할 때 데이터 일관성을 보장하고 서로간의 충돌을 방지하기 위해 사용됩니다.

방법

Lock 종류

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

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 구문을 수행 시 획득하는 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을 허용
• REFRES MATERIALIZED VIEW CONCURRENTLY 구문을 수행 할 때 획득

ACCESS EXCLUSIVE

• 모든 형태의 Lock 과 충돌 ( SELECT 구문도 막을 수 있는 유일한 Lock 형태 ) 
  그렇기 때문에 해당 Lock 이 발생하면 이 Lock을 획득한 트랜잭션을 수행 중임을 예상 할 수 있는 형태
• DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, REFRESH MATERIALIZED VIEW 구문을 수행 할 때 획득

Dead Lock 

데드락(Deadlock)은 둘 이상의 트랜잭션이 서로가 가진 리소스를 기다리는 상황으로, 두 트랜잭션이 모두 상대방이 가지고 있는 리소스를 기다리며 무한정 대기하게 되는 상태를 의미합니다. 

PostgreSQL에서 데드락을 시뮬레이션하려면 두 개 이상의 세션을 사용하여 다음과 같은 단계를 따를 수 있습니다.

데드락 시뮬레이션 테스트 예시 

테스트 데이터는 benchmarksql 로 생성합니다. 

1. 두 개의 세션 설정
   • 각각 다른 세션에서 두 개의 트랜잭션을 시작

-- 세션 1 
BEGIN ;
UPDATE public.bmsql_stock
SET s_order_cnt = s_order_cnt + 1,
WHERE s_quantity = 100;

-- 세션 2 
BEGIN ;
UPDATE public.bmsql_stock
SET s_order_cnt = s_order_cnt + 1,
WHERE s_quantity = 101;

2. 트랜잭션 간에 리소스를 기다릴 수 있도록 설정
   • 두 트랜잭션이 서로가 가진 리소스를 기다리도록 하기 위해 다음과 같이 쿼리를 실행

-- 세션 1
UPDATE public.bmsql_stock
SET s_order_cnt = s_order_cnt + 1,
WHERE s_quantity = 101;

-- 세션2
UPDATE public.bmsql_stock
SET s_order_cnt = s_order_cnt + 1,
WHERE s_quantity = 100;

3. 트랜잭션 커밋시도
   • 각 트랜잭션은 자신이 변경하려는 데이터에 대한 업데이트를 시도하고 커밋을 시도

-- 세션 1
COMMIT;

-- 세션2
COMMIT;

4. 데드락 발생 확인
   • 이 시나리오에서는 두 세션이 서로를 기다리는 상태가 되므로 데드락이 발생할 것 입니다.
   • PostgreSQL 에서는 데드락을 감지하고 하나의 트랜잭션을 롤백시키는 처리를 하기에 조절 후 테스트를 진행합니다.