From: Craig Ringer <craig@2ndquadrant.com>
To: Reza Taheri <rtaheri@vmware.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: High rate of transaction failure with the Serializable Isolation Level
Date: Fri, 25 Jul 2014 09:30:03 +0800
Message-ID: <53D1B31B.8070909@2ndquadrant.com> (raw)
In-Reply-To: <93e2642196e646adbcf5780d3cf54685@EX13-MBX-013.vmware.com>
References: <ded604822b28427a80b0751a973e4258@EX13-MBX-013.vmware.com>
<53D0923B.7050501@2ndquadrant.com>
<93e2642196e646adbcf5780d3cf54685@EX13-MBX-013.vmware.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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 http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig@2ndquadrant.com, rtaheri@vmware.com
Subject: Re: High rate of transaction failure with the Serializable Isolation Level
In-Reply-To: <53D1B31B.8070909@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox