debugging PostgreSQL serialization failures
8
votes
1
answer
2113
views
I am trying to migrate our PostgreSQL 9.4 database from transaction level
READ COMMITTED
to either REPEATABLE READ
or SERIALIZABLE
. In either case, I'm exposed to a new set of errors with the format:
(for both)
ERROR: could not serialize access due to concurrent update
(just for SERIALIZABLE)
ERROR: could not serialize access due to read/write dependencies among transactions
After reading [the wiki page on SSI](https://wiki.postgresql.org/wiki/SSI) and [the docs](http://www.postgresql.org/docs/9.4/static/transaction-iso.html) , I thoroughly understand the error conditions which could cause these errors, how to handle them, and even best practices to avoid them.
However, I see no way to determine the data dependency which caused them from any debug output that PostgreSQL could provide, or really any debug information at all. Is there any way to get this information from the database, either by executing additional queries at the time of the rollback or through some logging mechanism?
Having this information would allow me to make application-level changes (locking, different queries, etc.) that would eliminate some of the data races to avoid an excessive number of rollbacks.
Asked by Dan
(299 rep)
Apr 18, 2015, 05:03 AM
Last activity: Mar 28, 2017, 11:43 AM
Last activity: Mar 28, 2017, 11:43 AM