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

Hi Sam,  

Not sure what SQL OEM executes, but the Tom Kyte script looks ok. I like Steve Adams' script, which can be found at http://www.ixora.com.au/scripts/sql/enqueue_locks.sql  

Also, as to exact rowid, if a session is waiting on a specific row, then the following columns in V$SESSION will tell you which one: ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, and ROW_WAIT_ROW#. However, remember that not all waits are row-level waits. Waits on enqueues of type other than TX will never be row-level waits, and even TX enqueues may not necessarily be row-level waits.  

Hope that helps,  

-Mark  

-- 
Mark J. Bobak 
Senior Oracle Architect 
ProQuest Information & Learning 

"There are 10 types of people in the world:  Those who understand
binary, and those who don't." 

 

________________________________

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sam Bootsma
Sent: Wednesday, February 08, 2006 10:58 AM
To: oracle-l_at_freelists.org
Subject: Table Locking Anomaly (Do I Believe OEM or Tom Kyte Scripts?
....)



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_at_gbrownc.on.ca <mailto:sbootsma_at_gbrownc.on.ca> 

416-415-5000 x4933

 





--
http://www.freelists.org/webpage/oracle-l