Still working on this problem. I have reached my max connection counts, which I set to 100, and it seems like I have 100 "idle in transaction" connections. And from the pg_stat_activity table I can see that they are all stuck on this sql. I wonder if anyone can point me to where this sql originates. That might help me determine the reason why it is getting stuck.
2022-08-18 18:36:35,030 DEBUG org.hibernate.SQL @ select this_.policy_id as policy_i1_33_6_, this_.action_id as action_i2_33_6_, this_.dspace_object as dspace_o9_33_6_, this_.end_date as end_date3_33_6_, this_.eperson_id as eperson10_33_6_, this_.epersongroup_id as eperson11_33_6_, this_.resource_type_id as resource4_33_6_, this_.rpdescription as rpdescri5_33_6_, this_.rpname as rpname6_33_6_, this_.rptype as rptype7_33_6_, this_.start_date as start_da8_33_6_, dspaceobje2_.uuid as uuid1_13_0_, dspaceobje2_1_.bitstream_format_id as bitstre10_2_0_, dspaceobje2_1_.checksum as checksum1_2_0_, dspaceobje2_1_.checksum_algorithm as checksum2_2_0_, dspaceobje2_1_.deleted as deleted3_2_0_, dspaceobje2_1_.internal_id as internal4_2_0_, dspaceobje2_1_.bitstream_id as bitstrea5_2_0_, dspaceobje2_1_.sequence_id as sequence6_2_0_, dspaceobje2_1_.size_bytes as size_byt7_2_0_, dspaceobje2_1_.store_number as store_nu8_2_0_, dspaceobje2_2_.bundle_id as bundle_i1_4_0_, dspaceobje2_2_.primary_bitstream_id as primary_3_4_0_, dspaceobje2_3_.admin as admin3_8_0_, dspaceobje2_3_.collection_id as collecti1_8_0_, dspaceobje2_3_.logo_bitstream_id as logo_bit4_8_0_, dspaceobje2_3_.submitter as submitte5_8_0_, dspaceobje2_3_.template_item_id as template6_8_0_, dspaceobje2_3_.workflow_step_1 as workflow7_8_0_, dspaceobje2_3_.workflow_step_2 as workflow8_8_0_, dspaceobje2_3_.workflow_step_3 as workflow9_8_0_, dspaceobje2_4_.admin as admin3_10_0_, dspaceobje2_4_.community_id as communit1_10_0_, dspaceobje2_4_.logo_bitstream_id as logo_bit4_10_0_, dspaceobje2_5_.discoverable as discover1_24_0_, dspaceobje2_5_.in_archive as in_archi2_24_0_, dspaceobje2_5_.last_modified as last_mod3_24_0_, dspaceobje2_5_.item_id as item_id4_24_0_, dspaceobje2_5_.owning_collection as owning_c7_24_0_, dspaceobje2_5_.submitter_id as submitte8_24_0_, dspaceobje2_5_.withdrawn as withdraw5_24_0_, dspaceobje2_7_.can_log_in as can_log_1_14_0_, dspaceobje2_7_.digest_algorithm as digest_a2_14_0_, dspaceobje2_7_.email as email3_14_0_, dspaceobje2_7_.last_active as last_act4_14_0_, dspaceobje2_7_.eperson_id as eperson_5_14_0_, dspaceobje2_7_.netid as netid6_14_0_, dspaceobje2_7_.password as password7_14_0_, dspaceobje2_7_.require_certificate as require_8_14_0_, dspaceobje2_7_.salt as salt9_14_0_, dspaceobje2_7_.self_registered as self_re10_14_0_, dspaceobje2_8_.eperson_group_id as eperson_1_15_0_, dspaceobje2_8_.name as name2_15_0_, dspaceobje2_8_.permanent as permanen3_15_0_, case when dspaceobje2_1_.uuid is not null then 1 when dspaceobje2_2_.uuid is not null then 2 when dspaceobje2_3_.uuid is not null then 3 when dspaceobje2_4_.uuid is not null then 4 when dspaceobje2_5_.uuid is not null then 5 when dspaceobje2_6_.uuid is not null then 6 when dspaceobje2_7_.uuid is not null then 7 when dspaceobje2_8_.uuid is not null then 8 when dspaceobje2_.uuid is not null then 0 end as clazz_0_, bitstreamf3_.bitstream_format_id as bitstrea1_3_1_, bitstreamf3_.description as descript2_3_1_, bitstreamf3_.internal as internal3_3_1_, bitstreamf3_.mimetype as mimetype4_3_1_, bitstreamf3_.short_description as short_de5_3_1_, bitstreamf3_.support_level as support_6_3_1_, bitstream4_.uuid as uuid1_13_2_, bitstream4_.bitstream_format_id as bitstre10_2_2_, bitstream4_.checksum as checksum1_2_2_, bitstream4_.checksum_algorithm as checksum2_2_2_, bitstream4_.deleted as deleted3_2_2_, bitstream4_.internal_id as internal4_2_2_, bitstream4_.bitstream_id as bitstrea5_2_2_, bitstream4_.sequence_id as sequence6_2_2_, bitstream4_.size_bytes as size_byt7_2_2_, bitstream4_.store_number as store_nu8_2_2_, group5_.uuid as uuid1_13_3_, group5_.eperson_group_id as eperson_1_15_3_, group5_.name as name2_15_3_, group5_.permanent as permanen3_15_3_, group6_.uuid as uuid1_13_4_, group6_.eperson_group_id as eperson_1_15_4_, group6_.name as name2_15_4_, group6_.permanent as permanen3_15_4_, bitstream7_.uuid as uuid1_13_5_, bitstream7_.bitstream_format_id as bitstre10_2_5_, bitstream7_.checksum as checksum1_2_5_, bitstream7_.checksum_algorithm as checksum2_2_5_, bitstream7_.deleted as deleted3_2_5_, bitstream7_.internal_id as internal4_2_5_, bitstream7_.bitstream_id as bitstrea5_2_5_, bitstream7_.sequence_id as sequence6_2_5_, bitstream7_.size_bytes as size_byt7_2_5_, bitstream7_.store_number as store_nu8_2_5_ from public.resourcepolicy this_ left outer join public.dspaceobject dspaceobje2_ on this_.dspace_object=dspaceobje2_.uuid left outer join public.bitstream dspaceobje2_1_ on dspaceobje2_.uuid=dspaceobje2_1_.uuid left outer join public.bundle dspaceobje2_2_ on dspaceobje2_.uuid=dspaceobje2_2_.uuid left outer join public.collection dspaceobje2_3_ on dspaceobje2_.uuid=dspaceobje2_3_.uuid left outer join public.community dspaceobje2_4_ on dspaceobje2_.uuid=dspaceobje2_4_.uuid left outer join public.item dspaceobje2_5_ on dspaceobje2_.uuid=dspaceobje2_5_.uuid left outer join public.site dspaceobje2_6_ on dspaceobje2_.uuid=dspaceobje2_6_.uuid left outer join public.eperson dspaceobje2_7_ on dspaceobje2_.uuid=dspaceobje2_7_.uuid left outer join public.epersongroup dspaceobje2_8_ on dspaceobje2_.uuid=dspaceobje2_8_.uuid left outer join public.bitstreamformatregistry bitstreamf3_ on dspaceobje2_1_.bitstream_format_id=bitstreamf3_.bitstream_format_id left outer join public.bitstream bitstream4_ on dspaceobje2_2_.primary_bitstream_id=bitstream4_.uuid left outer join public.dspaceobject bitstream4_1_ on bitstream4_.uuid=bitstream4_1_.uuid left outer join public.epersongroup group5_ on dspaceobje2_3_.submitter=group5_.uuid left outer join public.dspaceobject group5_1_ on group5_.uuid=group5_1_.uuid left outer join public.epersongroup group6_ on dspaceobje2_4_.admin=group6_.uuid left outer join public.dspaceobject group6_1_ on group6_.uuid=group6_1_.uuid left outer join public.bitstream bitstream7_ on dspaceobje2_4_.logo_bitstream_id=bitstream7_.uuid left outer join public.dspaceobject bitstream7_1_ on bitstream7_.uuid=bitstream7_1_.uuid where (this_.dspace_object=? and this_.action_id=?) Thank you! -Jose On Wed, Aug 17, 2022 at 3:12 PM Jose Blanco <[email protected]> wrote: > > Just out of nowhere I'm getting Hibernate timeout errors on my dspace > 6 app. Not sure what is causing it. It seems to reach the max > connection setting quickly ( 30 is default, and I have been using that > for a long time, but I changed it to 50 ). This is what I am seeing > in the database for one of the connections that is not releasing: > > Any ideas? > > => SELECT relation, transactionid, pid, mode, granted, relname > FROM pg_locks > INNER JOIN pg_stat_user_tables > ON pg_locks.relation = pg_stat_user_tables.relid > WHERE pg_locks.pid='10269'; > > relation | transactionid | pid | mode | granted | > relname > -----------+---------------+-------+-----------------+---------+------------------------- > 24125181 | | 10269 | AccessShareLock | t | collection > 24125274 | | 10269 | AccessShareLock | t | eperson > 24125123 | | 10269 | AccessShareLock | t | bitstream > 24125535 | | 10269 | AccessShareLock | t | workspaceitem > 24125131 | | 10269 | AccessShareLock | t | > bitstreamformatregistry > 24125319 | | 10269 | AccessShareLock | t | handle > 24125187 | | 10269 | AccessShareLock | t | > collection2item > 24125512 | | 10269 | AccessShareLock | t | > versionhistory > 24125208 | | 10269 | AccessShareLock | t | > community2collection > 24125242 | | 10269 | AccessShareLock | t | > metadatafieldregistry > 24125374 | | 10269 | AccessShareLock | t | item2bundle > 24125154 | | 10269 | AccessShareLock | t | > bundle2bitstream > 24125407 | | 10269 | AccessShareLock | t | > metadataschemaregistry > 24125440 | | 10269 | AccessShareLock | t | > resourcepolicy > 24125371 | | 10269 | AccessShareLock | t | item > 24125151 | | 10269 | AccessShareLock | t | bundle > 24125517 | | 10269 | AccessShareLock | t | versionitem > 913986038 | | 10269 | AccessShareLock | t | dspaceobject > 24125282 | | 10269 | AccessShareLock | t | epersongroup > 24125213 | | 10269 | AccessShareLock | t | > community2community > 24125530 | | 10269 | AccessShareLock | t | workflowitem > 24125249 | | 10269 | AccessShareLock | t | metadatavalue > 24125202 | | 10269 | AccessShareLock | t | community > 913986043 | | 10269 | AccessShareLock | t | site > 24125314 | | 10269 | AccessShareLock | t | > group2groupcache -- All messages to this mailing list should adhere to the Code of Conduct: https://www.lyrasis.org/about/Pages/Code-of-Conduct.aspx --- You received this message because you are subscribed to the Google Groups "DSpace Technical Support" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/CAK%3DKc-s2%3DWen2KjaCJPbLV7bd8G498LF9VhTy-zn1Mougta1Zg%40mail.gmail.com.
