Sample Header Ad - 728x90

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