[SQL] RDBMS LOCK 조회 및 해제 방법 정리

필요할 때마다 검색해서 찾게 되는 RDBMS의 락 조회/해제 SQL을 하나의 글로 정리하였습니다.

---

락(Lock)이란?

락(Lock)은 다중 사용자 환경에서 데이터 무결성을 보호하기 위한 핵심 메커니즘입니다. 여러 사용자가 동시에 같은 데이터에 접근할 때 발생할 수 있는 충돌을 방지하고, 트랜잭션의 ACID(원자성, 일관성, 격리성, 지속성) 속성을 보장합니다.

주요 락 유형:

• 공유 락(Shared Lock): 읽기 작업을 위해 사용되며, 여러 트랜잭션이 동시에 획득 가능
• 배타적 락(Exclusive Lock): 쓰기 작업을 위해 사용되며, 한 트랜잭션만 획득 가능
• 행 락(Row Lock): 특정 행(레코드)에 대한 락
• 테이블 락(Table Lock): 테이블 전체에 대한 락
• 페이지 락(Page Lock): 데이터 페이지 단위의 락

락이 장시간 유지되거나 교착 상태(Deadlock)가 발생하면 시스템 성능 저하와 사용자 대기 시간 증가로 이어질 수 있습니다. 따라서 락을 효과적으로 모니터링하고 필요시 해제하는 방법을 아는 것이 중요합니다.

---

주요 RDBMS별 락 조회 방법

Oracle

Oracle에서는 여러 데이터 딕셔너리 뷰를 통해 락 정보를 조회할 수 있습니다.

현재 락 조회

-- 락 정보와 잠금 세션 조회
SELECT l.session_id, s.serial#, s.username, s.status, 
       l.lock_type, l.mode_held, l.mode_requested, 
       o.owner, o.object_name, o.object_type
FROM v$lock l
JOIN v$session s ON l.session_id = s.sid
JOIN v$locked_object lo ON l.session_id = lo.session_id
JOIN dba_objects o ON lo.object_id = o.object_id
WHERE s.username IS NOT NULL
ORDER BY l.session_id;

-- 특정 세션이 대기 중인 락 조회
SELECT s.username, s.sid, s.serial#, s.wait_class, s.seconds_in_wait,
       p.spid as "OS PID", s.blocking_session
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.blocking_session IS NOT NULL;

락 대기 조회

-- 락 대기 상황 조회
SELECT w.session_id waiting_session, 
       h.session_id holding_session,
       w.lock_type, 
       w.mode_requested,
       h.mode_held,
       w.lock_id1, 
       w.lock_id2
FROM v$lock w, v$lock h
WHERE w.mode_requested > 0
AND h.mode_held > 0
AND w.lock_id1 = h.lock_id1
AND w.lock_id2 = h.lock_id2
AND w.session_id != h.session_id;

MySQL

MySQL에서는 INFORMATION_SCHEMA 내의 다양한 테이블을 통해 락 정보를 확인할 수 있습니다.

InnoDB 트랜잭션 및 락 조회

-- 현재 진행 중인 트랜잭션 및 락 정보 조회
SELECT trx_id, trx_state, trx_started, trx_requested_lock_id,
       trx_wait_started, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx;

-- 트랜잭션이 보유하거나 대기 중인 락 조회
SELECT * FROM information_schema.innodb_locks;

-- 락 대기 관계 조회
SELECT * FROM information_schema.innodb_lock_waits;

프로세스 목록 조회

-- 프로세스 목록과 실행 중인 쿼리 조회
SHOW PROCESSLIST;

-- 더 상세한 프로세스 정보 조회
SELECT * FROM information_schema.processlist;

MySQL 8.0부터는 다음 테이블들로 대체되었습니다:

-- MySQL 8.0 이상에서의 락 조회
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

PostgreSQL

PostgreSQL에서는 여러 시스템 뷰를 통해 락 정보를 조회할 수 있습니다.

락 조회

-- 현재 대기 중인 락 조회
SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query AS blocked_statement,
       blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_locks.pid = blocked_activity.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocked_locks.transactionid = blocking_locks.transactionid
AND blocked_locks.pid != blocking_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_locks.pid = blocking_activity.pid
WHERE NOT blocked_locks.granted;

액티브 트랜잭션 조회

-- 활성 트랜잭션 조회
SELECT pid, usename, application_name, client_addr, 
       backend_start, xact_start, query_start, state, query
FROM pg_stat_activity
WHERE state = 'active';

SQL Server

SQL Server에서는 주로 동적 관리 뷰(DMV)를 사용하여 락 정보를 조회합니다.

락 조회

-- 현재 락 정보 조회
SELECT tl.resource_type,
       tl.resource_database_id,
       tl.resource_associated_entity_id,
       tl.request_mode,
       tl.request_session_id,
       t.text,
       es.login_name,
       es.host_name,
       es.program_name
FROM sys.dm_tran_locks tl
JOIN sys.dm_exec_sessions es ON tl.request_session_id = es.session_id
LEFT JOIN sys.dm_exec_requests er ON tl.request_session_id = er.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) t;

블로킹 세션 조회

-- 블로킹 세션 정보 조회
SELECT 
    blocked.session_id AS blocked_session_id,
    blocking.session_id AS blocking_session_id,
    sqltext.text AS blocked_query,
    DB_NAME(blocked.database_id) AS database_name,
    blocked.wait_type,
    blocked.wait_duration_ms,
    blocking_sqltext.text AS blocking_query
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_sessions blocking ON blocking.session_id = blocked.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(blocked.sql_handle) sqltext
OUTER APPLY sys.dm_exec_sql_text(
    (SELECT sql_handle FROM sys.dm_exec_requests WHERE session_id = blocked.blocking_session_id)
) blocking_sqltext
WHERE blocked.blocking_session_id > 0;

---

락 해제 방법

락을 해제하는 방법은 RDBMS에 따라 다르지만, 일반적으로 두 가지 접근 방식이 있습니다:

1. 세션/트랜잭션 종료
2. 특정 명령어를 사용한 직접 해제

Oracle 락 해제

-- 세션 정보 확인
SELECT sid, serial#, username, status FROM v$session WHERE username IS NOT NULL;

-- 특정 세션 강제 종료 (SID와 SERIAL# 사용)
ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

-- 예시
ALTER SYSTEM KILL SESSION '123,45678' IMMEDIATE;

MySQL 락 해제

-- 프로세스 목록 확인
SHOW PROCESSLIST;

-- 특정 프로세스 종료
KILL [프로세스_ID];

-- 예시
KILL 123;

-- 특정 쿼리 종료
KILL QUERY [프로세스_ID];

PostgreSQL 락 해제

-- 프로세스 목록 확인
SELECT pid, datname, usename, query FROM pg_stat_activity;

-- 특정 프로세스 종료
SELECT pg_terminate_backend([PID]);

-- 특정 프로세스의 현재 쿼리만 취소
SELECT pg_cancel_backend([PID]);

SQL Server 락 해제

-- 세션 정보 확인
SELECT session_id, login_name, status, program_name
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;

-- 특정 세션 종료
KILL [session_id];

-- 예시
KILL 123;

---

락 관련 문제 해결 팁

1. 락 경합 최소화 전략

   • 트랜잭션 시간 최소화
   • 한 번에 많은 데이터를 수정하는 작업 피하기
   • 적절한 인덱스 사용
   • 트랜잭션 격리 수준 조정

2. 교착 상태(Deadlock) 감지 및 해결

   • 교착 상태 로그 분석
   • 교착 상태를 유발하는 쿼리 패턴 식별
   • 교착 상태 해결 정책 설정

3. 정기적인 모니터링

   • 장기 실행 트랜잭션 모니터링
   • 락 대기 시간 모니터링
   • 교착 상태 발생 빈도 추적

4. 락 타임아웃 설정

   • 적절한 락 타임아웃 값 설정으로 무한정 대기 방지

---

결론

RDBMS에서 락은 데이터 무결성을 보장하는 필수적인 메커니즘이지만, 제대로 관리되지 않으면 성능 저하와 사용자 경험 악화로 이어질 수 있습니다.
락 관련 문제는 복잡한 경우가 많으므로, 실제 해제 작업을 수행하기 전에 항상 문제의 근본 원인을 파악하고 신중하게 대응하는 것이 중요합니다.