a conceptual queston; i seem to remember reading it somewhere but can't recall the solution in Oracle:

say 2 users retrieve the same record for read.
user A updated the record first.
what would happen when user B tries to update the same record?

i seem to remember user B would not be able to update because the data he retrieves is out-of-date. Is that right?

Best Regards,

Mark

Hi,
For this you need to lock your records. That is lock it while selecting.
select * from emp where empno < 2000 FOR UPDATE;
The FOR UPDATE clause will lock the selected rows and prevent
the other user from update/delete.

What happens depends on how the retrieve is done: just SELECT, or SELECT FOR UPDATE.

1) SELECT (without FOR UPDATE)

- User A selects the record
- User B selects the record
- User A updates the record
- User B tries to update the record - is blocked by user A and hangs
- User A commits
- User B's update now completes
- User B commits

There is now a potential "lost update", because user A's change may have been overridden by user B's.

2) SELECT FOR UPDATE

- User A selects the record FOR UPDATE
- User B selects the record FOR UPDATE - is blocked by user A and hangs
- User A updates the record
- User A commits
- User B now gets the record (including A's updates)
- User B updates the record
- User B commits

thanks andrew

it's the 2nd solution i was looking for

Best Regards

Mark