ACID Properties

Why interviewers love this: ACID is the interview shorthand for whether your database can protect money, orders, and inventory when failures happen at the worst possible time.

Question: What are ACID properties in SQL?

Answer: ACID stands for Atomicity, Consistency, Isolation, and Durability. Together, they describe the guarantees a transaction should provide: all changes happen together or not at all, the data rules still hold, concurrent transactions do not interfere in unsafe ways, and a committed change survives a crash.

Interview-Ready Answer: In SQL, ACID is the set of transaction guarantees I rely on to keep data correct under failure and concurrency. Atomicity means all-or-nothing, consistency means the transaction leaves the data in a valid state, isolation means other transactions should not see my half-finished work, and durability means once I commit, the change survives a power loss or crash. Under the hood, databases usually combine locks or snapshots with write-ahead logging to make that happen.

Detailed Explanation:

What each letter really means

• Atomicity means a transaction is one logical unit. If one step fails, the whole unit is undone. Think of transferring money: if the debit succeeds but the credit fails, the database must not keep only half the change.
• Consistency means the transaction preserves declared rules such as primary keys, foreign keys, CHECK constraints, and other invariants. Important nuance: the database does not magically know every business rule; your schema and application logic define what valid means.
• Isolation means concurrent transactions behave as if they ran separately, or at least close to that depending on the chosen isolation level. This prevents one transaction from reading another transaction's half-finished work.
• Durability means a committed transaction stays committed even if the server crashes immediately after COMMIT. The usual mechanism is write-ahead logging, or WAL, which means the log is saved before the final success is acknowledged.

How the database enforces ACID under the hood

1. The application starts a transaction with BEGIN. The database creates transaction state and, in many engines, a snapshot of what this transaction is allowed to see.
2. Reads and writes happen. The engine may use locks (temporary blocks on rows or tables) and/or MVCC, which means multi-version concurrency control: the database keeps multiple row versions so readers and writers can overlap safely.
3. Every change is recorded in the transaction log before it is considered durable. This is the key idea behind write-ahead logging: if the server dies, the log tells recovery how to replay committed work.
4. When you issue COMMIT, the database flushes the needed log records to stable storage, often forcing an fsync call. fsync means the operating system is told to push buffered data to disk, not just to memory.
5. After the log is durable, the client gets success. The actual data pages may be written later by background processes, because the log is enough to recover the committed state.
6. If a crash happens, recovery replays committed log records and ignores incomplete work. That is why a committed transaction should survive a restart.

Isolation levels are where many interview questions go next

Isolation is not one single setting; SQL databases offer different levels, each trading correctness for concurrency.

Level	What you get	What can still happen
Read Uncommitted	Fastest, weakest	Dirty reads
Read Committed	No dirty reads	Non-repeatable reads
Repeatable Read	Stable row reads	Phantom issues in some engines
Serializable	As if one-by-one	Most expensive, may abort conflicts

Defaults vary: PostgreSQL defaults to READ COMMITTED, MySQL InnoDB often defaults to REPEATABLE READ, SQL Server defaults to READ COMMITTED, and Oracle uses READ COMMITTED. A strong interview point: SERIALIZABLE does not always mean transactions literally run one after another; it means the result is equivalent to some serial order.

A subtle gotcha is write skew: two transactions each read the same snapshot, make different updates, and together violate a rule. Snapshot-style isolation can still allow that in some engines, which is why true serializable mode matters for strict invariants.

When and why to use ACID

• Use it for money movement, inventory updates, booking systems, user balances, and any place where one broken invariant is expensive.
• Use a weaker model only when the business can tolerate temporary inconsistency, like analytics dashboards, caches, feeds, or some search indexes.
• ACID is not free: every commit usually pays for log work and sometimes disk flushes. On SSDs a flush may be sub-millisecond to a few milliseconds, but under load or networked storage it can be slower.
• Long transactions are a hidden cost in MVCC databases because they keep old row versions alive longer, which can increase storage use and slow cleanup.

Best mental model

Memory hook: think of a transaction like a sealed bank vault: either every item goes in together, the rules still hold, nobody sees the contents mid-transfer, and once the vault is locked, the contents stay safe.

Real-World Story: A checkout service for an online store creates an order row, reserves inventory, and records payment authorization. The team wraps those writes in one SQL transaction so a customer is never charged for an order that did not exist, and stock is never reduced without a matching order.

Now imagine a bug: an engineer moves the payment step outside the transaction because they want the code to feel faster. Under load, the service sometimes writes the order, then crashes before inventory is updated. Support starts seeing complaints like my card was charged but the order disappeared, while warehouse reports show items still available even though customers already bought them. Logs show retries, occasional unique-key errors, and mismatched order counts between the payments table and the orders table.

The outage is not just a code bug; it is a transaction boundary bug. ACID is what prevents the database from saving you halfway through a business action. Without it, your reports drift, your support queue fills up, and your retry logic can make the damage worse by creating duplicates or double-debits.

Follow-up & Tricky Questions:

• How does the database actually make durability work? A good answer mentions write-ahead logging, log flushes, and crash recovery replaying committed records before the data pages are fully written.
• What is the difference between atomicity and consistency? Atomicity is about all-or-nothing execution. Consistency is about ending in a valid state according to schema rules and business invariants.
• What is the difference between isolation levels? The higher the level, the fewer concurrency anomalies you allow, but the more blocking or retries you may pay for. SERIALIZABLE is the strongest and usually the most expensive.
• What are dirty reads, non-repeatable reads, and phantom reads? Dirty reads see uncommitted data, non-repeatable reads see the same row change between reads, and phantom reads see new qualifying rows appear in a repeated query.
• What are savepoints for? Savepoints are partial rollback markers inside a transaction. They let you undo one risky step without discarding the entire transaction.
• Does ACID guarantee no application bugs? No. It protects transaction behavior, but it cannot fix bad business logic, wrong constraints, or a race in code that happens outside the database.
• Is ACID enough for distributed systems? Not always. Once data is split across services or regions, you may need coordination patterns, sagas, or eventual consistency because one database transaction may no longer cover the whole workflow.
• Does commit mean every replica is updated instantly? Not necessarily. A primary database can commit durably on the leader first, while replicas catch up afterward. Durability is about the committed transaction surviving failure, not about all copies being in sync at the same microsecond.
• Does serializable mean the database runs one transaction at a time? Not literally. It means the final result is equivalent to some serial order, even if the engine used concurrency internally.
• Can ACID transactions deadlock? Yes. Two transactions can still wait on each other, and the database usually breaks the deadlock by aborting one transaction and asking it to retry.

Common Mistakes:

• Mixing up consistency meanings. In ACID, consistency means valid data rules. It does not mean all replicas have the same value at the same time.
• Assuming SERIALIZABLE is always the default. Defaults vary by database, and many systems ship with READ COMMITTED or REPEATABLE READ instead.
• Thinking ACID removes the need for constraints. The database cannot protect a business rule that you never encoded anywhere.
• Forgetting about the cost. Strong isolation and durable commits need logging, locking, and sometimes retries; they are correct, but not free.

Memory Hook: Sealed vault - all changes in or out together, rules stay valid, nobody peeks mid-transfer, and once locked, the result stays put.

Cheat Sheet:

• Atomicity = all or nothing.
• Consistency = rules stay true after commit.
• Isolation = concurrent transactions do not see unsafe intermediate states.
• Durability = committed data survives crashes.
• WAL and fsync are the usual durability tools.
• Isolation levels trade correctness for speed; stronger usually means more cost.

Practice Tasks:

• Write a money transfer transaction that refuses to overdraft the sender.
• Add a CHECK constraint and test how a bad update fails, then recover with a savepoint.
• Compare READ COMMITTED and SERIALIZABLE in your own database by running two concurrent sessions and looking for anomalies.