ACID properties define the guarantees relational databases make about transaction reliability — understanding them clarifies why databases behave the way they do, and helps you use transactions correctly in your applications.
What a Transaction Is
A group of database operations treated as a single unit — either all operations succeed together, or none of them take effect at all, preventing a partial, inconsistent state.
Atomicity
All operations within a transaction succeed together, or none do — if any part fails, the entire transaction rolls back, leaving the database as if it never started.
Consistency
A transaction moves the database from one valid state to another, never leaving it in a state that violates defined constraints (foreign keys, unique constraints, and similar rules).
Isolation
Concurrent transactions don't interfere with each other's intermediate states — each transaction behaves as if it's the only one running, even when many execute simultaneously (with actual behavior depending on the configured isolation level).
Durability
Once a transaction commits successfully, the change is permanent, surviving even a subsequent crash or power failure — achieved through mechanisms like write-ahead logging.
Using a Transaction in Practice
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;A classic example — transferring money between accounts; both updates must succeed together, or neither should take effect, since a partial transfer (money debited from one account but not credited to the other) would be a serious consistency violation.
Rolling Back a Transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- something goes wrong
ROLLBACK;Isolation Levels: A Trade-Off Between Consistency and Performance
| Isolation Level | Behavior |
|---|---|
| Read Uncommitted | Can see other transactions' uncommitted changes (rarely used, weakest isolation) |
| Read Committed | Only sees committed changes from other transactions — common default |
| Repeatable Read | Guarantees the same query returns the same result throughout the transaction |
| Serializable | Strongest isolation — transactions behave as if executed one at a time sequentially |
Higher isolation levels provide stronger consistency guarantees but generally reduce concurrency/performance — choose based on your application's actual consistency requirements, not automatically defaulting to the strictest level.
Setting Isolation Level
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;When ACID Guarantees Matter Most
Financial transactions, inventory management, anything where partial/inconsistent updates would have real business consequences — genuinely benefit from and depend on these guarantees, which is why relational databases with strong ACID compliance remain the standard choice for such workloads.
NoSQL and ACID: A More Nuanced Picture
Some NoSQL databases traditionally relaxed certain ACID guarantees in favor of scalability/performance (a trade-off often summarized as "eventual consistency") — though many modern NoSQL databases now offer configurable consistency/transaction support; verify your specific database's actual guarantees rather than assuming based on category alone.
Common Mistakes
- Not using transactions for genuinely related multi-step operations that need atomicity
- Using an unnecessarily strict isolation level, hurting performance without a genuine consistency need
- Long-running transactions holding locks longer than necessary, causing contention with other operations
Continue Reading
- How to Write and Optimize SQL Queries: Indexing Basics
- Choosing Between SQL and NoSQL: A Decision Framework
- How to Implement Idempotent API Endpoints
Browse more articles in Databases.
