Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XA7gE-0004KQ-Pq for pgsql-performance@arkaria.postgresql.org; Thu, 24 Jul 2014 01:18:47 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XA7gD-00053C-CV for pgsql-performance@arkaria.postgresql.org; Thu, 24 Jul 2014 01:18:45 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XA7gC-000534-CS for pgsql-performance@postgresql.org; Thu, 24 Jul 2014 01:18:44 +0000 Received: from smtp-outbound-1.vmware.com ([208.91.2.12]) by magus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1XA7g3-0004vI-RE for pgsql-performance@postgresql.org; Thu, 24 Jul 2014 01:18:41 +0000 Received: from sc9-mailhost3.vmware.com (sc9-mailhost3.vmware.com [10.113.161.73]) by smtp-outbound-1.vmware.com (Postfix) with ESMTP id 7AF73283CE for ; Wed, 23 Jul 2014 18:18:30 -0700 (PDT) Received: from EX13-CAS-004.vmware.com (EX13-CAS-004.vmware.com [10.113.191.54]) by sc9-mailhost3.vmware.com (Postfix) with ESMTP id 7640B41395 for ; Wed, 23 Jul 2014 18:18:30 -0700 (PDT) Received: from EX13-MBX-017.vmware.com (10.113.191.37) by EX13-MBX-005.vmware.com (10.113.191.25) with Microsoft SMTP Server (TLS) id 15.0.775.38; Wed, 23 Jul 2014 18:18:24 -0700 Received: from EX13-MBX-013.vmware.com (10.113.191.33) by EX13-MBX-017.vmware.com (10.113.191.37) with Microsoft SMTP Server (TLS) id 15.0.775.38; Wed, 23 Jul 2014 18:18:24 -0700 Received: from EX13-MBX-013.vmware.com ([fe80::b82b:d0bb:9cd7:392e]) by EX13-MBX-013.vmware.com ([fe80::b82b:d0bb:9cd7:392e%15]) with mapi id 15.00.0775.031; Wed, 23 Jul 2014 18:18:06 -0700 From: Reza Taheri To: "pgsql-performance@postgresql.org" Subject: High rate of transaction failure with the Serializable Isolation Level Thread-Topic: High rate of transaction failure with the Serializable Isolation Level Thread-Index: Ac+m3REUfhN8/sxlQTylzpfYlwqkMQ== Date: Thu, 24 Jul 2014 01:18:05 +0000 Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.113.160.246] Content-Type: multipart/alternative; boundary="_000_ded604822b28427a80b0751a973e4258EX13MBX013vmwarecom_" MIME-Version: 1.0 X-Pg-Spam-Score: -6.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 --_000_ded604822b28427a80b0751a973e4258EX13MBX013vmwarecom_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hello PGSQL performance community, [By way of introduction, we are a TPC subcommittee that is developing a ben= chmark with cloud-like characteristics for virtualized databases. The end-t= o-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 w= ith a constant workload, a TPC-E workload if you will, to focus on this thi= s problem. We are running with PGSQL 9.2.4, ODBC 2.2.14 (as well as 2.3.3pr= e, 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 SQ= L_TXN_REPEATABLE_READ, we get less than 1% deadlocks, all of which occur be= cause each row in one table, BROKER, may be read or written by multiple tra= nsactions at the same time. So, there are legitimate conflicts, which we de= al with using an exponential backoff algorithm that sleeps for 10ms/30ms/90= ms/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 tot= al failure count (due to many transactions failing more than once) is 31,38= 8. What is unusual is that the majority of the failures occur in a statement t= hat 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 rea= d/write dependencies among transactions 2014-07-23 11:27:15 PDT 26085 DETAIL: Reason code: Canceled on identificat= ion as a pivot, during write. 2014-07-23 11:27:15 PDT 26085 HINT: The transaction might succeed if retri= ed. 2014-07-23 11:27:15 PDT 26085 CONTEXT: SQL statement "update TRADE set T_COMM =3D comm_amount, T_DTS =3D trade_dts, T_ST_ID =3D st_completed_id, T_TRADE_PRICE =3D trade_price where T_ID =3D trade_id" PL/pgSQL function traderesultframe5(ident_t,value_t,character,times= tamp 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 co= nflicts 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 rea= d/write dependencies among transactions 2014-07-23 11:27:23 PDT 26039 DETAIL: Reason code: Canceled on identificat= ion as a pivot, during conflict in checking. 2014-07-23 11:27:23 PDT 26039 HINT: The transaction might succeed if retri= ed. 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 rea= d/write dependencies among transactions 2014-07-23 11:27:23 PDT 26037 DETAIL: Reason code: Canceled on identificat= ion as a pivot, during commit attempt. 2014-07-23 11:27:23 PDT 26037 HINT: The transaction might succeed if retri= ed. 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 tra= nsactions on MS SQL Server see almost no conflicts. Thanks, Reza --_000_ded604822b28427a80b0751a973e4258EX13MBX013vmwarecom_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hello PGSQL performance community,

[By way of introduction, we are a TPC subcommittee t= hat is developing a benchmark with cloud-like characteristics for virtualiz= ed databases. The end-to-end benchmarking kit will be publicly available, a= nd 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 a= s 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_TX= N_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 usin= g an exponential backoff algorithm that sleeps for 10ms/30ms/90ms/etc.=

 

When we raise the Trade-Result transaction to SQL_TX= N_SERIALIZABLE, we face a storm of conflicts. Out of 37,342 Trade-Result tr= ansactions, 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 1= 7K of failures are from the statement below:

2014-07-23 11:27:15 PDT 26085 ER= ROR:  could not serialize access due to read/write dependencies among = transactions

2014-07-23 11:27:15 PDT 26085 DE= TAIL:  Reason code: Canceled on identification as a pivot, during writ= e.

2014-07-23 11:27:15 PDT 26085 HI= NT:  The transaction might succeed if retried.

2014-07-23 11:27:15 PDT 26085 CO= NTEXT:  SQL statement "update   TRADE=

     &n= bsp;          set  &= nbsp;  T_COMM =3D comm_amount,

     &n= bsp;            = ;      T_DTS =3D trade_dts,

     &n= bsp;            = ;      T_ST_ID =3D st_completed_id,

     &n= bsp;            = ;      T_TRADE_PRICE =3D trade_price

     &n= bsp;          where  = ; T_ID =3D trade_id"

     &n= bsp;  PL/pgSQL function traderesultframe5(ident_t,value_t,character,ti= mestamp without time zone,trade_t,s_price_t) line 15 at SQL statement<= /o:p>

 

This doesn’t make sense since at any given tim= e, only one transaction might possibly be accessing the row that is being u= pdated. 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:<= o:p>

2014-07-23 11:27:23 PDT 26039 ER= ROR:  could not serialize access due to read/write dependencies among = transactions

2014-07-23 11:27:23 PDT 26039 DE= TAIL:  Reason code: Canceled on identification as a pivot, during conf= lict in checking.

2014-07-23 11:27:23 PDT 26039 HI= NT:  The transaction might succeed if retried.

2014-07-23 11:27:23 PDT 26039 CO= NTEXT:  SQL statement "insert

     &n= bsp;          into  =   SETTLEMENT (    SE_T_ID,

     &n= bsp;            = ;            &n= bsp;         SE_CASH_TYPE,

     &n= bsp;            = ;            &n= bsp;         SE_CASH_DUE_DATE,=

     &n= bsp;            = ;            &n= bsp;         SE_AMT)

     &n= bsp;          values ( &n= bsp;      trade_id,

     &n= bsp;            = ;            &n= bsp; cash_type,

     &n= bsp;            = ;            &n= bsp; due_date,

     &n= bsp;            = ;            &n= bsp; se_amount

     &n= bsp;            = ;      )"

     &n= bsp;  PL/pgSQL function traderesultframe6(ident_t,timestamp without ti= me zone,character varying,value_t,timestamp without time zone,trade_t,small= int,s_qty_t,character) line 23 at SQL statement

 

I don’t understand why an insert would hit a s= erialization conflict

 

We also have 4.5K conflicts when we try to commit:

2014-07-23 11:27:23 PDT 26037 ER= ROR:  could not serialize access due to read/write dependencies among = transactions

2014-07-23 11:27:23 PDT 26037 DE= TAIL:  Reason code: Canceled on identification as a pivot, during comm= it attempt.

2014-07-23 11:27:23 PDT 26037 HI= NT:  The transaction might succeed if retried.

2014-07-23 11:27:23 PDT 26037 ST= ATEMENT:  COMMIT

 

 

Does PGSQL raise locks to page level when we run wit= h SQL_TXN_SERIALIZABLE? Are there any knobs I can play with to alleviate th= is?  FWIW, the same transactions on MS SQL Server see almost no confli= cts.

 

Thanks,
Reza

--_000_ded604822b28427a80b0751a973e4258EX13MBX013vmwarecom_--