When a transaction as SERIALIZABLE isolation level is failing, is there a way to know which connection(s), thus transaction(s), were involved in the overall process ?
I'm working on an accounting database. I know that we should retry the transaction, this is what we do. I've encountered an issue where a transaction has to be run 10 times to succeed on a busy system.
There should be a serious problem here. I'm wondering if there is a configuration parameter that would allow the system to tell in the exception detail the other connections involved in the failure.
The Postgresql wiki on SSI has no such information.
Thanks is advance.
EDIT 1 :
I pushed the logs to DEBUG5 level with no luck.
So I decided to take a look at the code. Serialization conflict detection is done in src/backend/storage/lmgr/predicate.c, where transactions that are doomed to fail are marked as such with the SXACT_FLAG_DOOMED flag.
I simply added elog(...) calls with the DEBUG1 level, compiled the code and give it a try.
This is interesting as now it shows me something like :
2014-11-28 09:52:47 CET [9888]: [1-1] db=postgres,user=postgres DEBUG: 00000:
SERIALIZABLE TX on Connection [12864] is marked DOOMED by SERIALIZABLE TX on Connection [9888]
Now I have the building blocks to understand what's going on.
First, look at the main PostgreSQL log file. On my box, it's under /var/log/postgresql.
After that, there are a lot of options for logging and error reporting. You can log connection attempts, and you can log every SQL statement. You should be able to put together a pretty good picture of what the server was doing.
I think you should read most of that linked documentation. Some of the options have a significant effect on server throughput.