MediumExperience level: 3-5 years · 8-15 years1 min read

Explain ACID and the four transaction isolation levels — which is the default?

Reviewed by Gurusankar M.

Asked inAmazonMicrosoftDeloittebanking
#acid#isolation levels#read committed#serializable#mvcc
Report issue

Short Answer

ACID = Atomicity, Consistency, Isolation, Durability. Isolation levels trade consistency for concurrency: READ UNCOMMITTED < READ COMMITTED < REPEATABLE READ < SERIALIZABLE. Most DBs default to READ COMMITTED (Postgres/Oracle/SQL Server); MySQL/InnoDB defaults to REPEATABLE READ.

On this page

Concept Made Simple

Coffee Chat Question

“Explain ACID and the four transaction isolation levels — which is the default?”

Remember It Faster

Mind Map Answer

READ UNCOMMITTED
dirty reads possible
READ COMMITTED
no dirty reads (common default)
REPEATABLE READ
no non-repeatable reads
SERIALIZABLE
fully isolated, lowest concurrency

Think Beyond the Expected

What If?

Higher isolation is 'safer' — why not always use SERIALIZABLE?

SERIALIZABLE maximizes correctness but minimizes concurrency: it takes more locks (or aborts more transactions under MVCC), causing blocking, deadlocks, and serialization failures that you must retry. You pick the lowest level that prevents the anomalies your workload actually cares about.

Real World

Most OLTP runs at READ COMMITTED and adds explicit locking (SELECT ... FOR UPDATE) or optimistic versioning only where needed — rather than globally raising isolation and tanking throughput.

Interviewer's Expectation

Keywords they're listening for:

  • ACID meaning
  • four levels + anomalies prevented
  • default per DB
  • concurrency vs consistency trade-off
  • MVCC

Common Mistakes

  • Defaulting to SERIALIZABLE everywhere
  • Assuming all DBs share the same default
  • Ignoring retry handling at high isolation

Best Practices

  • Use the lowest level that prevents your anomalies
  • Add targeted locking/versioning where needed
  • Retry on serialization failures

Follow-up Questions

  1. Which anomalies does each level prevent?
  2. How does MVCC provide isolation without read locks?
  3. What is a serialization failure and how do you handle it?

Continue Learning with AI

Take this question deeper with your favourite AI assistant. Pick a depth, copy the prompt, or open it directly — AI is your learning companion, not a shortcut.

Plain-language foundations

I'm preparing for a software engineering interview and want to understand this from scratch, as a beginner.

Topic: Transactions (SQL)
Interview question: "Explain ACID and the four transaction isolation levels — which is the default?"

Please:
1. Explain the core idea in simple, plain language, using an everyday analogy.
2. Define any technical terms you use.
3. Walk through one small, concrete example.
4. Finish with a single sentence I can easily remember.

Keep the tone friendly and assume I'm new to this topic.
Open inChatGPTGeminiClaude

Was this answer helpful?

Affiliate links

Tools for Your Developer Workspace

Equipment for a comfortable, productive setup.

As an Amazon Associate I earn from qualifying purchases.