ACID Properties in Databases

Interview Guide for Software Engineers

Table of Contents

1. Introduction to ACID Properties

2. What is a Database Transaction?
3. Atomicity (All or Nothing)
4. Consistency (Database Stays Valid)
5. Isolation (Transactions Don't Interfere)
6. Durability (Saved Means Saved)
7. ACID in a Real E-commerce Flow
8. ACID vs BASE
9. ACID in [Link] Backend Development
10. Common Interview Questions on ACID
11. Common Mistakes Developers Make
12. Best Practices
13. Summary / Cheat Sheet

1. Introduction to ACID Properties

What ACID Means

ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability. These
are the four key properties that a database management system (DBMS) uses to guarantee that
data transactions are processed reliably.

Why ACID Properties Exist

In a real-world application, thousands of users interact with the database simultaneously. Systems
crash, networks drop, and application code throws errors. ACID properties exist to ensure that
despite all these chaotic events, the database remains in a correct, predictable, and uncorrupted
state.

Problems ACID Solves and Why Databases Need It

Databases need ACID to provide trust. Without ACID, a backend system cannot guarantee the
integrity of critical data, such as financial records, user identities, or physical inventory. ACID acts
as a safety net against hardware failures and software bugs.

What Happens Without ACID (Real-World Production Problems)

Payment deducted but order not created: If a script crashes right after charging a user's
credit card but before saving the order details, the user loses money and gets nothing.
Double booking issue: In a movie theater or airline system, if two users try to book the
exact same seat at the exact same millisecond, both might be issued a ticket for one seat.
Inventory mismatch: Ten people buy the last remaining iPhone on an e-commerce site
simultaneously, resulting in a negative inventory of -9.
Data corruption after crash: The database server loses power while writing a large
record, leaving half the data written and permanently corrupting the file.

2. What is a Database Transaction?

What a Transaction Is

A transaction is a single, logical unit of work that consists of one or more database operations
(like INSERT, UPDATE, DELETE). A transaction must execute completely, or it must not execute
at all.

Single Query vs Transaction

A single query is executing one command, like fetching a user's profile. A transaction groups
multiple queries that depend on each other. If one step in the group fails, the entire group is
canceled.

Real-World Example: Bank Transfer

Imagine transferring $100 from Account A to Account B. This involves two steps:

1. Deduct $100 from Account A.

2. Add $100 to Account B.

If step 1 succeeds but step 2 fails (e.g., database server crashes), $100 disappears from the
system. Wrapping these two steps in a transaction ensures both happen, or neither happens.

BEGIN, COMMIT, ROLLBACK

BEGIN (or START TRANSACTION): Tells the database that a sequence of operations is
starting.
COMMIT: Tells the database that all operations succeeded and should be permanently
saved.
ROLLBACK: Tells the database that an error occurred, and all operations since BEGIN
should be undone.
 -- SQL Example of a Transaction
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE user_id = 'A';

UPDATE accounts SET balance = balance + 100 WHERE user_id = 'B';

COMMIT; -- If both succeed

-- Or ROLLBACK; if an error occurs

3. Atomicity (All or Nothing)

Definition

Atomicity guarantees that a transaction is treated as a single, indivisible logical unit of work. It is
an "all or nothing" rule: either all operations in the transaction successfully complete, or none of
them are applied to the database.

Why Atomicity Matters

It prevents partial data updates. The "Partial Transaction Problem" occurs when a process halts
halfway through. Without atomicity, the system is left in a broken state.

Real-World Analogy

Buying a coffee. You hand over the money, and the barista hands you the coffee. If the barista
drops the coffee, they give your money back. The exchange either happens completely, or you
return to your initial state.

Examples

Banking: Balance deducted but credit failed. Atomicity ensures the deduction is reversed
(rolled back).
E-commerce: Payment success but order creation failed. Atomicity ensures the payment
record is not saved without the order record.

How Rollback Works (Technical Explanation)

When you start a transaction, the database writes changes to a temporary undo log or creates
new data versions without deleting the old ones (MVCC). If a ROLLBACK is triggered, the
database uses these logs to revert the data to exactly how it was before the BEGIN statement.

// [Link] Backend Example (Pseudo-code structure)

async function transferMoney(db, senderId, receiverId, amount) {
await [Link]('BEGIN');
try {
await [Link]('UPDATE acc SET bal = bal - ? WHERE id = ?',
 [amount, senderId]);

// Imagine an error happens here (e.g., receiver account is closed)

await [Link]('UPDATE acc SET bal = bal + ? WHERE id = ?',
[amount, receiverId]);

await [Link]('COMMIT');
} catch (error) {
await [Link]('ROLLBACK'); // Undoes the sender deduction
throw error;
}
}

Interview One-Liner: Atomicity

Atomicity ensures that a transaction is an "all-or-nothing" operation; if any part of the

transaction fails, the entire transaction is rolled back, preventing partial data updates.

4. Consistency (Database Stays Valid)

Definition

Consistency ensures that a transaction takes the database from one valid state to another valid
state. It guarantees that all database constraints, business rules, and triggers are strictly followed.

Why Consistency Matters

It prevents illegal or corrupt data from being saved. Data validation and referential integrity
(foreign keys) ensure the application behaves predictably.

Constraints & Rules

Primary Key: Ensures every row is uniquely identifiable.

Foreign Key: Ensures a record points to a valid, existing record in another table.
Unique: Prevents duplicate values (e.g., email addresses).
NOT NULL: Ensures critical data is always present.
Check Constraints: Validates business logic at the database level (e.g., balance >=
0 ).

Real-World Examples

Negative bank balance: A check constraint ( balance >= 0 ) prevents a transaction

from pushing an account below zero.
Order for non-existing user: A foreign key constraint on user_id ensures an order
cannot be placed for a deleted or non-existent user.
 -- SQL Database Constraint Example
CREATE TABLE accounts (
id INT PRIMARY KEY,
user_id INT NOT NULL,
balance DECIMAL(10,2) NOT NULL,
CONSTRAINT chk_positive_balance CHECK (balance >= 0)
);

Interview One-Liner: Consistency

Consistency ensures that a transaction only saves data that adheres to all defined database
constraints, rules, and referential integrity, keeping the data perfectly valid.

5. Isolation (Transactions Don't Interfere)

Definition

Isolation determines how transaction integrity is visible to other users and systems. It ensures that
concurrent transactions occurring at the same time do not interfere with each other, making it
appear as if they were executing sequentially.

Concurrency Problems (Race Conditions)

1. Dirty Read: Transaction A reads data written by Transaction B, but Transaction B has not
committed yet. If B rolls back, A is working with fake data.
2. Non-repeatable Read: Transaction A reads a row. Transaction B modifies that row and
commits. Transaction A reads the row again and gets a different value.
3. Phantom Read: Transaction A runs a query finding 5 rows. Transaction B inserts a new
row matching A's query. Transaction A runs the query again and suddenly finds 6 rows (a
"phantom").

Isolation Levels

Non-
Dirty Phantom
Level repeatable Performance
Read Read
Read

Fastest, but dangerous. Used in

Read Uncommitted Possible Possible Possible
reporting non-critical metrics.

Read Committed Good balance. Queries only read

Prevented Possible Possible
(Default in Postgres) committed data.
 Non-
Dirty Phantom
Level repeatable Performance
Read Read
Read

Slower. Ensures same query

Repeatable Read
Prevented Prevented Possible yields same data during
(Default in MySQL)
transaction.

Slowest. Complete isolation

Serializable Prevented Prevented Prevented (locks everything). Used for strict
financial ledgers.

Interview One-Liner: Isolation

Isolation ensures that multiple transactions running concurrently do not impact each other,
preventing race conditions and keeping intermediate states invisible.

6. Durability (Saved Means Saved)

Definition

Durability guarantees that once a transaction has been committed, it will remain committed even
in the event of a system failure (e.g., power outage, server crash).

What Happens After Commit (Crash Recovery)

Databases achieve durability primarily through a mechanism called Write-Ahead Logging

(WAL). When you commit a transaction, the database doesn't instantly write the data directly to
the large data files on the hard drive (which is slow). Instead, it appends a quick log entry to a
sequential log file (WAL). Once the log is written, the database returns "Success" to the user.

Real-World Example

A user completes a payment. The database writes the success to the WAL and sends a 200 OK
response. A millisecond later, someone trips over the server power cord. When the server
restarts, the database reads the WAL, realizes the data wasn't fully saved to the main tables, and
replays the log to fully restore the transaction.

Interview One-Liner: Durability

Durability guarantees that once a transaction is successfully committed, it survives system

crashes and power failures because it is permanently saved to non-volatile storage.
7. ACID in a Real E-commerce Flow
Consider a user buying an iPhone. The flow requires multiple systems to update synchronously.

[User Clicks Buy]

|
(BEGIN)
|
1. Deduct $999 from User Wallet (Updates Balance)
|
2. Reduce iPhone Inventory by 1 (Updates Stock)
|
3. Create Order Record (Inserts Order)
|
4. Clear Shopping Cart (Deletes Cart Item)
|
(COMMIT)

Atomicity solves: If step 3 fails, steps 1 and 2 are rolled back. User gets their money back,
and inventory is restored.
Consistency solves: Step 2 cannot reduce stock below 0 due to a database check
constraint.
Isolation solves: If two users click buy at the exact same time for the last iPhone, row-level
locks prevent a race condition (preventing double booking).
Durability solves: If the server crashes one second after the COMMIT is successful, the
order still exists when the server reboots.

8. ACID vs BASE
Relational databases (SQL) rely on ACID. Modern distributed systems and NoSQL databases
often rely on BASE to scale horizontally across the globe.

Basically Available: The system guarantees availability, even if some nodes fail.
Soft state: The state of the system can change over time, even without input.
Eventual consistency: The system will eventually become consistent once all nodes sync
up.

Feature ACID (SQL) BASE (NoSQL)

Focus Data Consistency & Integrity High Availability & Scalability

Consistency Strong (Immediate) Eventual

Use Case Banking, Payments, Inventory Social Media Feeds, Analytics, Logs
9. ACID in [Link] Backend Development
When writing backend code in [Link], you must explicitly manage transactions when dealing
with multiple dependent queries.

Example using mysql2 promise wrapper:

const mysql = require('mysql2/promise');

async function processOrder(userId, productId, connection) {

// Start Transaction
await [Link]();

try {
// 1. Deduct Inventory
const [invRes] = await [Link](
'UPDATE inventory SET stock = stock - 1 WHERE id = ? AND stock
> 0',
[productId]
);

if ([Link] === 0) {
throw new Error('Out of stock');
}

// 2. Create Order
await [Link](
'INSERT INTO orders (user_id, product_id) VALUES (?, ?)',
[userId, productId]
);

// Commit transaction
await [Link]();
[Link]('Order successful');

} catch (error) {
// Rollback on any failure
await [Link]();
[Link]('Order failed, rolling back.', [Link]);
}
}

10. Common Interview Questions on ACID

Beginner Level

1. What does ACID stand for? Atomicity, Consistency, Isolation, Durability.

 2. What is a database transaction? A logical unit of work containing one or more SQL
operations treated as a single entity.
3. What does Atomicity mean? All operations in a transaction succeed, or none of them do
(all or nothing).
4. What does Consistency mean? A transaction must transition the database from one valid
state to another, respecting all constraints.
5. What does Isolation mean? Concurrent transactions do not affect each other.
6. What does Durability mean? Committed changes survive hardware or system crashes.
7. What is the difference between COMMIT and ROLLBACK? COMMIT permanently saves
changes; ROLLBACK discards uncommitted changes.
8. Why shouldn't you use transactions for single SELECT queries? It adds unnecessary
overhead; a single query is implicitly atomic.
9. What is the default isolation level in MySQL? Repeatable Read.
10. What is the default isolation level in PostgreSQL? Read Committed.

Intermediate Level

11. What is a Dirty Read? Reading uncommitted changes from another transaction.
12. What is a Non-repeatable Read? Reading the same row twice in a transaction and getting
different data because another transaction updated it.
13. What is a Phantom Read? A query returning different row counts within the same
transaction because another transaction inserted/deleted rows.
14. How does a database achieve Durability? Using Write-Ahead Logging (WAL). Changes
are written to a log file on disk before updating the actual tables.
15. Explain Repeatable Read vs Serializable. Repeatable Read locks the rows you query;
Serializable locks the entire table or range, preventing even phantom inserts.
16. What is a database constraint? Rules enforced on data columns (like NOT NULL,
UNIQUE, FOREIGN KEY) to maintain Consistency.
17. What is a database deadlock? When Transaction A locks Row 1 and needs Row 2, while
Transaction B locks Row 2 and needs Row 1. They wait indefinitely.
18. How do you resolve deadlocks? The database detects it and forcefully kills (rolls back)
one of the transactions.
19. What is MVCC? Multi-Version Concurrency Control. It allows databases to maintain
isolation by keeping multiple versions of a row without locking the whole table.
20. Is consistency in ACID the same as consistency in the CAP theorem? No. ACID
consistency refers to rules/constraints in a single database. CAP consistency refers to data
synchronization across distributed nodes.

Scenario and Backend Level

21. A payment API takes 5 seconds to respond. Should you put the API call inside a
database transaction? No. Long-running transactions lock database resources, causing
bottlenecks. Do the API call first, then update the DB in a quick transaction.
 22. How do you handle a double-booking problem for concert tickets? Use isolation levels
with row locking (e.g., SELECT ... FOR UPDATE ), ensuring only one thread can book
the specific seat at a time.
23. If you catch an error in [Link] but forget to write [Link]() , what
happens? The transaction stays open (dangling), holding locks on rows until the database
timeout kills it, severely degrading performance.
24. How would you design a distributed transaction across two different microservices?
Use patterns like Two-Phase Commit (2PC) or the Saga pattern (event-based with
compensating actions).
25. If a server crashes exactly halfway through writing a committed transaction to disk,
what happens on restart? The database reads the WAL during recovery. It sees the
COMMIT mark in the log and replays the incomplete writes to ensure Durability.
26. When would you intentionally use the 'Read Uncommitted' isolation level? For
generating heavy analytical reports where reading slightly stale or uncommitted data
doesn't affect the business outcome, to avoid locking tables.
27. How do ORMs like Prisma or TypeORM handle transactions? They usually provide a
callback function wrapper. If the callback returns successfully, they auto-commit. If it throws
an error, they auto-rollback.
28. A check constraint requires stock to be >= 0. A transaction updates stock to -1. What
happens? The database throws a constraint violation error, preventing the Consistency
rule from being broken. The application should catch this and trigger a rollback.
29. What is optimistic locking? Instead of locking rows at the database level (pessimistic),
you include a version column. Before updating, you check if the version matches what
you initially read. If not, it means another transaction modified it.
30. Why not always use Serializable isolation? It forces transactions to happen one after
another (sequentially), which destroys concurrency and causes massive performance drops
in high-traffic applications.

11. Common Mistakes Developers Make

Network calls inside a transaction: Waiting for Stripe or AWS inside a transaction blocks
database resources for seconds. Always perform external I/O outside the transaction block.
Forgetting to Rollback in the Catch block: Swallowing errors without explicitly calling
ROLLBACK causes connection pooling leaks and locked tables.

Overusing Transactions: Wrapping basic, single SELECT or INSERT statements in

explicit BEGIN/COMMIT blocks adds unnecessary overhead.
Ignoring Isolation Levels: Defaulting to Repeatable Read when strict Serializable is
required for financial ledgers, leading to phantom reads and race conditions.

12. Best Practices

Keep transactions short and fast: Process data locally, run your validation, and only open
the transaction right before you execute the queries.
 Use appropriate isolation levels: Only increase isolation strictness when necessary. Stick
to defaults for 95% of web apps.
Always use try/catch/finally blocks: Ensure connections are released back to the pool
regardless of whether the transaction committed or rolled back.
Rely on DB Constraints: Don't just validate in the [Link] application. Application bugs
happen. Add Check Constraints and Foreign Keys directly to the database.

13. Summary / Cheat Sheet

Property One-Line Definition Real-World Example

All steps succeed, or everything is If a bank transfer fails midway, the sender
Atomicity
undone. gets their money back.

Data must obey all database A database rejects an order if it drives

Consistency
rules/constraints. inventory below zero.

Simultaneous transactions don't mess Two people booking the same seat are
Isolation
each other up. queued, not double-booked.

Power failure after saving an order doesn't

Durability Once committed, data is never lost.
delete the order.