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
