SQL Question about atomicity and timing of UPDATES

If I have a table tbl with one record:

A B
- -
1 1

and I execute:

UPDATE tbl SET A=B+1, B=2

          A B A B
do I get - - or - - ?

          2 2 3 2

And am I guaranteed to always get that one? I would have expected 2,2 and indeed it usually appears I do. But sometimes I appear to get results that I can only explain if I was getting the equivalent of 3,2.

The actual query in hand is more like

UPDATE tbl SET A=decode(B,2,A+1,1), B=2

and I appear to be getting A incremented even when B=1, and B becomes 2. This only happens under heavy load, when I'm testing it it always seems to work atomically as expected.

--
greg Received on Tue Oct 26 1999 - 14:38:54 CDT