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

On May 9, 10:52*am, Thomas Blankschein <tho... (AT) blankschein (DOT) de> wrote:
It's really handy to have your own table with all that information in
there. The ERP I work on has that, though it sometimes depends on
rolling back the addition to that table, which doesn't always work for
some reason (which is probably an obscure app issue of when the
transaction is actually started - this mostly shows up when virtual
images go missing, rare, but happens). But that's what you have to
expect when forcing pessimistic locks on an optimistic system.

v$lock has SID's in it, as well as more info you can use to figure out
which object (see http://www.orafaq.com/node/854 ). v$session has
some interesting info, too.

Which version did you say you were on?

jg
--
@home.com is bogus.
http://www.washingtonpost.com/wp-dyn...050704503.html

Thomas Blankschein <thomas (AT) blankschein (DOT) de> writes:

I might be quite concerned about this. How many concurrent users are
you expecting? You are going to have to hold a distinct connection for
each user until they perform their work, and also commit. What if they
get their record and then, say, go get a coffee, or, go to a meeting,
or, ...

I'd choose to optimistic lock instead. And then, I'd, instead work on a
"merge" functionality which rereads the record they are on and compares
it to the data they have in their session. Holding a connection for the
undetermined length of a user's whim is dangerous.

--
Galen Boyer

--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

joel garry <joel-garry (AT) home (DOT) com> writes:

I would also add that it may be worthwhile asking the client why they
feel this information will be useful and/or how they would plan to use
it. I suspect it is likely they have seen that an update might not work
because the record is locked, but have not really thought things through
further. For example, once they have identified the user who was editing
the record when they wanted to edit it, what will be the business
process that follows on from that? It could be the client is thinking
they will then go and talk to the user about their edit, but most of the
time, that user has probably finished their edit anyway, so what would
be gained. Alternatively, the client may really only just want to know
who is the last user to edit the record, which could be tracked with a
last_modified_by column etc.

Tim


--
tcross (at) rapttech dot com dot au

On May 9, 10:52*pm, Thomas Blankschein <tho... (AT) blankschein (DOT) de> wrote:
Hi,
Pls, check:

DBA_WAITERS
DBA_BLOCKERS
DBA_DML_LOCKS
V$LOCK

Regards,
Sandy

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 (DOT) de> wrote:

On May 14, 1:14*pm, ucb_richp <richg... (AT) gmail (DOT) com> wrote:
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 --