Table Locking Anomaly (Do I Believe OEM or Tom Kyte Scripts? ....)

  • From: "Sam Bootsma" <sbootsma@xxxxxxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <'oracle-l@xxxxxxxxxxxxx'>
  • Date: Wed, 8 Feb 2006 10:58:19 -0500

Hello,

 

Friday afternoon, our trial tool (DBFlash) detected an enqueue problem.
I investigated the locking problem with both Oracle Enterprise Manager
and some scripts I lifted from Tom Kyte's book "Expert-One-on-One".  OEM
reported that user "A" was blocking user "B".  Tom Kyte's script
reported user "A" was blocking user "B" plus 15 additional users.
Although OEM did not show these 15 additional users as blocked, OEM did
show 1 of these 15 additional users was holding a row exclusive lock and
the remaining 14 users were holding row share locks. 

 

So who do I believe?  Is OEM that comes with Oracle 9i R2 known to be
buggy?  Or does the script from Tom Kyte need to be tweaked for Oracle
9i?  Or do they not contradict one another ... meaning the problem is
with me and my understanding is not correct?

 

Thanks for your input!  We are running Oracle 9.2.0.6 on AIX 5.1.  Here
is the script from Tom Kyte:

 

select 

    (select username from v$session where sid=a.sid) blocker, a.sid, '
is blocking ', 

    (select username from v$session where sid=b.sid) blockee, b.sid

from v$lock a, v$lock b

where a.block = 1     -- means this lock is blocking another lock
request

  and b.request > 0

  and a.id1 = b.id1

  and a.id2 = b.id2

/

 

 

Another script from the DBFlash software agreed with the results from
Tom Kyte's script:

-- this script comes from DBFlash software

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess, id1, id2,
lmode, request, type

FROM V$LOCK

WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE
request>0)

ORDER BY id1, request

/

 

Does anybody have a script that provides the exact row id(s) that a
waiter is waiting on?

 

Thanks Again,

 

 

Sam Bootsma

Oracle DBA

George Brown College

sbootsma@xxxxxxxxxxxxx <mailto:sbootsma@xxxxxxxxxxxxx> 

416-415-5000 x4933

 

Other related posts: