What If Someone Changes the Job While Botie Is Reading It?

Two technicians read J1 at version 4. One assigns a replacement specialist and saves version 5. The other closes the earlier copy, unaware that the job changed. A blind save can overwrite work that happened after the read.

We will first use an expected version to reject a stale edit. Then we will examine what snapshots, write locks and isolation levels can guarantee in a real database.

Compare the proposal with the stored version before applying its replacement status.

{
  "id": "J1",
  "status": "done",
  "version": 5
}

Reading a row tells the application what was visible at that moment under its transaction rules. Another transaction may commit before the application writes. A user thinking about an edit can make that gap quite long.

Carry the observed version with the proposed change. The application should explain a conflict and show the current state when the version no longer matches. Automatically reapplying the old decision can be wrong even if the SQL is retried successfully.

The conflict concerns the meaning of the edit, not merely a temporary database error.

Match both the primary key and expected version in the same UPDATE predicate. If it matches, update the fields and increment the version. A stale proposal affects zero rows. The application must inspect that outcome instead of reporting a successful save.

This protects against a lost update for cooperating writers that follow the version protocol. It does not independently enforce a condition involving several different rows. Every write path that can change the relevant state must maintain the version.

The version is a concurrency token. It need not be a timestamp, and clock ordering is unnecessary for this comparison.

The supplied edit expects version 4. The first application of the statement can produce version 5. Replaying the same proposal must leave version 5 untouched because the expectation is still 4.

An application that wants another edit obtains the new record and creates a new deliberate proposal. It should not silently replace expected_version with whatever is current just to make the UPDATE succeed.

The exercise has one edit row. The key and version predicate generalise when the driver binds a different proposal.

CREATE TABLE jobs(id TEXT PRIMARY KEY NOT NULL,status TEXT NOT NULL,version INTEGER NOT NULL);
INSERT INTO jobs VALUES('J1','assigned',4),('J2','open',1);
CREATE TABLE edit(id TEXT,expected_version INTEGER,status TEXT); INSERT INTO edit VALUES('J1',4,'done');

UPDATE jobs SET status='review',version=5 WHERE id='J1';
UPDATE jobs SET status='done',version=version+1 WHERE id='J1' AND version=4;
SELECT id,status,version FROM jobs ORDER BY id;

SQLite permits only one writer at a time. Its journal mode affects reader/writer interaction: WAL readers can keep a snapshot while a writer commits. A read transaction may be unable to upgrade to a writer when its snapshot is no longer current.

BEGIN IMMEDIATE requests the write transaction earlier and can fail when another writer holds it. It does not make multiple SQLite writers execute their writes simultaneously. Set bounded busy handling and keep transactions short. The in-browser worksheet uses one isolated connection, so it cannot demonstrate real lock contention.

Sequentially replaying a stale edit is a controlled illustration of an application precondition, not a concurrency benchmark.

At PostgreSQL Read Committed, a statement reads a snapshot established for that statement, with documented rules for concurrent updates. Repeatable Read gives a transaction a stable snapshot but still permits some anomalies that Serializable prevents. Serializable execution may reject a transaction so that the application retries the complete unit.

SELECT ... FOR UPDATE can lock selected rows when pessimistic coordination is appropriate. It does not automatically lock every possible future row that could satisfy an arbitrary business rule. Choose the isolation and locking strategy from the invariant, and test the exact interleaving that could break it.

A generic claim that transactions prevent all races hides the isolation level that actually governs them.

Suppose two engineers must never both leave the on-call rota. Each reads a snapshot showing the other still on call, then updates only their own row to off call. Both individual row versions can match even though their combined result breaks the rota rule. This is a write-skew pattern: the writes affect different rows, but both decisions relied on a shared condition.

Protect the condition at the database boundary. Depending on the engine and schema, the solution can use a common row that competing decisions lock, a suitable constraint, or serializable transactions with retry handling. PostgreSQL Repeatable Read uses snapshot isolation and can permit serialization anomalies of this kind; Serializable can reject a conflicting execution instead. SQLite permits one writer at a time, but an application that reads outside its eventual write transaction can still act on stale decisions. The transaction boundary is part of the design.

Our versioned job edit solves a single-row stale overwrite. It does not claim to enforce every rule involving a set of jobs.

A serialization failure can require retrying the transaction from the beginning with fresh reads. Repeating only the final UPDATE preserves stale calculations. Keep retries bounded and make external effects replay-safe.

For an optimistic user-edit conflict, asking the user to reconcile may be preferable to automatic retry. Record the conflict and current state. Avoid a loop that continually overwrites newer work until one write finally succeeds.

The next lessons improve query cost; they should preserve these correctness requirements while changing execution plans.