Re: Idle in transaction
Re: Idle in transaction
- Subject: Re: Idle in transaction
- From: Aaron Rosenzweig via Webobjects-dev <email@hidden>
- Date: Tue, 15 Dec 2020 13:14:31 -0500
Hi Markus,
So that means you, too, have some queries that are stuck. Postgres is waiting
for you to issue a commit. As long as they live, it will not be able to vacuum
properly. When you close down the .woa, it will no longer be “idle in
transaction”
It’s a curious thing.
For me it appears to be from jdbcInfo() when a new objectStore/editingContext
does a fetch it leaves that around for the life of the .woa instance.
> On Dec 15, 2020, at 2:41 AM, Markus Stoll, junidas GmbH
> <email@hidden> wrote:
>
> Hi Aaron,
>
> did the same on my single WO system using postgres (with only moderate load),
> but on your sql query I DO get some result rows. Did no yet have time for
> further analysis
>
> Regards, Markus
>
>> Am 14.12.2020 um 22:29 schrieb Aaron Rosenzweig via Webobjects-dev
>> <email@hidden <mailto:email@hidden>>:
>>
>> 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
>> <mailto:email@hidden>)
>> Help/Unsubscribe/Update your Subscription:
>>
>>
>> This email sent to email@hidden
>
> Mit freundlichen Grüßen
>
> Markus Stoll
>
> --
> Dr. Markus Stoll (Geschäftsführer)
> email@hidden <mailto:email@hidden>
>
> junidas GmbH, Aixheimer Str. 12, 70619 Stuttgart
> Tel. +49 (711) 4599799-11, Fax +49 (711) 4599799-10
> Geschäftsführer: Dr. Markus Stoll, Matthias Zepf
> Amtsgericht Stuttgart, HRB 21939
>
_______________________________________________
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