Dev Log Island

PostgreSQL AccessShareLock 이해 및 해제 가이드

AccessShareLock이란?

• Oracle의 TM LOCK과 유사: 데이터를 읽는 동안(SELECT) 테이블 구조 변경을 막기 위해 걸리는 락입니다.
• 성능에 미치는 영향: 일반적으로 성능 저하를 유발하지 않지만, 테이블 구조 변경 작업 시 방해가 될 수 있습니다.
• PostgreSQL에서의 역할: 데이터의 일관성 유지에 기여합니다.

Lock 조회 및 해제

1. Lock 조회:

SELECT  t.relname,
        l.locktype,
        page,
        virtualtransaction,
        pid,
        mode,
        granted
FROM pg_locks l,
     pg_stat_all_tables t
WHERE l.relation = t.relid
ORDER BY relation ASC;

• relname: 락이 걸린 테이블 이름
• locktype: 락 종류 (AccessShareLock 등)
• pid: 프로세스 ID
• mode: 락 모드

2. Lock 해제:

• 단일 프로세스 종료:

  SELECT pg_cancel_backend(PID);

  • 해당 PID의 프로세스만 종료합니다.
• 상위 쿼리까지 종료:

  SELECT pg_terminate_backend(PID) FROM pg_stat_activity;

  • PID와 관련된 모든 상위 쿼리까지 종료합니다.

3. 실행 중인 쿼리 상태 조회:

select * from pg_stat_activity;

주의 사항

• pg_terminate_backend는 강제 종료이므로 데이터 손실 가능성이 있습니다. 신중하게 사용해야 합니다.
• 락 해제 전 반드시 원인을 파악하고 해결하는 것이 좋습니다. 락이 자주 발생하는 경우, 쿼리 최적화나 데이터베이스 설정 변경이 필요할 수 있습니다.

요약

PostgreSQL의 AccessShareLock은 데이터 일관성을 위해 필요한 락입니다. 일반적으로 문제가 되지 않지만, 테이블 구조 변경 등 특정 상황에서 방해가 될 수 있습니다. 위의 SQL 쿼리를 사용하여 Lock을 조회하고 해제할 수 있으며, pg_terminate_backend를 사용할 때는 주의가 필요합니다.

더 자세한 정보는 PostgreSQL 공식 문서를 참고하세요.

핵심:

• AccessShareLock은 SELECT 시 걸리는 락으로, 일반적으로 성능에 영향을 주지 않습니다.
• Lock을 조회하고 해제하는 방법을 알아두면 문제 해결에 도움이 됩니다.
• pg_terminate_backend는 강력한 명령어이므로 신중하게 사용해야 합니다.

-- 락 걸린 테이블 확인
SELECT
    DO.OBJECT_NAME
  , DO.OWNER
  , DO.OBJECT_TYPE
  , DO.OWNER
  , VO.XIDUSN
  , VO.SESSION_ID
  , VO.LOCKED_MODE
FROM
    V$LOCKED_OBJECT VO
  , DBA_OBJECTS DO
WHERE VO.OBJECT_ID = DO.OBJECT_ID
;


-- 해당 테이블에 락이 걸렸는지 확인
SELECT
  A.SID
  , A.SERIAL#
  , B.TYPE
  , C.OBJECT_NAME
FROM
    V$SESSION A
  , V$LOCK B
  , DBA_OBJECTS C
WHERE A.SID = B.SID
  AND B.ID1 = C.OBJECT_ID
  AND B.TYPE='TM'
  AND C.OBJECT_NAME IN (${위 쿼리에서 가져온 OBJECT_NAME});


-- 락 발생 사용자, SQL, OBJECT 조회
SELECT
  DISTINCT X.SESSION_ID
  , A.SERIAL#
  , D.OBJECT_NAME
  , A.MACHINE
  , A.TERMINAL
  , A.PROGRAM
  , B.ADDRESS
  , B.PIECE
  , B.SQL_TEXT
FROM
    V$LOCKED_OBJECT X
  , V$SESSION A
  , V$SQLTEXT B
  , DBA_OBJECTS D
WHERE X.SESSION_ID = A.SID
  AND X.OBJECT_ID = D.OBJECT_ID
  AND A.SQL_ADDRESS = B.ADDRESS 
ORDER BY B.ADDRESS, B.PIECE;


-- 현재 접속자의 SQL 확인 
SELECT
    DISTINCT A.SID
  , A.SERIAL#
  , A.MACHINE
  , A.TERMINAL
  , A.PROGRAM
  , B.ADDRESS
  , B.PIECE
  , B.SQL_TEXT
FROM
    V$SESSION A
  , V$SQLTEXT B
WHERE A.SQL_ADDRESS = B.ADDRESS
ORDER BY A.SID, A.SERIAL#, B.ADDRESS, B.PIECE;


-- 락 세션 죽이기위한 sid 와 serial
SELECT
    A.SID
  , A.SERIAL#
  , A.SID || ', ' || A.SERIAL#
FROM
    V$SESSION A
  , V$LOCK B
  , DBA_OBJECTS C
WHERE A.SID = B.SID
  AND B.ID1 = C.OBJECT_ID
  AND B.TYPE = 'TM'
-- AND C.OBJECT_NAME IN ('${락 걸린 테이블 명}');


-- 해당 세선 kill
SELECT
    DISTINCT X.SESSION_ID
  , A.SERIAL#
  , D.OBJECT_NAME
  , A.MACHINE
  , A.TERMINAL
  , A.PROGRAM
  , A.LOGON_TIME
  , 'ALTER SYSTEM KILL SESSION '''||A.SID||', '||A.SERIAL#||''';'
FROM
    GV$LOCKED_OBJECT X
  , GV$SESSION A
  , DBA_OBJECTS D
WHERE X.SESSION_ID = A.SID
  AND X.OBJECT_ID = D.OBJECT_ID
ORDER BY LOGON_TIME
;


-- 최근 실행된 쿼리 찾기
SELECT 
    SQL_FULLTEXT
  , SQL_ID
  , PARSING_SCHEMA_NAME
  , SERVICE
  , MODULE
  , FIRST_LOAD_TIME
  , LAST_LOAD_TIME
FROM V$SQL
WHERE 1 = 1
  AND PARSING_SCHEMA_NAME = '${스키마 명}'
  AND SERVICE = '${서비스 명}'
ORDER BY LAST_LOAD_TIME DESC
;


-- 최근 실행된 쿼리에 바인팅 변수 찾기
SELECT
    NAME
  , POSITION
  , DATATYPE_STRING
  , VALUE_STRING 
FROM V$SQL_BIND_CAPTURE
WHERE 1=1
  AND SQL_ID = '${위 쿼리에서 가져온 SQL_ID}'
;

SELECT * FROM V$SQL;

SELECT 
  last_active_time
  ,parsing_schema_name
  ,sql_text 
FROM v$sqlarea
WHERE 
  parsing_schema_name <> 'SYS'
  AND parsing_schema_name <> 'SYSMAN'
  AND parsing_schema_name <> 'DBSNMP'
  AND parsing_schema_name <> 'MDSYS'
  AND parsing_schema_name <> 'EXFSYS'
ORDER BY 
  last_active_time DESC;

안타깝게도 어떤 쿼리가 락을 걸었는지는 알기 쉽지 않다.

특히나 Oracle DB S/W update를 주기적으로 하지 않는다면 유지보수가 쉽지 않다.