Re: High rate of transaction failure with the Serializable Isolation Level

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-TRANSACTION

... 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