Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Cannot rollback transaction in Zend Framework

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();
            }
like image 681
Billy Avatar asked Jul 05 '09 11:07

Billy


3 Answers

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.)

like image 129
Bill Karwin Avatar answered Nov 16 '22 04:11

Bill Karwin


For future use, to know if it's really a DB Exception, use Zend_Db_Exception instead.

} catch (Zend_Db_Exception $e) {
    $db->rollBack();
    echo $e->getMessage();
} catch (Exception $e) {
    echo $e->getMessage();
}
like image 35
Alex Avatar answered Nov 16 '22 03:11

Alex


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.

like image 34
Jonathan Ingram Avatar answered Nov 16 '22 04:11

Jonathan Ingram