In article <72704g$p1u$1_at_flint.sentex.net>,
"Dennis R. Leon" <dleon_at_rim.net> wrote:
> I'm having a problem with selecting and updating rows on a table by two
> concurrent users.
> I was wondering if anybody knows a way to prevent a second user from
> SELECTING the same row that a first user has already selected.
>
> My problem is this:
> User one selects a row, validates that row and then updates the status of
> that row. While User one is doing this, User two goes and selects that same
> row, validates the row and fails because User one has already updated the
> status of that row and is no longer valid for my operation.
>
> What I want is this:
> User one selects a row and HIDES/LOCKS that row from the next User,
> validates that row and then updates the status of that row. While User one
> is doing this, User two selects the next row (not the row User one is
> working on) and HIDES/LOCKS that row from the next user, validates that row
> and then updates the status of that row. This will prevent Users
> interfering with each-other's work.
>
> Your HELP would be greatly appreciated.
> ~~~~~~~~~~~~~~~~~~~~~~~~~
> Dennis R. Leon
> dleon_at_rim.net
>
> Research In Motion Ltd.
> Voice: (519) 748-2100 x328
> Fax: (519) 748-4532
> http://www.rim.net
>
>
Try with select .... from <table> for update of <col1,col2,...> nowait;
This will try to lock the selected rows and if these cannot be lock (
because of the lock from anothe user) it will fail and will not wait.
I believe you could invent something with that. The other solution,
which implies a lot less concurrency would be to lock the table in exclusive
mode prior to selecting data from it. Then, you would be sure that nobody
else is messing around with the table. The problem with this solution is that
is seriously reduces concurrency. Third solution would be to write a PL/SQL
procedure to both lock the table and perform an update. That would reduce
the duration of the lock to a minimum.
Kindest regards,
Mladen Gogala
-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Mon Nov 09 1998 - 12:38:58 CST
