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

How can I use phalcon model to implement optimistic locking?

For example, we have "wallet" table.

function cost() {
    $wallet = Wallet::findFirstById(123);
    if ($wallet->balance >= 10) {
        $wallet->balance -= 10;
    } else {
        return -1;
    }
    $wallet->save();
}

Let's assume wallet "123" has its balance for $10. If we have two parallal requests to invoke "cost()", we might get "-10" as balance result. To avoid it, we have to use "locking" magic. I saw the phalcon doc that there is "for update " option for "findFirst" function which will lock the row. But I doubt this is the pessimistic locking which will cost a lot in the long-time transaction.

so... How can we implement "optimistic" locking by using "version" in the phalcon model?

I am doing the search in "save()" function in the model, but it is unfortunately not supporting "conditional update"....

AFAIK there is no ready-to-use mechanic in Phalcon for this. Your only options are for_update and shared_lock...

Locking may delay a response, but it's still the most robust solution for concurrency. Otherwise, you'll have to implement your own logic for this.

I was missing the ON DUPLICATE KEY UPDATE mechanism, so I've overridden it in the base mysql adapter:

namespace MyProject;

use Phalcon\Db\Adapter\Pdo\Mysql as PhalconMysql;
use Phalcon\Db\Exception;

class Mysql extends PhalconMysql
{
    public function checkConnection() {
        try {
            $this->fetchOne('SELECT 1');
        } catch (\PDOException $e) {
            if (strpos($e->getMessage(), 'MySQL server has gone away') !== false) {
                $this->connect();
            }
        }
    }

    public function ping() {
        $this->checkConnection();
        return $this;
    }

    public function insert($table, array $values, $fields = null, $dataTypes = null) {
        if(count($values)<1) {
            throw new Exception("Unable to insert into " . $table . " without data");
        }
        $placeHolders = [];
        $insertValues = [];
        $bindDataTypes = [];

        foreach($values as $position => $value) {
            if(is_object($value)) {
                $placeHolders[] = (string)$value;
            }
            else {
                if(is_null($value)) {
                    $placeHolders[] = "NULL";
                }
                else {
                    $placeHolders[] = "?";
                    $insertValues[] = $value;
                    if(is_array($dataTypes)) {
                        if(!array_key_exists($position, $dataTypes)) {
                            throw new Exception("Incomplete number of bind types");
                        }
                        $bindDataTypes[] = $dataTypes[$position];
                    }
                }
            }
        }

        $escapedTable = $this->escapeIdentifier($table);

        $joinedValues = join(', ', $placeHolders);
        if(is_array($fields)) {
            $onDuplicate = "ON DUPLICATE KEY UPDATE";
            foreach($this->describeColumns($table) as $col) {
                if($col->isPrimary()) {
                    $f = $this->escapeIdentifier($col->getName());
                    $onDuplicate .= " " . $f . " = LAST_INSERT_ID(" . $f . "),";
                    break;
                }
            }
            $escapedFields = [];
            foreach($fields as $field) {
                $f = $this->escapeIdentifier($field);
                $escapedFields[] = $f;
                $onDuplicate.= " ".$f." = VALUES(".$f."),";
            }
            $onDuplicate = substr($onDuplicate,0,-1);
            $insertSql = "INSERT INTO ".$escapedTable." (".join(", ",$escapedFields).") VALUES (".$joinedValues.") ".$onDuplicate;
        }
        else {
            $onDuplicate = "ON DUPLICATE KEY UPDATE";
            foreach($this->describeColumns($table) as $col) {
                $f = $this->escapeIdentifier($col->getName());
                if($col->isPrimary()) {
                    $onDuplicate .= " " . $f . " = LAST_INSERT_ID(" . $f . "),";
                }
                else {
                    $onDuplicate .= " " . $f . " = VALUES(" . $f . "),";
                }
            }
            $onDuplicate = substr($onDuplicate,0,-1);
            $insertSql = "INSERT INTO ".$escapedTable." VALUES(".$joinedValues.") ".$onDuplicate;
        }
        if(count($bindDataTypes)<1) {
            return $this->execute($insertSql, $insertValues);
        }
        return $this->execute($insertSql, $insertValues, $bindDataTypes);
    }
}