Can I retrieve pending queries during an InnoDB transaction?

Question

I start a transaction.

Then I need to rollback it.

Can I somehow get a list of the queries that get "discarded" this way?

(ps: of course I can log them beforehand; I was wondering if this could be done in a more "natural" way)

Answer 1

If you're on a recent MySQL 5.1, this should work:

SHOW ENGINE INNODB STATUS includes a list of active transactions for the InnoDB engine. Each is prefixed with a transaction id and a process id, and looks somewhat like this:

---TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id
34831 rollback of SQL statement
MySQL thread id 18272
<query may be here>

The MySQL thread id will correspond to the CONNECTION_ID() of your session, that you can get from SHOW FULL PROCESSLIST or information_schema.processlist, so you can determine which transaction is yours. You'll have to parse the text, and parse the query out of it, if it's present.

If that's not enough, you can try something like SET @PROGRESS = @PROGRESS + 1 before each ROLLBACK statement, and then SELECT @PROGRESS from DUAL at the end of your query to find out how far the transaction went before it hit a rollback.

Answer 2

If you're using InnoDB, take a look at the InnoDB monitor and stderr. I think that the best practice is to store them in the application (server), since it won't be dependent on the platform.