DSpace 6 Hibernate exacerbating "idle in transaction" locking issues

[Alan Orth]

Dear list,

We upgraded from DSpace 5.8 to 6.3 a few days ago and the database load went up significantly. Users report very long wait times and timeouts during item submission, taking items from the workflow, and approving items. You can see in the attached graph that we have a large number of connections waiting for locks.

When I look at the PostgreSQL status I see over 2,000 locks:

$ psql -c 'SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid;' | wc -l

2071

Looking at the locks I see at least half of them "idle in transaction" with the query being some SELECT statement. Why is Hibernate BEGINing transactions and not committing or rolling them back before returning them to the pool?

We are using DSpace 6.3 on Tomcat 7.0.104 with Tomcat's JDBC connection pool provided via JNDI.

Any help or pointers would be appreciated. Our editors can barely work now...

--

Alan Orth
alan...@gmail.com
https://picturingjordan.com
https://englishbulgaria.net
https://mjanja.ch

[Alan Orth]

Update on this for posterity:

After a few days the connections and locks have gone down and we are now able to submit items again. I have no clue what caused this "transaction storm". See attached graphs from our monitoring now that it's ~1 week later. Hoping someone else can comment on their DSpace 5 → 6 upgrade experience with special consideration to the database configuration!

Regards,