Re: More FOR UPDATE/FOR SHARE problems

On Mon, 2009-01-26 at 15:46 -0600, Kevin Grittner wrote:
> After the COMMIT succeeds, the locks from Session1 are released. 
> Session2 acquires its update lock and reads row 2, finds that it
> doesn't match its update criteria, downgrades the lock to shared,
> acquires an update lock on row 3, finds that it does match the
> selection criteria, upgrades the lock to exclusive, updates it,
> acquires and update lock on row 4 finds that it doesn't match the
> update criteria, downgrades the lock to shared, hits the end of table,
> releases the shared locks.

This is the part I'm having a problem with. This depends on row 3 being
read after row 2. If that weren't the case (say, with a more complex
update and a more complex search criteria), then the index scan would
have already passed by the value and would never know that it was
updated to a value that does match the search criteria.

Data:i  j
--------1  202  403  504  80

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

S2: BEGIN; UPDATE a SET j = j + 100 WHERE j = 10 or j = 40; -- Here, the index scan is already past j=10 by the time --
itblocks on a concurrently-updated tuple
 

S1: COMMIT;

S2: COMMIT;

In PostgreSQL this sequence results in:i | j  
---+----1 | 204 | 802 | 303 | 40

The second update matched no tuples at all.

> Let me restate -- I don't propose that PostgreSQL implement this
> locking scheme.  I think it can and should do better in approaching
> compliance with the standard, and with ACID properties, without
> compromising concurrency and performance to the degree required by
> this sort of locking and blocking.

I think Greg has it right: without predicate locking we can't really
achieve the behavior you're expecting. So how would we better approach
the semantics you want without it?

Regards,Jeff Davis