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

From: Connor McDonald <mcdonald.connor.cs_at_bhp.com.au>
Date: Thu, 14 Jan 1999 09:35:58 +0800
Message-ID: <369D49FE.786F_at_bhp.com.au>

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

[Quoted] We use the following view to assess locking on the database...It shows username, sid and object_name and the "block" column can be used to determine if there is blocking going on...

It should be quite easily tweaked to provide what you need..

Cheers

create view lock_contention is
SELECT substr(S.USERNAME,1,8),

        S.SID,
        L.TYPE,
        L.ID1,
        substr(o.name,1,22),
        DECODE(L.LMODE, 0, 'NONE', 1, 'NULL', 2, 'SS', 3, 'RX', 
		4, 'S', 5, 'SRX', 6, 'X ', '?'),
        DECODE(L.REQUEST, 0, 'NONE', 1, 'NULL', 2, 'SS', 3, 'RX', 
		4, 'S', 5, 'SRX', 6, 'X', '?'),
        l.ctime,
        decode(l.block,0,null,'TRUE')

FROM V$LOCK L, V$SESSION S, obj$ o
WHERE L.SID=S.SID
AND nls_upper(S.USERNAME) LIKE nls_upper(null)|| '%' and l.id1 = o.obj# (+)

-- 
==============================================
Connor McDonald
BHP Information Technology
Perth, Western Australia
"The difference between me and a madman is that I am not mad"
				- Salvador Dali

Received on Thu Jan 14 1999 - 02:35:58 CET