Cannot rollback transaction in Zend Framework

Question

I use the following code for transaction in Zend Framework but the rollback function doesn't work (Data is inserted into the database by insertSome($data)). What's wrong?

   $db->beginTransaction();
   try{
      $model->insertSome($data);
      $model->insertAll($data2); //this line cannot be run and the whole transaction should be rolled back.
      $db->commit();
   } catch (Exception $e) {
    $db->rollBack();
    echo $e->getMessage();
   }

Answer 1

We can't get this question out of the list of "unanswered" questions on StackOverflow unless there is at least one answer with an upvote. So I'm repeating the solution you discussed above in the comments.

@nos suggests:

Is your DB by any chance MySQL using MyISAM tables ? They don't support transactions. You'd have to use InnoDB tables if you want transaction support.

@Billy responds:

Yes, I am using MyISAM tables. I have changed to InnoDB tables and it works. Thanks.

(I've marked this as a community wiki answer so I don't get any points from it.)

Answer 2

If my table was InnoDB, (seen from SHOW CREATE TABLE xxx) and my transaction was not rolling back, what would you suggest?

CREATE TABLE `EarningCode` (
 `ID` int(11) NOT NULL auto_increment,
 `EarningCode` varchar(16) collate utf8_unicode_ci NOT NULL,
 `Description` varchar(255) collate utf8_unicode_ci NOT NULL,
 `DateEffective` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
 `Rate` float NOT NULL,
 PRIMARY KEY  (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1239 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

It's part of Unit Tests: I have a set up method that starts a transation:

protected function setUp()
{
 global $db;

 $db->beginTransaction();

 // Insert this tested object into db.
}

and a tear down method that should ensure that the row is not inserted into the db (each time a test is run in this test class, it executes the setUp/tearDown pair and so I don't want duplicates that fill up my db table).

protected function tearDown()
{
 global $db;

 $db->rollBack();
}

I have checked what SQL is executed, and I can see that autocommit is set to false when the transaction is started, and switched to true after rolling back, but the row remains inserted.

Answer 3

Your code is ok.

Check your table option. You need use Transacctional engine with InnoDb