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

The answer may be in SQL*NET and user profiles. It is possible to set an idle timeout in SQL*NET and I believe as a user profile, at least in 7.2 and later. Set the parameter for some 'reasonable' time (say 2 minutes) and the dbms will kill the sessions that it notices have been idle, rolling back the transaciton. I have used this some, not extensively, so you might have to play with settings, but it is supposed to be doable.

In article <36a440f2.5284671_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
> 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
>

Joseph R.P. Maloney, CCP,CSP,CDP
MPiR, Inc.
502-451-7404
some witty phrase goes here, I think.

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Jan 14 1999 - 08:19:33 CST