8

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)

o0'.
  • 11,739
  • 19
  • 60
  • 87
  • I think the natural way is to log them beforehand. If you don't know what the transaction is doing, how can you know that you want to roll it back? – Michael Mior Oct 02 '10 at 02:04

2 Answers2

2

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.

Ryan M
  • 688
  • 6
  • 12
  • unluckily `ERROR 1227 (42000): Access denied; you need the SUPER privilege for this operation` :( – o0'. Jul 20 '10 at 10:07
  • furthermore, there's no query anyway :/ – o0'. Jul 20 '10 at 10:14
  • If your query is fast, it might slip by too quickly for this method to work. You might try the InnoDB monitor as suggested by Marcus, but if your query is fast, you might need to set the monitor interval to be very small! – Ryan M Jul 20 '10 at 12:45
  • Useful to also find pending, uncommitted transactions. – ashes999 Jan 08 '11 at 19:27
1

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.

Marcus Adams
  • 53,009
  • 9
  • 91
  • 143