Reza Taheri <rtah...@vmware.com> wrote: > 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
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: http://www.postgresql.org/support/versioning/ > 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. As already pointed out by Craig, statements don't have serialization failures; transactions do. In some cases a transaction may become "doomed to fail" 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. If you want to understand the theory of how SERIALIZABLE transactions are implemented in PostgreSQL, these links may help: http://vldb.org/pvldb/vol5/p1850_danrkports_vldb2012.pdf http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob_plain;f=src/backend/storage/lmgr/README-SSI;hb=master http://wiki.postgresql.org/wiki/Serializable For a more practical set of examples about the differences in using REPEATABLE READ and SERIALIZABLE transaction isolation levels in PostgreSQL, see: http://wiki.postgresql.org/wiki/SSI If you are just interested in reducing the number of serialization failures, see the suggestions near the end of this section of the documentation: http://www.postgresql.org/docs/9.2/interactive/transaction-iso.html#XACT-SERIALIZABLE Any of these items (or perhaps a combination of them) may ameliorate the problem. 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 use this isolation level, and this setting reserves shared memory for purposes of tracking serializable transactions; the space is wasted for those who don't choose to use them. There is still a lot of work possible to reduce the rate of false positives, which has largely gone undone so far due to a general lack of problem reports from people which could not be solved through tuning. If you have such a case, it would be interesting to have all relevant details, so that we can target which of the many enhancements are relevant to your case. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance