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

This is just bad application design and the problem is discussed in most DB manuals - the record should not be locked until just before the update. The client software can check to see if the row has changed since it fetched it for viewing (one way to do this is to have a field as an update counter - but I haven't tried this myself).

Given the app you have though, I don't know how you can find the offending users - perhaps you should run a periodic check and just disconnect users who have been inactive for a particular length of time (seems a bit rude, but you can tell them that 1hr breaks are not on).

I also believe that it is possible to identify a user's last SQL (V$SQL or v$SQLTEXT???). Perhaps that would be worthwhile method - should narrow the field anyhow, even if they are using bind variables.

Richard Gowan

Paul wrote in message <36ad4884.7222789_at_news.tcp.co.uk>...
>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 Thu Jan 14 1999 - 17:06:44 CST