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

Hi Craig,

> 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.

You didn't think I was going to bug you all with a trivial problem, did you? :-) :-)

Yes, I am going to have to take an axe to the code and see what pops out. Just to put this in perspective, the
transactionflow and its statements are borrowed verbatim from the TPC-E benchmark. There have been dozens of TPC-E
disclosureswith MS SQL Server, and there are Oracle and DB2 kits that, although not used in public disclosures for
variousnon-technical reasons, are used internally in by the DB and server companies. These 3 products, and perhaps
more,were used extensively in the prototyping phase of TPC-E. 

So, my hope is that if there is a "previously unidentified interdependency between transactions" as you point out, it
willbe due to a mistake we made in coding this for PGSQL. Otherwise, we will have a hard time convincing all the
councilmember companies that we need to change the schema or the business logic to make the kit work with PGSQL. 

Just pointing out my uphill battle!!

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

Yes, we have a lot of foreign keys. I dropped them all a few weeks ago with no impact. But when I start the axing
process,they will be one of the first to go 

Thanks,
Reza

> -----Original Message-----
> From: Craig Ringer [mailto:craig@2ndquadrant.com]
> Sent: Thursday, July 24, 2014 6:30 PM
> To: Reza Taheri; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] 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 stored procedure, 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 occur as 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 otherwise manipulate
> > 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
> https://urldefense.proofpoint.com/v1/url?u=http://www.2ndquadrant.com
> /&k=oIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0A&r=b9TKmA0CPjroD2HLPTH
> U27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=SLSpdQUFSC%2BXlQIgotLSghfyEB
> qC7q8Sh1AEizZ3pBw%3D%0A&s=ceb740d5d6686cda7ed9dd31b4dce2de0eda
> 3cf3a46ffead645c5bb6d9e7ec5c
>  PostgreSQL Development, 24x7 Support, Training & Services