ORACLE-lock sesion

Mendeteksi lock

Posted on August 13, 2008 by Rohmad

Kadang transaksi (update atau delete) berjalan lama banget,padahal biasanya dua detik juga selesai. Atau juga mau nglakuin DDL (misalnya aler table) tidak bisa dengan error “ORA-00054: resource busy and acquire with NOWAIT specified”. Nah, kalau begini pasti ada session yang me-lock object (table, index).

Gunakan SQL command berikut untuk melihat locking di database Oracle kita

set linesize    150
set pages       100

col name                for a21         head "Locked Object"
col session_id          for 99999       head SID
col serial#             for 99999       head SER#
col oracle_username     for a12         head "Locking User"
col lock_type           for a12         head "Lock Type"
col mode_held           for a12         head "Mode Held"
col event               for a30

SELECT a.session_id, b.serial#, a.oracle_username, c.name,
decode(d.type,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distrib Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
d.type) lock_type,
decode(d.lmode,
0, 'None',           /* Mon Lock equivalent */
1, 'Null',           /* N */
2, 'Row-S (SS)',     /* L */
3, 'Row-X (SX)',     /* R */
4, 'Share',          /* S */
5, 'S/Row-X (SSX)',  /* C */
6, 'Exclusive',      /* X */
to_char(d.lmode)) mode_held,
e.event, e.SECONDS_IN_WAIT "Wait(Seconds)"
FROM  sys.obj$ c, v$session b, v$locked_object a,
sys.v_$lock d, v$session_wait e
WHERE  a.session_id=b.sid
AND b.sid=e.sid
AND c.obj#=a.object_id
AND a.object_id=d.id1
AND b.sid=d.sid
order by e.SECONDS_IN_WAIT desc
;

Misalkan hasilnya berikut ini:

 SID   SER# Locking User Locked Object         Lock Type    Mode Held    EVENT                          Wait(Seconds)
---- ------ ------------ --------------------- ------------ ------------ ------------------------------ -------------
1558  45822 APPUSR1      CST_CM_TRXLOG_TRACK   DML          Row-X (SX)   SQL*Net message from client              231
2151    449 APPUSR1      RM_RESOURCE_STK_HIST  DML          Row-X (SX)   direct path read temp                     34
2151    449 APPUSR1      CUSTOMER              DML          Row-X (SX)   SQL*Net message from client                9
3453  26576 APPUSR1      RM_RESOURCE_STK_HIST  DML          Row-X (SX)   SQL*Net message from client                1
4185  40934 APPUSR1      VM1_TENT_VCH          DML          Row-X (SX)   db file sequential read                    1
3453  26576 APPUSR1      RM_RESOURCE_STOCK     DML          Row-X (SX)   SQL*Net message from client                0
4185  40934 APPUSR3      VM1_TENT_VCH_HISTORY  DML          Row-X (SX)   db file sequential read                    0
4185  40934 APPUSR2      VM1_TENT_VCH_HISTORY  DML          Row-X (SX)   db file sequential read                    0
4185  40934 APPUSR2      VM1_TENT_VCH          DML          Row-X (SX)   db file sequential read                    0
3453  26576 APPUSR2      MLOG$_RM_RESOURCE_STO DML          Row-X (SX)   SQL*Net message from client                0

Penjelasan:

1. EVENT: Aktivitas session (yang melakukan lock) saat ini.
   Wait(Seconds): Lamanya aktivitas saat ini
2. User APPUSR1 (SID 1558) tengah me-lock tabel CST_CM_TRXLOG_TRACK. Dari keterangan kolom EVENT adalah “SQL*Net message from client”, ini berarti user sedang idle (INACTIVE). Kalau begitu, tadi user APPUSR1 tersebut pernah melakukan transaksi (insert, update, atau delete) pada tabel CST_CM_TRXLOG_TRACK namun sampai sekarang belum COMMIT atau ROLLBACK

Aktivitas lock di Oracle adalah hal yang biasa. Sebagai DBA kita harus bisa mengetahui mana-mana session yang sedang menunggu (wait) karena tabel yang di-update sedang di-lock oleh session lain. Berikut ini perintah SQL untuk melihat session yang melakukan lock dan yang menunggu:

SELECT a.sid "Locking Sid (yang nge-lock)",
b.sid "Locked SID (Sedang Menunggu)"
FROM v$lock a , v$lock b
WHERE a.id1=b.id1
AND   a.id2=b.id2
AND   a.request=0
AND   b.lmode=0
;

Misalkan hasilnya berikut ini:

Locking Sid (yang nge-lock) Locked SID (Sedang Menunggu)
--------------------------- ----------------------------
1558                         2223

Gara-gara user APPUS1 (SID 1558) belum melakukan commit atau rollback, session dengan SID 2223 terpaksa harus menunggu. Kalau begitu apa solusinya? User APPUS1 tersebut harus segera melakukan COMMIT atau ROLLBACK, kalau terpaksa ya mungkin bisa di-kill.