Transactions & Concurrency
saveChanges() is always atomic on its own. When you need several saves (or raw SQL) in one unit, open an explicit transaction. When two writers may touch the same row, add a concurrency token so the loser finds out instead of silently overwriting. Both are modelled directly on Entity Framework Core.
saveChanges() atomicity#
Every saveChanges()wraps its change set in one transaction — inserts, updates and deletes commit or roll back together. It also holds the engine’s async mutex for its duration, so two units of work on the same pooled connection take turns instead of interleaving statements into one another’s BEGIN … COMMIT.
| Engine | saveChanges() | Migrations |
|---|---|---|
| PostgreSQL | transactional | one transaction per migration (DDL + tracking row) |
| SQLite | transactional | transactional; FK enforcement toggled around it (EF’s SQLite approach) |
| MySQL | transactional (DML) | DDL implicitly commits — a migration is not atomic (EF documents the same) |
Explicit transactions#
db.transaction(fn) is the recommended form; beginTransaction() / commit() / rollback() give manual control:
// Recommended form (EF Database.BeginTransaction + using): begin → run → commit; rollback + rethrow on error
await db.transaction(async (tx) => { // tx is the same context
tx.Account.add(account); await tx.saveChanges();
await tx.execute('UPDATE Ledger SET balance = balance - ? WHERE id = ?', [amount, ledgerId]);
tx.Audit.add(entry); await tx.saveChanges();
});
// Manual control
await db.beginTransaction();
try {
db.User.add(u); await db.saveChanges();
db.Audit.add(a); await db.saveChanges();
await db.commit(); // alias: commitTransaction()
} catch (e) {
await db.rollback(); // alias: rollbackTransaction()
throw e;
}
db.inTransaction; // true while open- Inside a user transaction each
saveChanges()is protected by a savepoint and does not commit by itself; a failed save leaves the outer transaction usable (EF semantics). executeUpdate()/executeDelete()anddb.query()/db.execute()join the open transaction.- The transaction holds the engine lock until commit/rollback, so another unit of work on the same connection waits.
- Nested
beginTransaction()is rejected — use savepoints.
Savepoints#
await db.transaction(async (tx) => {
await tx.createSavepoint('beforeBulk');
try {
await tx.Session.where((s) => s.expires_at < $$, Date.now()).executeDelete(); // joins the transaction
await tx.releaseSavepoint('beforeBulk');
} catch (e) {
await tx.rollbackToSavepoint('beforeBulk'); // transaction stays open and usable
}
tx.Audit.add(entry); await tx.saveChanges();
});Optimistic concurrency#
Without a token, two writers that load the same row and save different changes silently overwrite each other — last write wins. Mark a column and MasterRecord adds its original value to every UPDATE/DELETE WHERE, exactly like EF’s concurrency tokens:
export default class Doc {
id(db) { db.integer().primary().auto(); }
title(db) { db.string(); }
version(db) { db.rowVersion(); } // ORM-managed; you never set it
}
export default class Tagged {
id(db) { db.integer().primary().auto(); }
etag(db) { db.string().concurrencyToken(); } // app-managed; rotate it when you change the row
}| Modifier | EF Core | Behaviour |
|---|---|---|
.rowVersion() | IsRowVersion() / [Timestamp] | Integer token bumped atomically in the same statement (SET v = v + 1 … WHERE v = <original>), mirrored onto the entity and re-snapshotted after save. Portable across all three engines. |
.concurrencyToken() | IsConcurrencyToken() | Any column whose as-loaded value is added to the WHERE; you rotate it (GUID / etag) when you change the row. |
Rows-affected is always checked on UPDATE and DELETE, token or not: a row that was concurrently modified (token mismatch) or deleted affects 0 rows, so saveChanges() rolls the whole batch back and throws ConcurrencyError (EF’s DbUpdateConcurrencyException).
import { ConcurrencyError } from 'masterrecord/errors'; // also masterrecord.ConcurrencyError
const mine = await db.Doc.find(1);
const theirs = await otherDb.Doc.find(1);
theirs.title = 'B'; await otherDb.saveChanges(); // version 0 → 1
mine.title = 'A';
try {
await db.saveChanges(); // UPDATE … WHERE id = 1 AND version = 0 → 0 rows
} catch (err) {
err instanceof ConcurrencyError; // true — EF DbUpdateConcurrencyException
err.code; // 'MR_CONCURRENCY_CONFLICT'
err.entries; // [mine] — conflicting entities, still tracked and dirty
}Resolving a conflict#
Conflicting entities stay tracked and dirty, and every entity carries its original values (captured at load / after save), so the EF resolution strategies all work:
// Database wins: reload then re-apply what you still want
for (let attempt = 0; attempt < 3; attempt++) {
try { await db.saveChanges(); break; }
catch (e) {
if (!(e instanceof ConcurrencyError)) throw e;
for (const entity of e.entries) {
await entity.reload(); // fresh values + fresh originals (EF Reload())
entity.title = myNewTitle; // re-apply
}
}
}
// Client wins: reload (to refresh the token), then set every value you hold and save
// Merge per field: compare entity.__originalValues with the reloaded row (EF OriginalValues / GetDatabaseValues)
const live = await db.entry(mine).getDatabaseValues(); // current DB row without touching the entityRetry on transient failures#
EF’s EnableRetryOnFailure(): operations that fail with a transient error — SQLite SQLITE_BUSY/LOCKED, MySQL deadlock / lock-wait timeout / too many connections, Postgres 40001/40P01/57P0x/08xxx, and network ECONNRESET/ETIMEDOUT/PROTOCOL_CONNECTION_LOST on any engine — are retried with capped exponential backoff + jitter. Constraint and syntax errors and ConcurrencyError are never retried. Off by default, like EF.
import masterrecord from 'masterrecord';
db.setRetryOnFailure({ maxRetries: 3, maxDelayMs: 2000, baseDelayMs: 50 }); // per context; false disables
masterrecord.configureRetry({ maxRetries: 3 }); // process-wide default
db.on('retry', ({ attempt, maxRetries, delayMs, error }) => {
console.warn(`transient DB error, retry ${attempt}/${maxRetries} in ${delayMs}ms`, error.code);
});
masterrecord.isTransientError(err, 'postgres'); // the classifier, if you need ittoList/single/count…), saveChanges() (the whole unit of work re-runs; a failed attempt rolled back and left the entities dirty) and executeUpdate/executeDelete. Not inside an explicit transaction — the transaction is the retry unit; re-run it yourself (EF semantics).Save events & interceptors#
EF’s SavingChanges / SavedChanges / SaveChangesFailed events and command interceptors — the hooks for audit columns and soft delete in one place:
db.on('savingChanges', ({ entries }) => { // before the flush — may mutate entities
for (const { entity, state } of entries) {
if (state === 'modified') entity.updated_at = new Date().toISOString();
if (state === 'delete') { entity.__state = 'modified'; entity.deleted_at = Date.now(); } // delete → soft delete
}
});
db.on('savedChanges', ({ entries }) => { /* after commit */ });
db.on('saveChangesFailed', ({ error }) => { /* before the error is rethrown */ });
db.on('command', ({ sql, params, durationMs, engine, error }) => { /* every SQL statement */ });on() returns an unsubscribe function; once() fires one time. Edits made in savingChanges ship in the same save because the change set is re-collected afterwards.