Oracle Locks

Manchmal kann es hilfreich sein direkt auf Datenbankebene zu schauen welche Session welchen Lock hält um zu sehen warum es wo genau klemmt und zum Beispiel ein Workprozess nicht weiter arbeiten kann oder die erwarteten Updates noch immer nicht fertig gestellt wurden.

Ich persönlich finde folgenden Statements dabei hilfreich.

Mit diesem Statement kann man schnell und einfach sehen, welche Session einen Lock hält und welche andere Session genau auf diesen Lock wartet:

SQL> set pages 200 lines 200
SQL> select HOLDING_SESSION, MODE_HELD, WAITING_SESSION from DBA_WAITERS order by HOLDING_SESSION;

HOLDING_SESSION MODE_HELD WAITING_SESSION
--------------- ---------------------------------------- ---------------
147 Exclusive 918
147 Exclusive 4621
147 Exclusive 4626
147 Exclusive 428
428 None 918
428 None 4621
428 None 4626
428 None 428
4621 None 4626
4621 None 4621
4621 None 918
4621 None 428
4626 None 4626
4626 None 4621
4626 None 918
4626 None 428

16 rows selected.

SQL>

Oracle liefert ein Standard Script mit aus, mit dem man sich die Locks als Tree anzeigen lassen kann. Dies vereinfacht das visuelle Erkennen von umfangreichen Abgängigkeiten enorm. Das Script legt eine temporäre Tabelle an und dropt diese auch wieder:

SQL> @?/rdbms/admin/utllockt
drop table lock_holders
*
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

drop table dba_locks_temp
*
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

4 rows created.

Commit complete.

Table dropped.

1 row created.

Commit complete.

WAITING_SESSION LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1 LOCK_ID2
----------------- ----------------- -------------- -------------- ----------------- -----------------
147 None
428 Transaction Exclusive Exclusive 19857416 2624382
918 Transaction Exclusive Exclusive 19857416 2624382
4621 Transaction Exclusive Exclusive 19857416 2624382
4626 Transaction Exclusive Exclusive 19857416 2624382

Table dropped.

SQL>

Folgendes Statement gibt die beteiligten OS User und das Schema mit aus:

select SID, SERIAL#, USERNAME, OSUSER , o. owner, o.object_name ,ROW_WAIT_BLOCK# , ROW_WAIT_FILE#, ROW_WAIT_ROW# FROM v$session , dba_objects o WHERE lockwait is not null AND object_id = ROW_WAIT_OBJ#;

SID SERIAL# USERNAME OSUSER OWNER
---------- ---------- ------------------------------ ------------------------------ ------------------------------
OBJECT_NAME ROW_WAIT_BLOCK# ROW_WAIT_FILE# ROW_WAIT_ROW#
-------------------------------------------------------------------------------------------------------------------------------- --------------- -------------- -------------
4626 7 SAPSCHEMA sidadm SAPSCHEMA
TABELLE 3581556 388 36

4621 1 SAPSCHEMA sidadm SAPSCHEMA
TABELLE 3575278 388 6

918 5 SAPSCHEMA sidadm SAPSCHEMA
TABELLE 527538 4 29

428 3 SAPSCHEMA sidadm SAPSCHEMA
TABELLE 3056920 392 11

SQL>

Mit diesem Statement werden die SIDs, Objekte, LOCK Modes und Requests angezeigt:

SQL> SELECT
SID, CTIME, substr(OBJECT_NAME,1,30), SUBOBJECT_NAME, LMODE, REQUEST, BLOCK
FROM
( SELECT L.INST_ID, O.OBJECT_NAME, O.SUBOBJECT_NAME, L.SID, L.CTIME, L.LMODE, L.REQUEST, L.BLOCK, ROW_NUMBER () OVER (PARTITION BY O.OBJECT_NAME, O.SUBOBJECT_NAME ORDER BY CTIME DESC) RN FROM GV$LOCK L, DBA_OBJECTS O WHERE L.TYPE = 'TM' AND L.ID1 = O.OBJECT_ID ORDER BY OBJECT_NAME, SUBOBJECT_NAME, CTIME DESC );

SID CTIME SUBSTR(OBJECT_NAME,1,30) SUBOBJECT_NAME LMODE REQUEST BLOCK
---------- ---------- ------------------------------------------------------------------------------------------ ------------------------------ ---------- ---------- ----------
147 607100 TABELLE1 3 0 0
428 381060 TABELLE1 3 0 0
4626 367493 TABELLE1 3 0 0
4621 287233 TABELLE1 3 0 0
918 256501 TABELLE1 3 0 0
147 607100 TABELLE2 3 0 0
428 381060 TABELLE2 3 0 0
4626 367493 TABELLE2 3 0 0
4621 287233 TABELLE2 3 0 0
918 256501 TABELLE2 3 0 0
428 381060 TABELLE3 3 0 0
4626 367493 TABELLE3 3 0 0
4621 287233 TABELLE3 3 0 0
918 256501 TABELLE3 3 0 0

14 rows selected.

SQL>

About the author