Kevin,
> In the "for what it's worth" department, I tried out the current
> Serializable Snapshot Isolation (SSI) patch with this test case at
> the SERIALIZABLE transaction isolation level. Rather than defining
> a foreign key, I ran the queries which an SSI implementation in a
> SERIALIZABLE-only environment would -- that didn't use FOR SHARE or
> FOR UPDATE. Not surprisingly, the behavior was the same up to the
> second UPDATE on Process 2, at which point there was no deadlock.
> Process 2 was able to commit, at which point Process 1 failed with:
>
> ERROR: could not serialize access due to concurrent update
Does this happen immediately, not waiting 2 seconds for deadlock checking?
> If you have other examples of "user-hostile" behaviors you want to
> share, I can see how they would behave under an SSI implementation.
> I can almost guarantee that you won't see deadlocks, although you
> will likely see more overall rollbacks in many transaction mixes.
They'd be more variations of this same theme; transactions updating each
other's FKs.
-- -- Josh Berkus PostgreSQL Experts Inc.
http://www.pgexperts.com