6

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.

5

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.

  • Collecting the extra info would be ruinous for performance -- maybe... but PostgreSQL has to store this in memory somehow to detect the races at commit time. However, you're probably right in that these may simply be an internal format and not something that PostgreSQL users would be able to understand, and that extending them to be usable for debugging might be performance-prohibitive. – Dan Mar 29 '17 at 19:43
  • 1
    @Dan What it stores is "pivot" information between transactions. It doesn't try to track which statement it was, let alone the statement text. Storing details of statement text, which exact tuple was affected, etc, is where we'd probably hit serious performance issues. – Craig Ringer Mar 30 '17 at 6:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.