Today, I spent about an hour debugging a part of the script that I was writing. This is what I faced:
I had a function writing some logs into a table. The function was pretty simple as all it had to do was insert some data into a row (using the data passed to it). The problem was that the data wasn’t getting inserted. I tried hard-coding the data into the sql statement(originally I was using Zend_Db_Pdo_Mysql::insert() method and it was returning 1 after an insert) and that wasn’t helping either.

I asked my colleague to have a look at the code to see if he could detect any issues. We were trying different things and then after a while he asked if there were any transactions involved. Since I was using the InnoDB engine for all my tables, this made me to start thinking. I had disabled autcommit for a series of transactions (that involved a SELECT … FOR UPATE statement – hoping to acheive row level locking). Since, I was using the same Db object within the function that had the above statement and in the function that was doing the logging afterwards, we realised there could be a problem there. On closer inspection I found that I had forgot to commit the transaction that involved the SELECT … FOR UPDATE statement and since the logging function was using the same Db object afterwards the logging became part of the transaction that never got committed!

But, it turned out that when I modified the sql statement in the logging function to something like ‘DROP TABLE audit’, that actually dropped the table even-though I didn’t explicitly commit (after disabling autocommit). I was intrigued by this behaviour and found that MySQL + InnnoDB implicitly commit after certain statements(http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html).

I also found out PostsgreSQL(and a few others) exhibit a different behaviour (and IMO better). You can pretty much ROLLBACK after all statemnts that do not involve dropping the db or a tablespace
(http://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis).

I guess the moral of the story is: “BEGINning without COMMITing to it can only be a waste of time” ;).