Re: Row-level Locks & SERIALIZABLE transactions, postgres vs. Oracle

On May 14, 2010, at 2:37 , Greg Stark wrote:

> On Thu, May 13, 2010 at 10:25 PM, Florian Pflug <fgp@phlo.org> wrote:
>> C1: BEGIN
>> C1: SELECT * FROM t WHERE id = 1 FOR UPDATE
>> C2: BEGIN
>> C2: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
>> C2: SELECT * FROM t -- Take snapshot before C1 commits
>> C1: COMMIT
>> C2: DELETE FROM t WHERE id = 1
>> C2: COMMIT
>>
>
> Can you give an actual realistic example -- ie, not doing a select for
> update and then never updating the row or with an explanation of what
> the programmer is attempting to accomplish with such an unusual
> sequence? The rest of the post talks about FKs but I don't see any
> here...

The table "t" is supposed to represent the parent table of a FK constraint. The SELECT FOR UPDATE is done upon an
INSERTto the child table to protect the parent row against concurrent deletion. I've used FOR UPDATE instead of FOR
SHAREbecause I did test this against oracle also, and oracle does not support FOR SHARE.  

Here's a full example of a pair of FK triggers in PL/PGSQL that work correctly in READ COMMITTED mode but fail to
enforcethe constraint in SERIALIZABLE mode as the following sequence of commands show. With my proposal, the DELETE
wouldagain raise a serialization error and hence keep the constraint satisfied. 

C1: BEGIN
C1: INSERT INTO child (parent_id) VALUES (1) -- Locks the parent row FOR UPDATE
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 = 1 -- Succeeds
C2: COMMIT

----------
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$
BEGINPERFORM 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$
BEGINPERFORM TRUE FROM parent WHERE parent_id = NEW.parent_id FOR UPDATE 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();
----------

best regards,

Florian Pflug