Re: Idle in transaction
Re: Idle in transaction
- Subject: Re: Idle in transaction
- From: "Markus Stoll, junidas GmbH via Webobjects-dev" <email@hidden>
- Date: Tue, 15 Dec 2020 20:17:48 +0100
Hi Aaron,
for each of my instances I have two database connections to the postgres DB -
obviously one with the idle transaction and the other working one. And each
idle transaction originates from the woa startup. So your conclusion sounds
reasonable.
But I still did not find where this select statement is created...
Markus
> Am 15.12.2020 um 19:14 schrieb Aaron Rosenzweig <email@hidden>:
>
> 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 <mailto: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 <mailto: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
>>
>
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
Attachment:
smime.p7s
Description: S/MIME cryptographic signature
_______________________________________________
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