Serialization errors in Postgres 9.4.0
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
Detail can be one of these:
DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
DETAIL: Reason code: Canceled on identification as a pivot, during write.
DETAIL: Reason code: Canceled on identification as a pivot, during conflict out checking.
Location is usually one of these:
LOCATION: PreCommit_CheckForSerializationFailure, predicate.c:4654
LOCATION: OnConflict_CheckForSerializationFailure, predicate.c:4600
LOCATION: CheckForSerializableConflictOut, predicate.c:3888
It may end up with
ERROR: 25P02: current transaction is aborted, commands ignored until end of transaction block
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,
but it would be helpful to understand why we did not see so many errors in the past.
Did something changed from 8.4.4 to 9.4.0?
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,
Michael.
This email and any attachments are intended solely for the use of the individual or entity to whom it is addressed and may be confidential and/or privileged.
If you are not one of the named recipients or have received this email in error,
(i) you should not read, disclose, or copy it,
(ii) please notify sender of your receipt by reply email and delete this email and all attachments,
(iii) Dassault Systemes does not accept or assume any liability or responsibility for any use of or reliance on this email.
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers