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

Hi Craig,
> It's not just that statement that is relevant.
> Is that statement run standalone, or as part of a larger transaction?

Yes, the "size" of the transaction seems to matter here. It is a complex transaction (attached). Each "frame" is one
storedprocedure, and the 6 frames are called one after the other with no pause. After frame6 returns, we call
SQLTransact(...,...,  SQL_COMMIT). Below is the failure rate of the various frames: 

    112 tid 18883: SQL Failed: DoTradeResultFrame3
    102 tid 18883: SQL Failed: DoTradeResultFrame4
  18188 tid 18883: SQL Failed: DoTradeResultFrame5
   8566 tid 18883: SQL Failed: DoTradeResultFrame6
   4492 tid 18883: ERROR: TradeResultDB: commit failed

So, no failures in frames 1 and 2, and then the failure rate grows as we approach the end of the transaction.

> If the INSERTing transaction previously queried for a key that was created by a concurrent transaction this can occur
asthere is no serialization 
> execution order of the transactions that could produce the same result.

As far as the inserts, your point is well-taken. But in this case, I have eliminated the transactions that query or
otherwisemanipulate the SETTELEMENT table. The only access to it is the single insert in this transaction 

> A lower FILLFACTOR can spread data out at the cost of wasted space.

Interesting idea!  Let me look into this. Even if this is not practical (our tables are 10s and 100s of GBs), if I can
forcea single row per page and the problem goes away, then we learn something 

> PostgreSQL's isolationtester tool, in src/test/isolation, can be handy for automating this kind of conflict,
> and provides some useful examples of cases that are detected.

Didn't know about this tool. Let me look into it!

Thanks again for the reply,
Reza

> -----Original Message-----
> From: Craig Ringer [mailto:craig@2ndquadrant.com]
> Sent: Wednesday, July 23, 2014 9:58 PM
> To: Reza Taheri; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] High rate of transaction failure with the Serializable
> Isolation Level
>
> On 07/24/2014 09:18 AM, Reza Taheri wrote:
> > 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:
>
> It's not just that statement that is relevant.
>
> At SERIALIZABLE isolation the entire transaction's actions must be
> considered, as must the conflicting transaction.
>
> > 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.
>
> Is that statement run standalone, or as part of a larger transaction?
>
> > The second most common conflict happens 7.6K times in the statement
> below:
> ...
> > I don't understand why an insert would hit a serialization conflict
>
> If the INSERTing transaction previously queried for a key that was created by
> a concurrent transaction this can occur as there is no serialization execution
> order of the transactions that could produce the same result.
>
> This doesn't produce exactly the same error, but demonstrates one such
> case:
>
>
> regress=> CREATE TABLE demo (id integer primary key, value integer);
> CREATE TABLE regress=> INSERT INTO demo(id, value) VALUES (1, 42);
> INSERT 0 1
>
> then
>
> regress=> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN
> regress=> SELECT id FROM demo WHERE id = 2;  id
> ----
> (0 rows)
>
>
> session1=> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN
> session2=> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN
>
> session1=> SELECT id FROM demo WHERE id = 2;  id
> ----
> (0 rows)
>
> session2=> SELECT id FROM demo WHERE id = 3;  id
> ----
> (0 rows)
>
>
> session1=> INSERT INTO demo VALUES (3, 43); INSERT 0 1
>
> session2=> INSERT INTO demo VALUES (2, 43); INSERT 0 1
>
> session2=> COMMIT;
> COMMIT
>
> session1=> COMMIT;
> ERROR:  could not serialize access due to read/write dependencies among
> transactions
> DETAIL:  Reason code: Canceled on identification as a pivot, during commit
> attempt.
> HINT:  The transaction might succeed if retried.
>
> > Does PGSQL raise locks to page level when we run with
> > SQL_TXN_SERIALIZABLE?
>
> From the documentation
> (https://urldefense.proofpoint.com/v1/url?u=http://www.postgresql.org/d
> ocs/current/static/transaction-
> iso.html%29:&k=oIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0A&r=b9TKmA0CPj
> roD2HLPTHU27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=TLPOH83mhBZDaYDaC
> sh%2F8g2qVmFXtdg7HcUqXymxn40%3D%0A&s=32832df25ebb8166a18523bd
> 9d6ec00f5ad545ea3bc1f8e95808ba65b4766130
>
> > Predicate locks in PostgreSQL, like in most other database systems, are
> based on data actually accessed by a transaction. These will show up in the
> pg_locks system view with a mode of SIReadLock. The particular locks
> acquired during execution of a query will depend on the plan used by the
> query, and multiple finer-grained locks (e.g., tuple locks) may be combined
> into fewer coarser-grained locks (e.g., page locks) during the course of the
> transaction to prevent exhaustion of the memory used to track the locks.
>
> ... so yes, it may raise locks to page level. That doesn't mean that's
> necessarily what's happening here.
>
> > Are there any knobs I can play with to alleviate this?
>
> A lower FILLFACTOR can spread data out at the cost of wasted space.
>
> > FWIW, the same transactions on MS SQL Server see almost no conflicts.
>
> Many DBMSs don't detect all serialization anomalies. PostgreSQL doesn't
> detect all possible anomalies but it detects many that other systems may not.
>
> To see what's going on and why MS SQL Server (version?) doesn't complain,
> it'd be best to boil each case down to a minimal reproducible test case that
> can be analyzed in isolation.
>
> PostgreSQL's isolationtester tool, in src/test/isolation, can be handy for
> automating this kind of conflict, and provides some useful examples of cases
> that are detected.
>
> --
>  Craig Ringer
> https://urldefense.proofpoint.com/v1/url?u=http://www.2ndquadrant.com
> /&k=oIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0A&r=b9TKmA0CPjroD2HLPTH
> U27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=TLPOH83mhBZDaYDaCsh%2F8g2q
> VmFXtdg7HcUqXymxn40%3D%0A&s=3b1587fc43a994ddcf59e658e2521e9a9c
> 847393fb4ab8dc48df009b547cca55
>  PostgreSQL Development, 24x7 Support, Training & Services