이 Study 는 기본적으로 [막힘없이 PostgreSQL - 액셈] 을 기본으로 하며, 각종 Open AI 및 공식 홈페이지, The Internals of PostgreSQL 를 참고하여 정리한 글입니다.
https://www.postgresql.org/docs/current/explicit-locking.html
정의
- PostgreSQL Lock은 데이터베이스에서 동시성 제어와 데이터 무결성을 보장하기 위해 사용하는 잠금(락) 메커니즘
- 여러 트랜잭션이 동시에 데이터에 접근하거나 수정할 때 충돌을 방지
Lock level
- Object level lock
- Object에 대한 변경이 발생할 때 object를 보호하는 역할
- Shared memory lock space 영역에 저장하며, 최대 동시 접속 수와 Transaction 최대 Lock 개수의 곱으로 정의
- (max_connectgions + max_jprepared_transactions) * max_locks_per_transaction
- max_locks_per_transaction
- default 64
- 하나의 Transaction 당 최대로 획득할 수 있는 object lock 수
- 하나의 Transaction 에서 파티션 테이블에 여러개인 파티션을 한번에 Query 하는 경우, 하나의 쿼리에서 Lock 이 조회한 파티션 수만큼 발생 가능성이 존재(유의)
- pg_catalog.pg_locks view 를 통해 object lock 정보를 확인이 가능
- Row level lock
- Tuple 에 대한 Lock
- MVCC 기반으로 작동하기 때문에 write 충돌 방지에만 사용
- shared memory 에 저장되는 것이 아닌 Page 내부의 Touple version 에 저장
- 메모리에 저장되는 것이 아니기 때문에, Lock 개수에는 영향을 받지 않음
- Lock 을 대기하는 프로세스를 대기 큐에 넣을 수 없기 때문에 모니터링이 힘듦
- Memory level lock
- PostgreSQL에서 말하는 Memory-level lock은 공유 메모리(shared memory) 내에서 이루어지는 경량 잠금(Lightweight Lock, LWLock) 또는 스핀락(spinlock) 계열의 잠금
- 서버 내부 동기화를 위한 메모리 구조 보호용
Memory level lock 종류
| 종류 |
설명 |
| Spinlock |
- CPU 단위의 아주 빠른 락
- 매우 짧은 작업 보호 (획득 시간이 매우 짧고 여러 프로세스가 별도의 메모리 영역을 변경하지 못하도록 보호 - 루프 방식으로 돌면서 Lock 획득)
- 사용 예
> 카운터 변수 증가
> flags 설정
> WALWriteLock 이전 준비 |
| LWLock (Lightweight Lock) |
- PostgreSQL 공유 메모리 내 구조체 보호용
- 읽기 모드(공유) / 쓰기 모드(배타) 지원
- 사용 예
> WALWriteLock : WAL 버퍼에 기록할 때
> BufMappingLock : shared buffer에 페이지 맵핑 시
> CLogControlLock : 트랜잭션 상태 제어
> ProcArrayLock : 트랜잭션 및 백엔드 배열 정보 제어
- PostgreSQL 내부에는 수백 개의 LWLock이 존재 |
| Buffer Pin Lock |
- 버퍼를 변경하기 위해 프로세스가 Buffer pin lock을 획득
- 특정 데이터 페이지가 Shared Buffer 에서 제거되지 않도록 보호
- 데이터를 참조하는 동안 해당 페이지가 교체(Evict) 되는 것을 방지
- 페이지 단위로 관리 |
| WAL Buffer Lock |
- WAL(Write-Ahead Logging) 메커니즘에서 WAL Buffer를 보호하고 동시성을 제어하기 위해 사용
- WAL Record 생성 → WALInsertLock 획득 요청 → Buffer 공간 예약 및 저장(필요 시) WALBufMappingLock 획득 후 WAL Page 확장 → WALInsertLock 해제 → (커밋 등 조건 시) WALWriteLock 획득 후 디스크로 Write
- WALInsertLock :WAL Buffer에 WAL Record를 저장할 때 획득하는 락(default 8)
- WALBufMappingLock : WAL Buffer 내에 새로운 WAL Page가 필요할 때(즉, 공간 확장이 필요할 때) 획득하는 락
- WALWriteLock :WAL Buffer의 내용을 디스크로 플러시(쓰기)할 때 사용하는 락 |
| Lock Tranche / Named Locks |
- LWLock 그룹화 및 모듈화 |
| Named LWLocks |
- 확장 기능이나 커널 모듈에서도 사용 가능 |
-- 현재 프로세스의 blocking 정보 (간접 확인 가능)
-- 메모리 수준 락은 pg_locks 에 안 나옴
-- row/table-level lock만 기록됨
SELECT * FROM pg_blocking_pids(<pid>);
- Advisory Lock (사용자 정의 Lock)
- Transaction 외부에서도 사용할 수 있는 Application level 의 lock
- Table이나 row와 직접 연결되지 않음
-- 세션 단위 Advisory Lock 획득
SELECT pg_advisory_lock(12345);
-- 세션 단위 Lock 해제
SELECT pg_advisory_unlock(12345);
#현재 Lock 확인: pg_locks 뷰와 pg_stat_activity를 조인하여 어떤 세션이 어떤 Lock을 보유/대기 중인지 확인
SELECT a.pid, a.usename, a.datname, a.state, a.query, l.locktype, l.mode, l.granted
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE a.state != 'idle';
주요 Lock 종류
| Access Share Lock |
SELECT 쿼리 실행 시 걸리는 기본 락. 다른 읽기 작업과 충돌하지 않음. |
| Row Share Lock |
외래키 제약조건이 있는 테이블에 INSERT, UPDATE 등 수행 시. 이름과 달리 테이블 수준의 락. |
| Row Exclusive Lock |
INSERT, UPDATE, DELETE 실행 시. 읽기 쿼리와는 충돌하지 않음. |
| Share Update Exclusive |
VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY 등 유지보수 명령 실행 시. |
| Share Lock |
테이블을 공유 모드로 잠금. 읽기는 가능하지만, 동시 업데이트는 불가. |
| Share Row Exclusive Lock |
CREATE TRIGGER, 일부 ALTER 명령 실행 시. |
| Exclusive Lock |
Materialized View 갱신 등에서 사용. |
| Access Exclusive Lock |
DROP TABLE, TRUNCATE 등 DDL 명령에서 사용. 모든 락과 충돌하는 가장 강력한 락. |
| Row-level Lock |
특정 행(row)에만 잠금. SELECT ... FOR UPDATE 등에서 사용. |
| Advisory Lock |
애플리케이션에서 명시적으로 사용하는 사용자 정의 락. |
Conflicting Lock Modes
| Requested Lock Mode |
Existing Lock Mode |
| Access share |
ROW SHARE |
ROW EXCL. |
SHARE UPDATE EXCL. |
SHARE |
SHARE ROW EXCL. |
EXCL. |
ACCESS EXCL. |
SQL |
| ACCESS SHARE |
|
|
|
|
|
|
|
X |
SELECT |
| ROW SHARE |
|
|
|
|
|
|
X |
X |
SELECT FOR UIPDATE/SHARE |
| ROW EXCL. |
|
|
|
|
X |
X |
X |
X |
INSERT, UPDATE, DELETE |
| SHARE UPDATE EXCL. |
|
|
|
X |
X |
X |
X |
X |
VACUUM, ALTER TABLE, CREATE INDEX CONCURRENCY |
| SHARE |
|
|
X |
X |
|
X |
X |
X |
CREATE INDEX |
| SHARE ROW EXCL. |
|
|
X |
X |
X |
X |
X |
X |
CREATE TRIGGER, ALTER TABLE |
| EXCL. |
|
X |
X |
X |
X |
X |
X |
X |
REFRESH MAT, VIEW CONCURRENTLY |
| ACCESS EXCL. |
X |
X |
X |
X |
X |
X |
X |
X |
DROP, TRUNCATE, VACUUM FULL, LOCK TABLE, ALTER TABLE, REFRESH MAT. VIEW |
-- waiting_pid, waiting_query 대기 중인 쿼리 및 세션 정보
-- holder_pid, holder_query 해당 잠금을 보유 중인 쿼리 및 세션
-- locked_relation 충돌 중인 테이블 이름
-- waiting_mode, holder_mode 잠금 모드 (RowExclusiveLock 등)
-- kill_holder_query 해당 holder 세션을 강제 종료하는 SQL 문
WITH lock_conflicts AS (
SELECT
w.pid AS waiting_pid,
w.query AS waiting_query,
w.usename AS waiting_user,
w.application_name AS waiting_app,
w.state AS waiting_state,
w.query_start AS waiting_query_start,
l1.relation::regclass AS locked_relation,
l1.mode AS waiting_mode,
h.pid AS holder_pid,
h.query AS holder_query,
h.usename AS holder_user,
h.application_name AS holder_app,
h.state AS holder_state,
h.query_start AS holder_query_start,
l2.mode AS holder_mode,
-- Kill query 자동 생성
'SELECT pg_terminate_backend(' || h.pid || ');' AS kill_holder_query
FROM
pg_locks l1
JOIN pg_stat_activity w ON l1.pid = w.pid
JOIN pg_locks l2 ON l1.locktype = l2.locktype
AND l1.database IS NOT DISTINCT FROM l2.database
AND l1.relation IS NOT DISTINCT FROM l2.relation
AND l1.page IS NOT DISTINCT FROM l2.page
AND l1.tuple IS NOT DISTINCT FROM l2.tuple
AND l1.virtualxid IS NOT DISTINCT FROM l2.virtualxid
AND l1.transactionid IS NOT DISTINCT FROM l2.transactionid
AND l1.classid IS NOT DISTINCT FROM l2.classid
AND l1.objid IS NOT DISTINCT FROM l2.objid
AND l1.objsubid IS NOT DISTINCT FROM l2.objsubid
AND l1.pid <> l2.pid
JOIN pg_stat_activity h ON l2.pid = h.pid
WHERE NOT l1.granted AND l2.granted
)
SELECT * FROM lock_conflicts
ORDER BY waiting_query_start;
Multi Transactions
- Shared lock mode는 서로 호환 가능한 lock mode 로, 요청할 경우 Multi transaction을 허용
- Multixact ID (multi transaction id)를 별도로 적용하여 shared lock상태를 저장
- Tuple 은 어느 시점에 Transaction ID와 Multixact ID가 동일한 값을 가질 수 있음
- Multi transaction 상태는 pgrowlocks extension 을 통해서도 확인 가능
- pgrowlocks를 조회하면 lock을 획득한 process, lock mode, tuple 정보 및 multi transaction 상태 등을 확인 가능
- 현재 상태만 확인이 가능하고, 종료되면 확인이 불가능
Tuple lock waiting
- Exclusive lock으로 인해 동일 모드로 lock을 요청하는 다른 Transaction 은 락을 대기할 수 밖에 없음.
- pg_locks View를 통해 확인이 가능
# pg_stat_activity : 현재 데이터베이스의 모든 세션 상태(쿼리, 접속자, 실행 시간 등)를 보여주는 뷰
# pg_locks : 현재 데이터베이스 서버의 모든 활성 세션이 보유하거나 대기 중인 Lock 정보를 보여주는 시스템 뷰
# 어떤 세션이 어떤 Lock을 보유하거나 대기 중인지, 쿼리 내용과 Lock 보유 시간까지 한 번에 확인
# datname: 데이터베이스명
# locked_relation: 잠금 대상(테이블명 등)
# mode: Lock 종류
# granted: Lock 보유 여부(true/false)
# query: 실행 중인 쿼리
# lock_age: Lock 보유 시간(경과 시간)
# pid: 프로세스 ID
SELECT
a.datname,
l.relation::regclass AS locked_relation,
l.transactionid,
l.mode,
l.granted,
a.usename,
a.query,
a.query_start,
age(now(), a.query_start) AS lock_age,
a.pid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
ORDER BY a.query_start;
# blocking session
# Lock으로 인해 대기 중인 세션과 이를 블로킹하는 세션을 한 번에 확인
SELECT
COALESCE(blockingl.relation::regclass::text, blockingl.locktype) AS locked_item,
now() - blockeda.query_start AS waiting_duration,
blockeda.pid AS blocked_pid,
blockeda.query AS blocked_query,
blockedl.mode AS blocked_mode,
blockinga.pid AS blocking_pid,
blockinga.query AS blocking_query,
blockingl.mode AS blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl
ON (
(blockingl.transactionid = blockedl.transactionid)
OR (blockingl.relation = blockedl.relation AND blockingl.locktype = blockedl.locktype)
) AND blockedl.pid != blockingl.pid
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
AND blockinga.datid = blockeda.datid
WHERE NOT blockedl.granted
AND blockinga.datname = current_database();
# https://blog.ex-em.com/1928
SELECT current_timestamp AS db_time,
waiter_pid,
w_info.usename AS waiter_user ,
w_info.query AS waiter_query ,
w_info.query_start AS waiter_query_start ,
case
when EXTRACT(EPOCH from current_timestamp - w_info.query_start ) < 0 then 0
else EXTRACT(EPOCH from current_timestamp - w_info.query_start )
end as waiter_elapsed_time,
holder_pid ,
h_info.usename AS holder_user ,
h_info.query AS holder_query ,
h_info.query_start AS holder_query_start,
case
when EXTRACT(EPOCH from current_timestamp - h_info.query_start ) < 0 then 0
else EXTRACT(EPOCH from current_timestamp - h_info.query_start )
end as holder_elapsed_time
FROM (
SELECT snaptime,
locktype,
waiter_pid,
w_cnt,
h_cnt ,
CASE
WHEN h_cnt=Max(h_cnt) OVER(partition BY waiter_pid) THEN holder_pid
END AS holder_pid
FROM (
SELECT current_timestamp AS snaptime,
blocked_locks.locktype AS locktype,
blocked_locks.pid AS waiter_pid,
count(*) over(partition BY blocked_locks.pid) AS w_cnt,
count(*) over(partition BY blocking_locks.pid) h_cnt,
blocking_locks.pid AS holder_pid
FROM pg_catalog.pg_locks blocked_locks
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.page IS NOT DISTINCT
FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT
FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT
FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT
FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT
FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT
FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT
FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
WHERE NOT blocked_locks.granted ) t ) t2
JOIN pg_catalog.pg_stat_activity w_info
ON w_info.pid = t2.waiter_pid
JOIN pg_catalog.pg_stat_activity h_info
ON h_info.pid = t2.holder_pid
WHERE holder_pid IS NOT null;
db_time |waiter_pid|waiter_user|waiter_query |waiter_query_start |waiter_elapsed_time|holder_pid|holder_user|holder_query |holder_query_start |holder_elapsed_time|
-----------------------------+----------+-----------+----------------------------------------------------+-----------------------------+-------------------+----------+-----------+----------------------------------------------------+-----------------------------+-------------------+
2024-04-30 12:08:49.078 +0900| 2914359|postgresdt |update lock_test set c2 = 'row_update' where c1 = 1;|2024-04-30 12:07:55.777 +0900| 53.301024| 2914369|postgresdt |update lock_test set c2 = 'row_update' where c1 = 1;|2024-04-30 12:07:50.784 +0900| 58.294596|
2024-04-30 12:08:49.078 +0900| 2914369|postgresdt |update lock_test set c2 = 'row_update' where c1 = 1;|2024-04-30 12:07:50.784 +0900| 58.294596| 2914377|postgresdt |update lock_test set c2 = 'row_update' where c1 = 1;|2024-04-30 12:07:45.711 +0900| 63.366759|
- Lock_timeout
- lock_timeout은 PostgreSQL에서 쿼리가 Table, Index , tuple 등 데이터베이스 객체의 잠금(Lock)을 획득하기 위해 대기하는 최대 시간(밀리초 단위)을 설정하는 parameter
- 설정 단위: 세션(Session) 또는 트랜잭션(Transaction) 단위로 설정 가능
- Default: 0 (disable, 무한정 대기)
- 적용 대상: 명시적/암시적 Lock 요청(예: DML, DDL, LOCK TABLE 등)
- 동작: 지정한 시간 내에 Lock을 획득하지 못하면 쿼리가 취소되고, canceling statement due to lock timeout 오류가 발생
- statement_timeout과 차이: statement_timeout은 쿼리 전체 실행 시간을 제한하지만, lock_timeout은 Lock 획득 대기 시간만 제한 -> 둘 다 설정되어 있으면, 먼저 도달한 타임아웃이 우선 적용
# ms 단위
set lock_timeout = 5000;
set lock_timeout = '5s';
ALTER TABLE mytable ADD COLUMN newcol int;
- Deadlock
- PostgreSQL은 데드락 상황을 자동으로 감지하고, 관련 트랜잭션 중 하나를 강제로 중단(ROLLBACK)시켜 교착상태를 해소.
- 어떤 트랜잭션이 중단될지는 예측할 수 없음
- deadlock_timeout parameter
- Deadlock 상황을 일정한 주기로 체크하여 감시하는 parameter
- Default : 1000ms
- Deadlock 방지 및 해결 전략
- 락 획득 순서 일관성 유지: 모든 트랜잭션이 동일한 순서로 Lock을 획득하도록 설계하면 데드락 발생 가능성을 크게 줄일 수 있음
- 작은 배치 크기: 한 번에 처리하는 데이터의 범위를 줄여 Lock 충돌 가능성을 낮춤
- 트랜잭션 분리: DDL(DROP, TRUNCATE 등) 작업과 DML(INSERT, UPDATE 등)을 분리하여 실행
- 재시도 메커니즘: 데드락 발생 시 자동으로 트랜잭션을 재시도하도록 애플리케이션을 설계
- Lock 대기 시간 제한: lock_timeout 파라미터를 설정해 장기 대기를 방지
