Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003

    Question Unanswered: To Wait Or Not To Wait


    User A
    (Committed read)
    1.Begin Work
    2."Select for update" of a row (id=1)

    User B
    (Committed read)
    1.Se Lock Mode to Wait
    2.Select where id>0 (there are are rows with id=1,id=2,id=3)
    3.Fetch the rows

    After step 2 User B have to wait until User A ends the transaction...

    I wonder why if User B SET LOCK MODE TO NO WAIT, he can fetch the rows without problem. I think he should get an error message.
    Or am I wrong?

  2. #2
    Join Date
    Aug 2002
    User A has only a declared a cursor 'for update'.
    This places a promotable ('U') lock on the row which
    has been fetched with the update cursor (or on the page
    if you have page level locking on that table).

    An 'U' lock does only prevent additional 'U' locks or
    exclusive locks ('X') on this object.
    It does not prevent other 'shared locks' ('S') on this
    object. So User B is able to fetch this row.

    In addition 'committed read' does not really place
    a shared lock on the row. It only tests if it could
    set a shared lock on the row (to avoid the reading
    of phantom rows) but it doesn't actually place the
    shared lock on the row (in contrast to the isolation
    levels 'cursor stability' or 'repeatable read').

    That said, you can only be sure that nobody else is
    able to place a shared lock ('S') on an object where you
    already placed a promotable lock ('U') on, by making
    a 'dummy update' on this row which promotes the 'U'
    lock to an 'X' lock. Now, nobody else is allowed to
    place any additional locks on the same object.

    Best regards

    IT-Consulting Herber

    Download the IFMX Database-Monitor for free at:

  3. #3
    Join Date
    Jun 2003
    Thank you very much Eric

    But I still can't understand why if user B makes a SET LOCK MODE TO WAIT he has to wait until transaction of user A has finished and if user B makes a SET LOCK MODE TO NOT WAIT he doesn't have to wait and there's no error message.

    Why user B has to wait in the first case? Because of the "COMMMITTED READ" or there's another reason?

Posting Permissions

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