Consistency vs Atomicity

Direct Answer

Consistency means a successful transaction preserves the invariants that the database schema and application actually enforce. Atomicity means the transaction's changes commit as one unit or have no committed effect. Atomicity can help preserve consistency, but it cannot repair missing or incorrect business rules.

Code Example

-- A CHECK constraint expresses a consistency rule
CREATE TABLE accounts (
    id      INT PRIMARY KEY,
    balance DECIMAL(12,2) NOT NULL CHECK (balance >= 0)  -- consistency rule
);

START TRANSACTION;                       -- atomicity begins
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- If id=1 would go negative, the CHECK fails, the statement errors,
-- and ROLLBACK leaves the database in a valid (consistent) state.
COMMIT;                                  -- both updates persist together

Atomicity (all-or-nothing) is how the database avoids leaving a half-applied transfer that would violate consistency.

Common Mistakes & Pitfalls

• Confusing consistency with eventual consistency: The C in ACID means constraint enforcement and data validity, not the distributed systems concept where replicas converge over time. These are completely different meanings of consistency.
• Thinking consistency is only about constraints: Consistency also includes application-level business rules that the database can't validate, like discount logic or approval workflows.
• Not realizing consistency is shared responsibility: The database enforces foreign keys and check constraints, but the application must implement complex business rules that can't be expressed as SQL constraints, like cross-entity invariants.