Re: Who is blocking the record I want to edit?

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