Postgresql – debugging PostgreSQL serialization failures

isolation-levelpostgresqlpostgresql-9.4serialization

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 and the docs, 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.

Best Answer

It'd be good to have, but I don't think there's really much there at the moment. It's particularly tricky because multiple statements are often involved and the culprit(s) aren't always the current running ones, especially on at-commit failures. Collecting the extra info would be ruinous for performance and memory use, but it'd be a great option to be able to turn on when debugging.

Your best bet is probably to greatly increase your log levels and make sure you have a log_line_prefix including %m, %p, %c:%l and %v:%x so you have the transaction and session information required to identify and reassemble what sessions and their xacts did from the logs.

Related Question