Using Transactions with Zend\Db in Zend Framework 2

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();

6 Comments

  1. Nisal said:

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

    August 11, 2013
    Reply
    • Bo Andersen said:

      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

      August 17, 2013
      Reply
      • Nisal said:

        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?

        September 1, 2013
        Reply
        • Jay Yap said:

          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!

          March 24, 2014
          Reply
  2. Mehmet Aydemir said:

    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.

    September 26, 2014
    Reply
  3. Alok Rai said:

    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.

    September 23, 2016
    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *