From: | Jeff Davis <pgsql(at)j-davis(dot)com> |
---|---|
To: | Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> |
Cc: | npboley(at)gmail(dot)com, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Subject: | Re: More FOR UPDATE/FOR SHARE problems |
Date: | 2009-01-27 01:16:31 |
Message-ID: | 1233018991.19843.99.camel@dell.linuxdev.us.dell.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ron Mayer | 2009-01-27 01:22:06 | Re: 8.4 release planning |
Previous Message | Tom Lane | 2009-01-27 01:11:34 | Re: 8.4 release planning |