Reply
Explorer
Posts: 15
Registered: ‎11-23-2017

hanging session in state "idle in transaction" on user scm

Hi,

 

We have CMS and CDH in version 5.15.0.

We use a pgpool/postgres as metadata repository for all components.

We noticed that we have many sessions in postgres on user scm in state "idle in transaction".

 

A few days ago we stopped CMS, killed such sessions and start CMS. Now we have 21 such sessions with the following time of query start.

 2018-08-24 11:58:55.403395+00
 2018-08-24 12:00:22.307214+00
 2018-08-24 12:01:30.023778+00
 2018-08-24 12:02:02.566545+00
 2018-08-24 12:02:23.217016+00
 2018-08-24 12:02:54.172696+00
 2018-08-24 12:03:14.532998+00
 2018-08-24 12:03:51.846813+00
 2018-08-24 12:04:43.142894+00
 2018-08-24 12:17:37.338209+00
 2018-08-24 12:19:48.868457+00
 2018-08-24 12:21:22.216285+00
 2018-08-24 19:16:04.203381+00
 2018-08-27 10:20:59.562903+00
 2018-08-27 10:21:47.175394+00
 2018-08-27 10:28:35.555014+00
 2018-08-27 10:35:48.140317+00
 2018-08-27 10:36:18.134924+00
 2018-08-27 10:36:49.021812+00
 2018-08-29 11:13:42.534586+00
 2018-08-29 11:14:00.625328+00

 

The queries are the same or almost the same:

select this_.CLIENT_CONFIG_ID as CLIENT1_1_1_, this_.OPTIMISTIC_LOCK_VERSION as OPTIMIST2_1_1_, this_.CREATED_INSTAN
T as CREATED3_1_1_, this_.SERVICE_ID as SERVICE4_1_1_, this_.GATEWAY_ID as GATEWAY5_1_1_, this_.CLUSTER_ID as CLUSTER6_1_1_, this_.MIME_TYPE as MIME7
_1_1_, this_.FILENAME as FILENAME8_1_1_, this_.CONFIG_ARCHIVE as CONFIG9_1_1_, this_.METADATA as METADAT10_1_1_, this_.CONFIG_STALENESS_STATUS as CON
FIG11_1_1_, this_.GENERATION as GENERAT12_1_1_, this_.RESOURCES as RESOURC13_1_1_, hosts3_.CLIENT_CONFIG_ID as CLIENT2_1_, hostsalias1_.HOST_ID as HO
ST1_2_, hostsalias1_.HOST_ID as HOST1_21_0_, hostsalias1_.OPTIMISTIC_LOCK_VERSION as OPTIMIST2_21_0_, hostsalias1_.HOST_IDENTIFIER as HOST3_21_0_, ho
stsalias1_.NAME as NAME4_21_0_, hostsalias1_.IP_ADDRESS as IP5_21_0_, hostsalias1_.PUBLIC_NAME as PUBLIC6_21_0_, hostsalias1_.PUBLIC_IP_ADDRESS as PU
BLIC7_21_0_, hostsalias1_.CLOUD_PROVIDER as CLOUD8_21_0_, hostsalias1_.RACK_ID as RACK9_21_0_, hostsalias1_.STATUS as STATUS10_21_0_, hostsalias1_.MA
INTENANCE_COU

I will be grateful for any suggestions.

Andrzej