Database Consistency: ACID Transactions and Normalization
This article explores database consistency by detailing ACID properties for transactions and the role of normalization in data integrity. It provides examples to illustrate how these concepts contribute to reliable data storage and retrieval in relational databases, which is fundamental for robust system design.
Read original on Medium #system-designIntroduction to Database Consistency
Database consistency is a cornerstone of reliable system design, ensuring that data adheres to predefined rules and integrity constraints. This means that any data written to the database must be valid according to all defined rules, including constraints, triggers, and cascades. Maintaining consistency is crucial, especially in distributed systems where data can be spread across multiple nodes and accessed concurrently by many users.
ACID Properties and Transactions
The ACID properties (Atomicity, Consistency, Isolation, Durability) are a set of principles guaranteeing reliable transaction processing. Transactions are sequences of operations performed as a single logical unit of work. For a transaction to be successful, all its operations must complete; otherwise, none of them should. This atomicity prevents partial updates that could leave the database in an inconsistent state.
- Atomicity: All or nothing. If any part of a transaction fails, the entire transaction is rolled back, leaving the database state unchanged.
- Consistency: A transaction brings the database from one valid state to another, maintaining all defined rules and constraints.
- Isolation: Concurrent transactions execute in isolation, as if they were running serially. This prevents dirty reads, non-repeatable reads, and phantom reads, depending on the isolation level.
- Durability: Once a transaction is committed, its changes are permanent and survive system failures (e.g., power loss, crashes).
Isolation Levels
Understanding different isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) is vital for balancing consistency guarantees with performance in concurrent systems. Higher isolation levels offer stronger consistency but can introduce more locking overhead.
Normalization for Data Integrity
Normalization is a database design technique used to organize tables to minimize data redundancy and improve data integrity. It involves breaking down large tables into smaller, less redundant tables and defining relationships between them. This process helps ensure that data stored in the database is consistent and less prone to anomalies during insertion, update, and deletion operations.
- First Normal Form (1NF): Eliminate repeating groups in tables. Each column contains atomic values, and there are no repeating groups of columns.
- Second Normal Form (2NF): Be in 1NF and eliminate partial dependencies. Non-key attributes must be fully functionally dependent on the primary key.
- Third Normal Form (3NF): Be in 2NF and eliminate transitive dependencies. Non-key attributes must not depend on other non-key attributes.
- Boyce-Codd Normal Form (BCNF): A stricter version of 3NF, where every determinant is a candidate key. This often applies when a table has multiple overlapping candidate keys.