What Is a Database Transaction?
A database transaction is a sequence of one or more database operations treated as a single logical unit of work. Either the transaction completes successfully and its changes are committed, or it fails and its changes are rolled back.
Transactions protect data when operations depend on each other, multiple requests access the same records concurrently, or failures occur halfway through an update. They are fundamental to reliable relational database systems and are closely connected to ACID properties, isolation levels, locking, MVCC, and write-ahead logging.
Table of Contents
- 1. How Database Transactions Work
- 2. Why Database Transactions Are Needed
- 3. ACID Properties
- 4. Concurrent Transactions
- 5. Locking and MVCC
- 6. Durability and Write-Ahead Logging
- Choosing Transaction Boundaries
- Transactions and External Services
- Production Design Example
- Common Transaction Mistakes
- Production Checklist
- Frequently Asked Questions
- Conclusion
1. How Database Transactions Work
A transaction groups related database operations into one logical operation.
Consider transferring $100 from Account A to Account B:
1. Subtract $100 from Account A
2. Add $100 to Account BThese statements should not be treated as unrelated updates. If the first succeeds and the second fails, $100 effectively disappears from the system.
A transaction creates a boundary around them:
BEGIN
│
├── subtract $100 from Account A
│
├── add $100 to Account B
│
↓
COMMITIf every required operation succeeds, the database commits the transaction. If something fails, the transaction can be rolled back so its partial changes do not become the final database state.
BEGIN, COMMIT, and ROLLBACK
A typical SQL transaction looks like:
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
UPDATE accounts
SET balance = balance + 100
WHERE id = 2;
COMMIT;BEGIN starts an explicit transaction. The following statements execute inside that transaction until it finishes.
COMMIT tells the database to make the transaction's changes permanent.
If the operation cannot safely complete:
ROLLBACK;the database discards the transaction's uncommitted changes.
Applications usually wrap this behavior in error handling:
try:
with connection.transaction():
debit_account()
credit_account()
except Exception:
raiseThe exact API depends on the database driver or ORM, but the important concept is the same: all related operations share one transaction boundary.
Transaction Lifecycle
A simplified lifecycle looks like:
BEGIN
│
↓
execute statements
│
├── error ─────────→ ROLLBACK
│
↓
validation succeeds
│
↓
COMMIT
│
↓
changes become committedBefore commit, the transaction is still in progress. Exactly what other transactions can observe during that period depends on the database and the selected isolation level.
The commit point is particularly important. Once the database reports a successful commit, the transaction is expected to satisfy its durability guarantees.
2. Why Database Transactions Are Needed
Many business operations require several database changes to succeed together.
Examples include:
- transferring money between accounts;
- creating an order and its order items;
- reserving inventory and recording the reservation;
- updating multiple related records;
- moving ownership of a resource;
- recording a payment state transition.
Without a transaction, failures between statements can leave the database in a partially updated state.
Money Transfer Example
Suppose the initial balances are:
Account A: $1,000
Account B: $500
Total: $1,500A $100 transfer should produce:
Account A: $900
Account B: $600
Total: $1,500Without a transaction:
UPDATE A: -$100
│
↓
A = $900
│
X application crashes
│
UPDATE B never happens
A = $900
B = $500
Total = $1,400The database contains individually valid rows but an invalid business result.
Inside a transaction, the database can roll back the first update when the complete operation cannot finish.
Failure in the Middle of an Operation
Failures can happen for many reasons:
- application exceptions;
- constraint violations;
- deadlocks;
- database connection failures;
- process crashes;
- database restarts;
- hardware failures.
Transactions define what should happen when work stops halfway through.
Operation 1 ✓
Operation 2 ✓
Operation 3 ✗
Operation 4 never runs
↓
ROLLBACK
↓
transaction changes are discardedThis all-or-nothing behavior is the transaction's atomicity guarantee.
3. ACID Properties
Database transactions are commonly described through four properties known as ACID: Atomicity, Consistency, Isolation, and Durability.
These properties describe different guarantees. A transaction is not simply synonymous with ACID; ACID explains the behavior expected from transactional processing.
Atomicity
Atomicity means the transaction is treated as one logical unit.
Transaction
Operation A ✓
Operation B ✓
Operation C ✗
↓
ROLLBACK ALLThe system should not expose a final state where only part of the transaction was committed.
Atomicity is especially important when several writes together represent one business operation.
Consistency
Consistency means a transaction takes the database from one valid state to another valid state while respecting the integrity rules enforced by the database.
Those rules can include:
- primary keys;
- foreign keys;
- unique constraints;
- check constraints;
- data types;
- application-defined invariants.
For example:
CREATE TABLE accounts (
id BIGINT PRIMARY KEY,
balance NUMERIC NOT NULL CHECK (balance >= 0)
);A transaction attempting to violate the constraint cannot successfully commit that invalid value.
Not every business rule is automatically guaranteed by the database. The schema and transaction logic must actually encode or enforce the invariant.
Isolation
Isolation determines how concurrent transactions interact and which intermediate changes they can observe.
Suppose two transactions operate simultaneously:
Transaction A Transaction B
read balance = 100 read balance = 100
│ │
↓ ↓
calculate 100 - 20 calculate 100 - 30
│ │
↓ ↓
write 80 write 70Without sufficient concurrency control, one update can overwrite the other.
Databases provide isolation mechanisms to control these interactions. Stronger isolation usually reduces the set of possible anomalies but can increase blocking, retries, or coordination.
Database Concurrency covers lost updates, dirty reads, non-repeatable reads, phantom reads, and the mechanisms used to control them.
Durability
Durability means that once a transaction is successfully committed, its changes survive failures according to the database's durability guarantees.
Transaction
│
↓
COMMIT ✓
│
↓
database crash
│
↓
restart
│
↓
committed data remainsDatabases commonly use write-ahead logging and durable storage to provide this property.
What Is Write-Ahead Logging? explains how logging allows databases to recover committed changes after crashes.
4. Concurrent Transactions
Production databases rarely execute only one transaction at a time. Hundreds or thousands of requests can read and modify data concurrently.
Concurrency improves throughput, but transactions can interfere with each other.
Transaction A ───────┐
Transaction B ───────┼──→ same database records
Transaction C ───────┘The database needs rules for deciding which versions can be read, which writes conflict, and when transactions must wait or retry.
Concurrency Anomalies
Common anomalies include:
- dirty read — reading another transaction's uncommitted change;
- non-repeatable read — reading the same row twice and receiving different committed values;
- phantom read — repeating a query and seeing rows appear or disappear;
- lost update — one transaction overwrites another transaction's update;
- write skew — individually valid concurrent writes violate a cross-row invariant.
Different databases and isolation levels prevent different sets of anomalies.
Isolation Levels
SQL databases commonly expose isolation levels such as:
| Isolation Level | General Goal |
|---|---|
| Read Uncommitted | Minimal isolation with high concurrency |
| Read Committed | Prevent reading uncommitted data |
| Repeatable Read | Provide stronger stability for repeated reads |
| Serializable | Provide behavior equivalent to some serial execution of transactions |
The exact guarantees differ between database engines, so an isolation-level name alone is not enough to understand every possible anomaly.
Serializable isolation provides the strongest standard isolation model, but stronger guarantees can require more locking, validation, serialization failures, or transaction retries.
Isolation should therefore match the business invariant rather than simply being set to the strongest available level everywhere.
5. Locking and MVCC
Transactions define the logical unit of work. The database still needs mechanisms to coordinate concurrent transactions.
Two major techniques are locking and Multi-Version Concurrency Control (MVCC).
Database Locks
Locks can prevent conflicting operations from proceeding simultaneously.
For example:
BEGIN;
SELECT balance
FROM accounts
WHERE id = 42
FOR UPDATE;
UPDATE accounts
SET balance = balance - 100
WHERE id = 42;
COMMIT;FOR UPDATE can lock the selected row against conflicting updates until the transaction ends.
Transaction A
│
├── lock row 42
│
│
Transaction B
│
└── waits for row 42
│
↓
Transaction A commits
│
↓
Transaction B continuesLocks provide control but introduce waiting and can create deadlocks when transactions acquire resources in conflicting orders.
MVCC
Multi-Version Concurrency Control allows a database to maintain multiple versions of rows so readers can often access a consistent snapshot without blocking writers.
Row versions:
V1: balance = 100
V2: balance = 120
Transaction A snapshot → V1
Newer transaction → V2This can significantly improve read/write concurrency.
MVCC does not eliminate transaction conflicts. Concurrent writes, serialization rules, version cleanup, and visibility still need to be managed by the database.
What Is MVCC? covers row versions, snapshots, visibility, and concurrent transaction behavior in more detail.
6. Durability and Write-Ahead Logging
A transaction does not become durable merely because application memory contains the updated values.
Databases need a recovery mechanism that can reconstruct committed state after a crash.
A common design uses a write-ahead log:
Transaction changes
│
↓
write WAL record
│
↓
make log durable
│
↓
COMMIT acknowledged
│
↓
data pages written laterThe database does not necessarily need to write every modified table page to its final location before returning from every commit. Instead, the recovery log records enough information to restore the committed state.
After a crash:
database starts
│
↓
read WAL
│
↓
identify committed work
│
↓
recover database stateThis separation is critical for both performance and durability.
Choosing Transaction Boundaries
A transaction should contain the operations that must succeed or fail together, but it should generally be no larger than necessary.
Consider this flow:
BEGIN
│
├── create order
├── insert 10 order items
├── update inventory
├── call payment API
├── wait 3 seconds
├── send HTTP request
└── update order
COMMITKeeping a database transaction open while waiting for slow network operations can hold locks, retain old row versions, consume connections, increase contention, and make failures more expensive.
A better boundary often separates local atomic database work from external operations.
BEGIN
│
├── create order
├── update local state
└── record required follow-up work
COMMIT
↓
external processingThe exact boundary depends on the business guarantee. Short transactions are desirable, but correctness comes first.
Transactions and External Services
A local database transaction cannot normally roll back an external HTTP request, message already delivered to another system, or payment already processed by an independent service.
Consider:
BEGIN
│
├── update database
│
├── publish message ✓
│
└── COMMIT ✗The database rolled back, but the message may already be visible to another service.
Reversing the order creates the opposite problem:
BEGIN
│
├── update database
├── COMMIT ✓
│
└── publish message ✗Now the database contains committed state, but the event was never published.
This is known as a dual-write problem.
One common solution is the Transactional Outbox. The business update and an outbox record are committed in the same local transaction. A separate process publishes the event afterward.
When a business operation must coordinate changes across independent services, local database transactions are often combined with distributed patterns such as sagas. What Is a Saga Pattern? covers that model.
Production Design Example
Consider an e-commerce service processing an inventory reservation.
The database contains:
products
--------
id: 847
available_quantity: 12
reservations
------------
order_id
product_id
quantityOrder ORD-9001 requests three units.
A naive application could:
1. SELECT available_quantity
2. application checks quantity >= 3
3. INSERT reservation
4. UPDATE available_quantityUnder concurrency, two requests can both observe the same available quantity before either updates it.
Transaction A Transaction B
read quantity = 3 read quantity = 3
│ │
quantity sufficient quantity sufficient
│ │
reserve 3 reserve 3The application can oversell inventory if the database operation does not correctly coordinate the concurrent transactions.
One design uses a conditional atomic update inside a transaction:
BEGIN;
UPDATE products
SET available_quantity = available_quantity - 3
WHERE id = 847
AND available_quantity >= 3;
-- verify that exactly one row was updated
INSERT INTO reservations (
order_id,
product_id,
quantity
)
VALUES (
'ORD-9001',
847,
3
);
COMMIT;If the conditional update affects zero rows, there is not enough inventory and the transaction is rolled back.
If inserting the reservation fails after inventory was reduced, rollback restores the inventory change as well.
The transaction therefore protects the local invariant:
available_quantity must never become negative
and
inventory reduction must correspond
to a reservation recordNow suppose the system must publish an InventoryReserved event.
Publishing directly inside the transaction creates the dual-write problem. Instead, an outbox record can be inserted in the same transaction:
BEGIN;
UPDATE products
SET available_quantity = available_quantity - 3
WHERE id = 847
AND available_quantity >= 3;
INSERT INTO reservations (
order_id,
product_id,
quantity
)
VALUES ('ORD-9001', 847, 3);
INSERT INTO outbox (
event_type,
aggregate_id,
payload
)
VALUES (
'InventoryReserved',
'ORD-9001',
'{"product_id":847,"quantity":3}'
);
COMMIT;Now the reservation and the requirement to publish its event are atomic from the database's perspective.
A background publisher can read the outbox and send the event after commit.
Production monitoring should include more than transaction success rates. Useful signals include:
- transaction duration;
- lock wait time;
- deadlock count;
- serialization failures;
- rollback rate;
- database connection utilization;
- long-running transactions;
- retry frequency.
For example:
Transaction p50: 8 ms
Transaction p95: 24 ms
Transaction p99: 71 ms
Lock wait p95: 4 ms
Deadlocks: 2/hour
Serialization retries: 0.08%
Transactions > 5 sec: 0If transaction p99 suddenly increases from 71 ms to several seconds while lock waits rise, the problem may be contention rather than raw query execution speed.
Transaction behavior should therefore be observed together with database concurrency and locking metrics.
Common Transaction Mistakes
- Using multiple independent writes for one atomic business operation. A failure between statements can leave partial state.
- Keeping transactions open during external API calls. This unnecessarily extends lock and resource lifetimes.
- Assuming a transaction automatically prevents every race condition. Correctness also depends on isolation level and query design.
- Reading a value and later writing it without considering concurrent updates. This can produce lost updates.
- Using the strongest isolation level everywhere. Stronger isolation can increase contention and retries without providing useful additional correctness for every operation.
- Ignoring deadlocks. Applications should expect some transactions to be aborted and retried.
- Retrying non-idempotent operations blindly. The application may not always know whether an earlier attempt committed.
- Publishing messages independently from database commits. This creates dual-write failure windows.
- Using very large transactions. They can hold locks, consume resources, increase WAL volume, and make rollback expensive.
- Assuming COMMIT means every modified data page has already been written to its final table file. Durable logging allows databases to acknowledge commits without that requirement.
- Ignoring transaction observability. Long-running transactions and lock waits can degrade an entire database.
Production Checklist
- Define the business invariant the transaction protects.
- Put operations that must succeed or fail together inside one transaction.
- Keep transaction boundaries as small as correctness allows.
- Avoid network calls while holding a database transaction open.
- Choose an isolation level based on actual concurrency requirements.
- Understand the database engine's exact isolation semantics.
- Use atomic updates, locks, or optimistic concurrency where appropriate.
- Expect deadlocks and serialization failures to occur.
- Retry retryable transactions with bounded backoff.
- Make retry-sensitive operations idempotent.
- Use constraints to enforce important database invariants.
- Avoid uncontrolled dual writes between a database and message broker.
- Use an outbox or another reliable coordination pattern when required.
- Monitor transaction duration and rollback rates.
- Monitor lock waits and deadlocks.
- Detect unexpectedly long-running transactions.
- Load-test concurrent access to hot records.
- Test failures between every important transaction step.
Frequently Asked Questions
Transactions are simple at the SQL syntax level, but their production behavior depends heavily on concurrency, isolation, durability, and where transaction boundaries are placed.
Is a Single SQL Query a Transaction?
Often yes. Database systems commonly execute individual statements transactionally even when the application does not explicitly issue BEGIN and COMMIT.
Explicit transactions become important when several statements need to share the same atomic boundary.
Can a Transaction Be Rolled Back After COMMIT?
No. Once a transaction has successfully committed, a normal ROLLBACK cannot undo that transaction.
Reversing committed business changes requires another transaction that performs the appropriate compensating updates.
Do Transactions Prevent Concurrency Problems?
Not automatically. Transactions provide a boundary for database work, while isolation levels and concurrency-control mechanisms determine how simultaneous transactions interact.
An application can still experience lost updates, write skew, serialization failures, or other concurrency behavior if the transaction and isolation strategy do not protect the required invariant.
Can One Database Transaction Span Multiple Services?
A normal local database transaction protects operations within its database transaction manager. It does not automatically include independent databases, HTTP services, queues, or third-party APIs.
Distributed workflows generally require additional coordination mechanisms such as distributed transactions, transactional outbox patterns, idempotency, or sagas.
Are Database Transactions Slow?
Transactions introduce coordination and durability work, but that does not make them inherently slow. Small, well-designed transactions are fundamental to high-throughput production databases.
Problems usually appear when transactions remain open for too long, touch heavily contended rows, acquire excessive locks, perform unnecessary work, or wait on external systems.
Conclusion
A database transaction groups related operations into one logical unit of work. Successful transactions commit their changes, while failed transactions can roll back partial work.
ACID properties describe the core guarantees around atomicity, consistency, isolation, and durability. In production, those guarantees depend on mechanisms such as constraints, isolation levels, locks, MVCC, and write-ahead logging.
The most important design decision is often not whether to use a transaction, but where the transaction should begin and end. A good transaction boundary protects the required business invariant without unnecessarily holding locks, connections, versions, or other database resources.
Key takeaway: use a database transaction when multiple operations must form one correct state transition, then choose the isolation and concurrency-control strategy required to keep that transition correct under failures and concurrent requests.
Comments (0)