Using Transactions with Zend\Db in Zend Framework 2

Published on June 2, 2013 by

There are many situations where database transactions are necessary and useful. If you have to execute two insert queries, then what if the second one fails? Data could potentially be left incomplete, thus affecting data integrity. Zend Framework 2 provides the Zend\Db component which does indeed support transactions, although the documentation seems to be missing as of now.

The methods we need are defined in Zend\Db\Adapter\Driver\ConnectionInterface; beginTransaction, commit, and rollback. Therefore all we need is to access the connection when using transactions. The purpose of the ConnectionInterface is to provide a unified contract across different database adapters, and thus we can access the connection while remaining independent of the concrete database vendor.

Below is an example of how transactions are typically used.

public function saveUser(\User\Model\User $user)
{
	$connection = null;
	
	try {
		$connection = $this->dbAdapter->getDriver()->getConnection();
		$connection->beginTransaction();
		
		/* Execute one or more queries here */
		
		$connection->commit();
	} catch (\Exception $e) {
		if ($connection instanceof \Zend\Db\Adapter\Driver\ConnectionInterface) {
			$connection->rollback();
		}
		
		/* Other error handling */
	}
}

If a database query generates an error, an exception will be thrown, at which point the transaction will be rolled back. This ensures that the database will be in the same state as it was before the queries within the transaction were executed (but not committed). This satisfies the atomicity property of the ACID properties. What this property states is basically that a transaction should be all or nothing such that if a part of the transaction fails, then everything will be rolled back and the database left unchanged.

On a side note, one can also get the last generated ID on the connection. In this way, one can get the most recently auto generated ID for tables where auto increment is enabled.

$this->dbAdapter->getDriver()->getConnection()->getLastGeneratedValue();

Featured

Learn Zend Framework today!

Take an online course and become a ZF2 ninja!

Here is what you will learn:

  • Understand the theory of Zend Framework in details
  • How to implement an enterprise-ready architecture
  • Develop professional applications in Zend Framework
  • Proficiently work with databases in Zend Framework
  • ... and much more!
Zend Framework logo
Author avatar
Bo Andersen

About the Author

I am a back-end web developer with a passion for open source technologies. I have been a PHP developer for many years, and also have experience with Java and Spring Framework. I currently work full time as a lead developer. Apart from that, I also spend time on making online courses, so be sure to check those out!

6 comments on »Using Transactions with Zend\Db in Zend Framework 2«

  1. Nisal

    How this can be used to update two tables in the same transaction.

    • Hello Nisal,

      Thank you for your comment.

      All you have to do is to replace the comment in the above code with your code that executes your update queries. Depending on the use case, you could test the number of affected rows and if no rows are affected, you can issue a rollback for the transaction.

      Best regards,
      Bo Andersen

      • Nisal

        I tried this with TableGateway pattern. But got errors. After Googleing found I have to use data mapper pattern. Have you test this with TableGateway pattern?

        • Jay Yap

          Hello Nisal!
          I have tested this with the TableGateway pattern.
          In general, you can retrieve the connection using
          $connection = $this->yourTableGatewayObject->getAdapter()->getDriver()->getConnection();

          Once you have the connection object, you simply
          1) $connection->beginTransaction();
          2) perform whatever database inserts/updates you need to do
          3) depending on the results of 2, you may either
          a) $connection->commit();
          or b) $connection->rollback();

          Hope this helps!

  2. Mehmet Aydemir

    Hi,
    I have two tables (master-detail) and i had start transaction and insert to master table and i got lastinsertedid. But when i trying to insert detail table for lastinsertid of master table i got error (violation key error). how can i fix this.

  3. Alok Rai

    Sir, You explain the thing in right way, this code can be easily implemented, I am having problem in model creation because in user’ guide its very difficult for me to understand, i tried to find on google but the result was same for me . Can you Give a little advice on how to manage (create) models in simple way and use them.

Leave a Reply

Your e-mail address will not be published.