ACID, SOLID, and Design Patterns: A Complete Software Design Reference

Part 3 of 26 · ~2 min

Consistency: Valid States Only

Consistency is the promise that a transaction can only ever leave the database in a shape the schema itself would sign off on — every foreign key, every check constraint, every rule declared up front gets enforced on the way out. It's the odd one of the four guarantees, because the transaction manager doesn't deliver it alone. Getting it right takes two things working together: the constraints actually written into the schema, and application code that uses them correctly while a transaction is open.

CREATE TABLE Holds (
    id INTEGER PRIMARY KEY,
    seat_id INTEGER NOT NULL REFERENCES Seats(id),       -- foreign key constraint
    customer_id INTEGER NOT NULL,
    expires_at DATETIME NOT NULL CHECK (expires_at > CURRENT_TIMESTAMP), -- check constraint
    UNIQUE (seat_id)                                       -- one live hold per seat
);

-- Rejected — seat 9999 doesn't exist.
INSERT INTO Holds (seat_id, customer_id, expires_at) VALUES (9999, 8831, datetime('now', '+3 minutes'));

-- Rejected — a hold can't expire in the past.
INSERT INTO Holds (seat_id, customer_id, expires_at) VALUES (214, 8831, datetime('now', '-1 minutes'));

Here's the distinction worth holding onto: ACID's Consistency only ever checks a transaction against rules the schema actually wrote down. It has no opinion on whether the transaction represents a decision anyone at the box office would call correct. A transaction can sail past every constraint that exists and still be a bad decision in a way no CHECK clause was ever positioned to catch. Someone fat-fingers the hold-expiry logic and grants a three-hour hold instead of three minutes — nothing above rejects it, expires_at is still comfortably in the future, every constraint is satisfied — and now one seat is effectively off the market for the entire on-sale window while thousands of people are refreshing the page. Think of Consistency as a floor, never a ceiling — it stops the database from accepting outright garbage, but it was never going to catch a decision that's merely unwise. Catching that kind takes a human reading the diff, a test that encodes the actual business rule, and — which is exactly where the second half of this reference picks up — business logic that's organized well enough to make the rule easy to find and enforce in the first place.