When is an "inactive" session REALLY inactive?
85558Oct 25 2010 — edited Oct 26 2010I have a somewhat unusual situation and I'm not sure how to fix it or even if it is "fixable". I'm running a 10g database (10.2.0.4) underneath a java-based application. We use a connection pool with multiple threads into the database for our application connections. When a user logs into this application, it picks up one of the unused threads and assigns it to the connection. Now, as a matter of course, most of the time, these "sessions" will read as "INACTIVE" and really BE inactive. But, occasionally, it seems like an operation will perform the beginning of a transaction (such as an update or insert) and then the thread just ... goes comatose. The transaction is never committed or rolled back and, as a result, will block other transactions that rely on doing updates or inserts into those objects.
I know there's a way to automatically disconnect sessions that have been idle for a given period of time, but I figure if I implement that procedure then, over time, ALL of my multiple threads in the connection pool will fall into the "idle" state and be destroyed. So, I wondered, if I implement a "heartbeat" on each of the threads, something like "SELECT 1 FROM DUAL" every x minutes, and then set the timeout for connections = x+some value then the connection time out should ONLY catch those threads that are truely "dead" and not those who are simply waiting for someone to log in and grab them. Any thoughts? Is there a better way to handle this kind of situation? Is there a way to identify those sessions that have a transaction that's waiting for a conclusion (i.e., commit or rollback)?
I have a query that shows me the last sql statement processed by a session and what session is blocking the transaction, but I need something that I can make more automatic so that it shakes the connection pool and makes sure that the "INACTIVE" sessions are REALLY "INACTIVE" (i.e., not being used by my application) and which ones are claiming to be "INACTIVE" (i.e., no recent operations from the JDBC connector) but are in that state because the thread has gone wonky.
Any and all help and suggestions gratefully appreciated.