Lock과 Conflict 모니터링

사무실에서 누군가 방 안에 열쇠를 두고 나와서 모든 문이 잠겨버린 상황을 상상해봐. PostgreSQL의 lock도 딱 그런 느낌이야. 어떤 쿼리나 트랜잭션이 리소스를 lock하면, 같은 리소스에 접근하려는 다른 작업들은 그게 끝날 때까지 기다려야 해. 이러면 지연, conflict 상황이 생기고, 심하면 시스템이 멈출 수도 있어.

Lock은 언제 생길까?

PostgreSQL에서 lock(Locks)은 데이터에 동시 접근을 제어하려고 써. lock이 생기는 경우는:

1. 쓰기 작업할 때: UPDATE, DELETE, INSERT 같은 거.
2. 트랜잭션이 리소스를 필요 이상 오래 잡고 있을 때.
3. 여러 트랜잭션이 같은 리소스를 두고 충돌할 때.

진짜 데이터베이스는 리소스를 두고 "전쟁터"야. 시스템이 완벽하게 돌아가는 것 같아도, 한 번의 실수로 트랜잭션이 모든 걸 "멈춰버릴" 수 있어. Git에서 merge 꼬이는 거랑 비슷하지.

Lock 분석 도구: pg_locks

pg_locks는 PostgreSQL의 시스템 뷰로, 현재 트랜잭션이 잡고 있거나 기다리는 lock을 보여줘. 이걸로 "누가 lock을 잡고 있고, 누가 기다리는지" 알 수 있어.

pg_locks의 주요 필드:

• locktype: lock 타입 (예: relation, transaction, page, tuple 등).
• database: 데이터베이스 ID.
• relation: 테이블 ID (lock이 테이블에 걸린 경우).
• mode: lock 모드 (예: RowExclusiveLock, AccessShareLock 등).
• granted: lock이 이미 부여됐는지(true) 아니면 아직 기다리는 중인지(false) 표시하는 플래그.

참고: PostgreSQL은 "계층적 lock 모드"를 써. 즉, 어떤 작업은 덜 엄격한 lock(AccessShareLock처럼 읽기용)만 걸고, 어떤 건 더 강한 lock(ExclusiveLock처럼 테이블 구조 바꿀 때)을 걸어.

예시: 현재 모든 lock 보기

SELECT *
FROM pg_locks;

근데 pg_locks 전체를 그냥 보면 너무 복잡해. 좀 더 의미 있는 걸 해보자!

예시: 아직 부여되지 않은 lock(즉, 트랜잭션이 기다리는 중)

SELECT pid, locktype, relation::regclass AS table_name, mode, granted
FROM pg_locks
WHERE NOT granted;

여기서 뭐가 일어나냐면?

• granted = false인 row만 필터링해서, 아직 lock이 부여되지 않은 것만 보여줘.
• relation::regclass는 테이블 ID를 이름으로 바꿔서 보기 쉽게 해줘.

결과는 대충 이렇게 나올 수 있어:

이런 쿼리로 어떤 테이블/리소스가 lock되고 있고, 어떤 트랜잭션이 원인인지 찾을 수 있어.

Conflict 분석: pg_blocking_pids()

lock만 있으면 그나마 괜찮은데, 한 트랜잭션이 다른 걸 막고 있으면? PostgreSQL은 pg_blocking_pids() 함수로 "누가 문제냐"를 쉽게 찾게 해줘.

pg_blocking_pids() 함수는 현재 트랜잭션을 막고 있는 프로세스(pid) 리스트를 반환해.

예시: 다른 트랜잭션을 막고 있는 트랜잭션 찾기

SELECT pid, pg_blocking_pids(pid) AS blocking_pids
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

여기서 뭐가 일어나냐면?

• pg_stat_activity 뷰로 시스템의 active 프로세스를 가져와.
• pg_blocking_pids(pid) 함수가 각 pid를 막고 있는 프로세스 리스트를 반환해. 리스트가 비어있지 않으면(길이 0 초과), 그 프로세스는 막혀있는 거야.

예시 결과:

pid = 4567인 트랜잭션은 1234랑 5678 프로세스에 막혀 있어. "문제아"를 찾은 거지.

Blocking 프로세스 종료하기

blocking 프로세스를 찾았으면, pg_terminate_backend() 함수로 종료시킬 수 있어:

SELECT pg_terminate_backend(1234); -- 1234 프로세스 "죽이기"

근데 조심해야 해! 강제로 프로세스를 죽이면 해당 트랜잭션의 데이터가 롤백될 수 있어. 진짜 급할 때만 "핵버튼"처럼 써야 해.

실전: Lock 분석 시나리오

예를 들어, 대학 데이터베이스에 students랑 enrollments 테이블이 있다고 해보자. 여러 트랜잭션이 동시에 enrollments에 데이터를 쓰려고 하면 lock이 걸릴 수 있어.

1. lock 찾기:

SELECT pid, locktype, relation::regclass AS table_name, mode, granted
FROM pg_locks
WHERE NOT granted;

2. blocking 프로세스 찾기:

SELECT pid, pg_blocking_pids(pid) AS blocking_pids
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

3. lock 해제:

conflict 중인 프로세스 중 하나를 강제로 종료해:

SELECT pg_terminate_backend(1234); -- 1234 프로세스 종료

참고: 프로세스를 "죽이기" 전에 왜 lock이 생겼는지 먼저 파악해봐. 트랜잭션 로직을 다시 생각해보는 게 좋을 수도 있어.

흔한 실수와 예방법

lock은 트랜잭션을 잘못 관리해서 자주 생겨. 예를 들어:

실수: 한 트랜잭션이 아무 작업도 안 하면서 lock만 오래 잡고 있는 경우("idle in transaction" 상태).

해결법: pg_stat_activity로 트랜잭션 상태를 잘 모니터링하고, "멈춰있는" 트랜잭션은 종료해.

SELECT pid, state, query
FROM pg_stat_activity
WHERE state = 'idle in transaction';

실수: 쿼리에 인덱스를 안 써서 테이블 전체에 lock이 걸리는 경우.

해결법: 자주 쓰는 조건에 인덱스를 추가해서 쿼리를 최적화해.

"누가 누구를 기다리는지" 테이블 출력

진단을 쉽게 하려면, 어떤 트랜잭션이 누구를 막고 있는지 보여주는 dependency 트리를 만들 수 있어:

WITH RECURSIVE blocking_tree AS (
  SELECT pid, pg_blocking_pids(pid) AS blocked_by
  FROM pg_stat_activity
  WHERE cardinality(pg_blocking_pids(pid)) > 0
  UNION ALL
  SELECT a.pid, pg_blocking_pids(a.pid)
  FROM pg_stat_activity a
  JOIN blocking_tree b ON a.pid = ANY(b.blocked_by)
)
SELECT pid, blocked_by FROM blocking_tree;

결과:

여기서 보면 5678 프로세스가 1234를 막고, 1234는 4567을 막고 있어.