Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How does Zend\Db in ZF2 control transactions?

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.

like image 905
Greg.Forbes Avatar asked Dec 12 '12 01:12

Greg.Forbes


3 Answers

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')
like image 146
Diemuzi Avatar answered Oct 14 '22 20:10

Diemuzi


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.

like image 30
Charles Avatar answered Oct 14 '22 22:10

Charles


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.

like image 8
M Rostami Avatar answered Oct 14 '22 21:10

M Rostami