Re: More FOR UPDATE/FOR SHARE problems

On Mon, 2009-01-26 at 14:31 -0600, Kevin Grittner wrote:
> > Do you re-run the query to find new tuples that might now satisfy
> > the search condition that didn't before?
>
> There can't be any. Blocks taken during the reading of rows so far
> have not been released, and would preclude the update from changing
> results read so far.

Let's say the sequence is:

Data:
i j
--------
1 10
2 10
3 20
4 20

Session1:
BEGIN;
UPDATE a SET j = (j - 10) WHERE i = 2 OR i = 3;

Session2:
BEGIN;
UPDATE a SET j = j + 100 WHERE j = 10;

Session1:
COMMIT;

Session2:
COMMIT;

In PostgreSQL, the result is:

i | j
---+-----
4 | 20
2 | 0
3 | 10
1 | 110
(4 rows)

Which cannot be obtained by any serial execution. What is the result in
Sybase, Oracle, etc.?

It seems like it would be a challenge to know that the tuple with i=3
would be updated to a value that matches the search condition j=10. So
can you tell me a little more about the mechanism by which Sybase solves
this problem?

Regards,
Jeff Davis