long running transactions w/locks causing tx timeouts
I'm using mysql Ver 12.22 Distrib 4.0.22, for pc-linux-gnu (i686).
I am seeing transaction timeouts in my application log (via the ConnectorJ 3.0.8 jdbc driver).
Using 'show innodb status', I can see that I have a number of long running transactions. Here's a sample,
---TRANSACTION 0 20978544, ACTIVE 13907 sec, process no 8370, OS thread id 11288715
23 lock struct(s), heap size 2496, undo log entries 112
MySQL thread id 680, query id 2464098 localhost 127.0.0.1 miguel
Trx read view will not see trx with id >= 0 20978545, sees < 0 20957376
--------
I believe the long running transactions are holding locks that other transactions need, and this is causing the other transactions to timeout.
Ideally, the transaction that is timing out would indicate the lock that it is causing the timeout. How can I gather this information?
Also, is there any way to trace back from the identifiers (tx 20978544, MySQL thread id 680, query id 2464098) to the sql statement that is running?
thanks,
Miguel DeAvila