Hello PGSQL performance community,
[By way of introduction, we are a TPC subcommittee that is developing a
benchmark with cloud-like characteristics for virtualized databases. The
end-to-end benchmarking kit will be publicly available, and will run on PGSQL]
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, ODBC 2.2.14 (as well as 2.3.3pre,
which didn't help), RHEL 6.4, and a 6-way VM with 96GB of memory on a 4-socket
Westmere server.
With our 9 transactions running with a mix of SQL_TXN_READ_COMMITTED and
SQL_TXN_REPEATABLE_READ, we get less than 1% deadlocks, all of which occur
because each row in one table, BROKER, may be read or written by multiple
transactions at the same time. So, there are legitimate conflicts, which we
deal with using an exponential backoff algorithm that sleeps for
10ms/30ms/90ms/etc.
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. About 17K of failures are from the
statement below:
2014-07-23 11:27:15 PDT 26085 ERROR: could not serialize access due to
read/write dependencies among transactions
2014-07-23 11:27:15 PDT 26085 DETAIL: Reason code: Canceled on identification
as a pivot, during write.
2014-07-23 11:27:15 PDT 26085 HINT: The transaction might succeed if retried.
2014-07-23 11:27:15 PDT 26085 CONTEXT: SQL statement "update TRADE
set T_COMM = comm_amount,
T_DTS = trade_dts,
T_ST_ID = st_completed_id,
T_TRADE_PRICE = trade_price
where T_ID = trade_id"
PL/pgSQL function traderesultframe5(ident_t,value_t,character,timestamp
without time zone,trade_t,s_price_t) line 15 at SQL statement
This doesn't make sense since at any given time, only one transaction might
possibly be accessing the row that is being updated. There should be no
conflicts if we have row-level locking/isolation
The second most common conflict happens 7.6K times in the statement below:
2014-07-23 11:27:23 PDT 26039 ERROR: could not serialize access due to
read/write dependencies among transactions
2014-07-23 11:27:23 PDT 26039 DETAIL: Reason code: Canceled on identification
as a pivot, during conflict in checking.
2014-07-23 11:27:23 PDT 26039 HINT: The transaction might succeed if retried.
2014-07-23 11:27:23 PDT 26039 CONTEXT: SQL statement "insert
into SETTLEMENT ( SE_T_ID,
SE_CASH_TYPE,
SE_CASH_DUE_DATE,
SE_AMT)
values ( trade_id,
cash_type,
due_date,
se_amount
)"
PL/pgSQL function traderesultframe6(ident_t,timestamp without time
zone,character varying,value_t,timestamp without time
zone,trade_t,smallint,s_qty_t,character) line 23 at SQL statement
I don't understand why an insert would hit a serialization conflict
We also have 4.5K conflicts when we try to commit:
2014-07-23 11:27:23 PDT 26037 ERROR: could not serialize access due to
read/write dependencies among transactions
2014-07-23 11:27:23 PDT 26037 DETAIL: Reason code: Canceled on identification
as a pivot, during commit attempt.
2014-07-23 11:27:23 PDT 26037 HINT: The transaction might succeed if retried.
2014-07-23 11:27:23 PDT 26037 STATEMENT: COMMIT
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.
Thanks,
Reza
