Re: Who is blocking the record I want to edit?

> 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?
>

I would also add that it may be worthwhile asking the client why they feel this information will be useful and/or how they would plan to use it. I suspect it is likely they have seen that an update might not work because the record is locked, but have not really thought things through further. For example, once they have identified the user who was editing the record when they wanted to edit it, what will be the business process that follows on from that? It could be the client is thinking they will then go and talk to the user about their edit, but most of the time, that user has probably finished their edit anyway, so what would be gained. Alternatively, the client may really only just want to know who is the last user to edit the record, which could be tracked with a last_modified_by column etc.

Tim

-- 
tcross (at) rapttech dot com dot au