Yii 2 ActiveRecord Optimistic Locking: Configuration and Limits

The Problem: Lost Updates in Concurrent Requests

When two users load the same ActiveRecord row, modify different fields, and save, the second save silently overwrites the first. Yii 2 solves this with built‑in optimistic locking: a version column that must match the value read before the update proceeds.

How It Works

Implement optimisticLock() in your model to return the name of a version attribute (usually an integer column named version). On every save(), update(), or delete(), Yii increments that column and adds WHERE version = :oldValue to the generated SQL. If no row matches, a yii\db\StaleObjectException is thrown.

Worked Configuration Example

1. Migration – Add Version Column

// Run: php yii migrate/create add_version_to_post
public function up()
{
    $this->addColumn('{{%post}}', 'version', $this->integer()->notNull()->defaultValue(1));
}
public function down()
{
    $this->dropColumn('{{%post}}', 'version');
}

Apply with php yii migrate (requires console access and DB write permissions).

2. Model – Enable Optimistic Lock

namespace app\models;
use yii\db\ActiveRecord;
class Post extends ActiveRecord
{
    public function optimisticLock()
    {
        return 'version';
    }
}

No extra configuration needed. The column must exist and be numeric; Yii will manage its value automatically.

3. Handling Conflicts

use yii\db\StaleObjectException;
try {
    $post = Post::findOne($id);
    $post->title = 'New title';
    $post->save();
} catch (StaleObjectException $e) {
    // Reload and retry, or show conflict UI
    $post->refresh();
    // reapply changes and save again
}

Catch StaleObjectException where you call save(). Typical response: reload the record, reapply user changes, and retry.

Verification Steps

1. Inspect generated SQL – Enable Yii debug toolbar or set 'enableLogging' => true in DB config. You should see UPDATE `post` SET `title`='...', `version`=2 WHERE `id`=1 AND `version`=1.
2. Concurrency test – Write a console script or PHPUnit test that loads the same row in two processes, saves the first, then attempts the second save and expects StaleObjectException.
3. Schema check – Run php yii migrate/history or inspect the table with DESCRIBE post to confirm version INT NOT NULL DEFAULT 1.

Limits and Common Mistakes

• Only ActiveRecord paths – Raw SQL, query builder update(), or direct execute() bypass the version check.
• Single‑row guard – Optimistic locking does not replace transactions for multi‑statement consistency.
• Initialization required – New rows must start with version = 1 (handled by migration default). Manually setting the column elsewhere causes spurious exceptions.
• Triggers/default expressions – Avoid using the version column in DB triggers or default expressions outside Yii's control.

Practical Checklist

• Add integer version column with default 1 via migration.
• Implement optimisticLock() returning 'version'.
• Wrap save()/update()/delete() in try‑catch for StaleObjectException.
• Test with two concurrent requests; verify exception and SQL includes version in WHERE.
• Do not update version manually or via raw SQL.