ACID Transactions
The four guarantees (Atomicity, Consistency, Isolation, Durability) that ensure database transactions are processed reliably even under concurrent access and system failures.
Description
ACID is the set of properties that guarantees reliable transaction processing. Atomicity means a transaction is all-or-nothing -- if any statement fails, the entire transaction rolls back. Consistency ensures that a transaction brings the database from one valid state to another, respecting all constraints and triggers. Isolation controls how concurrent transactions interact, preventing one transaction from seeing another's uncommitted changes. Durability guarantees that once a transaction commits, its changes survive crashes, typically through write-ahead logging (WAL).
In practice, ACID transactions are the foundation of any system that handles money, inventory, or state that must not be corrupted. Wrap related operations in a single transaction: deducting from one account and crediting another must either both succeed or both fail. In PostgreSQL, transactions are implicit per statement (auto-commit) unless you explicitly BEGIN ... COMMIT. Most application frameworks provide transaction helpers -- Prisma's $transaction(), Knex's transaction(), or Spring's @Transactional annotation.
Understanding ACID is essential for reasoning about failure modes. If your application crashes between two related writes that are not in a transaction, you have corrupted data. If two users simultaneously update the same row without proper isolation, one update may be silently lost. ACID is not free -- stronger isolation levels reduce concurrency, and durability requires fsync to disk. NoSQL databases often relax one or more ACID properties to achieve higher throughput or horizontal scalability, which is why understanding these trade-offs matters when choosing a data store.
Prompt Snippet
Wrap all multi-statement business operations in explicit database transactions using BEGIN/COMMIT with appropriate isolation levels. In Prisma, use prisma.$transaction() with a timeout of 5000ms for interactive transactions. For financial operations, set the isolation level to SERIALIZABLE to prevent phantom reads. Implement application-level retry logic for serialization failures (SQLSTATE 40001) with jittered exponential backoff. Log transaction duration and set an alert threshold at p99 > 500ms to catch long-running transactions that hold locks.
Tags
Related Terms
Transaction Isolation Levels
Database settings that control the visibility of concurrent transaction changes, trading off between data consistency and throughput.
Optimistic vs Pessimistic Locking
Two concurrency control strategies: optimistic locking detects conflicts at write time using version numbers, while pessimistic locking prevents conflicts by acquiring locks at read time.
Database Connection Pooling
Maintaining a reusable pool of database connections to avoid the overhead of establishing new connections for every query.