I upgraded our entire backend mysql db from 5.0 to 5.6, including changing a few tables to innodb, and we've been having nothing but problems with never ending transactions since. I still have a staging server that uses 5.0, and I can confirm that we only get stalled transactions on the new database server. Both servers are running in tx_isolation = REPEATABLE-READ mode (http://dev.mysql.com/doc/refman/5.6/en/set-transaction.html ). I'm pretty sure all the tables involved are InnoDB for both servers.
So a simple example of the problem we've been having is some process that sends welcome emails, which runs as a supervisord child (not really important). On the stage env with mysql 5.0, the connection lasts a few minutes and has no open transactions:
From show full processlist:
1639945 dbuser <app-stage>:54536 db Sleep 246 NULL
InnoDB transaction logs:
<nothing>
The exact same program on our production environment with mysql 5.6 and its suddenly the demon child that locks really important tables and never releases them.
From show full processlist:
28674638 dbuser <app-prod>:54836 db Sleep 67131 NULL
Innodb transaction:
---TRANSACTION 90461789, ACTIVE 67062 sec
MySQL thread id 28674638, OS thread handle 0x7f8ab934f700, query id 758722407 <app-prod> dbuser cleaning up
Trx read view will not see trx with id >= 90461790, sees < 89033402
When its not causing horrible problems, the transaction looks like:
---TRANSACTION 111578756, not started
MySQL thread id 42149496, OS thread handle 0x7f8ac29b4700, query id 975441865 <app-prod> dbuser cleaning up
Anyone have any suggestions? I'm sort of thinking about enabling transaction mode of read-uncommited but.... it seems like a patch for a different problem, and I really need to know what the original problem is!
So I never quite figured out exactly how to trigger the problem, but it had something to do with really large innodb tables in one of our databases. One of them was over 303 million lines long. When I setup some scripts to remove old data every night, the problem went away for good. These really weren't very important tables, and were for the most part only written to, usually not read. They also all had too many indexes.
Anyway if you have this problem, try getting all your tables under 10-15 million entries.