I found this problem when I was trying to do a mysqldump --single-transaction --all-databases which blocks on trying to do FLUSH TABLES which never succeeds as something is holding a lock.
After sending this information to MySQL Enterprise Support, it turns out that ActiveMQ is holding a lock because it has a long running transaction from the time ActiveMQ was started. Running the following commands. SHOW FULL PROCESSLIST; SHOW ENGINE INNODB STATUS\G Sample from the file: ---TRANSACTION 0 3664001, ACTIVE 2150143 sec, process no 4847, OS thread id 1148832080 mysql tables in use 1, locked 1 2 lock struct(s), heap size 368, undo log entries 71673 MySQL thread id 202, query id 50575399 q01.corp.xxxx.com 10.xx.xxx.49 q01_maintest Looking for the thread id 202 in the PROCESSLIST output: | 202 | q01_maintest | q01.xxx.com:48802 | q01_maintest_amq | Sleep | 21 | | NULL | In the above sample, you can see that thread 202 is a connection from q01 using user q01_maintest over DB name q01_maintest_amq (the ActiveMQ DB) which has a transaction ACTIVE for 2150143 seconds with 71673 undo log entries, causing 1 table to be locked for the whole time. Can someone please shed some light on me what's going on? Thanks a lot. -- View this message in context: http://www.nabble.com/uncommitted-transaction-for-several-days-in-activemq-tp25264946p25264946.html Sent from the ActiveMQ - User mailing list archive at Nabble.com.
