Hi

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

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?

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.

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?