How to kill db transaction flowing from jboss

Question

I am using jboss 4.2.3.

It has a TransactionTimeout parameter (in jboss-service.xml) that indicates how long the transaction can take.

Unfortunately, when the timeout passes, the execution is not canceled right now, if the transaction does something, only it is marked as rolled back later.

The effect is when I have a long transaction and the flow is wainting on prepareStatement.execute, for example, and when the TransactionTimeout passes, nothing happens, the client still freezes, only when the readyStatement ends, is there an Exception that the transaction was rolled back.

I tried the interceptor from http://management-platform.blogspot.com/2008/11/transaction-timeouts-and-ejb3jpa.html , but it only marks the stream as interrupted, most methods will not check this when it is executed, so the effect is same.

I also tried setting prepareStatement.setQueryTimeout, but on Oracle (which we use) it waits with a session termination until the oracle wants to do this (for example, it does not interrupt the plsql procedure that dbms_lock.sleep (..) does).

I would like to kill a database session associated with a transaction, timeout - I know which transaction it is and which thread it is associated with (because I use the interceptor from the link above), but I don’t know How to get the session that the transaction is attached to - I have to get it in order to kill it - and then the thread will be aborted.

Am I missing a simpler solution, or am I doing it completely wrong :)?

Answer 1

I don't know if this answer helps, like at the JDBC level, which is probably abstracted by JBOSS, but give it a chance.

You can cancel the execution of JDBC statements using the Statement.cancel () method, although the behavior is dependent on the DBMS and database. As far as I know, it should work with Oracle databases. You must call the cancel statement from a thread other than the one that the statement is executing.

Answer 2

Just a few studies on this topic. There is a JTA InterruptThreads configuration parameter, which is false by default. When reading documents, this means that the stream will NOT be interrupted, but simply marked for rollback, as you said.

It sounds like this: 1) set InterruptThreads (in jboss-service.xml) to true, and 2) We also discuss the definition of our own CheckedAction class, which is included in the process of processing and completing a transaction.

It seems that with the default settings, the thread is basically allowed to get to some point where it will roll back from your update.

There is also a transaction converter configuration that defaults to 2 minutes - where they check which transactions can be timed out - so with a default timeout of 5 minutes plus a 2-minute reaper - the worst case, assuming you're interrupting threads You can wait 7 minutes.

Answer 3

This looks like a duplicate. What if the user closes the page in the middle of a long mysql query? . This is the same principle, except that the server finishes it before completion, and not the client.

Answer 4

I had a similar problem in one of my projects. I used Hibernate to connect to the database of a web application deployed on JBoss 1.4.2 with MySQL 1.5.x.

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

• show process list

  in the database, which gave a list of queries performed by each user, and the time for which he ran. Other details include queryId, username, host, database name and status

• I retrieved the transactions and executed the kill XXXX command, using queryID for queries running in the specified interval.

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

Again, this may not be the best way to handle this . I would appreciate a better solution that would solve the problem at a higher level. . Let me know if you need the source code of the application.