Обсуждение: Serialization exception : Who else was involved?
Hello,
I’m using PostgreSQL .9.2.8 on Windows from a .NET application using Npgsql.
I’m working in the Radiology Information System field.
We have thousands of users against a big accounting database.
We’re using the SERIALIZABLE isolation level to ensure data consistency.
Because of the large number of users, and probably because of the database design, we’re facing serialization exception and we retry our transactions.
So far so good.
I was wondering if there was a log level in PostgreSQL that could tell me which query was the trigger of a doomed transaction.
The goal is to understand the failures to improve the database and application designs.
I pushed the logs to the DEBUG5 level with no luck.
After carefully reviewing the documentation, it seems that there was nothing.
So I downloaded the code and looked at it.
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 NOTIFY level, each time the flag is set, compiled the code and give it a try.
The results are amazing for me, because this simple modification allows me to know which query is marking other running transactions to fail.
I’m pretty sure that in the production environment of our major customers, there should be no more than a few transaction involved.
I would like to see this useful and simple addition in a future version of PostgreSQL.
Is it in the spirit of what is done when it comes to ease the work of the developer ?
May be the level I’ve chosen is not appropriate ?
Please let me know what you think.
Kind Regards.
Olivier.
On Tue, Dec 02, 2014 at 11:17:43AM +0100, Olivier MATROT wrote: > 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 NOTIFY level, each time the flag > is set, compiled the code and give it a try. > I would like to see this useful and simple addition in a future version > of PostgreSQL. > Is it in the spirit of what is done when it comes to ease the work of > the developer ? > May be the level I've chosen is not appropriate ? I would value extra logging designed to help users understand the genesis of serialization failures. A patch the community would adopt will probably have more complexity than your quick elog(NOTICE, ...) addition. I don't have a clear picture of what the final patch should be, but the following are some thoughts to outline the problem space. See [1] for an earlier discussion. The logging done in DeadLockReport() is a good baseline; it would be best to consolidate a similar level of detail and report it all as part of the main serialization failure report. That may prove impractical. If transaction TA marks transaction TB's doom, TA can be long gone by the time TB reports its serialization failure. TA could persist the details needed for that future error report, but that may impose costs out of proportion with the benefit. If so, we can fall back on your original idea of emitting a message in the command that performs the flag flip. That would need a DEBUG error level, though. Sending a NOTICE to a client when its transaction dooms some other transaction would add noise in the wrong place. Thanks, nm [1] http://www.postgresql.org/message-id/flat/AANLkTikF-CR-52nWAo2VG_348aTsK_+0i=chBPNqdepv@mail.gmail.com
On 12/02/2014 06:17 PM, Olivier MATROT wrote: > I was wondering if there was a log level in PostgreSQL that could tell > me which query was the trigger of a doomed transaction. It's not necessarily possible to tell which *query* in another transaction caused the current one to fail. It might not be a single query in the local session or the other session. What can be identified is the other transaction ID. If you set log_line_prefix to include the txid and pid, and you log_statement = 'all', you can examine the logs to see what happened. I admit it's pretty clumsy. It'd be very nice to provide more information on the causes of failures - but I suspect doing so *efficiently*, without making serializable a huge impact on performance, would be quite challenging. -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On 12/02/2014 06:17 PM, Olivier MATROT wrote: > 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 NOTIFY level, each time the flag > is set, compiled the code and give it a try. I don't see how that'd necessarily correctly identify the query/queries in the other tx that're involved, though. Perhaps I'm thinking in terms of more complicated serialization failures? What sorts of failures are you actually running into? -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
Craig Ringer <craig@2ndquadrant.com> wrote: > I don't see how that'd necessarily correctly identify the > query/queries in the other tx that're involved, though. > > Perhaps I'm thinking in terms of more complicated serialization > failures? Yeah, it might be possible to provide useful information about specific conflicting queries in some cases, but I'm skeptical that it would be available in the majority of cases. In most cases you can probably identify one or two other transactions that are involved. In at least some cases you won't even have that. For one fun case to think about, see this example where a read-only transaction fails with on conflict with two already-committed transactions: https://wiki.postgresql.org/wiki/SSI#Rollover Also consider when there is a long-running transactions and SSI falls back to SLRU summarization. If we can find a way to provide some useful information in some cases without harming performance, that's fine as long as nobody expects that it will be available in all cases. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Indeed, NOTICE is wrong because it would doom the transaction that sets the flag if it should be later PREPARED. I think that reporting the PIDs and the current activity of each process would be nice. DeadLoackReport() is using pgstat_get_backend_current_activity()to get the process activity. I'll see what I could come up with. Thanks, om -----Message d'origine----- De : Noah Misch [mailto:noah@leadboat.com] Envoyé : samedi 27 décembre 2014 10:51 À : Olivier MATROT Cc : pgsql-hackers@postgresql.org Objet : Re: Serialization exception : Who else was involved? On Tue, Dec 02, 2014 at 11:17:43AM +0100, Olivier MATROT wrote: > 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 NOTIFY level, each time the > flag is set, compiled the code and give it a try. > I would like to see this useful and simple addition in a future > version of PostgreSQL. > Is it in the spirit of what is done when it comes to ease the work of > the developer ? > May be the level I've chosen is not appropriate ? I would value extra logging designed to help users understand the genesis of serialization failures. A patch the communitywould adopt will probably have more complexity than your quick elog(NOTICE, ...) addition. I don't have a clearpicture of what the final patch should be, but the following are some thoughts to outline the problem space. See [1]for an earlier discussion. The logging done in DeadLockReport() is a good baseline; it would be best to consolidate a similar level of detail and reportit all as part of the main serialization failure report. That may prove impractical. If transaction TA marks transactionTB's doom, TA can be long gone by the time TB reports its serialization failure. TA could persist the detailsneeded for that future error report, but that may impose costs out of proportion with the benefit. If so, we can fall back on your original idea of emitting a message in the command that performs the flag flip. That wouldneed a DEBUG error level, though. Sending a NOTICE to a client when its transaction dooms some other transaction wouldadd noise in the wrong place. Thanks, nm [1] http://www.postgresql.org/message-id/flat/AANLkTikF-CR-52nWAo2VG_348aTsK_+0i=chBPNqdepv@mail.gmail.com