Read Committed

Interviewers love this one because it tells them whether you understand what a transaction really promises, not just the vocabulary.

Question: What does READ COMMITTED mean in SQL transactions?

Answer: READ COMMITTED means each SQL statement can only see data that has already been committed by other transactions. It prevents dirty reads, so you will not read half-finished or rolled-back work. But if you run the same query twice inside one transaction, you may get a different result because a new commit happened between the two statements.

Interview-Ready Answer: I would say READ COMMITTED is the isolation level where every statement sees only committed data at the moment that statement starts. That means I do not read uncommitted changes, but I can still see different results on two reads in the same transaction if another transaction commits in between. It is a common middle ground: good concurrency, low blocking, but not strong enough if I need a stable snapshot for the whole transaction.

What READ COMMITTED promises

READ COMMITTED is one of the SQL isolation levels. The simple rule is: a statement never sees uncommitted data. That makes it safer than READ UNCOMMITTED, but weaker than REPEATABLE READ and SERIALIZABLE.

How it works under the hood

1. A transaction begins, but it does not get one forever-frozen view of the database.
2. Each statement gets its own view, called a snapshot in MVCC databases. MVCC means multi-version concurrency control, a design where the database keeps old row versions so readers and writers do not fight as much.
3. When a statement reads, it skips versions from transactions that are still open or later rolled back.
4. When a statement writes, the target rows are locked so two writers do not safely trample each other at the same instant.
5. If another transaction commits before your next statement starts, your next statement can see the newer committed row.

This is why people say READ COMMITTED gives you a fresh photo for every statement, not one album for the whole transaction.

What anomalies it blocks, and what it still allows

A useful way to think about isolation is to ask which problems are prevented. A dirty read means reading data that may still be rolled back. A non-repeatable read means the same row returns a different value when you read it twice. A phantom means a later query sees extra rows that match the same condition.

Important nuance: the exact behavior can vary by database engine. PostgreSQL uses MVCC and its READ COMMITTED means each statement gets a fresh snapshot. SQL Server also supports READ COMMITTED, but can optionally use row versioning with READ_COMMITTED_SNAPSHOT. MySQL InnoDB defaults to REPEATABLE READ, which surprises many candidates.

When and why to use it

• Good for normal web requests where a small change between statements is acceptable.
• Good when you want to reduce blocking and keep concurrency high.
• Good for inventory, checkout, and status pages if each write is atomic and guarded.
• Not enough by itself for money movement, counters, or any logic that reads, computes, and writes later without protection.

Performance and edge cases

There is no classic Big-O story here. The practical cost is usually low: a statement-level snapshot check plus normal row locks on writes. In real systems, the bigger cost comes from waiting on locks, retrying failed writes, or keeping transactions open too long. As a rule of thumb, short OLTP transactions are often kept under tens of milliseconds to a few hundred milliseconds when possible.

Two common edge cases matter in interviews. First, READ COMMITTED does not guarantee the same result twice inside one transaction. Second, it does not prevent lost updates if your application reads a value, does math in memory, and later writes back a stale result without a lock or version check. That is why READ COMMITTED is safe for visibility, but not a magic shield for business rules.

Real-world story

Picture an e-commerce checkout service that decrements stock when a customer buys the last item. The team uses READ COMMITTED, which is fine for seeing only committed inventory. One engineer, though, reads the stock in application code, stores it in memory, and later writes back a computed value based on that stale number.

What goes wrong? Two buyers click at nearly the same time. The first order commits, the second request still thinks the item is available, and the service either oversells or throws a late constraint error. In logs you might see one request succeed, the next wait on a row lock, then either update the row to an invalid value or fail a CHECK constraint. From the user side, symptoms look like duplicate order confirmations, out-of-stock items sold anyway, and customer support tickets about missing inventory.

The fix is not just changing isolation level. The safer pattern is an atomic update such as UPDATE ... WHERE stock > 0, or a stronger protection strategy like row locking or optimistic version checks. READ COMMITTED gives you clean visibility, but your write logic still has to be atomic.

Follow-up & Tricky Questions:

• How is READ COMMITTED different from REPEATABLE READ? READ COMMITTED lets later statements in the same transaction see newer committed data; REPEATABLE READ tries to keep the same view for the whole transaction.
• Does READ COMMITTED prevent dirty reads? Yes. That is its key guarantee: you do not see uncommitted changes from other transactions.
• Can READ COMMITTED still allow non-repeatable reads? Yes. If another transaction commits between two reads, the second read can return a different value.
• Is READ COMMITTED the default everywhere? No. PostgreSQL and SQL Server commonly default to it, Oracle also uses it, but MySQL InnoDB defaults to REPEATABLE READ.
• Can you still lose updates at READ COMMITTED? Yes, if your app does read-then-write logic without a guard. Use atomic updates, row locks, version columns, or a stronger isolation level when needed.
• Does one transaction always see the same rows? No. That is the subtle gotcha: each statement can see a newer committed world.
• Does a plain SELECT block writers? Usually no in MVCC databases. Reads and writes are designed to coexist, which is why concurrency is usually good at this level.
• Is READ COMMITTED enough for bank transfers? Not by itself. Money moves need atomic updates and often extra checks, because business correctness matters more than the basic isolation label.
• Tricky: If I run the same SELECT twice inside one transaction, should I expect the same answer? No. Under READ COMMITTED, the second statement can see newly committed data.
• Tricky: Does a stronger isolation level always mean slower? Usually more contention and retries, yes, but the real cost depends on workload, indexes, and how long you keep transactions open.

Common Mistakes:

• Mistake: Thinking READ COMMITTED means the whole transaction is frozen. Correction: Only each statement is protected from dirty reads; later statements can see newer commits.
• Mistake: Assuming it prevents all race conditions. Correction: It does not stop lost updates or business-rule bugs unless you use atomic writes or locks.
• Mistake: Saying every database has the same default. Correction: Defaults differ, and MySQL InnoDB is the classic gotcha because it defaults to REPEATABLE READ.
• Mistake: Mixing up dirty reads and non-repeatable reads. Correction: Dirty read = uncommitted data; non-repeatable read = committed data changes between two reads.

Memory Hook: New photo for every statement. If you remember that, you will remember the core behavior instantly: no dirty data, but no promise that the picture stays the same across the whole transaction.

Cheat Sheet:

• Prevents dirty reads.
• Allows non-repeatable reads and phantoms in the standard model.
• Each statement gets a fresh committed view.
• Great for everyday OLTP where slight drift is acceptable.
• Not enough for read-modify-write unless you add locks or version checks.
• Default isolation level depends on the database engine.

Practice Tasks:

• Write two separate transactions that prove the same SELECT can return different values under READ COMMITTED.
• Add a CHECK constraint or WHERE stock > 0 guard to prevent overselling in the inventory example.
• Compare the behavior of READ COMMITTED and REPEATABLE READ in your own database and note the default isolation level.