DB Lock & Current 트랜잭션

Tibero DB Lock과 트랜잭션에 대하여 설명합니다.

Current 세션 수

대상 데이터베이스에 접속되어 있는 모든 세션과 현재 작업 중인 세션의 수, Total WPM(Working Process Memory)을 확인합니다.

세션의 상태는 READY, RUNNING, TX_RECOVERING 등이 있습니다.

Single DB

다음은 Single DB 쿼리 작성 예입니다.

SELECT to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') "Time", 
       "Working Process Memory",
       a.tots "Total Session" , 
       b.run "Running Session"
FROM (SELECT SUM(pga_used_mem) "Working Process Memory",
            COUNT(*) tots 
       FROM v$session) a, 
       (SELECT COUNT(*) run
       FROM v$session
       WHERE status='RUNNING') b;

결과는 아래와 같습니다.

Time                   Working Process Memory     Total Session   Running Session
-------------------    -----------------------   --------------   ---------------
2023/07/04 14:21:45                   8872424                14                10

1 row selected.

각 항목에 대한 설명은 아래의 “TAC DB” 내용을 참고합니다.

TAC DB

다음은 TAC DB 쿼리 작성 예입니다.

SELECT  a.INST_ID,
        to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') "Time", 
        "Work Proc Memory",
        a.tots "Tot Session",
        b.run "Run Session" 
FROM (SELECT INST_ID,
                SUM(pga_used_mem) "Work Proc Memory",
                COUNT(*) tots 
        FROM gv$session 
        group by inst_id) a, 
        (SELECT INST_ID,
                COUNT(*) run 
        FROM gv$session
        WHERE status='RUNNING'
        group by inst_id) b
WHERE a.INST_ID = b.INST_ID(+);

결과는 아래와 같습니다.

INST_ID     Time                    Work Proc Memory     Tot Session     Run Session
--------    --------------------    -----------------    ------------    ------------
    1       2023/07/04 14:24:59            8703984                14             11
    2       2023/07/04 14:24:59             653512                 5              1
    
2 rows selected.

각 항목에 대한 설명은 다음과 같습니다.

항목	설명
INST_ID	Tibero 인스턴스 정보 (TAC DB 질의 쿼리)
Time	쿼리를 날린 현재 시간
Work Proc Memory	현재 모든 세션이 사용하고 있는 PGA의 전체 크기
Tot Session	접속 중인 총 세션의 수
Run Session	실제 작업 중인 세션의 수

총 세션의 상당수가 Running 세션이라면 Lock과 Current 트랜잭션 정보를 확인하여 적절한 조치를 합니다.

---

Current 트랜잭션

현재 진행 중인 트랜잭션들의 목록과 관련 정보를 확인합니다.

Single DB

다음은 Single DB 쿼리 작성 예입니다.

SELECT  distinct vs.sid, 
        vs.serial#, 
        vs.username, 
        va.object, 
        vs.status, 
        vt.used_blk, 
        vt.usn, 
        vt.start_time,
        floor(mod((sysdate - vt.start_time)*24, 24)) || ':'|| 
        lpad(floor(mod((sysdate - vt.start_time)*1440, 60)), 2, 0) ||':'||
        lpad(floor(mod((sysdate - vt.start_time)*86400, 60)), 2, 0) AS "Transaction Time",
vst.sql_text
FROM v$session vs,
vt_transaction vt, 
v$sqltext vst, 
v$access va
WHERE vt.sess_id = vs.sid
        and vt.sess_id = va.sid
        and nvl(vs.sql_id, vs.prev_sql_id) = vst.sql_id 
ORDER BY 1;

결과는 아래와 같습니다.

SID   SERIAL#  USERNAME   OBJECT           STATUS    USED_BLK    USN   START_TIME  Transaction Tim    SQL_TEXT                      
----- -------- --------- ----------------  -------- --------- -------  ----------- -----------------  --------------
 135     9907  TPCC       BMSQL_NEW_ORDER  RUNNING          1      9   2023/07/04          0:00:05     DELETE FROM bmsql_new_order  
                                                                                                      
                                                                                                       WHERE no_w_id = ? AND no_d_id = ?
                                                                                                      
                                                                                                       AND no_o_id = ?    

각 항목에 대한 설명은 다음의 “TAC DB” 내용을 참고합니다.

TAC DB

다음은 TAC DB 쿼리 작성 예입니다.

SELECT distinct vs.inst_id, 
      vs.sid,
      vs.serial#, 
      vs.username, 
      va.object, 
      vs.status, 
      vt.used_blk, 
      vt.usn, 
      vt.start_time,
      floor(mod((sysdate - vt.start_time)*24, 24)) || ':'|| 
      lpad(floor(mod((sysdate - vt.start_time)*1440, 60)), 2, 0) ||':'||
      lpad(floor(mod((sysdate - vt.start_time)*86400, 60)), 2, 0) AS "Transaction Time",
      vst.sql_text 
FROM gv$session vs,
      gv$transaction vt, 
      gv$sqltext vst, 
      gv$access va
WHERE vt.sess_id = vs.sid
      and vt.sess_id = va.sid
      and nvl(vs.sql_id, vs.prev_sql_id) = vst.sql_id 
ORDER BY 1, 2;

결과는 아래와 같습니다.

INST_ID	  SID	   SERIAL#  USERNAME  OBJECT	        STATUS	  USED_BLK   USN   START_TIME   Transaction Tim	 SQL_TEXT
-------  ------  ---------  --------  ---------------   --------- --------- -----  ----------  ----------------- ------------
    1     152        10785  TPCC      BMSQL_NEW_ORDER   RUNNING          1     4   2023/07/04   0:00:16          DELETE FROM bmsql_new_order  
    2     134           97  TPCC      BMSQL_NEW_ORDER   READY            1     8   2023/07/04   0:00:02          select * from tab

각 항목에 대한 설명은 다음과 같습니다.

항목	설명
INST_ID	Tibero 인스턴스 정보 (TAC DB 질의 쿼리)
SID	트랜잭션을 수행 중인 세션의 ID
USERNAME	트랜잭션을 수행 중인 세션의 사용자
OBJECT	현재 동작 중인 트랜잭션에서 사용 중인 Table 목록 (하나의 트랜잭션에서 여러Table을 Access할 경우 Table마다 하나의 Row로 출력)
STATUS	트랜잭션을 수행하고 있는 세션의 상태 정보
USED_BLK	현재 트랜잭션이 사용 중인 Block의 개수
START_TIME	트랜잭션이 시작된 시간
Transaction Time	트랜잭션이 진행된 시간
SQL_TEXT	현재 수행 중인 SQL 내용

현재 트랜잭션이 사용 중인지 확인하고 장시간 Commit이나 Rollback 없이 진행되는 트랜잭션의 경우 애플리케이 션의 잘못된 코딩인지 아니면 관리자나 개발자의 실수인지를 판단하여 목적 없는 트랜잭션의 Commit 또는 Rollback을 권고합니다.

---

Current SQL 정보 (For 세션)

현재 세션별 수행되고 있는 SELECT 및 DML 구문을 확인합니다.

Single DB

다음은 Single DB 쿼리 작성 예입니다.

SELECT sid,
       serial#,
       '{' || aggr_concat(sql_text, '' ORDER BY PIECE) || '}' SQL 
FROM   v$session vs,
       v$sqltext vst
WHERE vs.sql_id=vst.sql_id 
GROUP BY SID, SERIAL#;

결과는 아래와 같습니다.

SID    SERIAL#     SQL                                                                                  
----- ----------   ------------------------------------------------------------------------
 134      12535     {DELETE FROM bmsql_new_order WHERE no_w_id = ? AND no_d_id = ? AND no_o_id = ?}
 143      12549     {UPDATE bmsql_order_line SET ol_delivery_d = ? WHERE ol_w_id = ? AND ol_d_id = ? AND ol_o_id = ?}

각 항목에 대한 설명은 아래의 “TAC DB” 내용을 참고합니다.

TAC DB

다음은 TAC DB 쿼리 작성 예입니다.

SELECT max(vs.inst_id) inst_id,sid, 
       serial#,
       '{' || aggr_concat(sql_text, '' ORDER BY PIECE) || '}' SQL 
FROM gv$session vs,
     gv$sqltext vst
WHERE vs.sql_id=vst.sql_id 
GROUP BY SID, SERIAL#;

결과는 아래와 같습니다.

INST_ID        SID     SERIAL#      SQL                                                                             
----------     -----   --------    ---------------------------------------------------------------
 1             134      12535       {DELETE FROM bmsql_new_order     WHERE no_w_id = ? AND no_d_id = ? AND no_o_id = ?}
 2             143      12223       {SELECT"INST_ID","HASH_VALUE","PLAN_HASH_VALUE","SQL_ID","CHILD_SELECT "INST_ID","HASH_VALUE","PLAN
                                      
 _HASH_VALUE","SQL_ID","CHILD_NUMBER","COMMAND_TYPE","PIECE","SQL_TEXT" FROM (select 
instance_NUMBER"
                           ,"COMMAND_TYPE","PIECE","SQL_TEXT" FROM (select instance_id() 
as inst_id, "V"."HASH_VALUE", "V"."PLA
                           N_HASH_VALUE", "V"."Sid() as inst_id, "V"."HASH_VALUE", 
"V"."PLAN_HASH_VALUE", "V"."SQL_ID", "V"."CH
                           ILD_NUMBER", "V"."COMMAND_TYPE", "V"."PIECE", "V"QL_ID", 
"V"."CHILD_NUMBER", "V"."COMMAND_TYPE", "V"
                           ."PIECE", "V"."SQL_TEXT" from v$sqltext v) /*GV$SQLTEXT*/ 
QB_006 WHERE ("SQL_."SQL_TEXT" from v$sqlt
                           ext v) /*GV$SQLTEXT*/ QB_006 WHERE ("SQL_ID" IS NOT NULL) ID" 
IS NOT NULL) }                                    

각 항목에 대한 설명은 다음과 같습니다.

항목	설명
INST_ID	Tibero 인스턴스 정보 (TAC DB 질의 쿼리)
SID	세션 ID
SERIAL#	세션의 Serial (같은 세션 ID라도 사용자가 바뀌면 Serial도 변경됨)
SQL	현재 수행 중인 SQL 내용

현재 활성화된 세션의 SQL 구문을 확인하여 불량 SQL문이나 루프문으로 DB에 부하를 주고 있는 세션이 있다면 그 쿼리를 찾아내고 해당 세션에 대한 적절한 조치를 합니다.

---

WLOCK 정보 (For 세션)

현재 실행 중인 세션의 트랜잭션 시간 및 Lock 모드를 확인합니다.

Single DB

다음은 Single DB 쿼리 작성 예입니다.

SELECT  s.sess_id "SID", 
        s.serial_no "SERIAL#", 
        s.status "STATUS", 
        s.user_name "USER", 
        o.object_name,
        FLOOR((sysdate - vt.start_time)*24) || ':'||
                LPAD(FLOOR(MOD((sysdate - vt.start_time)*1440, 60)),2,0) ||':'|| 
                LPAD(FLOOR(MOD((sysdate - vt.start_time)*86400,60)),2,0) AS "LOCK_TIME",
        DECODE(lmode, 0, '[0]', 1, '[1]Row-S(RS)', 2, '[2]Row-X(RX)', 3, '[3]Shared(S)', 4,
'[4]S/Row-S(SRX)', 5, '[5]Exclusive(X)', 6, '[6]PIN', TO_CHAR (lmode) ) "LOCK_MODE",
        NVL(s.sql_id, s.prev_sql_id) || '/' || NVL2(s.sql_id, s.sql_child_number, 
s.prev_child_number) "SQL_ID"
FROM    vt_wlock l,
        vt_session s, 
        dba_objects o , 
        vt_transaction vt
WHERE l.type='WLOCK_DML'
        AND l.sess_id = s.vtr_tid 
        AND l.id1 = o.object_id (+)
        AND l.sess_id = vt.sess_id order by "LOCK_TIME" DESC;

결과는 아래와 같습니다.

SID     SERIAL# STATUS   USER   OBJECT_NAME          LOCK_TIME  LOCK_MODE        SQL_ID                        
----- --------- -------- ------ -------------------- ---------  -------------   -------------------
 140      14497 RUNNING  TPCC   BMSQL_NEW_ORDER        0:01:45  [2]Row- X(RX)    7ptad1jfq93j6/152
 135      14491 RUNNING  TPCC   BMSQL_OORDER           0:00:09  [2]Row- X(RX)    3s4c9v1c9xzc5/155
 135      14491 RUNNING  TPCC   BMSQL_ORDER_LINE       0:00:09  [2]Row- X(RX)    3s4c9v1c9xzc5/155
 135      14491 RUNNING  TPCC   BMSQL_CUSTOMER         0:00:09  [2]Row- X(RX)    3s4c9v1c9xzc5/155
 137      14495 RUNNING  TPCC   BMSQL_NEW_ORDER        0:00:06  [2]Row- X(RX)    7ptad1jfq93j6/152

각 항목에 대한 설명은 아래의 “TAC DB” 내용을 참고합니다.

TAC DB

다음은 TAC DB 쿼리 작성 예입니다.

SELECT  vt.inst_id,
        s.sess_id "SID", 
        s.serial_no "SERIAL#", 
        s.status "STATUS", 
        s.user_name "USER", 
        o.object_name,
        FLOOR((sysdate - vt.start_time)*24) || ':'||
                LPAD(FLOOR(MOD((sysdate - vt.start_time)*1440, 60)),2,0) ||':'|| 
                LPAD(FLOOR(MOD((sysdate - vt.start_time)*86400,60)),2,0) AS "LOCK_TIME",
        DECODE(lmode, 0, '[0]', 1, '[1]Row-S(RS)', 2, '[2]Row-X(RX)', 3, '[3]Shared(S)', 4,
'[4]S/Row-S(SRX)', 5, '[5]Exclusive(X)', 6, '[6]PIN', TO_CHAR (lmode) ) "LOCK MODE",
        NVL(s.sql_id, s.prev_sql_id) || '/' || NVL2(s.sql_id, s.sql_child_number, 
s.prev_child_number) "SQL_ID"
FROM    vt_wlock l,
        vt_session s, 
        dba_objects o , 
        gv$transaction vt
WHERE l.type='WLOCK_DML'
        AND l.sess_id = s.vtr_tid 
        AND l.id1 = o.object_id (+) 
        AND l.sess_id = vt.sess_id
ORDER BY "LOCK_TIME" DESC;

결과는 아래와 같습니다.

 INST_ID   SID    SERIAL#    STATUS       USER          OBJECT_NAME          LOCK_TIME      LOCK MODE       SQL_ID                        
---------- ----- ---------- --------  --------------- -------------------- --------------- -------------   ------------------------------
        1   136        196  RUNNING    TPCC            BMSQL_OORDER         0:00:00        [2]Row-X(RX)    8c940cxrfrc8d/78
        1   136        196  RUNNING    TPCC            BMSQL_NEW_ORDER      0:00:00        [2]Row-X(RX)    8c940cxrfrc8d/78
        2   137        198  RUNNING    TPCC            BMSQL_DISTRICT       0:00:00        [1]Row-S(RS)    f3sacguuzunmw/57
        2   143        210  RUNNING    TPCC            BMSQL_DISTRICT       0:00:00        [1]Row-S(RS)    f3sacguuzunmw/57

DML Lock 관련 정보에 대한 각 항목의 설명은 다음과 같습니다.

항목	설명
INST_ID	Tibero 인스턴스 정보 (TAC DB 질의 쿼리)
SID	트랜잭션을 수행 중인 세션 ID
SERIAL#	트랜잭션을 수행 중인 세션 일련번호
STATUS	트랜잭션을 수행하고 있는 세션의 상태 정보
USER	트랜잭션을 수행 중인 세션의 사용자
OBJECT_NAME	DML Lock이 걸려있는 오브젝트
LOCK_TIME	DML Lock이 걸려있는 시간
LOCK_MODE	DML Lock 종류 (RS=1, RX=2, S=3, SRX=4, X=5)
SQL_ID	현재 수행 중인 SQL_ID

---

WLOCK Holder & Waiter 정보

Blocking 하고 있는 Lock 정보와 Waiting 하고 있는 Lock 정보를 모니터링합니다.

Single DB

다음은 Single DB 쿼리 작성 예입니다.

SELECT  bs.user_name "Blocking User" , 
        ws.user_name "Waiting User" , 
        bs.sess_id "Blocking Sid" , 
        ws.sess_id "Waiting Sid" , 
        wk.type "Lock Type" ,
        hk.lmode "Holding mode" , 
        wk.lmode "Request mode" ,
        nvl(bs.sql_id, bs.prev_sql_id) "Sql_id"
FROM    vt_wlock hk,
        vt_session bs, 
        vt_wlock wk, 
        vt_session ws
WHERE   wk.status = 'WAITER' 
        and hk.status = 'OWNER' 
        and hk.lmode > 1
        and wk.type = hk.type 
        and wk.id1 = hk.id1 
        and wk.id2 = hk.id2
        and wk.sess_id = ws.vtr_tid
        and hk.sess_id = bs.vtr_tid 
ORDER BY 1;

결과는 아래와 같습니다.

locking User    Waiting User    Blocking Sid     Waiting Sid     Lock Type       Holding mode      Request mode     Sql_id                        
-------------   --------------- ------------     -----------     --------------- ------------     -------------     ------------------------------
TPCC            TPCC                     133         146         WLOCK_TX                   5                6     3s4c9v1c9xzc5
TPCC            TPCC                     133         147         WLOCK_TX                   5                6     3s4c9v1c9xzc5
TPCC            TPCC                     133         148         WLOCK_TX                   5                6     3s4c9v1c9xzc5
TPCC            TPCC                     133         149         WLOCK_TX                   5                6     3s4c9v1c9xzc5
TPCC            TPCC                     133         150         WLOCK_TX                   5                6     3s4c9v1c9xzc5

항목에 대한 설명은 아래와 같습니다.

항목	설명
INST_ID	Tibero 인스턴스 정보 (TAC DB 질의 쿼리)
Blocking User	Lock을 붙잡고 있는 Holder
Waiting User	Lock을 기다리고 있는 사용자
Blocking Sid	Lock을 붙잡고 있는 세션의 SID
Waiting Sid	Lock을 기다리고 있는 세션의 SID
Lock Type	기다리고있는 Wlock의 타입
Holding mode	Lock을 Holding하고 있는 모드
Request mode	Lock을 Request하고 있는 모드
Sql_id	실행한 쿼리의 ID

Lock에 걸려 대기 중인 세션 및 현황 정보를 모니터링합니다.

Lock에 걸려있는 세션을 확인하고 해당 세션을 끊거나 애플리케이션 상 잘못된 구현 또는 사용자의 잘못된 쿼리로 장시간 Lock이 걸려있는 부분을 확인하고 Commit 또는 Rollback을 권고합니다.

LOCK_TYPE이 WLOCK_TX일 경우 Row Level 단위 Lock에 걸려있을 확률이 높습니다.