Re: [Glue] Deadlock bug

Josh Berkus <josh@agliodbs.com> wrote:
> That's correct. This is the generic example I was talking about
> earlier on -hackers.  I'm not certain it's a bug per spec; I
> wanted to talk through with Kevin what we *should* be doing in
> this situation.
I'm certainly happy to address what impact the SSI patch will have
on such behavior, and I've been known to have opinions on related
issues, but I don't know if I can carry the weight you seem to be
suggesting with that statement.  ;-)
[gamely doing my best...]
In general, the spec defines levels less strict than serializable
(and also serializable in spec versions before 1999) in terms of
anomalies which are prohibited, with the database being allowed to
block and/or generate serialization failures as needed to prevent
the anomalies.  In the 1999 version and later there is the
additional requirement that behavior of concurrent serializable
transactions which successfully commit be consistent with *some*
serial execution of those transactions.
I don't see anything in PostgreSQL's current behavior on the
particular example you raised which isn't compliant with the spec,
even if it is surprising.  (Well, with the exception of the SQLSTATE
used for deadlocks, which in my opinion should be '40001'.)
> This is one example of a set of user-hostile FK-related deadlock
> behavior we have.  I'm just not certain it's logically possible to
> improve it.
If there are a lot of user-hostile behaviors there, it might be
worth looking at the possibility of bending the SSI techniques to
that end, although I think it would be a mistake to burden the
initial patch with that.  Off the top of my head, I think it would
require extending much of the SSI behavior to most of the DML
execution on tables which participate in FK relationships,
regardless of transaction isolation level.  I'm not sure if that's
even feasible -- if it is, someone would need to work out a solid
theoretical basis for why and how it would work.  It might be that
the only way SSI could cover FK relationships is if there was a
database or cluster option to make all transactions fully
serializable.  (NOTE: If there were, *I* would use it, since it
would guarantee that I could rely upon any business rules enforced
by database triggers, which I would consider a valuable guarantee.)
-Kevin