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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
