What are ACID properties in databases? | SQL Interview Questions

What You'll Learn

ACID components
Why they matter
Real-world examples

ACID Properties

Atomicity

All or nothing - transactions complete entirely or not at all.

query.sqlSQL

BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- Both happen, or neither

Consistency

Database remains in valid state before and after transaction.

query.sqlSQL

-- Constraints ensure consistency
CREATE TABLE accounts (
  id INT PRIMARY KEY,
  balance DECIMAL(10,2) CHECK (balance >= 0)
);

-- This will fail if balance would go negative
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;

Isolation

Concurrent transactions don't interfere with each other.

query.sqlSQL

-- Transaction A
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- Returns 1000
-- Transaction B updates balance to 500
SELECT balance FROM accounts WHERE id = 1;  -- Still sees 1000 (depending on isolation level)
COMMIT;

Durability

Committed transactions survive system failures.

query.sqlSQL

COMMIT;  -- Data is now permanently saved
-- Even if server crashes, this data persists

Isolation Levels

Level	Dirty Read	Non-repeatable Read	Phantom Read
READ UNCOMMITTED	Yes	Yes	Yes
READ COMMITTED	No	Yes	Yes
REPEATABLE READ	No	No	Yes
SERIALIZABLE	No	No	No

query.sqlSQL

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- Your queries here
COMMIT;

Why ACID Matters

Banking: Money transfers must be atomic
E-commerce: Inventory and orders must be consistent
Healthcare: Patient records must be durable