Re: SHARE locks vs. DELETE in SERIALIZABLE mode (Was: Partitioning/inherited tables vs FKs)

2010/5/11 Florian Pflug <fgp(at)phlo(dot)org>:
> On May 11, 2010, at 13:29 , Robert Haas wrote:
>> On Tue, May 11, 2010 at 2:16 AM, Dmitry Fefelov <fozzy(at)ac-sw(dot)com> wrote:
>>>> The referential integrity triggers contain some extra magic that isn't
>>>> easily simulatable in userland, and that is necessary to make the
>>>> foreign key constraints airtight.  We've discussed this previously but
>>>> I don't remember which thread it was or the details of when things
>>>> blow up.  I think it's something like this: the parent has a tuple
>>>> that is not referenced by any child.  Transaction 1 begins, deletes
>>>> the parent tuple (checking that it has no children), and pauses.
>>>> Transaction 2 begins, adds a child tuple that references the parent
>>>> tuple (checking that the parent exists, which it does), and commits.
>>>> Transaction 1 commits.
>>>
>>> Will SELECT ... FOR SHARE not help?
>>
>> Try it, with the example above.  I think you'll find that it doesn't.
>
> That example does in fact work. Here is the precise sequence of commands I tested with constraint checking triggers implemented in PL/PGSQL.
[...]
> The serialization error, however, disappears if the two transactions are swapped. The following sequence of commands succeeds, even though the FK constraint is not satisfied.

Thanks for figuring this out. I thought there was a case like this
but I couldn't remember exactly how to reproduce it.

> C1: BEGIN
> C1: INSERT INTO child (parent_id) VALUES (0)
> C2: BEGIN
> C2: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> C2: SELECT TRUE -- Take snapshot *before* C1 commits
> C1: COMMIT
> C2: DELETE FROM parent WHERE parent_id = 0 -- Works!
> C2: COMMIT
>
> It seems that while SHARE-locking a concurrently deleted row causes a serialization error, deleting a concurrently SHARE-locked is allowed. I do wonder if this shouldn't be considered a bug - whether locks conflict or not does not usually depend on the other in which they are taken.

Wait - I'm confused. The DELETE in your example happens after C1
commits, so C1 can't still be holding any locks (nor does C2 take any
locks prior to the commit of C1).

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company