termEngineering & architecture

Database Transaction

A database unit of work that groups operations so they commit together or are rolled back while concurrent work follows defined isolation rules.

Download Markdown (.md)

01What it means

A database transaction groups one or more operations into a single logical unit. It begins, performs reads or writes, and either commits its changes or rolls them back. Transaction systems commonly aim for atomicity, consistency, isolation, and durability—ACID—but exact isolation and durability behavior depends on configuration and implementation.[1][2]

All-or-nothing logical unit A transaction either commits its complete change or rolls partial work back
  1. BeginChoose isolation and establish scope
  2. Read + writePreserve intended invariant
  3. CheckConstraints and conflicts
  4. CommitMake the valid unit durable
  5. RollbackDiscard partial changes on failure

Retry only recognized transient conflicts safely

Isolation and durability depend on the database and configuration; transient conflicts may require a bounded safe retry.

02Why it matters

Transactions protect business invariants when operations span several records or compete with concurrent requests. They prevent partial updates from becoming visible and give teams a defined boundary for handling errors, retries, conflicts, and durable completion.

03When to use it

Use a transaction when related database operations must succeed as one unit or when reads and writes require controlled concurrency. Keep boundaries short and focused; a database transaction cannot atomically include arbitrary external APIs, messages, or user interaction without additional coordination patterns.

04How it works

  1. Begin a transaction and choose an isolation level suitable for the operation's concurrency risks.
  2. Read and change the records needed to preserve the intended invariant.
  3. Let constraints and conflict detection reject invalid or incompatible changes.
  4. Commit when every operation succeeds, making the unit durable according to database guarantees.
  5. Roll back on failure so the transaction's partial changes do not take effect.
  6. Retry recognized transient conflicts safely, with bounds and idempotent surrounding behavior.

05A practical example

Example

A wallet transfer debits one balance and credits another inside one transaction. A constraint prevents negative funds; if either update fails, both roll back. A concurrent conflict is retried from the start rather than leaving only one side changed.

06Where it breaks

Long or highly contended transactions hold resources and can block or abort other work. Isolation levels permit different anomalies, and ACID guarantees stop at the database boundary. Distributed transactions add coordination and availability costs and are not automatically the right answer.

Common mistakes

  • Treating each statement's automatic transaction as sufficient for a multi-step business invariant.
  • Holding a transaction open while waiting for user input or a slow external service.
  • Assuming the default isolation level prevents every concurrency anomaly.
  • Retrying a failed transaction without bounding attempts or considering duplicated external effects.

07Sources

  1. TransactionsPostgreSQL Global Development Group · Official technical documentationTransactions
  2. Transaction IsolationPostgreSQL Global Development Group · Official technical documentationTransaction Isolation
View saved topics

Start typing to search titles, definitions, categories, and frameworks.