You are right that the transaction is 'running' for several hours. This basically means you have a query that has failed to run 'commit' and is holding the transaction open.
If you are running in the default REPEATABLE READ
transaction isolation level, you will also see your 'history list length' growing large in the TRANSACTION section of the SHOW ENGINE INNODB STATUS
output
The lock status in SHOW OPEN TABLES
, assuming you meant 'name_locked', is only for whether the name is locked for operations such as dropping or renaming the tables src
I am curious if SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS
returns anything, especially if db1.table_a
and db1.table_b
are InnoDB tables. If they are MyISAM I would urge not using BEGIN TRANSACTION
or SET autocommit=0
, as you can run into some serious issues with potentially mixing InnoDB and MyISAM tables in a transaction.
Unfortunately, getting access to what that transaction has done is fairly impossible unless you're running the PERFROMANCE_SCHEMA to capture statemen. See: http://www.markleith.co.uk/2012/07/13/monitoring-processes-with-performance-schema-in-mysql-5-6/ Even still, it might not be possible for a transaction active that long depending on your server's activity and performance_schema settings.
So, you're really only left with the choice to kill the thread, which will rollback that transaction (again, assuming it's InnoDB tables being modified). Letting it run until it hits a timeout will result in the same rollback. And rollbacks might take a really long time, depending on what it's done!
I would scour the application's code-base to make sure you are closing/committing every transaction you are creating to prevent this in the future, as long-active transactions and an unruly history list length (undo space) can have a severe impact on the performance of your queries.
Best Answer
The process is undoing its changes after being killed (that is why killing write processes on a long running transaction can be dangerous- it can lead to a long undo process with blocking).
The rule of thumb is that InnoDB's UNDO can take multiple times the time it took to write. If it is unsustainable, you can stop the server when ther is no more write activity and restart it skipping pending UNDOs (so it doesn't start again). This is done with the start option:
Not ideal as a normal start option, as it could make you lose data. Generating a backup before the unclean restart (or in general) is always a good idea, even if technically safe assuming no other writes or undos are ongoing.