We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Exceptions causing transaction rollback

I have a big loop that does a bunch of SELECTs and INSERTs. Before the loop I call $this->db->begin(), and after the loop I call $this->db->commit().

Sometimes in the loop, an exception is thrown when I try to save a Model. I've got the offending code in a try...catch(), and I do successfully catch the exception. However, looking at the query log after the loop is done, I see a ROLLBACK is executed. This ROLLBACK is done at the very end of the loop - not when the exception is encountered.

Does \Phalcon\Mvc\Model automatically cause a rollback to be executed at the (more or less) end of the script? I don't have $this->db->rollback in my code anywhere in the loop, so I know my code isn't calling it.

I've read that PDO will rollback any open transactions at the end of script execution. However, the call to $this->db->commit() is being reached in my code, so there shouldn't be any open transactions.

I've brute forced it by calling $this->db->execute('COMMIT'). I see that commit in the query log, but no result of calling $this->db->commit(). And, I still see ROLLBACK in the query log.

edited Dec '19

I doesn't know direct answer to Your question, but can You look into log and see if is the ROLLBACK executed right after erroneous SQL, or later? If it's right after that, it might be indeed somewhere inside ORM, not in Your code.

The ROLLBACK is executed after the last query is run. I can copy-paste the last query and run it manually with no problems - so that last query isn't causing a problem.

It seems like the whole loop is inside a hidden transaction that doesn't get closed, and thus rolls back.

I doesn't know direct answer to Your question, but can You look into log and see if is the ROLLBACK executed right after erroneous SQL, or later? If it's right after that, it might be indeed somewhere inside ORM, not in Your code.

edited Dec '19

The ROLLBACK is executed after the last query is run. I can copy-paste the last query and run it manually with no problems - so that last query isn't causing a problem.

Maybe try to trace Your code execution using for example xdebug? It would show You what exactly is happening.

edited Dec '19

You are checking mysql or phalcon query log?