Re: A row locking problem that baffles all...

In article <36a644a0.6226112_at_news.tcp.co.uk>, aspscott_at_tcp.co.uk (Paul) wrote:
>Here is a scenario that is driving myself and others mad! There must
>be an Oracle guru out there who knows a solution.
>
>User 1 & 2 are two of many users utilising a Client program attached
>with an Oracle Server back-end. The system is live, and is processing
>many customers data / orders / transactions and millions of Dollars.
>
>User 1 is performing some unimportant changes to a customer's data, so
>goes to edit a record, the client program automatically sends a
>
>select
> 1
>from
> ATable
>where
> Primary_Key = APrimaryKey
>for update nowait
>
>for the given record, row locking it ready for updating. Note : The
>Client program is "dumb" in that it is effectively transparent to SQL
>having no comprehension of what is sending to Oracle; regarding the
>"...where Primary_Key = APrimaryKey for update nowait" as plain text.
>User 1 continues to edit the record when urgently he gets called away
>from his machine for - what he doesn't realise at the time - a long
>period, so he hasn't committed / rolled back his changes and released
>the row lock.
>
>User 2 gets a long distance phone call from an important customer who
>needs to correct an error discovered in some of their details. The
>error is causing the customer to lose lots of money. Unfortunately,
>these precise details happen to be in the same record as User 1 was
>editing. So, as User 2 goes to edit the record, the client program
>again automatically sends a
>
>select
> 1
>from
> ATable
>where
> Primary_Key = APrimaryKey
>for update nowait
>
>attempting to row lock ready for updating. (The nowait directive is
>important for the client so that User 2's session doesn't lock up, but
>rather returns immediately.) Oracle raises a "Resource busy... with
>NOWAIT..." exception which the client program traps and shows a dialog
>of the form "You cannot edit the current record as it is locked by
>another user". User 2 waits a while and tries again, and again and
>again. His important customer is getting irate, losing money by the
>second, and because of the importance of correcting the error a very
>large contract might be terminated, or court action might be taken.
>
>The trouble is, over the years more and more users have been added to
>this large Client / Server system, and there are now hundreds of
>Client machines in the building, any one of which could be the person
>preventing User 2 from editing. User 2 ideally wants to know who is
>locking him out. Ideally, the exception dialog he wants to see is
>"You cannot edit the current record as it was locked by JBloggs on
>machine Workstation/Accounts at 10:30am". Where "JBloggs" is the
>Oracle User name, and "machine" is the Machine name. That way User 2
>knows JBloggs has been away for some time, so he can contact JBloggs,
>or JBloggs's manager to release the lock by way of commiting /
>rollback (cancelling) User 1's work.
>
>Surely this type of scenario requirement is common, so how is it
>achieved? How do I determine who locked the record in Oracle 8
>without having to sift through a million code lines of a well
>established, stable live Client program code making changes to
>implement a more complicated workaround?
>
>I believe it is possible to find the sessions who have records locked
>on the desired table, but what if several other users have different
>rows on the same table locked? Knowing which sessions have row locks
>on the table is not unique enough to get the information required.
>How can I query Oracle to find the information that User 2 needs to be
>shown to find out exactly who caused his row lock request to be
>refused
>i.e./e.g. the row locking Session's ID (User 1's SID) so I can query
>V$SESSION for that information? I can't find a way of doing it, two
>other experienced Oracle DBAs in my company can't answer it. Three
>experienced support DBAs at Oracle either say they can't do it or
>don't believe it is possible, they've tried the locking facilities of
>the Catblock.sql and Utllockt.sql script as well as bespoke scripts.
>V$SQL / V$SQL_TEXT can't be used for a join as the locking SQL text
>can be truncated by the limitations of the views' SQL column VARCHAR2
>size.
>
>So is this problem demonstrating a flaw in Oracle's locking
>architecture / lock logging? Or do any Oracle gurus know the solution
>that has defeated even the most experienced Oracle DBAs? Solution
>scripts will be greeted with amazement and kindest regards!
>
>
>Please help

Try the following query immediately after the failed "select ... for update nowait"

select s.username, s.osuser, s.terminal from v$session s
 , v$lock l
 , x$ksuse b
where b.KSUUDSES=userenv('sessionid')
and b.KSUSEOBJ = l.id1
and l.sid = s.sid

it seemed to do the trick in the few testcases I tried. You'll have to create a view for the x$ksuse table to make it accessible to non-SYS users. Received on Tue Jan 19 1999 - 18:37:27 CST