Обсуждение: Ynt:[GENERAL] postgresql : could not serialize access due toread/write dependencies among transactions

Поиск
Список
Период
Сортировка

Ynt:[GENERAL] postgresql : could not serialize access due toread/write dependencies among transactions

От
Neslisah Demirci
Дата:

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
www.facebook.com/markafoni
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.

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

Re: Ynt:[GENERAL] postgresql : could not serialize access due toread/write dependencies among transactions

От
Christoph Moench-Tegeder
Дата:
## 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.


Neslisah Demirci wrote:

> First I started conversation between two person ;
>
>
> sql: 'INSERT INTO "XXXXX" ("id","customer","createdAt","updatedAt","CompanyId") VALUES
(DEFAULT,\'905322653555\',\'2017-01-1719: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