시스템 관리_37 운영 데이터베이스 블로킹 세션 및 데드락 식별과 안전한 강제 종료 해결 가이드

# 1. PostgreSQL: pg_blocking_pids() 함수를 이용한 블로킹 트리 및 락 대기 세션 즉시 조회
$ psql -U postgres -d mydb -c "
SELECT 
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    now() - blocked.query_start AS blocked_duration,
    blocked.query AS blocked_query,
    blocking.query AS current_blocking_query
FROM pg_stat_activity blocked
JOIN unnest(pg_blocking_pids(blocked.pid)) blk(pid) ON true
JOIN pg_stat_activity blocking ON blocking.pid = blk.pid
WHERE NOT blocked.wait_event IS NULL
ORDER BY blocked_duration DESC;
"

# 2. MySQL / MariaDB: sys.innodb_lock_waits 뷰를 이용한 블로킹 및 블록당한 스레드 추적
$ mysql -u root -p -e "
SELECT 
    waiting_account,
    waiting_pid,
    waiting_query,
    blocking_account,
    blocking_pid,
    blocking_query,
    wait_age
FROM sys.innodb_lock_waits;
"

# 3. SQL Server (Linux/Windows): sys.dm_os_waiting_tasks로 블로커 SPID 식별
$ sqlcmd -S localhost -U sa -P 'MyStrongPassword123!' -Q "
SELECT 
    blocking_session_id AS Blocker_SPID,
    session_id AS Blocked_SPID,
    wait_type,
    wait_duration_ms,
    resource_description
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id IS NOT NULL AND blocking_session_id != 0;
"

# 4. OS 관점: 해당 블로커 프로세스가 CPU 폭주 중인지 I/O 대기(D 상태)인지 확인
$ ps -ef | grep -E "postgres|mysqld|oracle|sqlservr" | grep -v grep

#!/bin/bash
# find_blocking_sessions.sh - Identify and safely terminate database blocking sessions (PostgreSQL)

DB_NAME=${1:-"postgres"}
DB_USER=${2:-"postgres"}
MIN_SECONDS=${3:-10}

echo "=========================================="
echo " [운영 DB 블로킹 세션 진단] 대상 DB: ${DB_NAME}"
echo " 최소 대기 시간 기준: ${MIN_SECONDS}초 이상"
echo "=========================================="

if ! command -v psql &>/dev/null; then
    echo "  [오류] psql 유틸리티를 찾을 수 없습니다."
    exit 1
fi

# 1. 블로킹 관계 트리 및 지속 시간 쿼리 실행
QUERY="
WITH RECURSIVE lock_tree AS (
    SELECT 
        bl.pid AS blocked_pid,
        pg_blocking_pids(bl.pid) AS blocker_pids,
        now() - bl.query_start AS duration,
        bl.state,
        bl.query AS blocked_query
    FROM pg_stat_activity bl
    WHERE cardinality(pg_blocking_pids(bl.pid)) > 0
)
SELECT 
    l.blocker_pids[1] AS root_blocker,
    l.blocked_pid,
    EXTRACT(EPOCH FROM l.duration)::int AS duration_sec,
    l.state,
    LEFT(REGEXP_REPLACE(l.blocked_query, '\s+', ' ', 'g'), 60) AS summary_query
FROM lock_tree l
WHERE EXTRACT(EPOCH FROM l.duration) >= ${MIN_SECONDS}
ORDER BY duration_sec DESC;
"

BLOCK_RESULTS=$(psql -U "$DB_USER" -d "$DB_NAME" -t -A -F'|' -c "$QUERY" 2>/dev/null)

if [ -z "$BLOCK_RESULTS" ]; then
    echo "  [정상] ${MIN_SECONDS}초 이상 지연 중인 블로킹 세션이 없습니다."
    exit 0
fi

echo -e "\n[1] 락 대기 상태인 세션 목록:"
printf "  %-12s | %-12s | %-10s | %-12s | %-30s\n" "원인PID(Blocker)" "대기PID(Blocked)" "지연(초)" "상태" "대기 쿼리 요약"
echo "  -------------------------------------------------------------------------------------"

ROOT_BLOCKERS=()

while IFS='|' read -r BLOCKER BLOCKED DUR STATE QUERY_TEXT; do
    [ -z "$BLOCKER" ] && continue
    printf "  %-12s | %-12s | %-10s | %-12s | %-30s\n" "$BLOCKER" "$BLOCKED" "${DUR}s" "$STATE" "$QUERY_TEXT"
    ROOT_BLOCKERS+=("$BLOCKER")
done <<< "$BLOCK_RESULTS"

# 2. 최상위 블로커(Root Blocker) 세부 정보 분석
UNIQUE_BLOCKERS=$(echo "${ROOT_BLOCKERS[@]}" | tr ' ' '\n' | sort -u)

echo -e "\n[2] 최상위 원인 세션(Root Blocker) 상세 정보:"
for BPID in $UNIQUE_BLOCKERS; do
    echo "------------------------------------------------------------------"
    echo "  >> 블로커 PID: ${BPID}"
    psql -U "$DB_USER" -d "$DB_NAME" -x -c "
        SELECT 
            pid, usename, client_addr, state,
            now() - state_change AS idle_duration,
            now() - xact_start AS transaction_duration,
            query
        FROM pg_stat_activity 
        WHERE pid = ${BPID};
    " 2>/dev/null | sed 's/^/    /'
    
    echo "  [권장 긴급 조치 명령어]:"
    echo "  - 부드러운 쿼리 취소 : SELECT pg_cancel_backend(${BPID});"
    echo "  - 강제 세션 연결 단절: SELECT pg_terminate_backend(${BPID});"
done

echo -e "\n=========================================="
echo " [진단 완료]"
echo "=========================================="