Thread: Serialization errors in Postgres 9.4.0

<div class="WordSection1"><p class="MsoNormal">Some time ago we upgraded from v. 8.4.4 to 9.4.0 and we are seeing
errorsin some regression tests.<p class="MsoNormal"> <p class="MsoNormal">LOCATION:  exec_simple_query,
postgres.c:887<pclass="MsoNormal">ERROR:  40001: could not serialize access due to read/write dependencies among
transactions<pclass="MsoNormal"> <p class="MsoNormal">Detail can be one of these: <p class="MsoNormal">   DETAIL: 
Reasoncode: Canceled on identification as a pivot, during commit attempt.<p class="MsoNormal">   DETAIL:  Reason code:
Canceledon identification as a pivot, during write.<p class="MsoNormal">   DETAIL:  Reason code: Canceled on
identificationas a pivot, during conflict out checking.<p class="MsoNormal"> <p class="MsoNormal">Location is usually
oneof these:<p class="MsoNormal">   LOCATION:  PreCommit_CheckForSerializationFailure, predicate.c:4654<p
class="MsoNormal">  LOCATION:  OnConflict_CheckForSerializationFailure, predicate.c:4600<p class="MsoNormal">  
LOCATION: CheckForSerializableConflictOut, predicate.c:3888<p class="MsoNormal"> <p class="MsoNormal">It may end up
with<pclass="MsoNormal">   ERROR:  25P02: current transaction is aborted, commands ignored until end of transaction
block<pclass="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal">The database at this point is likely to have a
barrageof read and write ops against few tables.<p class="MsoNormal">I understand that using serializable transaction
weprobably should be ready to retry, <p class="MsoNormal">but it would be helpful to understand why we did not see so
manyerrors in the past.<p class="MsoNormal"> <p class="MsoNormal">  Did something changed from 8.4.4 to 9.4.0?<p
class="MsoNormal"> Maybe Postgres has more aggressive predicate locking mechanism now?<p class="MsoNormal">  Can it be
thatbecause of the small table size it performs sequential scan and locks the entire table?<p class="MsoNormal"> <p
class="MsoNormal">Sorryif any of these questions are plain stupid.<p class="MsoNormal">Thanks,<p
class="MsoNormal">Michael.<pclass="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal"> </div><p style="FONT-SIZE:
9pt;MARGIN: 0px 0px 0px 35.4pt; COLOR: #9d9d9d; FONT-STYLE: italic; FONT-FAMILY: Arial, Helvetica, sans-serif"> This
emailand any attachments are intended solely for the use of the individual or entity to whom it is addressed and may be
confidentialand/or privileged.<p style="FONT-SIZE: 9pt; MARGIN: 0px 0px 0px 35.4pt; COLOR: #9d9d9d; FONT-STYLE: italic;
FONT-FAMILY:Arial, Helvetica, sans-serif"> If you are not one of the named recipients or have received this email in
error,<p style="FONT-SIZE: 9pt; MARGIN: 0px 0px 0px 35.4pt; COLOR: #9d9d9d; FONT-STYLE: italic; FONT-FAMILY: Arial,
Helvetica,sans-serif"> (i) you should not read, disclose, or copy it,<p style="FONT-SIZE: 9pt; MARGIN: 0px 0px 0px
35.4pt;COLOR: #9d9d9d; FONT-STYLE: italic; FONT-FAMILY: Arial, Helvetica, sans-serif"> (ii) please notify sender of
yourreceipt by reply email and delete this email and all attachments,<p style="FONT-SIZE: 9pt; MARGIN: 0px 0px 0px
35.4pt;COLOR: #9d9d9d; FONT-STYLE: italic; FONT-FAMILY: Arial, Helvetica, sans-serif"> (iii) Dassault Systemes does not
acceptor assume any liability or responsibility for any use of or reliance on this email.<p style="FONT-STYLE: italic;
MARGIN:0px 0px 0px 35.4pt; FONT-FAMILY: Arial, Helvetica, sans-serif; COLOR: #9d9d9d; FONT-SIZE: 9pt"><p
style="FONT-SIZE:9pt; MARGIN: 0px 0px 0px 35.4pt; COLOR: #9d9d9d; FONT-STYLE: italic; FONT-FAMILY: Arial, Helvetica,
sans-serif">For other languages, go to http://www.3ds.com/terms/email-disclaimer  

On Thu, Jun 25, 2015 at 04:02:33PM +0000, BRUSSER Michael wrote:
> Some time ago we upgraded from v. 8.4.4 to 9.4.0 and we are seeing errors in some regression tests.
> 
> LOCATION:  exec_simple_query, postgres.c:887
> ERROR:  40001: could not serialize access due to read/write dependencies among transactions

Would you be so kind as to send along a way to reproduce the problem
you are seeing, ideally a minimal one?

> The database at this point is likely to have a barrage of read and write ops against few tables.
> I understand that using serializable transaction we probably should be ready to retry,

Yes

> but it would be helpful to understand why we did not see so many errors in the past.

Changing the transaction isolation level, which was mislabeled
"serializable" in 8.4 but was actually snapshot isolation, can produce
differences.

https://en.wikipedia.org/wiki/Snapshot_isolation

>   Did something changed from 8.4.4 to 9.4.0?

Yes.  9.1 introduced SSI, which added serialization checks to the
snapshot isolation that existed before.

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

>   Maybe Postgres has more aggressive predicate locking mechanism now?
>   Can it be that because of the small table size it performs sequential scan and locks the entire table?
> 
> Sorry if any of these questions are plain stupid.

Thanks for asking.  These questions are quite reasonable.

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

BRUSSER Michael <Michael.BRUSSER@3ds.com> writes:
> Some time ago we upgraded from v. 8.4.4 to 9.4.0 and we are seeing errors in some regression tests.
> LOCATION:  exec_simple_query, postgres.c:887
> ERROR:  40001: could not serialize access due to read/write dependencies among transactions

9.1 substantially tightened Postgres' ideas of what "serializable" means.
If you want to go back to the behavior you had in 8.4, use REPEATABLE READ
isolation level instead of SERIALIZABLE.
        regards, tom lane