How to kill db transaction that timed out from jboss

I use jboss 4.2.3.

It has setting "TransactionTimeout" (in jboss-service.xml), that specifies how long Transaction is allowed to execute.

Unfortunately, when the timeout passes, the execution isn't aborted right now, if the transaction is doing something, only it is marked to be rolled back later.

The effect is - when I have long lasting transaction and thread is wainting on preparedStatement.execute for example, and when the TransactionTimeout passes, nothing happend, client still hang, only when the preparedStatement finishes, there is Exception that transaction was rolled back.

I tried the interceptor from http://management-platform.blogspot.com/2008/11/transaction-timeouts-and-ejb3jpa.html but it only markse thread as interrupted, most methods won't check fo开发者_Go百科r this when executing, so effect is the same.

I've tried also setting preparedStatement.setQueryTimeout, but on Oracle (which we use), it waits with aborting session till oracle feels like doing it (for example it won't abort plsql procedure that is doing dbms_lock.sleep(..)).

I would like to kill database session associated with the transaction, that timed out - I know which transaction it is, and with which thread it is associated (because I use the interceptor from the link I given above), but I don't know how to get session the transaction is bound to - I have to get it, to kill it - and then the thread will get interrupted.

Am I missing easier solution, or doing it completely wrong :) ?

Just did some research on this topic. There is an JTA configuration parameter InterruptThreads, which defaults to false. Reading the docs, this means that the thread will NOT be interrupted but merely marked for rollback as you said.

Sounds like the options are: 1) set InterruptThreads (in jboss-service.xml) to true and 2) Also some discussion on defining your own 'CheckedAction' class, which is included in the transaction handling and termination process.

Looks like with the default settings, the thread is basically allowed to get to some point where it will rollback your update.

There's also the transaction reaper configuration, which default to 2 minutes - where they check on which transactions may have timeed out - so with the default timeout of 5 minutes, plus the 2 minute reaper - worst case, assuming you interrupt threads, you could wait 7 minutes.

I don't know if this answer helps, as it's on JDBC level, which is probably abstracted by JBOSS, but give it a shot.

You can cancel running JDBC statements by using the Statement.cancel() method, though the behavior is DBMS and database driver dependent. As far as I know, it should work on Oracle databases. You have to call the cancel statement from another thread than the one executing the statement.

This seems like a duplicate of What to do if user closes page in the middle of a mysql long query?. It's the same principle except it's the server ending it before completion rather than the client.

I faced a similar issue in one of my projects.I used a Hibernate for database connectivity of a web application deployed on JBoss 1.4.2 with MySQL 1.5.x .

I used innotop to monitor transaction which would stay alive for long period of time.To manage these transactions I built an external app (mine was a web app) which ran a

1. show processlist

   on the database, which gave a list of query being executed by each user and the time for which it ran. Other details included queryId, user ,host , database name, and state

2. I fetched the transactions and ran a kill XXXX command using the queryID for queries running over a specified interval.

http://forums.oracle.com/forums/thread.jspa?threadID=906972 shows something similar in Oracle.

Again this might not be the most ideal way to deal with this. I would appreciate a better solution which handles the problem at a higher level. Let me know if need the source code of the app.