Re: Who is blocking the record I want to edit?
From: sandeep pande <sandy.soft80_at_gmail.com>
Date: Mon, 10 May 2010 23:48:58 -0700 (PDT)
Message-ID: <cc8ab27e-2f5a-4c70-8e09-6c57315d6261_at_g39g2000pri.googlegroups.com>
On May 9, 10:52 pm, 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
V$LOCK Regards,
Sandy Received on Tue May 11 2010 - 01:48:58 CDT
Date: Mon, 10 May 2010 23:48:58 -0700 (PDT)
Message-ID: <cc8ab27e-2f5a-4c70-8e09-6c57315d6261_at_g39g2000pri.googlegroups.com>
On May 9, 10:52 pm, 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
Hi,
Pls, check:
DBA_WAITERS DBA_BLOCKERS DBA_DML_LOCKS
V$LOCK Regards,
Sandy Received on Tue May 11 2010 - 01:48:58 CDT