read-merge-write Without FOR UPDATE Loses Keys

The short version

When several settings keys share one JSONB row and you write an upsert that reads the existing value, merges with a spread, and writes the whole thing back, that upsert overwrites whatever a concurrent transaction just wrote — unless the read takes a row lock with SELECT ... FOR UPDATE. Adding the row lock fixes it.

The state it could become

The school_configs table holding per-school display settings has one row with scope='school', kind='display_settings', and several settings keys share its value column (JSONB).

• assignmentDeadlineFormat (how a submission’s deadline is displayed: “daysLeft” or “until”)
• signageDesign (the school’s default signage design)
• editorDayCutover (the time at which the editor’s default target day switches)

school_configs.value is fully replaced, column and all, by an upsert. Even a save action that wants to change one key has to read the existing value first, spread it into an object, replace only the key it is changing, and write it back — otherwise the other two keys are deleted.

const prev = await lockDisplaySettingsValue(tx);
const base =
  prev && typeof prev === "object" && !Array.isArray(prev)
    ? (prev as Record<string, unknown>)
    : {};

const id = await upsertSchoolConfig(tx, {
  schoolId: actor.schoolId,
  kind: "display_settings",
  value: { ...base, assignmentDeadlineFormat: rawFormat },
  actorUserId,
});

This shape looks fine in itself. But if lockDisplaySettingsValue were a plain SELECT with no row lock, this concurrent ordering becomes possible.

1. Transaction A (saving assignmentDeadlineFormat) reads the existing value {signageDesign: "pattern2", editorDayCutover: "15:30"}
2. Almost simultaneously, transaction B (saving signageDesign, from raw JSON editing in /ops say) reads the same existing value
3. A UPSERTs {signageDesign: "pattern2", editorDayCutover: "15:30", assignmentDeadlineFormat: "until"} and commits
4. B UPSERTs {signageDesign: "pattern3", editorDayCutover: "15:30"}, based on the value it read before A’s commit, and commits

B’s write does not include the assignmentDeadlineFormat: "until" that A added. B’s UPSERT replaces the value column wholesale, so the setting A saved a moment earlier is gone from the table. Both saves succeeded correctly when viewed on their own, and no error appears anywhere.

Why

The read-merge-write pattern is hard to avoid as long as several keys share one row and partial updates are being emulated. The problem is that the row was not locked at the point of “read”.

A read without a row lock shows the same existing value to two transactions. Neither knows the other exists; each computes a correct delta and each executes a correct UPSERT. But whichever commits later overwrites the whole row with a value that does not include the earlier change. At the SQL level this is not a constraint violation or anything else — it is a last-writer-wins accident that goes through quietly, leaving no trace in the application log or the DB’s error log.

Fixing it

We changed the read of the existing value to a SELECT ... FOR UPDATE inside the same transaction.

async function lockDisplaySettingsValue(tx: TenantTx): Promise<unknown | null> {
  const [row] = await tx
    .select({ value: schoolConfigs.value })
    .from(schoolConfigs)
    .where(and(eq(schoolConfigs.scope, "school"), eq(schoolConfigs.kind, "display_settings")))
    .limit(1)
    .for("update");
  return row ? row.value : null;
}

With a row lock taken, a transaction that later tries to read the same row blocks until the earlier one commits (or rolls back) and releases the lock. At PostgreSQL’s default isolation level (READ COMMITTED), the read after the lock is released returns the latest committed value, so the later transaction always begins its merge from a state that includes the keys the earlier one wrote. The two saves are effectively serialised and neither key goes missing.

When the row does not yet exist (a first save that becomes an INSERT), there is nothing to lock. Concurrency on that path is not prevented by a row lock, but the target separately carries the ux_school_configs_target unique constraint and an upsert using onConflictDoUpdate, which guarantees uniqueness. The row lock takes effect from the second save onward, where a row exists and its value is read and merged.

Preventing a repeat

This pattern applies to any other table that, like display_settings, shares several settings keys in one row and does partial updates with read-merge-write. Making the read FOR UPDATE follows the same practice as the existing lockAndCountActiveSchoolAdmins; no new mechanism was introduced.

As long as the design of sharing several keys in one row is chosen, the read-merge-write path carries this lost-update risk. It is worth checking, every time you write a read-merge-write, whether that read takes a row lock.