I recently solved a thorn in my side relating to some Rspec tests in our code base when running on my development machine using MySQL. For some reason, some instances that were created using Factory Girl weren't getting cleaned up, which in turn would cause subsequent test runs to fail because of duplicate data. So, I'd DELETE the whole tables from the MySQL prompt. I looked in the test.log file, and I could see the save points being issued before the objects were created, but they weren't getting removed at the end of the test.
I didn't have a lot of time to look into it, and I didn't know where to look - Rspec, Factory Girl, Rails? So, in the short-term, I just added after_each calls to destroy the objects. And, I moved on.
Then, I was dumping schemas in MySQL using SHOW CREATE TABLE in order to analyze some tables and indexes, and I noticed the storage ENGINE flag on the tables. I went back and looked at the tables in my test database that were giving me trouble, and, of course(?), they were MyISAM rather than InnoDB. So, transaction rollback (used to clean up after tests) didn't work.
I changed the storage engine on those tables (ALTER TABLE t1 ENGINE = InnoDB), commented out the manual clean-up code, and voila! It works right now. Pretty obvious in retrospect, but I didn't even know where to start looking in our stack.
I hope this helps some other poor souls, too.