Active Record and Transactions

[eluser]Eric Cope[/eluser]
I am interested in using transactions along with active records, that happen to be in different models. The documentation only shows the use of the query function. Are CI transactions compatible with transactions? Has anyone used transactions and active records in MySQL?

[eluser]Cro_Crx[/eluser]
Yes they are compatible. I'm not sure why the documentation only shows an example with the query method, i did notice that when I was using them last.

If you want to use transactions across multiple models then run the transaction statements within the controller.

[eluser]Eric Cope[/eluser]
that's what i thought, thanks!

[eluser]Eric Cope[/eluser]
Quick question regarding transactions - If there is a PHP error halfway through the transaction and the script exits, will the transaction automatically roll back?

[eluser]Cro_Crx[/eluser]
I wouldn't rely on transactions to do their jobs if your PHP is running into errors. Transactions are used to fix race conditions and internal database errors and not PHP errors. Although I'm assuming that if your PHP errors then the transaction won't complete (assuming you haven't completed the transaction yet) the changes will roll back after a period of time or just not be applied at all.

If your controller has already completed the transaction and then runs into an error the changes should stick.

[eluser]Tpojka[/eluser]
This is useful. Just bump.

[eluser]jvicab[/eluser]
$this->db->trans_begin();

// execute first query here

// check the result:
if ($this->db->trans_status() === false) {
// error, so rollback changes and exit:
$this->db->trans_rollback();
return array('error' => $this->db->_error_message());
}
// repeat the above for each query inside the transaction

// if no error: commit the transaction:
$this->db->trans_commit();


One note: the type of the tables involved in the queries should support transactions, line InnoDB. MyISAM does not support transactions