Spring GetHibernateTemplate() or GetSession()

Question

I am facing an issue due to improper understanding of Spring methods -> GetHibernateTemplate/GetSession. When I use a native sql query [getSession().createSQLQuery(nativeQuery)] to fetch some records from the database, it seems to me that there is a deadlock kind of situation and after N number of requests, the db connection pool exhausts. This is a sample of what I see when I check this in db "SHOW ENGINE INNODB STATUS" :

MySQL thread id 1106, query id 1360 localhost 127.0.0.1 test2
---TRANSACTION 0 19491, not started, OS thread id 2960035840

What am I doing wrong? What is a good way to run native sql queries?

Answer 1

Spring transparently handles opening and closing DB connections. The table at the beginning of the data access chapter of the documentation shows what you handle and what Spring handles, the latter of which includes opening and closing connections. It seems unlikely that Spring itself is the issue.

Instead, here are two possibilities for why you might be experiencing your situation:

1. Your connection pool is misconfigured in some fashion
2. You don't realize it, but you have some out-of-control, or really long-running/inefficient queries

If you up your logging to debug, that would give more clues.