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: Thu, 24 Jul 2014 13:01:15 +0800
Message-ID: <53D0931B.7060604@2ndquadrant.com> (raw)
In-Reply-To: <ded604822b28427a80b0751a973e4258@EX13-MBX-013.vmware.com>
References: <ded604822b28427a80b0751a973e4258@EX13-MBX-013.vmware.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 07/24/2014 09:18 AM, Reza Taheri wrote:
> Does PGSQL raise locks to page level when we run with
> SQL_TXN_SERIALIZABLE? Are there any knobs I can play with to alleviate
> this? FWIW, the same transactions on MS SQL Server see almost no conflicts.
>
Also, in the documentation
(http://www.postgresql.org/docs/current/static/transaction-iso.html):
> When the system is forced to combine multiple page-level predicate locks into a single relation-level predicate lock because the predicate lock table is short of memory, an increase in the rate of serialization failures may occur. You can avoid this by increasing max_pred_locks_per_transaction.
... so I suggest experimenting with higher
max_pred_locks_per_transaction values.
http://www.postgresql.org/docs/9.1/static/runtime-config-locks.html#GUC-MAX-PRED-LOCKS-PER-TRANSACTI...
... though that should only really affect object level locks (tables,
etc) according to the docs. I'd need to dig further to determine how to
reduce or eliminate lock combining of row-level to page-level and
page-level to object-level locks.
--
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: <53D0931B.7060604@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