A row locking problem that baffles all...

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
Paul
aspscott_at_tcp.co.uk
^^please remove 'as' anti-spam prefix to contact me Paul
aspscott_at_tcp.co.uk
^^ remove 'as' anti spam prefix to reply Received on Wed Jan 13 1999 - 19:13:11 CST