ACID
ACID stands for Atomicity, Consistency, Isolation, and Durability.
These four ACID properties define database transactions. When they are all met, they guarantee database transaction validity, even in the event of system crashes, power failures, and other errors.
- Atomicity ensures that all operations within a transaction are treated as a single unit, which all succeed or all fail together.
- Consistency ensures that a transaction can only bring the database from one valid state to another, preventing data corruption.
- Isolation determines how and when changes made by one transaction become visible to others. Serializable and snapshot isolation are the top two isolation levels from a strictness standpoint.
- Durability ensures that transaction results are permanently stored in the system (such as backups and transaction logs). Modifications must persist even if the system fails or there is a loss of power.
Foundational Aspects of ACID
Four foundational aspects must be designed and developed to support ACID transactions in a monolithic or distributed database.
- Provisional updates are required for atomicity. Transactions involve multiple operations across multiple rows. Since these operations must be treated as a single unit, a provisional update (in a temporary space) is needed first, followed by a commit.
- A strongly consistent core (for consistency) is the foundation for achieving ACID guarantees in a transaction involving a single operation on a single row. Additional data integrity constraints are built on top of this core to achieve full ACID compliance (with multiple operations across multiple rows).
- Transaction ordering is necessary for isolation. For a database to support the strictest serializable isolation level, a mechanism such as globally ordered timestamps is required to sequentially arrange all transactions. However, the snapshot isolation level relies on partial ordering, where sub-operations in one transaction may interleave with those from other transactions. This results in lower latency and higher throughput than the serializable level, while still detecting write-write conflicts.
- Persistent storage (for durability) is easy to achieve with modern storage technology. A storage engine stores data in an underlying persistent device, such as an HDD or SSD. To understand how different types of storage engines work and their workload optimization patterns, check out A Busy Developer’s Guide to Database Storage Engines.