ACID Properties in Databases

ACID is an acronym describing four guarantees that a reliable transactional database provides: Atomicity, Consistency, Isolation, and Durability. These properties are what let application developers reason about the database as a trustworthy source of truth even when hardware fails, multiple users act at the same time, or programs crash mid-operation.

Not every database system provides full ACID guarantees — some NoSQL systems trade some of these properties for higher performance or availability — but traditional relational databases like PostgreSQL, MySQL, and SQL Server are built around ACID as a core design principle.

Why it matters

ACID is the set of four guarantees that make a database trustworthy with data that matters: money, orders, medical records. Each letter rules out a particular kind of disaster. It is asked in almost every back-end interview, and understanding it explains why relational databases are still the default for critical data.

Atomicity — All or Nothing

Atomicity guarantees that a transaction's statements are treated as a single unit: either all of them take effect, or none do. Scenario: a transaction inserts an order row and decrements product stock in two separate statements. If the server crashes after the insert but before the stock update, atomicity ensures the entire transaction is rolled back on recovery — you never end up with an order that was placed but never deducted from stock.

Consistency — Valid States Only

Consistency guarantees that a transaction can only move the database from one valid state to another valid state, respecting all defined rules: constraints, foreign keys, unique indexes, and triggers. Scenario: a CHECK constraint requires that an account's balance never go below zero. If a transaction's UPDATE would violate that constraint, the database rejects the transaction rather than allowing the balance to become invalid, even temporarily.

Isolation — Concurrent Transactions Don't Interfere

Isolation guarantees that concurrently running transactions do not see each other's uncommitted, in-progress changes, so each transaction behaves as if it were running alone. Scenario: two customers try to buy the last item in stock at the same instant. Proper isolation ensures the database serializes the effect of their transactions correctly rather than both reading "1 in stock" and both succeeding, which would oversell the item.

Durability — Committed Means Permanent

Durability guarantees that once a transaction is committed, its changes survive any subsequent crash, power loss, or restart — the database has written the change to non-volatile storage (typically via a write-ahead log) before confirming success. Scenario: a payment transaction commits and the customer sees "Payment successful." A power outage hits the server one second later. Durability guarantees that payment record is still there when the server comes back up.

Example

BEGIN;

INSERT INTO orders (customer_id, product_id, quantity)
VALUES (501, 77, 1);

UPDATE products
SET stock = stock - 1
WHERE id = 77 AND stock > 0;  -- CHECK-style condition enforcing consistency

-- If the UPDATE affects 0 rows (stock was already 0), the application
-- issues a ROLLBACK instead of COMMIT, undoing the INSERT too.
COMMIT;

INSERT 0 1
UPDATE 1
COMMIT
(Order recorded and stock decremented together; if stock had been 0, both changes would be rolled back.)

Exercise

The CHECK constraint forbids a negative balance. Inside one transaction, add 500 to B and then try to take 500 from A, which has only 100; the second update is rejected. Roll the transaction back so that the first update is undone as well, and print the total of both balances as total.

Expected output: total then 150

CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER CHECK (balance >= 0));
INSERT INTO accounts (name, balance) VALUES ('A', 100), ('B', 50);

-- TODO: begin, add 500 to B, subtract 500 from A (this fails), roll back

SELECT SUM(balance) AS total FROM accounts;

CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER CHECK (balance >= 0));
INSERT INTO accounts (name, balance) VALUES ('A', 100), ('B', 50);

BEGIN;
UPDATE accounts SET balance = balance + 500 WHERE name = 'B';
UPDATE accounts SET balance = balance - 500 WHERE name = 'A';
ROLLBACK;

SELECT SUM(balance) AS total FROM accounts;

Quick quiz

Interview questions