Subject: Interesting Aspects of the SQL*DBA Monitor Lock Display
Last Revision Date: 11 September 1994
Author:Steve S. Tonas
The SQL*DBA Monitor Lock Display can offer a DBA some interesting
information for database problem solving. At first glance the display
can appear a bit cryptic in its content. This bulletin is aimed at
deciphering some of the Lock Monitor's information. I will use a
specific example to illustrate the Lock Monitor's functionality.
This bulletin is not intended to cover all aspects of the Lock
Monitor. However, it will provide some necessary definitions for any
future debugging activity. This bulletin assumes the reader knows how
to access the SQL*DBA Monitor and knows how to convert hex numbers to
decimal.
The following Lock Monitor Display shows two processes,
numbers 8 and 10, trying to update the same row of a table:
ORACLE Lock Monitor Fri May 4 07:57:0
UPPER case = owner lower case = waiter
12 Resources 9 Processes S=share X=exclusive L=row-S R=row-X C=S/row-X
====================--2-3-4-5-6-7-8-9-0-------------------+----------+
RW.1001319.......0 C
TD.......3.......0 S
TD.......d.......0 S
TD.......f.......0 S
TD......11.......0 S
TD......bf.......0 S
TD.....1ab.......0 S
TD.....ca8.......0 S
TD.....cc1.......0 S S
TM.....cc1.......0 R R
TX...1002b.....189 X s
We can determine a lot of information from the following
display. The TM.....cc1.......0 lock is a DML lock on the object
identified by the hex identifier cc1. Process 8 has an exclusive row
lock on this table. The table Monitor will contain the names of all
tables that have a DDL lock on them. Process 8 also holds a DDL lock
on the object identified by cc1, TD.....cc1.......0. From the Table
Monitor we can determine the name and owner of the table:
PID Obj# Owner ID Owner Name Table Name
---- -------- -------- ------------------------- -------------------
6 1ab 0 SYS DUAL
6 ca8 12 SYSTEXT TXDL_COLUMNS
7 3 0 SYS TAB$
7 d 0 SYS SEG$
7 f 0 SYS TS$
7 11 0 SYS OBJ$
7 bf 0 SYS USER_TABLES
8 cc1 15 STONAS SST1
10 cc1 15 STONAS SST1
The table name is SST1 and it is owned by the user STONAS.
The TX...1002b.....189 lock is a transaction lock that uniquely
identifies the transaction started by process number 8. The first
number in the second column identifies the rollback segment that this
process's current transaction is assigned to. In this case rollback
segment 1. The name of this rollback segment can be retrieved from
the Monitor Rollback Segment Display.
What is important to notice is that process 10 is waiting on
process 8's transaction to finish. Process 8 has updated the row but
has not issued an explicit or implicit commit or rollback. Process 10
is trying to update the same row and is currently hanging. The actual
row being waited on is at the top of the Lock Monitor in the form of a
row wait lock, RW.1001319.......0. On VMS the rowid of the row being
waited on would be 00001319.0000.0001. Unix only uses the first 6
high order bits to identify the file number. Remember that, except
for the process numbers, the Lock Monitor is all in hex. If this were
Unix, the rowid would then be 00001319.0000.0004.
VMS systems can have 255 files while Unix systems can only have
64. Unix systems can have larger files because they can use two more
bits for the number of Oracle blocks. Assuming a VMS system, the
first 1 in column 2 of the RW lock gives us file number 1, then the
Oracle block, and finally the third column gives us the row sequence
within the block. Hence the rowid of 00001319.0000.0001 for VMS.