I wrote:
> 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
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
I would have been surprised by any other outcome, but it seemed
worth a quick test. I already have routine tests (under Markus
Wanner's dtester tool) to confirm that simple queries to enforce
referential integrity under SSI correctly prevent any violations of
referential integrity. SSI could be a solution in some environments
*if* all relevant transactions can be run at the SERIALIZABLE
transaction isolation level and the software can deal with the
inevitable (although hopefully infrequent) serialization failures
due to false positives from the SSI algorithm.
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.
-Kevin