Idle in transaction
Idle in transaction
- Subject: Idle in transaction
- From: Aaron Rosenzweig via Webobjects-dev <email@hidden>
- Date: Mon, 14 Dec 2020 16:29:28 -0500
Has anyone dealt with SQL statements that linger and get stuck “idle in
transaction” ?
We started looking carefully at our WO app and Postgres data store. We
discovered that it wasn’t vacuuming because of these hung statements.
Turns out it appears to be deep into WO, when we do fetches sometimes there is
a “begin” with no “commit” - Predominantly it is a select from
“pg_catalog.pg_type” as a fetch of jdbcInfo. Doing a select doesn’t really
warrant a “begin/commit” but since it starts with a “begin” it should cleanup
with a “commit” but it often doesn’t do that… which yields “idle in transaction”
If you are using Postgres, you might want to run the following to see if you
have anything stuck in this state:
SELECT pid, datname, usename, state, backend_xmin,query_start,xact_start,
age(backend_xmin), backend_start,age(now(), pg_stat_activity.backend_start),
state_change, query FROM pg_stat_activity WHERE backend_xmin IS NOT NULL and
state = 'idle in transaction’;
For now, we are sidestepping the issue by forcing PG to cut those loose if they
are older than 5 minutes. This allows vacuum to occur. It’s not ideal, but not
a bad workaround either. Anyone have any thoughts or experience with this?
Cheers,
— Aaron
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden