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

From: Mike Rose <mmrose_at_home.com>
Date: Thu, 14 Jan 1999 08:03:38 GMT
Message-ID: <uxhn2.16034$Hy5.4634_at_news.rdc1.md.home.com>


Another viable solution is to use PL/SQL 'smart' Procedures for data manipulation -- that would record the users holding UPDATE locks to a table so that the situation you described could be mitigated. This use of PL/SQL Procedures would ENTIRELY replace your current direct operations on the tables and could be integrated over time.

Mike Rose

Connor McDonald wrote in message <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
>
>
>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 - 09:03:38 CET

Original text of this message