Locking

Two writers reading the same row, both modifying it, and both saving means one of the two changes is silently lost. Propulsion offers both standard answers, plus a database-arbitrated mutex for work that isn’t about a row at all (named locks):

• Pessimistic locking — take a lock when you read, so the second reader waits (or fails, or skips). Costs concurrency; guarantees correctness inside the transaction.
• Optimistic locking — don’t lock, but detect on write that the row changed since you read it, and fail loudly. Costs nothing until there’s an actual conflict.

A third option avoids the problem entirely for simple cases: let the database compute the new value from the old one in a single statement. See database-computed UPDATE values — for a counter increment that’s usually the right answer, and no locking is needed.

Pessimistic locking

setLockForUpdate() adds a write lock to a query; setLockForShare() adds a read lock. Both must be inside a transaction — the lock is held until the transaction ends.

$con = Propulsion::getConnection(BookPeer::DATABASE_NAME, Propulsion::CONNECTION_WRITE);
$con->beginTransaction();
try {
    $book = BookQuery::create()
        ->filterById(123)
        ->setLockForUpdate()
        ->findOne($con);

    // No other transaction can modify this row until we commit or roll back.
    $book->setViewCount($book->getViewCount() + 1);
    $book->save($con);

    $con->commit();
} catch (\Exception $e) {
    $con->rollBack();
    throw $e;
}

FOR UPDATE blocks concurrent writers and concurrent FOR SHARE/FOR UPDATE readers. FOR SHARE blocks concurrent writers but lets other FOR SHARE readers through — use it when you need the row to stay stable for the length of your transaction but aren’t going to modify it.

clearLock() removes a previously-set lock, e.g. when reusing a query object.

NOWAIT and SKIP LOCKED

By default a locking query blocks until the conflicting lock is released. Both parameters change that:

// Fail immediately instead of waiting.
BookQuery::create()->filterById(123)->setLockForUpdate(skipLocked: false, noWait: true);

// Skip rows another transaction already has locked.
BookQuery::create()->filterByStatus('pending')->limit(10)->setLockForUpdate(skipLocked: true);

The two are mutually exclusive — passing both throws a PropulsionException.

SKIP LOCKED is what makes a relational table usable as a work queue: each worker claims the next batch of unlocked rows and never contends with, or waits for, the others.

// A job-queue worker claiming its next batch.
$con->beginTransaction();
$jobs = JobQuery::create()
    ->filterByStatus('pending')
    ->orderByCreatedAt()
    ->limit(10)
    ->setLockForUpdate(skipLocked: true)
    ->find($con);

foreach ($jobs as $job) {
    $job->setStatus('running');
    $job->save($con);
}
$con->commit();

Platform support

Platform	FOR UPDATE	FOR SHARE	NOWAIT	SKIP LOCKED
PostgreSQL	✅	✅	✅	✅
MySQL / MariaDB	✅	✅	✅	✅
Oracle	✅	throws	✅	✅
MSSQL	table hints	table hints	throws	READPAST
SQLite	throws	throws	throws	throws

Every “throws” above is a PropulsionException with a message naming the unsupported capability — Propulsion never silently drops a lock you asked for.

The reasons, all of them properties of the database rather than of Propulsion:

• Oracle has no FOR SHARE equivalent. Only row-exclusive FOR UPDATE exists.
• MSSQL has no trailing lock clause at all. Locking is expressed as table hints spliced into the FROM/JOIN clauses — WITH (UPDLOCK, ROWLOCK) for a write lock, WITH (HOLDLOCK, ROWLOCK) for a read lock, plus READPAST for SKIP LOCKED. The effect is equivalent; the generated SQL looks structurally different. MSSQL has no per-query NOWAIT equivalent, since SET LOCK_TIMEOUT is session-scoped.
• SQLite has no row-level locking. It locks the whole database file at the connection/transaction level.

See Supported databases for more detail.

Optimistic locking

The optimistic_lock behavior adds a version column to a table. Every UPDATE is guarded by the version the object was loaded with, and bumps it:

<table name="book">
  <column name="id" required="true" primaryKey="true" autoIncrement="true" type="integer" />
  <column name="title" type="varchar" required="true" />
  <behavior name="optimistic_lock" />
</table>

use Propulsion\Exception\ConcurrencyException;

$book = BookQuery::create()->findPk(1);   // loaded at version 3
$book->setTitle('Anna Karenina');

try {
    $book->save();                        // UPDATE ... WHERE id = 1 AND version = 3
} catch (ConcurrencyException $e) {
    // Someone else saved this row since we loaded it. Re-read and retry,
    // or surface the conflict to the user.
    $stale = $e->getEntity();
}

A stale writer’s UPDATE matches zero rows instead of overwriting a change it never saw, and save() turns that into a ConcurrencyException. No locks are held, and readers never block — the cost is that the losing writer has to handle the conflict.

See the behavior page for configuration and the full mechanism.

Named (advisory) locks

A third kind, and not a variant of the two above: a mutex the database stores and arbitrates but attaches to nothing — no row, no table. It is what “only one of these should run at a time” needs when there is no row to take a FOR UPDATE on, or when the work is not a database write at all: a job-queue dispatcher, a cron entry deployed to three app servers, a migration guard.

use Propulsion\Exception\AdvisoryLockTimeoutException;

try {
    Propulsion::withAdvisoryLock('nightly-invoice-run', function ($con) {
        // At most one process in the cluster is in here.
    }, timeout: 0.0);
} catch (AdvisoryLockTimeoutException) {
    // Another run already holds it. Usually: log and exit.
}

timeout is in seconds: null (the default) waits indefinitely, 0.0 gives up at once, and a positive value waits at most that long. Failing to acquire throws AdvisoryLockTimeoutException rather than a generic error, because “somebody else has it” is an ordinary answer to asking for a mutex — a caller usually wants to log and skip on it, not treat it as a database fault.

Propulsion::acquireAdvisoryLock() / releaseAdvisoryLock() exist for a lock whose lifetime doesn’t fit a closure. Prefer withAdvisoryLock() where it does: its finally is the difference between a request that throws releasing the lock and the lock being held until the connection is reaped.

What to know before relying on it

• The lock lives on the connection, not the transaction. Every platform’s primitive is used in its session-scoped form, so a COMMIT inside the closure does not drop it. It is released by the finally, or by the connection closing — which is the main reason to prefer this over a lock table: the database cleans up after a crashed holder, and a row does not.
• The write connection is always used, never a replica, since acquire and release have to land on the same session. The closure receives that connection; use it for work that must be covered by the lock.
• Not re-entrant. Nesting the same name on one connection behaves differently on every platform, and the inner release would free a lock the outer scope still believes it holds. Don’t nest a name.
• Names are passed through, not hashed, except on PostgreSQL, whose primitive is numbered rather than named (a 63-bit SHA-256 prefix is used). MySQL rejects names over 64 characters and Oracle over 128, and that surfaces as the server’s own error rather than as a silent truncation two callers could collide on.
• Sub-second timeouts round up on the platforms whose primitive takes whole seconds (MySQL, Oracle), since rounding down would turn “wait briefly” into “don’t wait”.
• timeout: null is portable. MySQL 8.0.1+ reads a negative GET_LOCK timeout as an infinite wait while MariaDB rejects one, so Propulsion uses a very large finite wait — the one spelling both accept — with no version sniffing involved.

Platform	Primitive	Notes
PostgreSQL	pg_advisory_lock	Session-scoped, not the _xact_ variant. A finite timeout sets lock_timeout around the call and restores the session’s previous value.
MySQL / MariaDB	GET_LOCK	Takes a timeout directly, in whole seconds.
MSSQL	sp_getapplock	@LockOwner = 'Session', not T-SQL’s 'Transaction' default.
Oracle	DBMS_LOCK.REQUEST	release_on_commit => FALSE. Needs GRANT EXECUTE ON DBMS_LOCK, which is not granted by default; without it acquisition fails with Oracle’s own error rather than being reported as “busy”.
SQLite	throws	No named-lock primitive exists. It throws rather than running your closure unserialised — which is the exact outcome you were trying to prevent.

Propulsion::supportsAdvisoryLocks() is there to branch on when a single-instance deployment can live without it.

Choosing between them

Pessimistic locking is the right default when a conflict is likely and the work between read and write is short: you’d rather wait 5ms than write conflict-resolution code. It’s also the only option that works when the “check” isn’t a row you’re about to update (reserving stock, claiming a queue item).

Optimistic locking is the right default when conflicts are rare, the work between read and write is long (a user editing a form for two minutes), or the read and write happen in different requests — a pessimistic lock can’t span requests, because it can’t span a transaction.

They compose: nothing stops a table from having an optimistic_lock version column and being read FOR UPDATE where that’s warranted.

Related

• Transactions — the transaction API locks depend on, and the savepoint differences between platforms.
• Unit of Work — batching writes; a ConcurrencyException from an optimistic-locked row rolls back the whole batch.
• Bulk writes, upserts, and RETURNING — the lock-free alternatives for counters and insert-or-update.