From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Jonathan Vanasco <postgres(at)2xlp(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: how can i bugfix "idle in transaction" lockups ? |
Date: | 2010-11-30 19:21:57 |
Message-ID: | AANLkTi=2kO6_6ecmCSnRoz0Tdnhnbdo0vpT_4rQvuWNa@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, Nov 30, 2010 at 10:21 AM, Jonathan Vanasco <postgres(at)2xlp(dot)com> wrote:
> on a project, i find myself continually finding the database locked up with "idle in transaction" connections
>
> are there any commands that will allow me to check exactly what was going on in that transaction ?
>
> i couldn't find anything in the docs, and the project has decent traffic, so its going to be an issue to log all statements so I can sift through the data by PID
You can match the procpid on pg_stat_activity vs pid on pg_locks.
This will give you relation, which you can cross reference against
pg_database and pg_class system tables -- that should give a few
clues.
You can also break down various things interacting with the database
by role. For example, have the website auth into the database with a
'website' role, backend reporting scripts with 'reports', etc. That
way pg_stat_activity might tell you the specific trouble maker that is
doing this.
After that, it's about finding the bug -- are you using connection
pooling? Begin w/o commit is a grave application error and you should
consider reworking your code base so that it doesn't happen (ever).
merlin
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2010-11-30 19:32:45 | Re: lock file permisson |
Previous Message | Jasen Betts | 2010-11-30 19:08:47 | Re: finding rows with invalid characters |