[PostgreSQL] DB Lock

명시적 lock (Explicit Lock)

PostgreSQL은 여러 lock 모드들을 사용하여 테이블의 데이터를 동시 접근을 제어합니다. 이와 같은 lock 모드는 다중 버전 동시성 제어(MVCC)를 제공하지 않는 상황에서 어플리케이션을 제어하는 lock을 쓸 수 있고 대부분의 PostgreSQL 명령들은 해당 명령이 실행되는 동안 명령 실행의 대상이 되는 테이블이 삭제되거나 수정되지 않도록 적절한 모드의 lock을 자동으로 얻습니다. 예를 들어, 특정 테이블에서 truncate가 실행되는 동안 다른 작업이 실행되지 않도록 해당 테이블에 배타적 lock(exclusive lock)을 얻습니다.

동시성 제어 (Concurrency Control)

동시성 제어란 DBMS가 다수의 사용자 사이에서 동시에 작동하는 트랜잭션에서 DB를 보호하는 것을 의미합니다. 한 마디로 동일한 테이블, 테이블의 데이터에 동시에 쓰기, 수정, 삭제 등으로부터 보호하는 의미입니다. 동시성을 허용하면 일관성이 낮아지게 됩니다. 동시성을 제어하기 위해 Lock과 SET TRANSACTION 명령어를 사용해 트랜잭션의 격리성 수준(transaction isolation level)을 조절할 수 있는 기능을 제공합니다. 또한 동시성을 제어하는 방법으로 비관적 동시성 제어(Pessimistic Concurrency Control)와 낙관적 동시성 제어(Optimistic Concurrency Control)가 있습니다.

비관적 동시성 제어 (Pessimistic Concurrency Control)

사용자들이 같은 데이터를 동시에 수정할 것이라고 가정을 하고 데이터를 읽는 시점에 lock을 걸고 트랜잭션이 완료가 될 때까지 lock 상태를 유지합니다. 즉, select 시점에 lock을 걸기 때문에 데이터의 일관성을 확실히 지킬 순 있지만 시스템의 동시성을 저하시킬 수 있습니다. 따라서 옵션으로 nowait(lock이 풀릴 때까지 대기) 이나 wait(지정한 초만큼 대기)를 주어야 합니다. nowait이나 wait에 너무 긴 시간을 주게 된다면 대기 상태가 엄청나게 길어져 기아 상태(starvation)에 빠질 수 있으므로 적절한 시간을 주어야 합니다.

낙관적 동시성 제어 (Optimistic Concurrency Control)

사용자들이 같은 데이터를 동시에 수정하지 않을 것이라고 가정을 합니다. 비관적 동시성 제어와 다르게 데이터를 읽는 시점에 lock을 걸지 않기 때문에 수정 시점에 값이 변경 됐는지 검사해야 합니다. 보통 낙관적 동시성 제어를 구현할 때, version과 같은 플래그성 컬럼을 사용합니다. 수정이 일어날 때마다 version을 1씩 올리기 때문에 조회한 시점의 version과 수정하려할 때 version이 다르면 수정을 진행하지 않습니다.

lock의 문제점은 read와 write가 서로 간섭을 하기 때문에 동시성 문제가 발생하고 데이터 일관성에 문제가 생기는 경우가 있어 lock을 더 오래 유지하거나 테이블 단위의 lock을 사용하기도 합니다. 이럴 경우 동시성이 저하됩니다. 이러한 문제를 해결하기 위해 MVCC가 탄생했습니다.

다중 버전 동시성 제어 (Multi-Version Concurrency Control, MVCC)

MVCC는 동시 접근을 허용하는 데이터베이스에서 동시성을 제어하기 위해 사용하는 방법 중 하나입니다. MVCC 모델에서 데이터에 접근하는 사용자는 해당 시점에 데이터베이스의 snapshot을 읽습니다. 이 snapshot 데이터에 대한 변경이 완료될 때 (또는 트랜잭션이 완료될 때)까지 만들어진 변경사항은 다른 데이터베이스의 사용자가 볼 수 없습니다. 사용자가 데이터를 업데이트 하면 이전의 데이터를 덮어 씌우는 것이 아니라 새로운 버전의 데이터를 이전 버전의 데이터와 비교해서 변경된 내용을 UNDO에 생성합니다. 이러한 방식으로 하나의 데이터에 대해 여러 버전의 데이터가 존재하게 되고 사용자는 마지막 버전의 데이터를 읽게 됩니다. 

이러한 방식은 lock을 사용하지 않기 때문에 RDBMS보다 빠르게 동작합니다. 또한 데이터를 읽을 때, 다른 사용자가 해당 데이터를 삭제, 수정해도 영향을 받지 않습니다. 데이터는 여러 버전으로 존재하기 때문에 주기적으로 데이터를 정리해야 합니다. MVCC 모델은 하나의 데이터에 대한 여러 버전의 데이터를 허용하기 때문에 데이터 버전이 충돌될 수 있으므로 애플리케이션 영역에서 이러한 문제를 해결해야 합니다. 또한 UNDO 블록 I/O, CR Copy 생성, CR 블록 캐싱 같은 부가적인 작업의 오버헤드가 발생합니다. 

배타적 Lock (Exclusive Lock)

write lock이라고도 하며 트랜잭션에서 데이터를 변경하고자 할 때, 해당 트랜잭션이 완료될 때까지 테이블 또는 row를 다른 트랜잭션에서 읽거나 쓰지 못하게 합니다.

공유 Lock (Shared Lock)

read lock이라고도 하며 트랜잭션에서 데이터를 읽고자 할 때, 다른 shared lock은 허용하지만 exclusive lock은 불가능합니다. 즉, 동시에 읽을 순 있지만 읽기와 쓰기는 동시에 할 수 없는 것입니다.

테이블 수준의 lock

아래는 사용 가능한 lock 모드와 PostgreSQL에서 자동으로 사용되는 context입니다. lock 명령어를 통해 명시적으로 lock을 얻을 수 있습니다. 아래 리스트에서 row라는 단어가 포함 되더라도 여기서 설명하는 lock 모드는 모두 테이블 수준의 lock입니다. 이름은 각 lock 모드의 일반적인 사용법에 대한 의미를 내포하지만 테이블 수준의 lock을 거는 것은 동일합니다. lock의 수준에 따라 동일한 테이블에 2개 이상의 lock이 잡힐 수도 있고 없을 수 있습니다.

테이블 수준의 lock 모드

ACCESS SHARE

ACCESS EXCLUSIVE lock 과 충돌하고 나머지 lock과는 충돌하지 않습니다. 보통 SELECT 명령으로 대상 테이블에서 해당 lock을 얻을 수 있습니다. 즉, 테이블을 읽기만 하고 수정, 삭제 등을 하지 않는 쿼리에서 해당 lock을 얻습니다.

-- 1번 프로세스 

BEGIN ; 
SELECT * 
FROM public.test;

 -- 2번 프로세스 
select locktype, relation::regclass, mode, transactionid tid, pid, granted 
from pg_catalog.pg_locks 
where not pid = pg_backend_pid() and relation::regclass::text not like 'pg%';

ROW SHARE

EXCLUSIVE와 ACCESS EXCLUSIVE lock과 충돌합니다. SELECT FOR UPDATE 와 SELECT FOR SHARE 명령으로 대상 테이블에서 해당 lock을 얻을 수 있습니다.

-- 1번 프로세스 
BEGIN ; 
SELECT * 
FROM public.test for update; 

-- 2번 프로세스 
select locktype, relation::regclass, mode, transactionid tid, pid, granted 
from pg_catalog.pg_locks 
where not pid = pg_backend_pid() and relation::regclass::text not like 'pg%';

ROW EXCLUSIVE

SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE와 lock이 충돌합니다. UPDATE, DELETE, INSERT 명령으로 대상 테이블에서 해당 lock을 얻을 수 있습니다. 일반적으로 이 lock 모드는 테이블의 데이터를 수정하는 모든 명령어에서 얻을 수 있습니다.

-- 1번
BEGIN ;
update test
set quantity = quantity + 1
where id = 1;

-- 2번
select locktype, relation::regclass, mode, transactionid tid, pid, granted
from pg_catalog.pg_locks
where not pid = pg_backend_pid() and relation::regclass::text not like 'pg%';

SHARE UPDATE EXCLUSIVE

SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE lock과 충돌합니다. 해당 모드는 스키마 동시 변경 또는 VACUUM 실행으로부터 테이블을 보호합니다. VACUUM (FULL X) ANALYZE, CREATE INDEX CONCURRENTLY, ALTER TABLE VALIDATE 및 기타 ALTER TABLE에 의해 lock을 얻습니다.

begin을 실행하고 CREATE INDEX CONCURRENTLY 나 VACUUM ANALYZE 을 진행하면 블록이 걸려서 스샷을 못찍음

SHARE

ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE 및 ACCESS EXCLUSIVE lock과 충돌합니다. CREATE INDEX (CONCURRENCY 옵션 X) 에서 해당 lock을 얻을 수 있습니다.

SHARE ROW EXCLUSIVE

ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE 및 ACCESS EXCLUSIVE lock과 충돌합니다. 해당 모드는 동시 데이터 변경으로부터 테이블을 보호하고 한 번에 하나의 세션만 테이블을 차지할 수 있도록 처리합니다. CREATE TRIGGER와 다양한 형태의 ALTER TABLE에 의해 lock을 얻습니다.

EXCLUSIVE

ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE 및 ACCESS EXCLUSIVE lock과 충돌합니다. 해당 모드는 ACCESS SHARE lock만 허용합니다. 즉, 테이블에서 데이터를 읽고만 있는 트랜잭션이 있다면 병렬로 해당 lock을 잡을 수 있습니다. REFRESH MATERIALIZED VIEW CONCURRENTLY에 의해 lock을 얻습니다.

ACCESS EXCLUSIVE

모든 lock과 충돌합니다. 해당 모드는 모든 lock과 충돌하기 때문에 일관성이 가장 유지가 되는 lock입니다. DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, REFRESH MATERIALIZED VIEW (CONCURRENTLY 옵션 X) 명령에 의해 얻을 수 있고 많은 ALTER TABLE 명령어에서도 해당 lock을 얻을 수 있습니다. lock 모드를 명시적으로 지정하지 않는다면 LOCK TABLE 구문 기본인 lock입니다.

 

일단 lock을 얻으면 트랜잭션이 끝날 떄까지 유지가 됩니다. 그러나 savepoint를 설정한 다음, lock을 획득하게 되면 savepoint가 롤백될 때 lock이 해제됩니다. 이는 ROLLBACK이 savepoint 이후의 명령을 모두 취소하는 원칙과 동일합니다.

 

아래의 표는 lock끼리 충돌을 나타내는 표입니다.

행 수준의 lock

row lock은 테이블 lock과는 다르게 한 행에 대해서만 lock을 획득합니다. 하나의 행에만 lock을 걸기 때문에 테이블이나 동일한 테이블의 다른 행에는 영향을 주지 않습니다. 테이블 lock과 마찬가지로 트랜잭션이 종료되거나 savepoint 가 rollback된다면 해제됩니다.

행 수준의 lock 모드

FOR UPDATE

FOR UPDATE는 SELECT 문에 의해 검색된 행을 업데이트를 위해 lock을 겁니다. 트랜잭션이 끝날 때까지 다른 트랜잭션이 lock을 얻을 수 없으므로 수정이나 삭제를 할 수 없습니다. 해당 lock이 걸려있는 행에서 UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE 또는 SELECT FOR KEY SHARE를 시도한다면 lock이 풀릴때까지 차단됩니다. FOR UPDATE lock은 행의 DELETE와 특정 열의 값을 수정하는 UPDATE에 의해 얻을 수 있습니다. 

FOR NO KEY UPDATE

FOR UPDATE보단 유한 lock입니다. 동일한 행에서 lock을 얻으려고 하는 SELECT FOR KEY SHARE 명령을 차단하지 않습니다. 해당 lock은 FOR UPDATE lock을 얻지 않은 모든 UPDATE 에서 얻을 수 있습니다.

FOR SHARE

shared lock은 다른 트랜잭션이 해당 행에 대해 UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE를 수행하지 못하도록 차단하지만 SELECT FOR SHARE, SELECT FOR KEY SHARE는 막지 않습니다.

FOR KEY SHARE

SELECT FOR NO KEY UPDATE, SELECT FOR SHARE, SELECT FOR KEY SHARE를 차단하지 않습니다.

 

아래의 표는 lock끼리 충돌을 나타내는 표입니다.

 

 

일단 문서를 번역하고 실제 쿼리를 날려보면서 해당 락이 잡히는지 확인하려 했지만 뭔가 어려움

• 문서대로면 select for update끼리는 허용이 돼야 하지만 where 절을 제거하고 진행하면 lock이 충돌나서 대기를 하게 됨. 행 수준의 lock을 잡으려고 해서 테이블의 전체 row에 대해 lock을 잡은거 같은데 확실치가 않음

 

참고 문서: https://www.postgresql.org/docs/9.6/explicit-locking.html

https://medium.com/29cm/db-postgresql-lock-%ED%8C%8C%ED%97%A4%EC%B9%98%EA%B8%B0-57d37ebe057

https://mangkyu.tistory.com/53

https://jeong-pro.tistory.com/94