postgresql : could not serialize access due to read/write dependencies among transactions

On 1/17/2017 8:54 PM, Neslisah Demirci wrote:

could not serialize access due to read/write dependencies among
transactions

only way to answer this would be to know what the various concurrent
transactions are doing.

A message app like whatsapp i decided to use isolation level
serializable if i use other transaction levels many conversations
started with same number (think about whatsapp web sidebar multipling
with same number every message).

again, we'd need to know what your transactions are doing. what you're
describing sounds inherently incorrect, like you're doing too much in
the application and not enough in the database

I also add an index to my query and my query's execution plan don't
use seq scan .

that has no bearing on the above problems.

--
john r pierce, recycling bits in santa cruz

________________________________
Gönderen: Neslisah Demirci
Gönderildi: 18 Ocak 2017 Çarşamba 08:36
Kime: John R Pierce
Konu: Ynt: [GENERAL] postgresql : could not serialize access due to read/write dependencies among transactions

We use ORM sequelize.js . My transactions are here ;

First I started conversation between two person ;

sql: 'INSERT INTO "XXXXX" ("id","customer","createdAt","updatedAt","CompanyId") VALUES (DEFAULT,\'905322653555\',\'2017-01-17 19:13:37.751 +00:00\',\'2017-01-17 19:13:37.751 +00:00\',\'1\') RETURNING *;' },

name: 'error', length: 274, severity: 'ERROR', code: '40001', detail: 'Reason code: Canceled on identification as a pivot, during write.', hint: 'The transaction might succeed if retried.', position: undefined, internalPosition: undefined, internalQuery: undefined, where: undefined, schema: undefined, table: undefined, column: undefined, dataType: undefined, constraint: undefined, file: 'predicate.c', line: '4605', routine: 'OnConflict_CheckForSerializationFailure',

Secondly ; i inserted messages as below;

Begin;

'INSERT INTO "YYYYY" ("id","body","from","to","state","data","xId","createdAt","updatedAt","CompanyId","MessageTypeId","ConvId") VALUES (DEFAULT,\'messagetext\',\'905309788255\',\'905309788200\',\'customer\',NULL,\'463DA712296218E0D4\',\'2017-01-17 06:43:19.228 +00:00\',\'2017-01-17 06:43:19.228 +00:00\',\'1\',1,23286) RETURNING *;'

commit;

original: { error: could not serialize access due to read/write dependencies among transactions at Connection.parseE (/app/node_modules/pg/lib/connection.js:554:11) at Connection.parseMessage (/app/node_modules/pg/lib/connection.js:381:17) at Socket.<anonymous> (/app/

Best regards ,

Neslişah Demirci |Database Administrator

Ayazağa cad. No:4 Uniq İstanbul Plaza
B2 /Kat:3 34396 Ayazağa-SARIYER-İstanbul
T. (+90) 212 453 16 00 - 5516
F. (+90) 212 453 16 16
www.markafoni.com<http://www.markafoni.com/&gt;
www.facebook.com/markafoni<http://www.facebook.com/markafoni&gt;
blog.markafoni.com

________________________________
Gönderen: John R Pierce <pierce@hogranch.com> adına pgsql-general-owner@postgresql.org <pgsql-general-owner@postgresql.org>
Gönderildi: 18 Ocak 2017 Çarşamba 08:10
Kime: pgsql-general@postgresql.org
Konu: Re: [GENERAL] postgresql : could not serialize access due to read/write dependencies among transactions

On 1/17/2017 8:54 PM, Neslisah Demirci wrote:

could not serialize access due to read/write dependencies among transactions

only way to answer this would be to know what the various concurrent transactions are doing.

A message app like whatsapp i decided to use isolation level serializable if i use other transaction levels many conversations started with same number (think about whatsapp web sidebar multipling with same number every message).

again, we'd need to know what your transactions are doing. what you're describing sounds inherently incorrect, like you're doing too much in the application and not enough in the database

I also add an index to my query and my query's execution plan don't use seq scan .

that has no bearing on the above problems.

--
john r pierce, recycling bits in santa cruz

On 1/17/2017 9:41 PM, Neslisah Demirci wrote:

First I started conversation between two person ;

sql: 'INSERT INTO "XXXXX"
("id","customer","createdAt","updatedAt","CompanyId") VALUES
(DEFAULT,\'905322653555\',\'2017-01-17 19:13:37.751
+00:00\',\'2017-01-17 19:13:37.751 +00:00\',\'1\') RETURNING *;' },

*name: 'error', length: 274, severity: 'ERROR', code: '40001', detail:
'Reason code: Canceled on identification as a pivot, during write.',
hint: 'The transaction might succeed if retried.', position:
undefined, internalPosition: undefined, internalQuery: undefined,
where: undefined, schema: undefined, table: undefined, column:
undefined, dataType: undefined, constraint: undefined, file:
'predicate.c', line: '4605', routine:
'OnConflict_CheckForSerializationFailure',*

that reason code doesn't sound like anything PostgreSQL generates.
SQLSTATE 40001 is "serialization_failure", but all that stuff about
'identification as a pivot' ? that must be your ORM. Many ORM's
are very broken if you try and do anything outside the ORM designer's
way of thinking.

was this insert done inside a transaction? was it the first thing done
in this transaction? when you got the error, did you rollback the
transaction and retry? once you've gotten an error in a transaction,
no further queries can be done until you rollback the transaction and
start a new one.

--
john r pierce, recycling bits in santa cruz

## John R Pierce (pierce@hogranch.com):

that reason code doesn't sound like anything PostgreSQL generates.
SQLSTATE 40001 is "serialization_failure", but all that stuff about
'identification as a pivot' ? that must be your ORM.

That's PostgreSQL: https://wiki.postgresql.org/wiki/SSI

Regards,
Christoph

--
Spare Space.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Neslisah Demirci wrote:

First I started conversation between two person ;

sql: 'INSERT INTO "XXXXX" ("id","customer","createdAt","updatedAt","CompanyId") VALUES (DEFAULT,\'905322653555\',\'2017-01-17 19:13:37.751 +00:00\',\'2017-01-17 19:13:37.751 +00:00\',\'1\') RETURNING *;' },

name: 'error', length: 274, severity: 'ERROR', code: '40001', detail: 'Reason code: Canceled on identification as a pivot, during write.', hint: 'The transaction might succeed if retried.', position: undefined, internalPosition: undefined, internalQuery: undefined, where: undefined, schema: undefined, table: undefined, column: undefined, dataType: undefined, constraint: undefined, file: 'predicate.c', line: '4605', routine: 'OnConflict_CheckForSerializationFailure',

Secondly ; i inserted messages as below;

Begin;

'INSERT INTO "YYYYY" ("id","body","from","to","state","data","xId","createdAt","updatedAt","CompanyId","MessageTypeId","ConvId") VALUES (DEFAULT,\'messagetext\',\'905309788255\',\'905309788200\',\'customer\',NULL,\'463DA712296218E0D4\',\'2017-01-17 06:43:19.228 +00:00\',\'2017-01-17 06:43:19.228 +00:00\',\'1\',1,23286) RETURNING *;'

commit;

Are there triggers or foreign keys in these tables?

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Jan 17, 2017 at 10:54 PM, Neslisah Demirci
<neslisah.demirci@markafoni.com> wrote:

could not serialize access due to read/write dependencies among
transactions

I also add an index to my query and my query's execution plan
don't use seq scan .

These two issues are likely to be somewhat related -- if a
sequential scan is used, then any write to that table by another
connection causes a read-write dependency (a/k/a rw-conflict),
which can eventually contribute to a serialization failure. If you
can cause narrower access through indexes, you may see a
significant drop in the frequency of these serialization failures.
You might want to post the query and its execution plan with all
the information suggested here (exact pg version, configuration
information, machine descriptions, etc.):

https://wiki.postgresql.org/wiki/SlowQueryQuestions

On the other hand, if you are going to use serializable
transactions (or even repeatable read transactions) you should
probably be using some framework that can retry the transaction
from the start on a serialization failure.

You might be interested in this set of examples of how serializable
transactions differ from repeatable read:

https://wiki.postgresql.org/wiki/SSI

And of course, if you haven't already read the fine manual on the
topic:

https://www.postgresql.org/docs/current/static/mvcc.html

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Am 01/18/2017 um 05:54 AM schrieb Neslisah Demirci:

Hi ,

I have problem about this issue ;

could not serialize access due to read/write dependencies among transactions

That's something you have to expect when using SERIALIZABLE isolation
level. Maybe re-read
https://www.postgresql.org/docs/current/static/transaction-iso.html#XACT-SERIALIZABLE

A message app like whatsapp i decided to use isolation level
serializable if i use other transaction levels many conversations
started with same number (think about whatsapp web sidebar multipling
with same number every message).

Are you not using a sequence for generating these IDs? As those are
counting independent of transaction visibility...
What you say sounds more like "SELECT max(conversation_id) + 1 AS
new_conversation_id FROM ..."

I also add an index to my query and my query's execution plan don't use
seq scan .

How can i solve this issue ? Can i solve this on db orr on app ?

a) switch to a sequence for generating these IDs (you can go back to a
lower isolation level then). You may get holes in the IDs then (on
rolled back transactions), but I don't see how that would not be
acceptable for conversation IDs
b) deal with it in the app (probably not what you want, think roud-trip
time)

Neslişah Demirci | Veritabanı Yöneticisi

Ayazağa cad. No:4 Uniq İstanbul Plaza
B2 /Kat:3 34396 Ayazağa-SARIYER-İstanbul
T. (+90) 212 453 16 00 – 5516
F. (+90) 212 453 16 16
www.markafoni.com <http://www.markafoni.com/&gt;
www.facebook.com/markafoni <http://www.facebook.com/markafoni&gt;
blog.markafoni.com

Regards,
--
Gunnar "Nick" Bluth
DBA ELSTER

Tel: +49 911/991-4665
Mobil: +49 172/8853339