Re: Who is blocking the record I want to edit?
From: joel garry <joel-garry_at_home.com>
Date: Sun, 9 May 2010 17:15:44 -0700 (PDT)
Message-ID: <98e937ca-07eb-44d5-b495-5b7012a19599_at_32g2000prq.googlegroups.com>
On May 9, 10:52 am, Thomas Blankschein <tho..._at_blankschein.de> wrote:
> Hello,
>
> My Business case: In a Multiuser application the users need exclusive
> access to records in a table, to avoid lost updates.
>
> My solution: If a user presses the "Edit"-button, "SELECT * FROM table
> FOR UPDATE NOWAIT WHERE Keycolumn = :myid" is executed. Then he can edit
> the values in the application, and with the "Save"-button, an UPDATE
> table SET co1 = :val1... WHERE Keycolumn = :myid" and a "COMMIT" is
> executed.
>
> Result: User A presses "Edit". If User B presses "Edit" for the same
> record, he gets an ORA-00054: resource busy and acquire with NOWAIT
> specified. I translate this to a message "The record is in use by
> another user".
>
> So far so good.
>
> Problem: The users want the message to include the user-id of the
> blocking user. As far as I read Tom Kyte's explanations, that's not
> easy, because there is no data dictionary view like "TABLE - RECORD -
> BLOCKING_USER".
> I found some scripts, which shall work for "normal" locks, when one
> session waits for another session until commit or rollback. But that's
> not my case. In my case, the second session's locking try is immediately
> refused with ORA-00054.
> Is there any way to answer the question: "Which user is blocking the
> record I just tried to lock?" I know the table name, the primary key
> value of the record and of course all things about my own session.
>
> Thanks,
> Thomas
Date: Sun, 9 May 2010 17:15:44 -0700 (PDT)
Message-ID: <98e937ca-07eb-44d5-b495-5b7012a19599_at_32g2000prq.googlegroups.com>
On May 9, 10:52 am, Thomas Blankschein <tho..._at_blankschein.de> wrote:
> Hello,
>
> My Business case: In a Multiuser application the users need exclusive
> access to records in a table, to avoid lost updates.
>
> My solution: If a user presses the "Edit"-button, "SELECT * FROM table
> FOR UPDATE NOWAIT WHERE Keycolumn = :myid" is executed. Then he can edit
> the values in the application, and with the "Save"-button, an UPDATE
> table SET co1 = :val1... WHERE Keycolumn = :myid" and a "COMMIT" is
> executed.
>
> Result: User A presses "Edit". If User B presses "Edit" for the same
> record, he gets an ORA-00054: resource busy and acquire with NOWAIT
> specified. I translate this to a message "The record is in use by
> another user".
>
> So far so good.
>
> Problem: The users want the message to include the user-id of the
> blocking user. As far as I read Tom Kyte's explanations, that's not
> easy, because there is no data dictionary view like "TABLE - RECORD -
> BLOCKING_USER".
> I found some scripts, which shall work for "normal" locks, when one
> session waits for another session until commit or rollback. But that's
> not my case. In my case, the second session's locking try is immediately
> refused with ORA-00054.
> Is there any way to answer the question: "Which user is blocking the
> record I just tried to lock?" I know the table name, the primary key
> value of the record and of course all things about my own session.
>
> Thanks,
> Thomas
It's really handy to have your own table with all that information in there. The ERP I work on has that, though it sometimes depends on rolling back the addition to that table, which doesn't always work for some reason (which is probably an obscure app issue of when the transaction is actually started - this mostly shows up when virtual images go missing, rare, but happens). But that's what you have to expect when forcing pessimistic locks on an optimistic system.
v$lock has SID's in it, as well as more info you can use to figure out which object (see http://www.orafaq.com/node/854 ). v$session has some interesting info, too.
Which version did you say you were on?
jg
-- _at_home.com is bogus. http://www.washingtonpost.com/wp-dyn/content/article/2010/05/07/AR2010050704503.htmlReceived on Sun May 09 2010 - 19:15:44 CDT