Suggest an editImprove this articleRefine the answer for “ACID - consistency”. Your changes go to moderation before they’re published.Approval requiredContentWhat you’re changing🇺🇸EN🇺🇦UAPreviewTitle (EN)Short answer (EN)**ACID consistency** - a guarantee that every transaction moves the database from one valid state to another, enforcing all constraints. ```sql CREATE TABLE accounts ( balance NUMERIC(12,2) NOT NULL CHECK (balance >= 0) ); BEGIN; UPDATE accounts SET balance = balance - 150 WHERE owner = 'Alice'; -- tries -50 COMMIT; -- auto-rollback: CHECK violated, balance stays 100 ``` **Key point:** DB-level constraints enforce consistency at commit time, atomically. App-level checks can lose the race.Shown above the full answer for quick recall.Answer (EN)Image**ACID consistency** - a database guarantee that every transaction moves data from one valid state to another, enforcing all defined rules and constraints along the way. ## Theory ### TL;DR - Think of it as a bank vault rule: either both accounts update fully during a transfer, or nothing changes at all. - The database checks all constraints at commit time. Any violation triggers an automatic rollback. - Consistency is about your business rules (balance >= 0). Isolation is a separate concept about hiding concurrent changes. - Push rules to DB constraints and triggers first. Use app logic only for complex multi-database cases. ### Quick example ```sql -- PostgreSQL: enforce balance >= 0 at DB level CREATE TABLE accounts ( id SERIAL PRIMARY KEY, owner TEXT NOT NULL, balance NUMERIC(12,2) NOT NULL CHECK (balance >= 0) ); INSERT INTO accounts (owner, balance) VALUES ('Alice', 100); BEGIN; UPDATE accounts SET balance = balance - 150 WHERE owner = 'Alice'; -- tries -50 COMMIT; -- FAILS: CHECK constraint violation, auto-rollback SELECT balance FROM accounts WHERE owner = 'Alice'; -- still 100 ``` Alice's balance stays 100. The transaction rolled back automatically because `-50` violates the CHECK constraint. No partial state, no negative balance. ### Key difference ACID consistency differs from regular data validation because it ties enforcement atomically to the transaction. App-level checks can race or fail between check and write (this is the TOCTOU problem: time-of-check-to-time-of-update). DB-level constraints block the commit until all rules hold across the entire transaction. This eliminates "zombie" states where partial updates leave your invariants broken, even under concurrency. ### When to use - **Balance >= 0, NOT NULL, field formats** - DB CHECK constraints (fast, concurrent-safe) - **Cross-table rules (total debt <= credit limit)** - triggers or stored procedures - **Multiple databases or external systems** - application sagas, 2PC - **High-scale NoSQL** - app logic + eventual consistency (BASE trade-off) ### How PostgreSQL enforces this PostgreSQL validates consistency during transaction commit. It acquires locks on affected rows, re-checks all constraints and triggers against the final write set, and only writes to WAL if everything passes. If any CHECK, FK, or UNIQUE constraint fails, the entire transaction aborts via MVCC snapshot rollback. The constraint checker runs as a deferred validation phase: post-write but pre-commit. Deferred constraints (`SET CONSTRAINTS ALL DEFERRED`) wait until COMMIT to validate, which matters for circular FK references or multi-step transfers where interim states are temporarily invalid. After a crash, WAL replay re-applies committed transactions in commit order and rolls back incomplete ones. No partial state survives recovery. ### Common mistakes 1. **Validating only in app code** ```sql -- WRONG: app checks balance, then updates separately IF (SELECT balance FROM accounts WHERE id = 1) > 100 THEN UPDATE accounts SET balance = balance - 100 WHERE id = 1; END IF; -- A concurrent tx can drain the account between the check and the UPDATE ``` Two transactions can pass the check simultaneously and both deduct. The DB CHECK constraint closes this gap by evaluating at commit, under proper locking. 2. **Using READ COMMITTED when you need strict consistency** READ COMMITTED allows non-repeatable reads. A transaction can read the same row twice and see different values if another transaction committed between the two reads. For invariants that span multiple reads (like checking email uniqueness before insert), use SERIALIZABLE isolation. 3. **Immediate constraints on multi-step operations** ```sql -- WRONG for a two-leg transfer: SET CONSTRAINTS ALL IMMEDIATE; -- The debit fires CHECK before the credit happens. Fails on the interim state. -- RIGHT: SET CONSTRAINTS ALL DEFERRED; -- CHECK evaluates only at COMMIT, after all writes are complete. ``` 4. **Triggers without BEFORE context** `RAISE EXCEPTION` in an AFTER trigger fires too late in some scenarios. Use BEFORE triggers for constraint logic so the rollback happens before any row is actually written. ### Real-world usage - PostgreSQL + Rails: ActiveRecord wraps models in transactions and uses CHECK/FK constraints. Shopify runs this pattern at scale. - CockroachDB: distributed SQL enforces serializable consistency via total order broadcast. - Google Spanner: TrueTime API for external consistency in geo-distributed transactions. - MySQL InnoDB: gap locks prevent phantom reads in REPEATABLE READ mode. - NoSQL trade-off: DynamoDB and Cassandra favor availability (BASE). Use ACID for money and inventory, eventual consistency for logs and social feeds. ### Follow-up questions **Q:** What is the difference between Consistency and Isolation in ACID? **A:** Consistency enforces your business rules (invariants). Isolation hides concurrent changes from your transaction view. Consistency fails on bad data, Isolation fails on races. **Q:** How does consistency work in a distributed system like Google Spanner? **A:** Spanner uses TrueTime for external consistency. Commits get timestamps such that if Tx1 commits before Tx2 starts, Tx2 is guaranteed to see Tx1's effects. No 2PC required. **Q:** Why might a transaction pass all constraints locally but still fail at commit? **A:** Deferred constraints and triggers evaluate after all writes but before commit. A concurrent transaction can delete a FK target or invalidate a UNIQUE constraint in the window between your writes and your commit. **Q:** Walk through how WAL replay ensures consistency after a PostgreSQL crash. **A:** WAL stores full transaction images. On recovery, replay applies committed transactions in commit order and rolls back any that were incomplete. No partial state can survive this process. **Q:** What is the TOCTOU race and why does it break app-level consistency? **A:** TOCTOU is the gap between reading a value and writing based on it. A concurrent transaction can change the value in that gap. DB constraints eliminate this because they check at commit, under proper locking. ## Examples ### Basic: constraint blocking an invalid transaction ```sql CREATE TABLE accounts ( id SERIAL PRIMARY KEY, owner TEXT NOT NULL, balance NUMERIC(12,2) NOT NULL CHECK (balance >= 0) ); INSERT INTO accounts (owner, balance) VALUES ('Alice', 100), ('Bob', 10); BEGIN; UPDATE accounts SET balance = balance - 150 WHERE owner = 'Alice'; -- -50 COMMIT; -- ERROR: new row violates check constraint "accounts_balance_check" -- Alice's balance is still 100 ``` The transaction never committed. PostgreSQL detected the constraint violation and rolled back everything. No application code needed. ### Intermediate: atomic money transfer ```sql BEGIN; UPDATE accounts SET balance = balance - 100 WHERE owner = 'Alice' RETURNING balance; -- 0 after this UPDATE accounts SET balance = balance + 100 WHERE owner = 'Bob' RETURNING balance; -- 110 after this COMMIT; -- Result: Alice = 0, Bob = 110 -- If either update fails (Bob's row missing, CHECK hit), both roll back ``` This is the pattern Stripe uses for balance adjustments, combined with idempotency keys to handle retries safely. Both updates land or neither does. There is no state where Alice lost money but Bob did not receive it. ### Advanced: layered consistency with triggers and deferred constraints ```sql -- Optimistic locking with versioning CREATE TABLE wallets ( id SERIAL PRIMARY KEY, balance NUMERIC CHECK (balance >= 0), version INT DEFAULT 0 ); CREATE FUNCTION check_version() RETURNS TRIGGER AS $$ BEGIN IF NEW.version != OLD.version THEN RAISE EXCEPTION 'Concurrent update detected'; END IF; NEW.version := OLD.version + 1; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER version_check BEFORE UPDATE ON wallets FOR EACH ROW EXECUTE FUNCTION check_version(); ``` If two transactions read version 0 and both try to update, one fails the version check. If the other tries to withdraw more than the balance, CHECK catches it too. Two independent layers: the trigger blocks concurrent writes, CHECK blocks invalid state. I have seen engineers skip the CHECK thinking the trigger is enough. It is not. The trigger handles races, CHECK handles bad data. You need both.For the reviewerNote to the moderator (optional)Visible only to the moderator. Helps review go faster.