Backend Study Lab

System Design

Database Transactions Explained

Learn what database transactions are, why ACID matters, how commits and rollbacks work, and what backend developers should know.

By CloveCode Studio Jun 29, 2026 Updated Jun 29, 2026 4 min read
Diagram showing a database transaction boundary with commit and rollback outcomes.

Quick answer

A database transaction groups multiple operations into one unit of work. The database either commits the whole transaction or rolls it back so partial changes are not left behind.

Transactions are essential when backend code must keep related data consistent, such as creating an order and reserving inventory, transferring money, or updating a workflow state.

Why transactions matter

Consider an order workflow:

insert order
insert order items
reserve inventory
write payment record

If the system inserts the order but fails before inserting the order items, the database may contain incomplete data. A transaction lets the backend say: either all of these changes succeed, or none of them remain.

That property makes transactions one of the core tools for reliable backend systems.

Commit and rollback

A transaction usually has three phases:

BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

COMMIT;

If something fails before COMMIT, the transaction can be rolled back:

ROLLBACK;

After rollback, the database should not keep the partial changes from that transaction.

ACID

ACID describes important transaction properties:

PropertyMeaning
AtomicityAll operations in the transaction commit, or none do.
ConsistencyThe transaction moves the database from one valid state to another.
IsolationConcurrent transactions should not corrupt each other.
DurabilityOnce committed, data should survive crashes according to database guarantees.

ACID is a useful vocabulary, but backend developers still need to understand isolation levels and application-level invariants.

Isolation

Isolation controls what one transaction can see from another transaction.

Weak isolation may allow anomalies such as:

  • Dirty reads: reading uncommitted data.
  • Non-repeatable reads: reading the same row twice and seeing different values.
  • Phantom reads: re-running a query and seeing new matching rows.
  • Lost updates: one update overwrites another without detecting it.

Stronger isolation reduces anomalies but can increase locking, waiting, and retry needs.

A common transfer example

A money transfer must subtract from one account and add to another. These two changes belong together.

BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE id = 'acct_a'
  AND balance >= 100;

UPDATE accounts
SET balance = balance + 100
WHERE id = 'acct_b';

INSERT INTO ledger_entries (from_account, to_account, amount)
VALUES ('acct_a', 'acct_b', 100);

COMMIT;

Real financial systems need more safeguards than this short example, but the core idea is the same: related changes should be committed consistently.

Transactions and application code

Backend code should keep transactions focused.

Do inside a transaction:

  • Read rows needed for the decision.
  • Write related database changes.
  • Insert audit or ledger records.
  • Check constraints close to the data.

Avoid doing slow external work inside a transaction:

  • Calling third-party APIs.
  • Sending email.
  • Waiting on message brokers.
  • Rendering reports.
  • Performing long CPU-heavy work.

Long transactions can hold locks and increase contention. Commit the database state first, then enqueue follow-up work when possible.

Transactions and queues

A common backend problem is updating the database and publishing a message.

commit order -> publish order.created

If the database commit succeeds but message publishing fails, downstream systems may never hear about the order. If the message publishes before the commit and the transaction rolls back, consumers may see an event for data that does not exist.

The transactional outbox pattern is a common solution: write the business change and an outbox row in the same database transaction, then a separate worker publishes the outbox message.

Read Message Queues Explained for the queue side of this design.

Transactions and optimistic locking

Transactions do not automatically solve every concurrency problem at the API level.

If two users edit the same record based on stale reads, one can still overwrite the other unless the backend uses version checks, constraints, or stronger locking.

Optimistic Locking Explained covers the version-column pattern for detecting stale updates.

Common mistakes

The first mistake is assuming every group of repository calls automatically shares one transaction. In many frameworks, you must explicitly define the transaction boundary.

The second mistake is keeping transactions open while calling external services. That increases lock time and makes failure recovery harder.

The third mistake is doing check-then-write logic without a constraint or atomic update. Concurrent requests can both pass the check.

The fourth mistake is ignoring retry behavior. Some transaction failures are safe to retry, while others require user-visible conflict handling.

Practical backend checklist

When designing a transactional workflow:

  • Define the exact invariant that must remain true.
  • Put related writes in one transaction when they share one database.
  • Use constraints for uniqueness and referential integrity.
  • Keep the transaction short.
  • Avoid external calls inside the transaction.
  • Decide how to handle conflicts and deadlocks.
  • Test partial failure paths.

Read Optimistic Locking Explained for stale update protection and Idempotency in APIs Explained for retry-safe side effects. Read Database Migration Rollback Explained for production schema recovery planning and Database Connection Pooling Explained for how long transactions create pool pressure. Use the SQL Formatter when reviewing multi-statement examples and debugging transaction scripts.

Keep reading