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

SELECT...FOR UPDATE in Phalcon

I am using phalcon to develop our backend supporting for a mobile application. Because of bugs in early phalcon version, we workaround by a solution is adding PURE query between Phalcon ORM code lines. Ex:

$transaction = $transactionManager->get();
$mysql = 'SELECT * FROM sale_invoice WHERE id = ? FOR UPDATE';
$query = self::getDb()->query($mysql, array($this->invoice_id));
$this->invoice = $query->fetch(\Phalcon\Db::FETCH_ASSOC);
if (!$this->invoice) 
    return false;
$this->order = SaleOrder::findFirst($this->invoice['order_id']);

It worked, but sometime we got some timeout request revelant to APIs added "SELECT...FOR UPDATE" by this way. Is there anybody has experience to work in this case?

FOR UPDATE generation is fixed in current Phalcon 2.0.5 in master

edited Jul '15

Does the transaction began after this code line? $transaction = $transactionManager->get();

or we need to start it manually $transaction->begin();

Because follow this article the locked rows just be unlocked in case the transaction is committed. So we have to begin the transaction before execute "SELECT FOR UPDATE" and then commit (or rollback) to unlock them

You have to start the transaction with $this->db->begin(); and commit/rollback then.

https://docs.phalcon.io/en/latest/reference/models.html#manual-transactions