Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XAB9m-0007bY-37 for pgsql-performance@arkaria.postgresql.org; Thu, 24 Jul 2014 05:01:30 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XAB9l-0003NK-I3 for pgsql-performance@arkaria.postgresql.org; Thu, 24 Jul 2014 05:01:29 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XAB9k-0003NE-G0 for pgsql-performance@postgresql.org; Thu, 24 Jul 2014 05:01:28 +0000 Received: from mout.kundenserver.de ([212.227.17.24]) by makus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XAB9g-0002hJ-Qg for pgsql-performance@postgresql.org; Thu, 24 Jul 2014 05:01:26 +0000 Received: from ayaki.localdomain (58-7-92-198.dyn.iinet.net.au [58.7.92.198]) by mrelayeu.kundenserver.de (node=mreue102) with ESMTP (Nemesis) id 0LfRYv-1Whk9u0bCz-00p1y4; Thu, 24 Jul 2014 07:01:20 +0200 Message-ID: <53D0931B.7060604@2ndquadrant.com> Date: Thu, 24 Jul 2014 13:01:15 +0800 From: Craig Ringer Organization: 2nd Quadrant User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:24.0) Gecko/20100101 Thunderbird/24.4.0 MIME-Version: 1.0 To: Reza Taheri , "pgsql-performance@postgresql.org" Subject: Re: High rate of transaction failure with the Serializable Isolation Level References: In-Reply-To: X-Enigmail-Version: 1.6 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Provags-ID: V02:K0:Cjcww0OCSbsO/ekYlWVqA5FUZSfQXKg/eiiSe755BU0 t+LynOc7drSg8gPxhLSUP05KAUDJc/4ROO8+/iJC/S2pMitBu0 y7MH7F2XOFM2XS+zBewSmt/JAS4F8uaUXDDbGmApMA0XFd9Z+b XjGWEiOl0rQLgWxjh1gRQWrKpBeKzlMlO9AUta0ObO5SjhcE0i 4EUbyo4717JWtr+bLgcg0vP49ilfprrUScOrGUM9edPpwnPpiS k7A+QLp2aecd0snENfLFu9k4NoXNHeOMsTZ0hVytDX3NMSwChW wL2r5Ipo6lydENLTxQMRED09UrAodwdJiV+Jsi12S9DYRdY8jL BLOXbrpETk/oCKRpKRVgB62N11EPJMwW3qZXPgbMh X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org 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