2010/5/11 Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi>:
> On 2010-05-11 14:29 +0200, Robert Haas wrote:
>
>> On Tue, May 11, 2010 at 2:16 AM, Dmitry Fefelov <fozzy@ac-sw.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.
>
> TXA => delete from foo;
> DELETE 1
>
> TXB => select a from foo for share; -- waits
>
> What am I missing?
Slightly verbose example of what can go wrong:
CREATE TABLE a (i int PRIMARY KEY);
INSERT INTO a VALUES (1);
CREATE TABLE b (a_id int);
>>>>>> Start with T1:
T1> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
T1> SELECT i FROM a WHERE i = 1 FOR SHARE; -- Does a with i = 1 exist?i
---1
(1 Zeile)
T1> INSERT INTO b VALUES (1); -- Great, it existed, insert row
pointing to it in b.
INSERT 0 1
>>>>>> Switch to T2:
T2> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- Evil
transaction T2 is intervening!
BEGIN
T2> SELECT i FROM a WHERE i = 1 FOR SHARE; -- Lock a with i = 1 FOR SHARE.i
---1
(1 Zeile)
T2> SELECT a_id FROM b WHERE a_id = 1; -- Check whether it's got
anything pointing to it.a_id
------
(0 Zeilen)
T2> DELETE FROM a WHERE i = 1; -- Nope, so delete a with i = 1 (this
blocks, because T1 is still holding the lock).
>>>>>> Switch to T1:
1> COMMIT; -- Commit the insertion of a row pointing to a with i = 1
(this releases all locks that T1 is holding).
COMMIT
>>>>>> T2 continues:
DELETE 1
T2> COMMIT; -- Commit the deletion of a with i = 1.
COMMIT
T2> SELECT * FROM b EXCEPT SELECT * FROM a; -- Check for inconsistencies.a_id
------ 1
(1 Zeile)
Woops.
Nicolas
