Re: Avoiding deadlocks ...

Josh Berkus  wrote:
>> Two sessions, in transaction:
>>
>> Process A        Process B
>>
>> update session where id = X;
>>             update order where orderid = 5;
>> update order where orderid = 5;
>>             update order where orderid = 5;
>> ... deadlock error.
>
> Johto on IRC pointed out I left something out of the above:
> "session" is referenced in an FK by "orders", and session = X is
> related to orderid = 5.
The patch I'm offering implements the SSI techniques published by
Michael Cahill, et al.  Those techniques basically allow the current
snapshot isolation to run as it currently does, but monitors for
read/write conflicts to generate a new type of serialization failure
when a cycle becomes possible which could create an anomaly.  There
are no read/write conflict cycles in your example, so it would behave
just as REPEATABLE READ and SERIALIZABLE now behave -- you get a
deadlock which rolls back one of the transactions.
I don't see how SSI can be modified to generate some other form of
serialization failure here, but I'm always open to suggestions.
-Kevin