Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    98

    Unanswered: Concurrent user update

    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

  2. #2
    Join Date
    Jan 2003
    Location
    Woking
    Posts
    107

    Re: Concurrent user update

    Originally posted by mchih
    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.
    nn

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Concurrent user update

    Originally posted by mchih
    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
    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

  4. #4
    Join Date
    Nov 2002
    Posts
    98
    thanks andrew

    it's the 2nd solution i was looking for

    Best Regards

    Mark

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •