Thread: serialization failure why?

On Tue, 16 Jun 2015 13:33:12 +0001
Filipe Pina <filipe.pina@impactzero.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@potentialtech.com>

Filipe Pina <filipe.pina@impactzero.pt> wrote:

> if drop the foreign key constraint on stuff_ext table there are
> no failures at all…

It is my recollection that we were excluding the queries used to
enforce referential integrity constraints from the conflict
tracking, so I am surprised you are seeing this.  What is the exact
version you are using (as reported by the version() function)?

I am at a conference this week, away from my normal development
environment; but I will take a look next week.

Kevin Grittner

Simon Riggs <simon@2ndQuadrant.com> wrote:
> On 17 June 2015 at 13:52, Kevin Grittner <kgrittn@ymail.com> wrote:
>> Filipe Pina <filipe.pina@impactzero.pt> wrote:

>>> if drop the foreign key constraint on stuff_ext table there are
>>> no failures at all…
>>
>> It is my recollection that we were excluding the queries used to
>> enforce referential integrity constraints from the conflict
>> tracking, so I am surprised you are seeing this.  What is the exact
>> version you are using (as reported by the version() function)?
>
> I don't see any mechanism for excluding anything from
> serializable checks, so I can't see how that would work.

It is a matter of where calls to PredicateLockXxx and
CheckForSerializableConflictXxx calls were inserted into, for
example, heap and index AM code.  At least I think we omitted
placing some at locations which were known to be used for RI
enforcement; but apparently some more generic code is exercised by
the RI trigger execution which can still trigger serialization
failures based on FKs.

> I can't find any mention of serializability concerns in the RI
> code itself.

It is mentioned in the README-SSI file.

> AFAIK it would be strange to exclude FK checks from
> serializability checks, since they represent a valid observation
> of an intermediate state.

The idea that this is OK is based on the observations in the paper
"Automating the Detection of Snapshot Isolation Anomalies" by
Sudhir Jorwekar, Alan Fekete, Krithi Ramamritham, and S.
Sudarshan[1].  To quote a key sentence from that paper:

| The database system ensures the preservation of some integrity
| constraints which are explicitly declared to the system in the
| schema definition, such as uniqueness of primary key and
| referential integrity.  Some of the SI anomalies are avoided due
| to the dbms enforcement of these constraints.

For all the gory details, please reference that paper.  It was
discussed during PostgreSQL SSI development, although I'm not sure
whether that made it to the lists or was off-list discussion amongst
those working on it at the time.  Basically, the anomalies are
avoided due to the enforcement of the constraints, and it is next
to impossible to generate a serialization failure instead of the
constraint failure due to the timings involved.  Since the
constraints are more narrowly targeted (always at the tuple level),
using SSI techniques would be redundant effort (hurting
performance) that could only generate false positives.  If you see
some exception to that which we missed, let's discuss.

Perhaps that paper should be cited in the source code and/or
README.

> Mat Views are excluded but I don't understand why that should be
> the case.  There is no documented explanation.

Good point; it should be documented.  Basically, since the matview
is a materialized copy of data from other relations from some prior
point in time, the race conditions caught by SSI would be trivial
compared to those likely to exist based on the elapsed time since
the last REFRESH; so it would be kind of silly to try to enforce
the more subtle interactions while ignoring the big, glaring,
obvious one.  It would be a bit like treating a laceration of
someone's hand when they were not breathing -- it's not the thing
to worry about.  As we enhance matviews to have associated
freshness information and especially once we use them like indexes
to optimize queries this will deserve a close look, as there is
likely to be something meaningful we can do at that time.

Anyway, it appears that there is more that could be done to avoid
generating serialization failures based on the actions of RI
enforcement triggers.  I don't think we can properly call it a bug,
since it doesn't allow an incorrect state to be created; but it is
clearly an opportunity for performance enhancement of the feature.
One of many, unfortunately.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


[1] http://www.vldb.org/conf/2007/papers/industrial/p1263-jorwekar.pdf