Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

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

Explorer

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