From: Reza Taheri <rtaheri@vmware.com>
To: Kevin Grittner <kgrittn@ymail.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/sxlQTylzpfYlwqkMQApZTyAAALcmCA=
Date: Thu, 24 Jul 2014 19:50:41 +0000
Message-ID: <cada7898099045b7992cf1a3a08f1586@EX13-MBX-013.vmware.com>
References: <ded604822b28427a80b0751a973e4258@EX13-MBX-013.vmware.com>
 <1406210579.67466.YahooMailNeo@web122301.mail.ne1.yahoo.com>
In-Reply-To: <1406210579.67466.YahooMailNeo@web122301.mail.ne1.yahoo.com>
Accept-Language: en-US
Content-Language: en-US
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hi Kevin,
Thanks for the reply

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

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


> Note that I have seen reports of cases where max_pred_locks_per_transacti=
on 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 t=
hem to 6400, and saw no difference

max_locks_per_transaction =3D 6400
max_pred_locks_per_transaction =3D 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 Serializ=
able
> Isolation Level
>=20
> Reza Taheri <rtaheri@vmware.com> wrote:
>=20
> > 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
>=20
> 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:
>=20
> https://urldefense.proofpoint.com/v1/url?u=3Dhttp://www.postgresql.org/su
> pport/versioning/&k=3DoIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0A&r=3Db9TKm
> A0CPjroD2HLPTHU27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=3DH8C2sdUv2dsUC
> 9oH2yzDssdTbCEBF5mbQZbZ871laGw%3D%0A&s=3D6522bd258d0a034429522b
> 61239134b07f1cabc086e8c2cb330aa9c9bc4a337d
>=20
> > 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.
>=20
> As already pointed out by Craig, statements don't have serialization fail=
ures;
> transactions do.=A0 In some cases a transaction may become "doomed to fai=
l"
> 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.
>=20
> If you want to understand the theory of how SERIALIZABLE transactions are
> implemented in PostgreSQL, these links may help:
>=20
> https://urldefense.proofpoint.com/v1/url?u=3Dhttp://vldb.org/pvldb/vol5/p1
> 850_danrkports_vldb2012.pdf&k=3DoIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0
> A&r=3Db9TKmA0CPjroD2HLPTHU27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=3DH8C
> 2sdUv2dsUC9oH2yzDssdTbCEBF5mbQZbZ871laGw%3D%0A&s=3Dd1b8cd62c431
> c267124c21d4e639c98eebb650caaf8fd05ba47aa825a9b54a52
>=20
> https://urldefense.proofpoint.com/v1/url?u=3Dhttp://git.postgresql.org/gi=
twe
> b/?p%3Dpostgresql.git%3Ba%3Dblob_plain%3Bf%3Dsrc/backend/storage/lm
> gr/README-
> SSI%3Bhb%3Dmaster&k=3DoIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0A&r=3Db9T
> KmA0CPjroD2HLPTHU27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=3DH8C2sdUv2d
> sUC9oH2yzDssdTbCEBF5mbQZbZ871laGw%3D%0A&s=3D1f60010253b8012dbe5
> e5a51af48fcb831dae81200708f620438e6afb48c0eef
>=20
> https://urldefense.proofpoint.com/v1/url?u=3Dhttp://wiki.postgresql.org/w=
iki
> /Serializable&k=3DoIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0A&r=3Db9TKmA0CPj
> roD2HLPTHU27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=3DH8C2sdUv2dsUC9oH2
> yzDssdTbCEBF5mbQZbZ871laGw%3D%0A&s=3D040078780771088975f2abe3668
> 5b182ca626557ed2cd1c7241c78b9f417d325
>=20
> For a more practical set of examples about the differences in using
> REPEATABLE READ and SERIALIZABLE transaction isolation levels in
> PostgreSQL, see:
>=20
> https://urldefense.proofpoint.com/v1/url?u=3Dhttp://wiki.postgresql.org/w=
iki
> /SSI&k=3DoIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0A&r=3Db9TKmA0CPjroD2HLP
> THU27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=3DH8C2sdUv2dsUC9oH2yzDssdTb
> CEBF5mbQZbZ871laGw%3D%0A&s=3D3c2629d0256b802ed7b701be6bff7443480
> 5f94beb1c400c772ace91c7204bc5
>=20
> If you are just interested in reducing the number of serialization failur=
es, see
> the suggestions near the end of this section of the
> documentation:
>=20
> https://urldefense.proofpoint.com/v1/url?u=3Dhttp://www.postgresql.org/do
> cs/9.2/interactive/transaction-iso.html%23XACT-
> SERIALIZABLE&k=3DoIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0A&r=3Db9TKmA0CP
> jroD2HLPTHU27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=3DH8C2sdUv2dsUC9oH2
> yzDssdTbCEBF5mbQZbZ871laGw%3D%0A&s=3Dae5349ae3cafcd86c6ba6be9404
> 990ae800d93d6ccfe892402c2d8d463bd8574
>=20
> Any of these items (or perhaps a combination of them) may ameliorate the
> problem.=A0 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 u=
se
> this isolation level, and this setting reserves shared memory for purpose=
s of
> tracking serializable transactions; the space is wasted for those who don=
't
> choose to use them.
>=20
> There is still a lot of work possible to reduce the rate of false positiv=
es, which
> has largely gone undone so far due to a general lack of problem reports f=
rom
> people which could not be solved through tuning.=A0 If you have such a ca=
se, it
> would be interesting to have all relevant details, so that we can target =
which
> of the many enhancements are relevant to your case.
>=20
> --
> Kevin Grittner
> EDB:
> https://urldefense.proofpoint.com/v1/url?u=3Dhttp://www.enterprisedb.com
> /&k=3DoIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0A&r=3Db9TKmA0CPjroD2HLPTH
> U27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=3DH8C2sdUv2dsUC9oH2yzDssdTbCE
> BF5mbQZbZ871laGw%3D%0A&s=3Dca419a3d34bca730a6a153fe027150e4975396
> 4be76b78a2b81dc84378d091e6
> The Enterprise PostgreSQL Company


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