Re: More FOR UPDATE/FOR SHARE problems

On Mon, 2009-01-26 at 13:50 -0600, Kevin Grittner wrote:
> A somewhat dated description for Sybase (it predates their support of
> row level locks and the related predicate locks on indexes) is here:
>
> http://manuals.sybase.com/onlinebooks/group-asarc/srv10024/sag/@Generic__BookTextView/41766;pt=41535/*
>
> Simplified, in a READ COMMITTED transaction a SELECT takes a lock
> which conflicts with update before reading, and holds it until the
> executing statement is done with that table; an UPDATE takes a lock
> which conflicts with any access (read or write) before it updates a
> row, and holds it until COMMIT or ROLLBACK. This guarantees that the
> SELECT doesn't see the results of an incomplete UPDATE, at the expense
> of taking locks, blocking, and possible serialization failures (in the
> form of deadlocks).
>

That doesn't quite answer the question about UPDATE specifically.
Pretend for a second that SELECT ... FOR UPDATE is like UPDATE ...
RETURNING. The example in my original email can be easily changed to use
UPDATE ... RETURNING.

The tricky part is when an UPDATE with a search condition reads,
modifies, and writes a value that is used in a search condition for
another UPDATE.

Every DBMS will block waiting for the first UPDATE to finish. Then what?
Do you re-run the query to find new tuples that might now satisfy the
search condition that didn't before? Do you update the new value in all
the tuples you originally found, regardless of whether they still match
the search condition? Do you update the new value in all the tuples that
you found that still match the search condition? Do you update the old
value, perhaps overwriting changes made by the first UPDATE?

What does sybase do in that case?

Regards,
Jeff Davis