Results 1 to 2 of 2

Thread: For Update

  1. #1
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    Unanswered: For Update

    I read about the FOR UPDATE clause in Select statement recenty. It acquires the row level locks for the dataset selected by select statement unless commit or rollback is issued.

    I jus want to ask in which conditions we should use this feature. In1 of the procedure , it is used and the transaction of which the select is part is very long. (About 40 seconds) ,. Is it right to use this here?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Use FOR UPDATE to provide pessimistic locking whenever you select records in a cursor that you intend to update. This ensures that the record you update is the same record you selected earlier (be it 40 seconds or 40 milliseconds earlier).

    The alternative is optimistic locking, where you do not select with FOR UPDATE, but instead explicitly verify that the record you update is the same one you selected by inspection - e.g.

    Code:
    UPDATE emp
    SET sal = sal*1.1
    WHERE empno = cursor_rec.empno
    AND sal = cursor_rec.sal; -- This line ensures no one else changed the salary already
    If you don't use either of these approaches then you will end up with "lost updates", where 2 users work on the same record simultaneously and one trashes the others work unintentionally.

Posting Permissions

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