How does Zend\Db in ZF2 control transactions?

Question

The ZF1 Zend_Db reference manual has an entire section on performing transactions.

The ZF2 Zend\Db reference manual lacks any documentation on transactions.

How do I perform transactions in ZF2? Example code would be helpful.

Solution 1

The missing documentation is curious.

To find out what happened, I had to dive into the API docs for Zend\Db\Adapter.

It looks like beginTransaction, rollback and commit are defined in Zend\Db\Adapter\Driver\ConnectionInterface. This means that they are methods callable on every single adapter connection. Unfortunately the connection itself is rather buried.

What I'm not clear on -- and can't provide an example for at this time -- is figuring out which object you actually call these methods on. In the worst case, it looks like you might want to call $adapter->getDriver()->getConnection()->beginTransaction().

Eww.

I'm hoping someone else with more knowledge, and a copy of ZF2 handy, will see this and provide a better option.

Don't forget that you can just issue BEGIN TRANSACTION/ROLLBACK/COMMIT/SET autocommit=... SQL statements yourself. This is probably OK, as it doesn't look like Zend\Db keeps track of the transaction state.

Solution 2

You've got it. The proper way to Begin, Commit, and Rollback Transactions is as follows:

$this->getAdapter()->getDriver()->getConnection()->beginTransaction();

$this->getAdapter()->getDriver()->getConnection()->commit();

$this->getAdapter()->getDriver()->getConnection()->rollback();

Just to put this out there too you can also get the Last ID created by:

$this->getAdapter()->getDriver()->getConnection()->getLastGeneratedValue()

If you are using pgSQL you will need to add the sequence to return the Last ID created:

$this->getAdapter()->getDriver()->getConnection()->getLastGeneratedValue('mail_mailid_seq')

Solution 3

There are two matter for doing transaction.
1 - MyISAM is not a transactional engine , so change tables engine to InnoDB.
2 - Transaction query("START TRANSACTION;" OR "ROLLBACK;") connection must be same with other queries(Insert or Update).
For doing this in ZF2 you should get current db adapter and use it in all queries.

This code will not work correctly :

    $this->getAdapter()->getDriver()->getConnection()->beginTransaction();  
    //do some jobs - e.g : multiple tables update or insert.  
    $this->getAdapter()->getDriver()->getConnection()->rollback();   

Since $this->getAdapter()->getDriver()->getConnection() Creates new db connection.

Use following code instead:

    $connection = $this->getAdapter()->getDriver()->getConnection();
    $connection->beginTransaction();
    //do some jobs - e.g : multiple tables update or insert. 
    $connection->rollback();

For check if your connections is correct , just enable query log in mysql.
After running query you will see connection number before each query in mysql log.Those must be same in all transaction queries.

Comments

[Greg.Forbes]

Many thanks Charles - I will need to go and read carefully through the API, on your final point to issue these directly would I work through PDO to do this.

[Greg.Forbes]

Thanks @Diemuzi - this is really useful.

[evilReiko]

This great, well-explained, many thanks!! The documents of ZF2 and ZF3 are not clear about this part!

[Paiman Roointan]

is it true that getAdapter()->getDriver()->getConnection() creates a new db connection?