Re: Who is blocking the record I want to edit?
Date: Fri, 14 May 2010 10:14:56 -0700 (PDT)
Message-ID: <12c1d996-b4eb-4d63-9820-91fbfcdf6f35_at_h37g2000pra.googlegroups.com>
Hi,
I also share the concern about locking a table/record for any length of time... Perhaps you can add an additional query before attempting to issue your DML statement against the record in question. For example:
Add an update timestamp column to your records so that any time someone issues DML against that record, the timestamp is updated with sysdate.
- User queries record by ID, store the update timestamp currently associated with that record.
- User edits record on your application
- User hits SUBMIT button on the application
- Application queries the same record and checks if the update timestamp = the timestamp when the record was originally pulled into their app.
- If the timestamps do not =, then someone else has updated the record before them.
- Do not issue DML, instead notify the user that the record has been edited since last queried.
Step (6) could involve some additional app logic that either pulls the changes added by other users during the current user's query-edit period and prompts the user if they wish to overwrite or merge their edits with existing edits.
Rich
rgpascual -at- berkeley -dot- edu
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
Received on Fri May 14 2010 - 12:14:56 CDT