Transactions let a group of database operations succeed or fail as one unit. They are simple in principle, yet many production issues, such as lost updates, deadlocks, double charges and exhausted connection pools, come from how applications use them. The ten patterns below describe practices that hold across most relational databases.
1. Keep transactions short
A transaction holds locks and resources until it commits or rolls back. Avoid network calls to other services, file uploads or waiting for user input while a transaction is open. Gather inputs first, open the transaction, perform the database work and commit.
2. Define transaction boundaries in one place
Decide where transactions start and end, typically at the level of a use case or service method, rather than scattering commits through lower-level code. Many frameworks provide a unit-of-work or transactional decorator for this. Clear boundaries make it obvious which operations are atomic.
3. Choose the isolation level deliberately
Isolation levels control what concurrent transactions can see. Common levels are read committed, repeatable read and serializable, and defaults differ between engines: PostgreSQL defaults to read committed, while MySQL's InnoDB defaults to repeatable read. Higher isolation prevents more anomalies but increases the chance of conflicts. Know your default and raise it only for operations that need it.
4. Retry on serialization failures and deadlocks
At higher isolation levels or under contention, databases abort some transactions with serialization or deadlock errors. These are expected, and the correct response is to retry the whole transaction, with a limit and a short randomized backoff. Retry only these specific error types, not every failure.
5. Make operations idempotent
Retries are safe only if repeating an operation does not cause duplicate effects. Use idempotency keys for requests such as payments, unique constraints to prevent duplicate rows, and upserts where appropriate. Idempotency also protects you when a client retries after a timeout without knowing whether the first attempt succeeded.
6. Use optimistic locking for user edits
When users edit records over minutes, holding a database lock is impractical. Optimistic locking adds a version column; the update includes WHERE id = ? AND version = ? and increments the version. If no row is updated, someone else changed the record first, and the application can show a conflict instead of silently overwriting.
7. Use pessimistic locks sparingly
For short critical sections, such as reserving limited stock, SELECT ... FOR UPDATE locks the selected rows until the transaction ends. Lock rows in a consistent order across the codebase to reduce deadlocks, and keep these transactions especially short.
8. Publish events with a transactional outbox
Writing to the database and then sending a message to a broker is not atomic: the write can succeed while the message fails, or the reverse. The outbox pattern stores the event in an outbox table within the same transaction as the business change. A separate process reads the outbox and publishes events, retrying until they are delivered.
9. Avoid distributed transactions where possible
Coordinating a transaction across several databases or services, for example with two-phase commit, adds complexity and failure modes. Many systems instead use local transactions plus patterns such as the outbox and sagas, in which each step has a compensating action if a later step fails.
10. Set timeouts and monitor long transactions
Configure statement and transaction timeouts so a stuck transaction does not block others indefinitely. Monitor open transactions and lock waits; most databases expose views for this. An idle transaction left open by an application bug can hold locks and, in some engines, prevent cleanup of old row versions.
Key takeaways
- Keep transactions short, with boundaries defined at the use-case level.
- Know your isolation level and retry only serialization failures and deadlocks.
- Design for idempotency and use optimistic locking for long edits.
- Use an outbox instead of dual writes, and set timeouts on everything.