Transactions & Isolation Levels

Transactions & Isolation Levels Transactions group several statements into a single atomic unit of work: either every statement commits together, or the whole set is rolled back. prorm exposes transactions through a single prorm.transaction() method that supports both an automatic callback mode and a manual mode, along with isolation levels, savepoints, and per-query transaction routing. Callback mode (auto commit / rollback) Pass an async callback to prorm.transaction(). prorm opens a transaction, runs your callback, and commits automatically when the callback resolves. If the callback throws, the transaction is rolled back and the error is re-thrown to you. The callback's return value becomes the resolved value of prorm.transaction(), so you can compute and return data from inside the transaction: If anything inside the callback throws, nothing is persisted: How a nested transaction behaves A transaction inside another does not open a second transaction — it takes a savepoint. That is what lets an inner failure be caught and handled without discarding the outer work. The asymmetry is the point: an inner rollback loses only the inner work, but an outer rollback discards everything including the released savepoints. Passing the transaction to queries Every query you want to run inside the transaction must receive the transaction object through the transaction option. This applies to model methods (create, bulkCreate, findAll, findOne, update, destroy, increment, decrement, …) as well as raw queries. Queries that do not receive transaction: t run outside the transaction and will not be rolled back with it, so be deliberate about threading t through every call. Isolation levels The isolation level controls how much concurrent transactions can see of each other's uncommitted work. Set it with the isolationLevel option. prorm accepts both the IsolationLevel enum and the equivalent ANSI SQL string. The supported ANSI levels are: IsolationLevel enum | String literal | ------------------------------ | --------------------- | IsolationLevel.ReadUncommitted | 'READ UNCOMMITTED' | IsolationLevel.ReadCommitted | 'READ COMMITTED' | IsolationLevel.RepeatableRead | 'REPEATABLE READ' | IsolationLevel.Serializable | 'SERIALIZABLE' | When no level is specified, prorm's default transaction options use REPEATABLE READ. On PostgreSQL the level is emitted as part of the opening statement (BEGIN ISOLATION LEVEL ...). Some dialects accept native, non-ANSI levels (for example SQL Server's SNAPSHOT, or Db2's UR/CS/RS/RR abbreviations). Those are intentionally kept out of the IsolationLevel enum and can only be passed as plain strings to the dialects that support them. Other transaction options prorm.transaction() accepts the full TransactionOptions object: isolationLevel – ANSI isolation level (enum or string), as above. type – transaction start type used by SQLite: 'DEFERRED' (default), 'IMMEDIATE', or 'EXCLUSIVE'. autocommit – toggles the driver's autocommit behavior for the transaction. lock – applies a lock clause to queries in the transaction. Accepts true / 'UPDATE' (FOR UPDATE), 'SHARE' (FOR SHARE on PostgreSQL, LOCK IN SHARE MODE on MySQL), 'KEY SHARE' (PostgreSQL FOR KEY SHARE), or { of: Model } to lock only a specific table (PostgreSQL). Manual transactions Call prorm.transaction() without a callback to get a transaction object you drive yourself. You are then responsible for calling commit() or rollback(). Options may be passed as the first argument in this mode. Manual mode is useful when the transaction lifetime does not map cleanly onto a single function, but the callback mode should be preferred whenever possible because it guarantees the transaction is always finalized. Savepoints (nested transactions) Savepoints let you roll back part of a transaction without discarding the whole thing. Create one from an active transaction with createSavepoint(). The returned savepoint has its own commit() (which issues RELEASE SAVEPOINT) and rollback() (which issues ROLLBACK TO SAVEPOINT). You can also roll a transaction back to a savepoint directly by passing the savepoint to the transaction's rollback(): Error handling The two modes have different error-handling contracts: Callback mode finalizes for you. A thrown error triggers a rollback and is re-thrown, so a single try/catch around prorm.transaction() is enough. Never call commit()/rollback() yourself inside the callback. Manual mode does not finalize automatically. Always wrap the work in try/catch and call rollback() on failure, otherwise the transaction stays open and holds its connection. A rolled-back transaction leaves the database exactly as it was before the transaction began, so it is safe to retry the operation (for example on a serialization failure under SERIALIZABLE isolation). Summary prorm.transaction(async (t) => { ... }) runs work atomically, committing on success and rolling back on error. Thread the transaction into every query via the transaction option. Set concurrency behavior with isolationLevel (enum or ANSI string); the default is REPEATABLE READ. Use createSavepoint() for partial rollbacks, and t.rollback(savepoint) to unwind to a point without ending the transaction. Prefer callback mode for automatic cleanup; reach for manual mode only when the transaction cannot be scoped to one function. Next: Migrations — evolving a schema you cannot simply drop and recreate. Related reading Core concepts — the reading path these belong to Going further — the specialised material