From: | Olivier MATROT <olivier(dot)matrot(at)accelis-sir(dot)fr> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Serialization exception : Who else was involved? |
Date: | 2014-12-02 10:17:43 |
Message-ID: | B67C2F6B6C7C57468D1BA9F176237121057482CF@pluton.Vepro.intra |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Frost | 2014-12-02 10:57:56 | Re: Role Attribute Bitmask Catalog Representation |
Previous Message | Tomas Vondra | 2014-12-02 09:59:14 | Re: excessive amounts of consumed memory (RSS), triggering OOM killer |