Thread: how can i bugfix "idle in transaction" lockups ?
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 statementsso I can sift through the data by PID
On Nov 30, 2010, at 7:21 AM, Jonathan Vanasco 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 statementsso I can sift through the data by PID Are those transactions actually holding locks? (You can verify by checking pg_locks.) If so, that might give you a clue.If not, then they shouldn't be causing any lockups....
On Tue, Nov 30, 2010 at 10:21 AM, Jonathan Vanasco <postgres@2xlp.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 statementsso 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
begin w/o commit or rollback? and thanks. you've been very helpful! On Nov 30, 2010, at 2:21 PM, Merlin Moncure wrote: > Begin w/o commit is a grave application error and you should > consider reworking your code base so that it doesn't happen (ever).
On Nov 30, 2010, at 7:21 AM, Jonathan Vanasco wrote: > i couldn't find anything in the docs, and the project has decent traffic, so its going to be an issue to log all statementsso I can sift through the data by PID With machine-readable logs, it shouldn't be *that* serious an issue. 1. Are you using connection pooling? 2. What's the application server environment? -- -- Christophe Pettus xof@thebuild.com
On Fri, Dec 3, 2010 at 12:31 AM, Jonathan Vanasco <postgres@2xlp.com> wrote: > begin w/o commit or rollback? > > and thanks. you've been very helpful! > > On Nov 30, 2010, at 2:21 PM, Merlin Moncure wrote: > >> Begin w/o commit is a grave application error and you should >> consider reworking your code base so that it doesn't happen (ever). meaning, you opened a transaction (with 'begin') and didn't close it with 'commit' (or 'start/end' etc). You opened a transaction but didn't close it. Don't ever do this, and don't ever leave a transaction open waiting on indeterminate events, like user input. merlin