MediumConcept

What are ACID properties in a database?

Technology:
SQL
Experience:
Mid-level,
Senior

Quick answer

ACID stands for Atomicity, Consistency, Isolation and Durability: a transaction either fully succeeds or fully fails, keeps the data valid, does not interfere with other transactions, and survives crashes once committed.

Detailed explanation

Atomicity: all statements in a transaction are applied or none are, so a bank transfer never debits one account without crediting the other. Consistency: a transaction moves the database from one valid state to another, respecting constraints. Isolation: concurrent transactions behave as if they ran in some order, to the degree set by the isolation level. Durability: once committed, data is stored permanently, typically via a write-ahead log.

Relational databases provide ACID by default. Many NoSQL systems relax some of these guarantees to gain availability or scale, which is the trade-off behind the BASE and CAP discussions.

Example

sql
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;   -- or ROLLBACK; if anything failed

How to explain it in an interview

ACID describes what a transaction guarantees. Atomicity: all of it happens or none of it does. Consistency: constraints hold before and after. Isolation: concurrent transactions do not see each other half-finished. Durability: once committed, the change survives a crash. A bank transfer is the usual example: the debit and credit must both happen or neither.

Common mistakes

  • Mixing up ACID consistency with the "C" in the CAP theorem. ACID consistency is about constraints, CAP consistency is about replicas agreeing.
  • Assuming full isolation by default. Most databases default to Read Committed, which still allows some anomalies.
  • SQLMedium

    What is database normalization and what are the normal forms?

    Normalization is organising tables to reduce redundancy and update anomalies: 1NF means atomic values, 2NF removes partial dependencies on part of a composite key, and 3NF removes dependencies between non-key columns.

    Junior 路 Mid-level 路 Database
  • System DesignMedium

    When should you use a SQL database vs a NoSQL database?

    Choose SQL when your data is relational and you need transactions, joins and strong consistency; choose NoSQL when you need flexible schemas, very high write throughput or horizontal scaling for a specific access pattern.

    Mid-level 路 Senior 路 Database
  • SQLHard

    How do you optimize a slow SQL query?

    Read the execution plan with EXPLAIN to find full scans and expensive joins, add or fix indexes for the filter and join columns, select only the columns and rows you need, and rewrite predicates so indexes can be used.

    Senior 路 Performance