[Oracle] Blocking Lock 발생과 해결 방법

오라클은 수 많은 락 종류가 있습니다. 그 중에서 Blocking Lock은 Lock 경합이 발생해 특정 세션의 작업을 진행하지 못하게 멈춰 선 경우를 의미하며, 커밋 또는 롤백으로 해소가 가능 합니다.

아래와 같은 예시는 FOR UPDATE구문을 예시로 Execute 단계에서 묵시적 형 변환에 의해서 Blocking Session이 발생 하게 되는데 그것을 확인 하겠습니다.

세션 1 (V$SESSION.SID : 142)

/* 
세션 파라미터를 조정하여 실행계획과 데이터 버퍼를 초기화 합니다.  
*/

alter session set session_cached_cursors = 10;

alter system flush shared_pool;

alter system flush buffer_cache;


/* 테이블 생성 */
CREATE TABLE EST_TKPROF AS
SELECT A.NAME, A.TYPE, A.LINE FROM DBA_SOURCE A
;

/* 테이블 생성 */
CREATE INDEX EST_TKPROF_IX01 ON EST_TKPROF (TYPE, NAME);


/*
통계정보 수집

compute statistics : Table내의 모든 데이터에 기초한 통계정보 생성
for table : 테이블에 대한 통계정보 생성
for all columns : 테이블의 모든 [인덱스]컬럼에 대해 히스토리그램 정보 생성
*/

analyze table EST_TKPROF compute statistics for table for all columns;

/*
Histogram 사용 안 함
*/
exec dbms_stats.gather_table_stats('SYSTEM', 'EST_TKPROF', method_opt=>'for all columns size 1');

SELECT    A.TABLE_NAME
         ,A.COLUMN_NAME
         ,A.ENDPOINT_NUMBER
         ,A.ENDPOINT_VALUE
FROM      DBA_HISTOGRAMS A
WHERE     A.TABLE_NAME = 'EST_TKPROF'
ORDER BY  A.TABLE_NAME, A.COLUMN_NAME, A.ENDPOINT_NUMBER;


/*
 SELECT절로 조회를 할 때 FOR UPDATE구문을 이용하여 TX락을 발생 시킵니다.
*/
SELECT    A.*
FROM      EST_TKPROF A 
WHERE     A.TYPE ='PACKAGE' 
FOR UPDATE
;
--------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name       | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |        |       |   884 (100)|          |       |       |          |
|   1 |  FOR UPDATE         |            |        |       |            |          |       |       |          |
|   2 |   BUFFER SORT       |            |        |       |            |          | 73728 | 73728 |          |
|*  3 |    TABLE ACCESS FULL| EST_TKPROF |  76557 |  2168K|   884   (2)| 00:00:11 |       |       |          |
--------------------------------------------------------------------------------------------------------------

TKPROF 정보를 확인해보면 아래와 같습니다. Execute Call 단계에서 Consistent Read와 Current Read가 발생하고, Fetch Call 단계에서 최종 물리적인 데이터 501개를 가져오게 됩니다.  

위와 같은 구문을 실행과 동시에 신규 세션(접속)에서 UPDATE절을 이용하여 데이터를 변경 하도록 하겠습니다. 실행과 동시에 Blocking Lock가 걸리게 되면서 해당 세션은 다른 쿼리에 영향을 주게 됩니다.

세션 2 (V$SESSION.SID : 22)

세션 1은 이미 SELECT를 마치고, Fetch Call 단계까지 마쳤으나, 세션 2는 계속 수행 중으로 나오게 되는 현상이 발생 했습니다. 이를 해결 하기 위해서는 세션을 끊거나 FOR UPDATE 구분을 조정해야 하겠지만, 오라클에서 Blocking Session을 확인 하겠습니다.

UPDATE    EST_TKPROF A
SET       A.LINE      =
            (SELECT    NVL(MAX(SQ.LINE), 1) + 1
             FROM      EST_TKPROF SQ
             WHERE     SQ.TYPE = 'PACKAGE'
             AND       SQ.NAME = 'STANDARD')
WHERE     A.TYPE = 'PACKAGE'
AND       A.NAME = 'STANDARD'
AND       A.LINE = 1

세션 1 (V$SESSION.SID : 142)

/*
 세션 1번
   - 세션 2번에서 Blocking LocK로 대기현상이 발생 할 것입니다. 따라서 현재 Blocking Session과 해당 쿼리를 조회 해보겠습니다.
*/

SELECT    B.BLOCKING_SESSION AS BLOCKING_SESSION_SID
         ,C.SID AS LOCK_SESSION_SID
         ,C.OWNER AS OBJECT_OWNER
         ,C.OBJECT AS OBJECT
         ,B.LOCKWAIT
         ,A.PIECE
         ,A.SQL_TEXT AS SQL
FROM      V$SQLTEXT A
         ,V$SESSION B
         ,V$ACCESS C
WHERE     A.ADDRESS = B.SQL_ADDRESS
AND       A.HASH_VALUE = B.SQL_HASH_VALUE
AND       B.SID = C.SID
AND       B.BLOCKING_SESSION IS NOT NULL
AND       C.OWNER NOT IN ('SYS', 'PUBLIC')
AND       C.OBJECT NOT IN ('TOAD_PLAN_TABLE')
ORDER BY  A.PIECE;

------------------------------
/* 조회 결과 */
SYSTEM 22 SYSTEM EST_TKPROF 000007FF23CDAB88 0 UPDATE    EST_TKPROF A SET       A.LINE      = (                
SYSTEM 22 SYSTEM EST_TKPROF 000007FF23CDAB88 1          SELECT    NVL(MAX(SQ.LINE), 1) + 1                     
SYSTEM 22 SYSTEM EST_TKPROF 000007FF23CDAB88 2     FROM      EST_TKPROF SQ                         WHERE     SQ
SYSTEM 22 SYSTEM EST_TKPROF 000007FF23CDAB88 3 .TYPE = 'PACKAGE'                         AND       SQ.NAME = 'S
SYSTEM 22 SYSTEM EST_TKPROF 000007FF23CDAB88 4 TANDARD'                         ) WHERE     A.TYPE = 'PACKAGE' 
SYSTEM 22 SYSTEM EST_TKPROF 000007FF23CDAB88 5 AND       A.NAME = 'STANDARD' AND       A.LINE = 1
------------------------------

Blocking Session은 1번 세션이고, Lock Session은 2번 세션인 것을 확인 할 수 있습니다. 이럴 때 실제로 락이 걸린 세션을 잡아서 KILL해주면 됩니다.

ALTER SYSTEM  KILL SESSION '22,43337' IMMEDIATE;