From: Reza Taheri <rtaheri@vmware.com>
To: Craig Ringer <craig@2ndquadrant.com>, "pgsql-performance@postgresql.org"
 <pgsql-performance@postgresql.org>
Subject: Re: High rate of transaction failure with the
 Serializable Isolation Level
Thread-Topic: [PERFORM] High rate of transaction failure with the
 Serializable Isolation Level
Thread-Index: Ac+m3REUfhN8/sxlQTylzpfYlwqkMQAWWGKAAA8AZQAAHAtQgAAVfZpg
Date: Fri, 25 Jul 2014 18:58:06 +0000
Message-ID: <80d78b6fec064f14aa025c1ca86c8099@EX13-MBX-013.vmware.com>
References: <ded604822b28427a80b0751a973e4258@EX13-MBX-013.vmware.com>
 <53D0923B.7050501@2ndquadrant.com>
 <93e2642196e646adbcf5780d3cf54685@EX13-MBX-013.vmware.com>
 <53D1B31B.8070909@2ndquadrant.com>
In-Reply-To: <53D1B31B.8070909@2ndquadrant.com>
Accept-Language: en-US
Content-Language: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hi Craig,

> According to the attached SQL, each frame is a separate phase in the oper=
ation 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. J=
ust to put this in perspective, the transaction flow and its statements are=
 borrowed verbatim from the TPC-E benchmark. There have been dozens of TPC-=
E disclosures with MS SQL Server, and there are Oracle and DB2 kits that, a=
lthough not used in public disclosures for various non-technical reasons, a=
re 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 will be due to a mistake we made=
 in coding this for PGSQL. Otherwise, we will have a hard time convincing a=
ll the council member companies that we need to change the schema or the bu=
siness 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 fir=
st 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 Serializ=
able
> Isolation Level
>=20
> 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.
>=20
> According to the attached SQL, each frame is a separate phase in the
> operation and performs many different operations.
>=20
> 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.
>=20
> I think the most useful thing to do here is to start cutting and simplify=
ing the
> case, trying to boil it down to the smallest thing that still causes the =
problem.
>=20
> That'll likely either find a previously unidentified interdependency betw=
een
> 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.
>=20
> >> 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
>=20
> If there are foreign keys to it from other tables, they count too.
>=20
> --
>  Craig Ringer
> https://urldefense.proofpoint.com/v1/url?u=3Dhttp://www.2ndquadrant.com
> /&k=3DoIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0A&r=3Db9TKmA0CPjroD2HLPTH
> U27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=3DSLSpdQUFSC%2BXlQIgotLSghfyEB
> qC7q8Sh1AEizZ3pBw%3D%0A&s=3Dceb740d5d6686cda7ed9dd31b4dce2de0eda
> 3cf3a46ffead645c5bb6d9e7ec5c
>  PostgreSQL Development, 24x7 Support, Training & Services


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance