DB transactions skill

SKILL.md

name	description
database-transactions	Database transactions and pessimistic locking. Activates when working with DB::transaction, lockForUpdate, concurrent modifications, or read-modify-write patterns; or when the user mentions race conditions, locking, or data integrity.

Database Transactions & Pessimistic Locking

Always re-read data inside the transaction

The model passed to an action may already be stale — time passes between the initial load (e.g. in the controller) and the action execution. Never use the passed-in model directly for read-modify-write; always re-fetch it inside the transaction.

Why refresh() is not enough either

MySQL InnoDB uses REPEATABLE READ by default. A normal SELECT (including refresh()) reads from a snapshot established at the first SELECT of the transaction — it does NOT see other transactions' commits. SELECT ... FOR UPDATE (lockForUpdate()) reads the latest committed version AND acquires an exclusive row lock. Only lockForUpdate() guarantees fresh data + mutual exclusion.

Correct pattern for read-modify-write

When an action reads a model, computes a new value, then saves:

return DB::transaction(function () use ($model, ...) {
    // Re-fetch with exclusive lock — fresh data + mutual exclusion
    $model = Model::lockForUpdate()->findOrFail($model->id);

    // Validation MUST happen after the lock (stale data could pass validation)
    if ($someCondition) {
        throw ValidationException::withMessages([...]);
    }

    // Modify and save
    $model->amount = $model->amount->add($delta);
    $model->save();
});

Key rules:

• lockForUpdate()->findOrFail() must be the first operation inside the transaction
• Move all validation that depends on model state inside the transaction, after the lock
• Never rely on refresh() for concurrency safety inside a transaction