Re: High rate of transaction failure with the Serializable Isolation Level - Mailing list pgsql-performance

Hi Kevin,
Thanks for the reply

> As already pointed out by Craig, statements don't have serialization failures; transactions do.  In some cases a
transactionmay become 
> "doomed to fail" by the action of a concurrent transaction, but the actual failure cannot occur until the next
statementis run on the 
> connection with the doomed transaction; it may have nothing to do with the statement itself.

That's an interesting concept. I suppose I could test it by moving statements around to see what happens.


> Note that I have seen reports of cases where max_pred_locks_per_transaction needed to be set to 20x the default to
> reduce serialization failures to an acceptable level.


I was running with the following two parameters set to 640; I then raised them to 6400, and saw no difference

max_locks_per_transaction = 6400
max_pred_locks_per_transaction = 6400

Thanks,
Reza

> -----Original Message-----
> From: Kevin Grittner [mailto:kgrittn@ymail.com]
> Sent: Thursday, July 24, 2014 7:03 AM
> To: Reza Taheri; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] High rate of transaction failure with the Serializable
> Isolation Level
>
> Reza Taheri <rtaheri@vmware.com> wrote:
>
> > I am running into very high failure rates when I run with the
> > Serializable Isolation Level. I have simplified our configuration to a
> > single database with a constant workload, a TPC-E workload if you
> > will, to focus on this this problem. We are running with PGSQL 9.2.4
>
> I don't remember any bug fixes that would be directly related to what you
> describe in the last 15 months, but it might be better to do any testing with
> fixes for known bugs:
>
> https://urldefense.proofpoint.com/v1/url?u=http://www.postgresql.org/su
> pport/versioning/&k=oIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0A&r=b9TKm
> A0CPjroD2HLPTHU27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=H8C2sdUv2dsUC
> 9oH2yzDssdTbCEBF5mbQZbZ871laGw%3D%0A&s=6522bd258d0a034429522b
> 61239134b07f1cabc086e8c2cb330aa9c9bc4a337d
>
> > When we raise the Trade-Result transaction to SQL_TXN_SERIALIZABLE, we
> > face a storm of conflicts. Out of
> > 37,342 Trade-Result transactions, 15,707 hit an error, and have to be
> > rolled back and retired one or more times. The total failure count
> > (due to many transactions failing more than once) is 31,388.
> >
> > What is unusual is that the majority of the failures occur in a
> > statement that should not have any isolation conflicts.
>
> As already pointed out by Craig, statements don't have serialization failures;
> transactions do.  In some cases a transaction may become "doomed to fail"
> by the action of a concurrent transaction, but the actual failure cannot occur
> until the next statement is run on the connection with the doomed
> transaction; it may have nothing to do with the statement itself.
>
> If you want to understand the theory of how SERIALIZABLE transactions are
> implemented in PostgreSQL, these links may help:
>
> https://urldefense.proofpoint.com/v1/url?u=http://vldb.org/pvldb/vol5/p1
> 850_danrkports_vldb2012.pdf&k=oIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0
> A&r=b9TKmA0CPjroD2HLPTHU27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=H8C
> 2sdUv2dsUC9oH2yzDssdTbCEBF5mbQZbZ871laGw%3D%0A&s=d1b8cd62c431
> c267124c21d4e639c98eebb650caaf8fd05ba47aa825a9b54a52
>
> https://urldefense.proofpoint.com/v1/url?u=http://git.postgresql.org/gitwe
> b/?p%3Dpostgresql.git%3Ba%3Dblob_plain%3Bf%3Dsrc/backend/storage/lm
> gr/README-
> SSI%3Bhb%3Dmaster&k=oIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0A&r=b9T
> KmA0CPjroD2HLPTHU27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=H8C2sdUv2d
> sUC9oH2yzDssdTbCEBF5mbQZbZ871laGw%3D%0A&s=1f60010253b8012dbe5
> e5a51af48fcb831dae81200708f620438e6afb48c0eef
>
> https://urldefense.proofpoint.com/v1/url?u=http://wiki.postgresql.org/wiki
> /Serializable&k=oIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0A&r=b9TKmA0CPj
> roD2HLPTHU27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=H8C2sdUv2dsUC9oH2
> yzDssdTbCEBF5mbQZbZ871laGw%3D%0A&s=040078780771088975f2abe3668
> 5b182ca626557ed2cd1c7241c78b9f417d325
>
> For a more practical set of examples about the differences in using
> REPEATABLE READ and SERIALIZABLE transaction isolation levels in
> PostgreSQL, see:
>
> https://urldefense.proofpoint.com/v1/url?u=http://wiki.postgresql.org/wiki
> /SSI&k=oIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0A&r=b9TKmA0CPjroD2HLP
> THU27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=H8C2sdUv2dsUC9oH2yzDssdTb
> CEBF5mbQZbZ871laGw%3D%0A&s=3c2629d0256b802ed7b701be6bff7443480
> 5f94beb1c400c772ace91c7204bc5
>
> If you are just interested in reducing the number of serialization failures, see
> the suggestions near the end of this section of the
> documentation:
>
> https://urldefense.proofpoint.com/v1/url?u=http://www.postgresql.org/do
> cs/9.2/interactive/transaction-iso.html%23XACT-
> SERIALIZABLE&k=oIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0A&r=b9TKmA0CP
> jroD2HLPTHU27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=H8C2sdUv2dsUC9oH2
> yzDssdTbCEBF5mbQZbZ871laGw%3D%0A&s=ae5349ae3cafcd86c6ba6be9404
> 990ae800d93d6ccfe892402c2d8d463bd8574
>
> Any of these items (or perhaps a combination of them) may ameliorate the
> problem.  Note that I have seen reports of cases where
> max_pred_locks_per_transaction needed to be set to 20x the default to
> reduce serialization failures to an acceptable level.
> The default is intentionally set very low because so many people do not use
> this isolation level, and this setting reserves shared memory for purposes of
> tracking serializable transactions; the space is wasted for those who don't
> choose to use them.
>
> There is still a lot of work possible to reduce the rate of false positives, which
> has largely gone undone so far due to a general lack of problem reports from
> people which could not be solved through tuning.  If you have such a case, it
> would be interesting to have all relevant details, so that we can target which
> of the many enhancements are relevant to your case.
>
> --
> Kevin Grittner
> EDB:
> https://urldefense.proofpoint.com/v1/url?u=http://www.enterprisedb.com
> /&k=oIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0A&r=b9TKmA0CPjroD2HLPTH
> U27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=H8C2sdUv2dsUC9oH2yzDssdTbCE
> BF5mbQZbZ871laGw%3D%0A&s=ca419a3d34bca730a6a153fe027150e4975396
> 4be76b78a2b81dc84378d091e6
> The Enterprise PostgreSQL Company