Prev: Understanding PL/SQL tables and such
Next: Oracle Materialized Views in a RAC
From: Thomas Blankschein on 9 May 2010 13:52
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: joel garry on 9 May 2010 20:15
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

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/content/article/2010/05/07/AR2010050704503.html
From: galen_boyer on 9 May 2010 20:37
Thomas Blankschein <thomas(a)blankschein.de> writes:

> 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.

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(a)netfront.net ---
From: Tim X on 9 May 2010 20:39
joel garry <joel-garry(a)home.com> writes:

> 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
>
> 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?
>

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
From: sandeep pande on 11 May 2010 02:48
On May 9, 10:52 pm, 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

Hi,
Pls, check:

DBA_WAITERS
DBA_BLOCKERS
DBA_DML_LOCKS
V$LOCK

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