Prev: Understanding PL/SQL tables and such
Next: Oracle Materialized Views in a RAC
From: ucb_richp on 14 May 2010 13:14
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...(a)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

From: Mark D Powell on 15 May 2010 14:15
On May 14, 1:14 pm, ucb_richp <richg...(a)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...(a)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 --

First  |  Prev  | 
Pages: 1 2
Prev: Understanding PL/SQL tables and such
Next: Oracle Materialized Views in a RAC