Re: Who is blocking the record I want to edit?
Date: Sat, 15 May 2010 11:15:28 -0700 (PDT)
Message-ID: <14a879d4-6aba-40f6-8dfe-4936bebfffe2_at_y12g2000vbg.googlegroups.com>
On May 14, 1:14 pm, ucb_richp <richg..._at_gmail.com> wrote:
> 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.
>
> 1) User queries record by ID, store the update timestamp currently
> associated with that record.
> 2) User edits record on your application
> 3) User hits SUBMIT button on the application
> 4) Application queries the same record and checks if the update
> timestamp = the timestamp when the record was originally pulled into
> their app.
> 5) If the timestamps do not =, then someone else has updated the
> record before them.
> 6) 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- Hide quoted text -
>
> - Show quoted text -
Remember that with the Oracle Read Consistency Model readers do not wait on writers so if another session has updated the row but not committed the data can still be read but the last update time will not reflect the uncommitted update. What likely needs to be done is at the time of update the last transaction time has to be compared as part of the update statement where clause and the application has to check that the row was actually updated. The NOWAIT option can be used on the update to prevent waiting in the event the other session has still not committed while the test on the last update time would prevent the update to a row changed between the initial select and the update.
The select for update statement might be of use in the application though since this response is not hooked to the initial post I am not have the advantage of reading the application design description it provided.
HTH -- Mark D Powell -- Received on Sat May 15 2010 - 13:15:28 CDT