Re: High rate of transaction failure with the Serializable Isolation Level

On 07/25/2014 03:50 AM, Reza Taheri wrote:
> Hi Craig,
>> It's not just that statement that is relevant.
>> Is that statement run standalone, or as part of a larger transaction?
>
> Yes, the "size" of the transaction seems to matter here. It is a complex transaction (attached). Each "frame" is one
storedprocedure, and the 6 frames are called one after the other with no pause. After frame6 returns, we call
SQLTransact(...,...,  SQL_COMMIT). Below is the failure rate of the various frames: 
>
>     112 tid 18883: SQL Failed: DoTradeResultFrame3
>     102 tid 18883: SQL Failed: DoTradeResultFrame4
>   18188 tid 18883: SQL Failed: DoTradeResultFrame5
>    8566 tid 18883: SQL Failed: DoTradeResultFrame6
>    4492 tid 18883: ERROR: TradeResultDB: commit failed
>
> So, no failures in frames 1 and 2, and then the failure rate grows as we approach the end of the transaction.

According to the attached SQL, each frame is a separate phase in the
operation and performs many different operations.

There's a *lot* going on here, so identifying possible interdependencies
isn't something I can do in a ten minute skim read over my morning coffee.

I think the most useful thing to do here is to start cutting and
simplifying the case, trying to boil it down to the smallest thing that
still causes the problem.

That'll likely either find a previously unidentified interdependency
between transactions or, if you're unlucky, a Pg bug. Given the
complexity of the operations there I'd be very surprised if it wasn't
the former.

>> If the INSERTing transaction previously queried for a key that was created by a concurrent transaction this can
occuras there is no serialization 
>> execution order of the transactions that could produce the same result.
>
> As far as the inserts, your point is well-taken. But in this case, I have eliminated the transactions that query or
otherwisemanipulate the SETTELEMENT table. The only access to it is the single insert in this transaction 

If there are foreign keys to it from other tables, they count too.

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services