--락 상태 확인
SELECT relation :: regclass, mode, granted, pid, * FROM pg_locks;
-- 실행중인 쿼리 확인
SELECT * FROM pg_stat_activity ORDER BY query_start ASC;
-- 활동중인 쿼리만 확인 ('1 sec'를 적어 1초가 지난 'active' 상태인 쿼리 확인)
SELECT current_timestamp - query_start AS runtime, datname, usename, pid, query
FROM pg_stat_activity
WHERE state = 'active' AND current_timestamp - query_start > '1 sec'
ORDER BY 1 DESC
granted가 True 면 락이 허가되어 실행중
락 걸린 쿼리 중지
-- 해당 쿼리만 중지 (PID를 매개변수로 적어주면됨)
SELECT pg_cancel_backend(3209);
-- 위의 명령으로 잠금이 풀리지 않을때, 상위 명령까지 전부 종료
SELECT pg_terminate_backend(3209) FROM pg_stat_activity;
락 종류
크게 Exclusive Lock 과 Shared Lock으로 나눌수 있음
Shared Lock은 읽기 잠금(read lock)이라고도 하며, 데이터를 동시에 읽을수 있게 허용하지만, 변경은 불가능한 상태 (Shared Lock이 한개라도 걸려있으면 Exclusive Lock을 걸수 없음)
Exclusive Lock은 쓰기 잠금(write Lock)이라고도 하며, 해당 락을 건 트랜잭션이 완료 될때까지 테이블, 혹은 레코드를 다른 트랜잭션에서 읽거나 쓰지 못함
------------------------------------------
ACCESS SHARE– SELECT 명령은 쿼리에서 참조된 테이블에서 이 잠금을 획득,일반적인 규칙은 테이블을 읽기만 하는 모든 쿼리가 이 잠금을 획득합니다.
ROW SHARE– SELECT FOR UPDATE 및 SELECT FOR SHARE 명령은 대상 테이블에서 이 잠금을 획득합니다(쿼리에서 참조된 모든 테이블에 대한 ACCESS SHARE 잠금).
ROW EXCLUSIVE– UPDATE, INSERT 및 DELETE 명령은 대상 테이블에 대한 이 잠금을 획득합니다(쿼리에서 참조된 모든 테이블에 대한 ACCESS SHARE 잠금).일반적인 규칙은 테이블을 수정하는 모든 쿼리가 이 잠금을 획득한다는 것입니다.
SHARE UPDATE EXCLUSIVE– VACUUM(FULL 제외), ANALYZE, CREATE INDEX CONCURRENTLY 및 일부 형태의 ALTER TABLE 명령은 이 잠금을 획득합니다.
SHARE– CREATE INDEX 명령은 쿼리에서 참조하는 테이블에 대한 잠금을 획득합니다.
SHARE ROW EXCLUSIVE– 어떤 명령으로도 암시적으로 획득되지 않습니다.
EXCLUSIVE– 이 잠금 모드에서는 이 잠금을 획득한 트랜잭션과 병렬로 읽기만 처리할 수 있습니다.명령에 의해 암시적으로 획득되지 않습니다.
ACCESS EXCLUSIVE– ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER 및 VACUUM FULL 명령은 쿼리에서 참조하는 테이블에 대한 잠금을 획득합니다.이 모드는 LOCK 명령의 기본 모드입니다.
최근 인덱스에 문제가 있어 Drop index 또는 Reindex index 를 통해 인덱스를 수정하려던 중 작동하지 않아 명령을 취소하였더니, 해당 테이블이 먹통이 돼버렸음 (원래 20ms 면 나오던 데이터가 10000ms를 넘겨서 나오는 현상이...)
해서 인덱스 상태를 확인한 결과 해당 테이블의 인덱스가 동작하지 않고있었음 (새로 추가되는 데이터만 인덱스 fetch되고 있었음)
-- Index 확인 쿼리
SELECT
schemaname AS schema_name,
relname AS table_name,
indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;
그래서 reindex 명령으로 인덱스를 살리려고 노력하는데 명령이 몇시간이 지나도 끝나지 않았음,
SELECT relation :: regclass, mode, granted, pid, * FROM pg_locks
확인해보니 granted가 막혀있었음, 다른 트랜잭션에서 이미 락을 걸고 사용중이었음, 사이트에 데이터가 너무 느리게 뜨자, 여기저기서 전화가;;;
급한대로 빨리 reindex를 해서 인덱스를 살려야 해서, 해당 relation과 관련된 락을 건 pid를 중지 시켰음
SELECT pg_cancel_backend(5957);
-- 위의 명령으로 중지해서는 먹히지 않아 아래껄로 중지함
SELECT pg_terminate_backend(5957) FROM pg_stat_activity;
첫번째 명령으로 처리가 안되어, 급한대로 아래의 pg_terminate_backend를 사용하여 상위 프로세스 까지 중지시켰음
