| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: A row locking problem that baffles all...
A copy of this was sent to aspscott_at_tcp.co.uk (Paul)
(if that email address didn't require changing)
On Thu, 14 Jan 1999 01:12:41 GMT, you wrote:
the real fix is for the client application to realize its had a lock for what IT considers too long and rollback and put up an error message to the client saying -- "you took too long, you lose"
Lets say tho you really wanted to find out what session had your session blocked. A DBA or priveleged procedure can find this but it takes three sessions
session 1 - the blocker. session 2 - the blockee. session 3 - the dba who can see that session 2 is blocked by session 1.
Other then that, you cannot find out who has such and such a row locked, you can find the set of people who might have a row locked in the table in question, but you cannot find out who has a specific row locked (that information is not factored out into some lock table somewhere, locks are stored with the data itself in a fashion, they are not in some really big view anywhere).
So, what you could do is look at the script utllockt.sql in $ORACLE_HOME/rdbms/admin and see how it works. then, when session 2 gets an error upon locking a record (someone else has it) and session 2 really really wants that record, you could give session 2 the ability to try and get that record in a BLOCKING fashion, call the DBA (or give them a little utility on their desktop) that shows the blocking session (the one blocking them) and have the DBA kill the session or call their manager or whatnot.
>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
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Jan 14 1999 - 06:40:33 CST
 |
 |