ACID Properties
Understand ACID properties: Atomicity, Consistency, Isolation, and Durability in database transactions.
What are ACID properties?
ACID is a set of guarantees a database can apply to a transaction: one unit of work containing related reads and writes. Atomicity, consistency, isolation, and durability keep a transaction from leaving behind a partial, invalid, conflicting, or forgotten business fact.
ACID matters when a wrong result costs more than a retry. A payment must not remove money without adding it somewhere else; the last concert ticket must not be sold twice; and a confirmed order must survive a restart. The central invariant is simple: after a transaction finishes, the facts people rely on must still be true.
All or nothing
Atomicity
Every required write commits together, or the database rolls them all back. A failed transfer never exposes just the debit.
Rules stay true
Consistency
Constraints and application checks move data from one valid state to another. A balance rule or foreign key rejects an invalid state.
Concurrent work
Isolation
Concurrent transactions are controlled so their combined effect is valid, even when their individual steps overlap.
Commit survives
Durability
After a successful commit is acknowledged, recovery should retain the result through a process, host, or power failure.
A transaction protects one business decision
A transaction is not simply a bundle of SQL statements. It is the boundary around a business decision whose related facts must agree. For a transfer, the decision is not "subtract money" or "add money" independently; it is "move $80 from A to B."
1 Check the rules
Read the accounts and verify the transfer amount, account state, and authorization.
2 Apply related writes
Debit the source and credit the destination within the same transaction boundary.
3 Commit or roll back
Make all writes visible together, or restore the prior valid state if any required step fails.
4 Recover if needed
Persist the committed decision so database recovery can reproduce it after a crash.
The database can enforce some rules, such as NOT NULL, unique keys, foreign keys, and CHECK constraints. The application still owns cross-record business rules that are not encoded in the schema. Correctness comes from defining the invariant and putting every required check and write inside an appropriate boundary.
Lab: inject a failed transfer
Atomicity and consistency become easier to distinguish when a write fails. In this lab, change the transaction boundary, amount, and failure condition. Watch whether the two account balances still add up to the original $200 and whether the no-negative-balance rule remains true.
Trace one money transfer through a failure
A has $100 and B has $100. Change the boundary and inject a failed credit to see which facts remain true.
Transaction boundary
Inject a failure
Invariant preserved
The failed credit rolls the debit back. Both accounts keep their original balances.
$100
No debit visible
$100
No credit visible
$200
Original total preserved
- 1Validate that A has enough funds for the requested transfer.
- 2Debit A by $80, then roll it back.
- 3Credit B by $80, but this write fails.
Each letter answers a different failure question
Atomicity: can part of the decision escape?
Atomicity says a transaction has two visible outcomes: every required change commits, or none do. It is useful for transfers, order creation plus inventory reservation, and creating a user with required related records. Rollback logs or undo records let the database discard incomplete work.
Consistency: are the stored facts allowed?
Consistency means the transaction preserves declared and business invariants. A database constraint can reject an order with no customer, while a transaction-level check can prevent an account from going below its allowed limit. Consistency is not a synonym for replication consistency; here it means valid state before and after the transaction.
Isolation: what happens when work overlaps?
Isolation controls the observations and writes of transactions that run at the same time. It prevents one request from treating another request's unfinished or conflicting work as a safe basis for a decision. Locks, multiversion concurrency control (MVCC), conflict detection, and retry logic are common mechanisms.
Durability: can an acknowledged result disappear?
Durability begins after commit. Write-ahead logs, flushing policy, replication, and recovery procedures must make a committed result recoverable. A replica can improve availability, but it does not by itself prove that the primary acknowledged the transaction durably.
Isolation protects decisions, not every query
Isolation levels choose which concurrent anomalies a database prevents and how much waiting, aborting, or retrying the application may face. Product behavior depends on the database engine and the exact query pattern, so use its documentation for the final contract.
Common default
Read committed
Prevents reading another transaction's uncommitted data. A later statement can still observe a newer committed value, so a read-then-write decision may need a lock or conditional update.
Stable row view
Repeatable read
Typically keeps rows read by a transaction stable. Exact phantom and write-conflict behavior differs by database, so test the invariant rather than relying on the label alone.
Strongest ordering
Serializable
Produces an outcome equivalent to transactions running one at a time. It may block or abort a transaction; callers must be prepared to retry safely.
For a hot inventory row, a guarded write such as UPDATE inventory SET available = available - 1 WHERE sku = ? AND available > 0 can encode the invariant directly. The affected-row count tells the application whether a reservation succeeded. SELECT ... FOR UPDATE is another approach when a decision needs to read and then update a locked row.
Lab: race two buyers for one item
Two shoppers both see one item. Choose the concurrency strategy to see how a read-check-write flow can oversell, while a guarded write or serializable transaction preserves the inventory invariant with different operational costs.
Race two buyers for one item
Both buyers begin when inventory shows one item. Pick the concurrency strategy and inspect the resulting reservation trace.
Concurrency strategy
This is a simplified race. Real engine behavior also depends on the isolation level, query shape, indexes, and retry policy.
Both buyers were accepted for one item.
Each buyer read available = 1 before either write completed. The separate check and write did not protect the shared invariant.
1
Both buyers begin together
2
One order is oversold
-1
Negative stock signals oversell
Request trace
- 1Buyer 1 reads 1 available
- 2Buyer 2 reads 1 available
- 3Buyer 1 reserves the item
- 4Buyer 2 also reserves the item
Implement the critical path deliberately
The following example keeps the order record and inventory reservation in one transaction. It uses a conditional inventory update instead of trusting a stale value read by the application. The notification is placed in an outbox table so it is committed with the order and can be delivered later without holding the transaction open for a network call.
Keep transaction boundaries small
- Put only the reads, validations, and writes needed for one invariant inside the transaction.
- Avoid remote HTTP calls, long user interactions, and slow reports while locks or snapshots are held.
- Design an idempotency key for retried requests. Isolation can abort a transaction; a retry must not charge or create an order twice.
- Record an outbox event in the same transaction, then deliver it asynchronously. This avoids claiming an external side effect occurred before the transaction commits.
Choose guarantees by the cost of being wrong
Use strong transactional protection for facts that must agree immediately: account balances, inventory reservations, entitlements, and audit-relevant state. Lower-stakes derived data can often be asynchronous: search indexes, analytics aggregates, caches, notifications, and recommendation feeds can be rebuilt or corrected from a durable source of truth.
This is not a choice between "ACID" and "fast." A useful design places the strict transaction around the smallest critical decision, then uses asynchronous processing for work that can be retried or reconciled. Measure lock waits, deadlocks or serialization failures, rollback rate, commit latency, and recovery tests to verify the chosen boundary under load.
Start with the invariant in one sentence: "available inventory never becomes negative" or "an accepted payment has exactly one order." Then choose constraints, conditional writes, isolation, idempotency, and recovery behavior that make that sentence true.