A database transaction groups reads and writes into one commit or rollback boundary. Design that boundary around an invariant that must remain true, not around an arbitrary service method. Keep transactions short, use constraints as final protection, select isolation and locking deliberately, and move irreversible external effects into a coordinated after-commit workflow.
Who this is for: Backend developers implementing multi-step writes that must preserve money, inventory, membership, or other business invariants.
- State the invariant first, then include every database change needed to preserve it in one boundary.
- Use constraints, conditional writes, or locks to protect against concurrent transactions rather than trusting an earlier read.
- Coordinate email, queues, and remote APIs without holding a database transaction open across unreliable network calls.
Begin with the invariant
An invariant is a rule that committed data must satisfy, such as an account balance not exceeding an approved limit or one active membership per person and workspace. Identify which rows and tables participate, which commands can change them, and what a reader may observe. That statement defines the useful atomic unit.
Do not assume a sequence of successful statements is equivalent to a transaction. If inventory is decremented and an order insert later fails, the system has lost stock without an order. Put both changes in one transaction when they share the invariant, and let an error roll the whole unit back.
Concurrency changes what reads mean
Two transactions can read the same available value and both decide a write is allowed. Isolation levels govern which concurrent effects become visible, but names and exact guarantees vary by database. Learn the behavior of the chosen engine rather than treating serializable, repeatable read, or snapshot as interchangeable labels.
Constraints are durable protection for rules the database can express. Unique keys prevent duplicate active records, foreign keys preserve references, and checks bound values. For state-dependent commands, use an atomic conditional update, compare a version, or lock the relevant row. Catch the resulting conflict and return a meaningful retry or rejection outcome.
Keep the boundary short and recoverable
Long transactions retain locks or old row versions, increase contention, and make deadlocks more likely. Gather noncritical input before beginning, issue the necessary reads and writes, and commit promptly. Establish a consistent lock order when several resources are involved. Treat deadlock errors as expected concurrency outcomes that may permit a bounded retry.
A retry reruns application logic, so that logic must not perform an irreversible side effect before commit. It must also re-evaluate current data rather than reuse a stale decision. Set a retry limit with jitter where appropriate, log the final conflict safely, and do not retry validation failures or deterministic constraint violations blindly.
Separate database commit from external effects
A database cannot ordinarily atomically commit with an email provider or arbitrary remote API. Calling the provider inside the transaction holds resources while the network may stall, and a later rollback cannot unsend a message. Calling it after commit can lose the effect if the process crashes between those steps.
One common solution writes an outbox record in the same database transaction as the business change. A worker later delivers the effect and records completion with idempotent handling. This adds delay and worker operations, so it is not mandatory for every notification. Use it when losing or duplicating the effect has meaningful consequences.
Reserve the last event seat
Two customers try to reserve the final available seat at nearly the same time.
- Define the invariant: confirmed reservations must never exceed event capacity, and one customer cannot hold duplicate active reservations.
- Begin a transaction and perform an atomic capacity-checked update or lock the event row before counting current reservations.
- Insert the reservation under a uniqueness constraint, then add an outbox record for confirmation delivery.
- Commit both database changes together; on conflict, roll back and return a sold-out or existing-reservation result.
- Have a worker deliver the confirmation from the outbox with a stable delivery key and bounded retries.
Transaction boundary worksheet
Use this worksheet before coding a multi-record write.
- Invariant: rule, participating data, commands that change it, and acceptable visible intermediate states.
- Concurrency control: constraint, conditional update, version, lock target, lock order, and isolation assumption.
- Boundary: statements inside the transaction, work moved before it, and maximum expected duration.
- Failure: rollback result, conflict response, retryable errors, retry limit, and diagnostic evidence.
- External effects: outbox need, delivery key, ordering, duplicate handling, and reconciliation process.
Common mistakes
- Reading availability and later writing it without a constraint, lock, or conditional update.
- Holding row locks while waiting for a slow payment, email, or unrelated service call.
- Retrying a whole transaction after it has already triggered an irreversible external effect.
Try one
A transfer debits one wallet, credits another, and sends a receipt. Draw the transaction boundary and explain concurrent and external-effect handling.
The debit, credit, transfer record, balance constraints, and receipt outbox entry belong in one database transaction. Rows should be locked in a consistent order or updated conditionally, with insufficient funds rejected from current data. The receipt is delivered after commit from the outbox using a stable key. A good answer also describes bounded deadlock retries and reconciliation.
Sources
- MDN IndexedDB transactionsMDN's reference for transaction scope, modes, completion, and abort behavior in IndexedDB.
- GitHub database migration guideGitHub's operational description of background database migrations and upgrade safety.