Re: serialization failure why?

On Tue, 16 Jun 2015 13:33:12 +0001
Filipe Pina <filipe(dot)pina(at)impactzero(dot)pt> wrote:

> I have these 2 tables:
>
> CREATE TABLE "stuff" ("number" integer NOT NULL PRIMARY KEY, "title"
> varchar(40) NOT NULL);
> CREATE TABLE "stuff_ext" ("stuff_ptr_id" integer NOT NULL PRIMARY KEY,
> "extra" integer NOT NULL);
> ALTER TABLE "stuff_ext" ADD CONSTRAINT
> "stuff_ext_stuff_ptr_id_5a4ee8edae53404b" FOREIGN KEY ("stuff_ptr_id")
> REFERENCES "stuff" ("number") DEFERRABLE INITIALLY DEFERRED;
> CREATE SEQUENCE stuff_seq;
>
> And then the function:
>
> CREATE OR REPLACE FUNCTION create_stuff(number integer, title text)
> RETURNS integer AS $$
> DECLARE
> a1 stuff;
> a2 stuff_ext;
> BEGIN
> IF number IS NULL THEN
> number := nextval('stuff_seq');
> END IF;
>
> a1.number := number;
> a1.title := title;
>
> a2.stuff_ptr_id := a1.number;
>
> INSERT INTO stuff VALUES (a1.*);
> INSERT INTO stuff_ext VALUES (a2.*);
>
> RETURN number;
> END
> $$
> LANGUAGE plpgsql;
>
>
> The DB is configured for SERIALIZABLE transaction mode.
>
> Now, if I can the function without passing number, such as:
>
> select create_stuff(NULL,'title');
>
> in 10 forked processes in a loop with a few iterations in each, I get
> quite a few SERIALIZATON FAILURE (sqlstate 40001).
>
> If I comment out the "INSERT INTO stuff_ext" line, I don't get any.
>
> How is the second insert causing serialize dependencies...?

I'm not sure this is correct, but I have a theory.

Essentially, PostgreSQL can't be sure that the foreign key will be
valid if the other transaction rolls back. i.e., what if the foreign
key is valid becuase the other transaction created the matching row,
and that other transaction then rolls back?

In other isolation modes, it can just wait for the appropriate lock to
free up, then see what happens. But in serializable mode it hits a
condition where it can't ensure serializability.

> The specific error messages vary between
>
> ERROR: could not serialize access due to read/write dependencies among
> transactions
> DETAIL: Reason code: Canceled on identification as a pivot, during
> commit attempt.
> HINT: The transaction might succeed if retried.
>
> and
>
> ERROR: could not serialize access due to read/write dependencies among
> transactions
> DETAIL: Reason code: Canceled on commit attempt with conflict in from
> prepared pivot.
> HINT: The transaction might succeed if retried.
>
> Thanks!

--
Bill Moran <wmoran(at)potentialtech(dot)com>