Is row locking atomic in Update...Where...?

I want to update a number field in up to 500 records at a time in a transaction that consists simply of an Update...Where statement followed by a commit.

The trouble is, more than one session will perform this transaction independently and they will often be operating on overlapping sets of rows.

Is deadlock possible in this particular situation, or will one session's update always wait for the other to commit if there are any overlapping rows?

For example, if the following two statements are executed in quick succession in two different sessions (S1 and S2), and the second happens before the first commits:

S1: UPDATE logtable SET counter = counter + 1 WHERE id IN (1,3,5,8,9); S2 UPDATE logtable SET counter = counter + 1 WHERE id IN (2,5,6,7,8,9);

is it possible that S2 would get a lock on record 5 and S1 would get a lock on record 8, with the result that one would have to fail? Or would S2 always block until S1 commits?

In this particular application, updates would be so frequent that I'd rather not use NOWAIT and retry. Received on Sun Apr 28 1996 - 00:00:00 CEST