Engineering Fluency OS

Why This Matters

Databases serve many users at once. While you are reading a bank balance, someone else might be transferring money into your account. While an inventory system checks stock, another process is selling the last item. What data do you see? The old value? The new value? A mix of both? The answer depends on the isolation level.

Isolation levels control the tradeoff between correctness and performance in concurrent systems. Read Committed is the default in most databases and offers a reasonable balance. Serializable provides the strongest guarantees but is slower. Choosing the wrong level leads to subtle, hard-to-reproduce bugs -- the kind that only appear under heavy load in production.

Define Terms

Visual Model

Transaction A

WRITE x=50uncommitted

COMMIT

INSERT row

Transaction B

READ xsees 50? Dirty read!

READ x againchanged! Non-repeatable

SELECT countnew row! Phantom

Serializableprevents all anomalies

dirty?

changed?

phantom?

The full process at a glance. Click Start tour to walk through each step.

Higher isolation levels prevent more anomalies but reduce concurrency. Most apps use Read Committed.

Code Example

Code

-- Setting isolation level in SQL (PostgreSQL syntax)

-- Read Committed (default in PostgreSQL)
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;  -- sees committed data
COMMIT;

-- Repeatable Read
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;  -- snapshot taken here
-- Even if another transaction changes this row and commits,
-- this transaction will still see the original value
SELECT balance FROM accounts WHERE id = 1;  -- same result guaranteed
COMMIT;

-- Serializable (strictest)
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(balance) FROM accounts;  -- sees a consistent snapshot
-- If another transaction modifies accounts concurrently,
-- one of them will be aborted with a serialization error
COMMIT;

-- Demonstrating a non-repeatable read
-- Terminal 1:                    Terminal 2:
-- BEGIN;                         
-- SELECT balance FROM accounts   
--   WHERE id = 1;                
-- -- Returns: 1000               BEGIN;
--                                UPDATE accounts SET balance = 500
--                                  WHERE id = 1;
--                                COMMIT;
-- SELECT balance FROM accounts   
--   WHERE id = 1;                
-- -- Returns: 500 (changed!)     
-- -- This is a non-repeatable read
-- COMMIT;

Interactive Experiment

Try these exercises:

Open two database connections. In connection A, start a transaction and UPDATE a row without committing. In connection B, try to SELECT that row at Read Committed level. What do you see?
Repeat the experiment at Read Uncommitted level (if your database supports it). Can B see A's uncommitted change?
Demonstrate a non-repeatable read: have A read a value, B update and commit it, then A read again.
Try Serializable: have two transactions both read and update the same row. Does one get aborted?

Quick Quiz

Coding Challenge

Simulate Isolation Levels

Write a class `IsolationSimulator` that simulates two isolation levels. It has a shared `data` store and tracks transactions. At 'read-uncommitted' level, `read()` returns the latest written value even if uncommitted. At 'read-committed' level, `read()` only returns committed values. Implement `begin(level)`, `write(key, value)`, `read(key)`, `commit()`, and `rollback()`.

Loading editor...

Real-World Usage

Isolation levels affect every concurrent application:

Web applications: Most use Read Committed by default. Each HTTP request sees a consistent snapshot of committed data.
Financial systems: Bank transfers and payment processing often use Serializable to prevent double-spending and balance inconsistencies.
Inventory management: Serializable or explicit locking prevents selling the last item twice when two customers check out simultaneously.
Reporting queries: Long-running analytics queries use Repeatable Read or snapshot isolation to avoid seeing partial updates while scanning millions of rows.
Optimistic concurrency: Instead of high isolation levels, many apps use version columns to detect concurrent modifications and retry on conflict.

Isolation Levels