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 20
2 40
3 50
4 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
-- it blocks on a concurrently-updated tuple
S1:
COMMIT;
S2:
COMMIT;
In PostgreSQL this sequence results in:
i | j
---+----
1 | 20
4 | 80
2 | 30
3 | 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
