Database Reference
In-Depth Information
23:45:28 SQL> SET PAUSE "Hit return to continue"
23:45:28 SQL> SET PAUSE ON
23:45:28 SQL> SELECT tab.msgid
FROM "NDEBES"."POST_OFFICE_QUEUE_TABLE" tab
WHERE q_name=:q_name and (state=:state)
FOR UPDATE;
The second session is unable to retrieve any data. Here's the level 8 SQL trace output from
that session:
*** 2007-07-10 23:45:40.319
WAIT #1: nam='enq: TX - row lock contention' ela= 2999807 name|mode=1415053318 usn<<
16 | slot=327721 sequence=660 obj#=16567 tim=79706625648
WAIT #1: nam='enq: TX - row lock contention' ela= 3000541 name|mode=1415053318 usn<<
16 | slot=327721 sequence=660 obj#=16567 tim=79709637248
WAIT #1: nam='enq: TX - row lock contention' ela= 2999946 name|mode=1415053318 usn<<
16 | slot=327721 sequence=660 obj#=16567 tim=79712642844
WAIT #1: nam='enq: TX - row lock contention' ela= 3000759 name|mode=1415053318 usn<<
16 | slot=327721 sequence=660 obj#=16567 tim=79715649132
*** 2007-07-10 23:45:52.347
WAIT #1: nam='enq: TX - row lock contention' ela= 2999721 name|mode=1415053318 usn<<
16 | slot=327721 sequence=660 obj#=16567 tim=79718655012
We see repeated waits for a TX lock, due to a lock (enqueue) request constantly being reat-
tempted. A look at
V$LOCK
confirms that session 1 with
SID=134
blocks session 2 with
SID=158
.
SQL> SELECT sid, type, id1, id2, lmode, request, block
FROM v$lock
WHERE sid IN (134,158)
ORDER BY 1;
SID TYPE ID1 ID2 LMODE REQUEST BLOCK
--- ---- ------ --- ----- ------- -----
134 TM 16567 0 3 0 0
134 TX 458764 657 6 0 1
158 TM 16567 0 3 0 0
158 TX 458764 657 0 6 0
The
TYPE
and
LMODE
in
V$LOCK
are represented as
name|mode=1415053318
in the extended SQL
trace file. This is a decimal number with the upper two bytes representing the enqueue name
as ASCII encoded letters and the lowest byte representing the lock mode. Name and mode are
equivalent to
V$SESSION_WAIT.P1
when the wait in
V$SESSION_WAIT.EVENT
is for a TX enqueue.
Oracle9
i
uses the generic wait event name
enqueue
, whereas Oracle10
g
uses
enq: TX - row lock
contention
. Oracle10
g
provides the same information in
V$SESSION.EVENT
and
V$SESSION.P1
as well.
You have two options for converting name and mode into a more human readable format.
The first one uses decimal to hexadecimal and decimal to ASCII conversion, whereas the second
relies entirely on SQL. The UNIX utility
bc
(an arbitrary precision calculator) may be used to
convert between decimal and hexadecimal numbers (most implementations of
bc
do not support
