블로킹 및 락 위기

증상

쿼리가 멈추고 'lock wait timeout'이 발생하며, 많은 세션이 대기 중이지만 CPU는 낮다.

1. 블로킹 체인의 시작점(root blocker)을 찾는다

누가 누구를 막고 있는지 파악한다. 진짜 원인은 자신은 아무것도 기다리지 않으면서 다른 세션을 기다리게 하는 세션이다. 체인 맨 위의 세션에 집중한다.

Oracle

SELECT blocking_session, sid, serial#, username, seconds_in_wait, sql_id FROM v$session WHERE blocking_session IS NOT NULL ORDER BY seconds_in_wait DESC;

PostgreSQL

SELECT pid, usename, pg_blocking_pids(pid) blocked_by, state, query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid))>0;

MySQL

SELECT waiting_pid, waiting_query, blocking_pid, blocking_query FROM sys.innodb_lock_waits;

SQL Server

SELECT session_id, blocking_session_id, wait_type, wait_resource FROM sys.dm_exec_requests WHERE blocking_session_id<>0;

MongoDB

db.currentOp({ waitingForLock: true })

2. root blocker가 무엇을 하는지 파악한다

열려 있지만 커밋되지 않은 트랜잭션인가? 애플리케이션이 'idle in transaction' 상태로 두었는가? 긴 배치인가? 무작정 종료하기 전에 무엇인지 파악한다. 곧 끝날 수도 있다.

Oracle

SELECT s.sid, s.prev_sql_id, t.start_time, t.used_ublk FROM v$transaction t JOIN v$session s ON t.ses_addr=s.saddr;

PostgreSQL

SELECT pid, state, xact_start, now()-xact_start age, query FROM pg_stat_activity WHERE pid=<root_pid>;

3. 결정: 대기, 애플리케이션에서 해결, 또는 종료

곧 끝나면 기다린다. 애플리케이션이 커밋/롤백할 수 있으면 그렇게 하도록 한다. 그렇지 않고 업무가 멈췄다면 root blocker를 종료한다. 종료 전에 영향(롤백 시간, 데이터)을 고려한다.

4. 필요하면 root blocker를 종료한다

체인 맨 앞의 세션만 종료한다. 그 아래 세션들은 자동으로 해제된다.

Oracle

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

PostgreSQL

SELECT pg_terminate_backend(<root_pid>);

MySQL

KILL <root_id>;

SQL Server

KILL <root_session_id>;

MongoDB

db.killOp(<opid>)

5. 근본 원인을 해결한다

누락된 인덱스가 넓은 락을 유발할 수 있고, 애플리케이션이 트랜잭션을 너무 오래 열어 둘 수 있으며, 격리 수준이 잘못되었을 수 있다. 인덱스를 추가하고, 트랜잭션을 짧게 하고, 'idle in transaction' timeout을 설정하며, 적절한 격리 수준을 선택한다.