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

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.

C1: BEGIN
C1: DELETE FROM parent WHERE parent_id = 0
C2: BEGIN
C2: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE -- Optional
C2: INSERT INTO child (parent_id) VALUES (0) -- Waits for C1 to commit
C1: COMMIT -- Now C2 fails either with a constraint_violation or serialization_error

The reason this works is that C2's attempt to SHARE-lock the parent row blocks until C1 commits. In READ COMMITTED mode C2 will then realize that the parent row is now gone. In SERIALIZABLE mode it won't get that far, because the SHARE-locking attempt throws a serialization error since the parent row was concurrently modified.

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.

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.

The build-in constraint triggers avoid the second case by checking not only for rows visible under the transaction's snapshot but also for rows visible under a freshly taken snapshot in the ri_parent PERFORM statement. I do wonder if the recheck was still needed if the DELETE in the second case threw a serialization_error also. Does anyone have an example that proves it necessary?

best regards,
Florian Pflug

Here are the table definitions and trigger functions I used:

CREATE TABLE parent (parent_id SERIAL NOT NULL PRIMARY KEY);
CREATE TABLE child (child_id SERIAL NOT NULL PRIMARY KEY, parent_id INTEGER NOT NULL);

CREATE FUNCTION ri_parent() RETURNS TRIGGER AS $body$
BEGIN
PERFORM TRUE FROM child WHERE parent_id = OLD.parent_id;
IF FOUND THEN
RAISE SQLSTATE '23503' USING MESSAGE = 'Parent ' || OLD.parent_id || ' still referenced during ' || TG_OP;
END IF;
RETURN NULL;
END;
$body$ LANGUAGE PLPGSQL VOLATILE;
CREATE TRIGGER ri_parent AFTER UPDATE OR DELETE ON parent FOR EACH ROW EXECUTE PROCEDURE ri_parent();

CREATE FUNCTION ri_child() RETURNS TRIGGER AS $body$
BEGIN
PERFORM TRUE FROM parent WHERE parent_id = NEW.parent_id FOR SHARE OF parent;
IF NOT FOUND THEN
RAISE SQLSTATE '23503' USING MESSAGE = 'Parent ' || NEW.parent_id || ' does not exist during ' || TG_OP;
END IF;
RETURN NULL;
END;
$body$ LANGUAGE PLPGSQL VOLATILE;
CREATE TRIGGER ri_child AFTER INSERT OR UPDATE ON child FOR EACH ROW EXECUTE PROCEDURE ri_child();