Who is blocking the record I want to edit?

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 Received on Sun May 09 2010 - 12:52:22 CDT