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

The previous company I was working used the DBVision by Platinum.

mpir_at_compuserve.com wrote:
: 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

--
Best regards, Received on Sun Jan 17 1999 - 17:14:54 CST