Transaction in MySQL and Zend Framework

It has been a week I have not posted a new entry at my blog. This week has been very busy with managing the transition of my job, plus getting a new project for working as a consultant for a startup. As I don’t have much time to write tonight, I will write about a problem that is simple but too tricky when you don’t know.

I started using Zend Framework for one of my personal projects. Zend Framework is a collection of loosley tied PHP libraries developed by Zend Inc. You can use Zend Framework for implementing MVC(model-view-controller) architecture in developing your web applications. Anyhow, at one point in my project I need to perform several updates with the MySQL database from my application. For example, in a shopping cart application when the user checks out and makes payment, your application might need to do setting up account, generating invoice, and sending notification email etc. If one of these operations fail, you want to restart over or simply the policy is all or nothing.

This kind of case in database updates is called transaction. Theoritically, such a collection of succesive database updates is called as “Transaction”. A transaction maintains four properties, known as ACID for its acroynm. They are as follow.

  1. Atomicity – basically all the updates or tasks of a transaction are performed or nothing at all
  2. Consitency – the consitency of the database must be maintained before and after the transaction, such as abiding to database schema constraints.
  3. Isolation – even if there are multiple concurrent transactions, each transaction must be processed as if only one of them is running at a  time.
  4. Durability – the data updates made by the transaction must be persistent in the database system.

Basically, in our common case we want to get “all or nothing” constraint in our database transaction. Usually, in MYSQL the SQL syntax begining a transaction: BEGIN, COMMIT for committing the updates, ROLLBACK for undoing all the updates in the transaction.  In Zend Framework, Zend Db interface has its own class methods for beginning, commiting and rollback transaction. Bassically, as follow.

$db->beginTransaction(); //begin a transaction

try{

$db->query(“….”); //query1

$db->query(”…”); // query 2

$db->commit();

}

catch(Exception $e)

{

$db->rollback();

}

In my case, I used such kind of code above and can’t get it working properly for a while. I searched for solution and suggestion from other people on the Internet but I can hardly find one.

Finally, I just learned that in MySQL, transaction works only on database tables that are built using InnoDB storage engine. By default, MySQL uses MyISAM engine to create new tables.  The solution is to create whatever tables you want to use in a transaction to be built using InnoDB engine. If you already created tables with MyISAM, you can still change to InnoDB by changing the database schema.

Although the solution is simple, I hope this helps people who are in the same shoe for a while.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s