Isolation Levels in SQL
Introduction
Transaction isolation defines how concurrent database work can observe and affect other work. It determines whether one transaction can see tentative values, whether repeated queries remain stable, and whether the database blocks or rejects operations that cannot safely coexist.
The SQL standard names four isolation levels, but real databases implement them with different combinations of locks, multiversion concurrency control, snapshots, and conflict detection. This tutorial explains the standard model, practical anomalies, vendor behavior, performance tradeoffs, application patterns, and repeatable ways to test the guarantees that matter.
What Are Isolation Levels?
An isolation level is a contract governing the visible effects of concurrent transactions. Lower levels permit more interleaving and may increase throughput, while stronger levels prevent or detect more anomalies at the cost of blocking, version retention, or transaction retries.
Isolation does not mean transactions literally run one at a time. A database can execute them concurrently while producing results equivalent to the promises of the chosen level.
Why Isolation Levels Matter
A query that is correct with one user can fail under concurrency. Two buyers may reserve the last item, two withdrawals may spend the same balance, or a report may combine values from different moments. Such defects are difficult to reproduce because they depend on timing.
Isolation provides a vocabulary for those risks. Engineers can identify the anomaly that would violate a business invariant, select a suitable database mechanism, and implement retry or conflict handling where required.
Understanding Concurrent Transactions
A transaction performs reads and writes between its start and commit or rollback. When transactions overlap, one may read a version another changes, wait for a lock, overwrite a value, or receive a conflict. The engine's concurrency-control model decides which outcome is allowed.
Correct application design also matters. Even strong isolation cannot make an unrelated set of statements one business transaction, and weak read-modify-write code may lose updates unless it uses an atomic predicate, lock, or version check.
The Four Standard Isolation Levels
| Level | General guarantee | Common tradeoff |
|---|---|---|
| Read Uncommitted | Weakest standard read isolation | May expose tentative data |
| Read Committed | Ordinary reads see committed data | Repeated statements can differ |
| Repeatable Read | Previously read data remains stable | More locks, versions, or conflicts |
| Serializable | Outcome matches an allowed serial order | More blocking or aborted transactions |
The table is a conceptual guide. PostgreSQL, MySQL, SQL Server, Oracle, and SQLite do not provide identical behavior for each label.
Isolation and ACID
Isolation is the third ACID property. Atomicity controls all-or-nothing completion, consistency protects implemented invariants, and durability preserves acknowledged commits. Isolation ensures concurrent execution does not violate the interaction guarantees required by those invariants.
A transaction can be atomic and durable yet still make a wrong decision from a concurrency anomaly. ACID properties must be designed together.
Understanding Transaction Anomalies
An anomaly is an outcome that becomes possible when operations interleave. Standard discussions focus on dirty reads, non-repeatable reads, and phantom reads, but production systems also care about dirty writes, lost updates, read skew, and write skew.
Not every anomaly matters to every workflow. The task is to connect one anomaly to a concrete business failure rather than selecting an isolation level only by name.
Dirty Read
A dirty read occurs when one transaction reads another transaction's uncommitted value. If the writer later rolls back, the reader acted on data that never became accepted state. A report could show nonexistent revenue or a service could trigger an invalid action.
Read Committed and stronger common implementations prevent ordinary dirty reads. Some MVCC databases map Read Uncommitted to stronger behavior rather than exposing uncommitted versions.
Non-Repeatable Read
A non-repeatable read occurs when one transaction reads the same row twice and gets different committed values because another transaction updated it between statements. Both values were valid at their respective moments, but the first did not remain stable.
This is common at Read Committed. Repeatable Read generally stabilizes rows or a transaction snapshot, though implementation details differ.
Phantom Read
A phantom occurs when repeating a predicate query returns a different set of rows because another transaction inserted, deleted, or changed rows matching that predicate. A count of open reservations can change even if rows already examined remain stable.
Serializable prevents standard phantom anomalies. Repeatable Read may or may not, depending on snapshot and locking semantics.
Lost Update
A lost update happens when concurrent transactions read one value, calculate independent results, and later writes cause one effect to overwrite the other. Both requests may report success while one change disappears.
Prevent it with atomic SQL such as SET quantity = quantity - 1, conditional updates, row locks, serializable isolation, or optimistic version columns. Read isolation alone is not always sufficient.
Dirty Write
A dirty write would let one transaction overwrite another transaction's uncommitted write. If either later rolled back, restoring the correct prior value would be ambiguous. Mainstream transactional databases prevent dirty writes even at their weakest practical isolation.
This protection should not be confused with lost-update protection, which concerns committed or snapshot-based decisions and can still require explicit handling.
Write Skew
Write skew occurs when transactions read a shared condition, update different rows, and together violate a cross-row invariant. Two doctors can each observe that another is on duty and independently take themselves off duty.
Snapshot isolation may allow this because the writes do not touch the same row. Serializable execution, a shared guard row, explicit predicate locking, or a redesigned constraint can prevent it.
Read Skew
Read skew occurs when related values are read from different committed moments. A report might read one account before a transfer and another account after it, showing a total that never existed as one consistent state.
A transaction-level snapshot can provide a coherent point-in-time view. At statement-level Read Committed, each query may legitimately see a newer committed state.
Anomaly Comparison
| Anomaly | What changes | Typical protection |
|---|---|---|
| Dirty read | Uncommitted value is observed | Read Committed or stronger |
| Non-repeatable read | One row changes between reads | Repeatable Read or transaction snapshot |
| Phantom | Predicate result set changes | Serializable or product-specific range protection |
| Lost update | One write effect disappears | Atomic update, lock, version check, or stronger isolation |
| Write skew | Different-row writes violate a shared rule | Serializable or explicit invariant protection |
Read Uncommitted
Read Uncommitted is the weakest standard level and may allow dirty reads. It can reduce read blocking in lock-based products, but results may contain values that later disappear or combinations that were never committed together.
It is unsuitable for financial decisions, inventory allocation, workflow transitions, or anything that triggers external effects. Even for reporting, understand the exact engine implementation.
Read Uncommitted Example
-- Session A
BEGIN;
UPDATE accounts SET balance = 0 WHERE account_id = 101;
-- Do not commit yet.
-- Session B at Read Uncommitted
SELECT balance FROM accounts WHERE account_id = 101;
-- Session A
ROLLBACK;
If Session B sees zero, it performed a dirty read. PostgreSQL will not expose that uncommitted value because it treats Read Uncommitted like Read Committed.
Read Committed
Read Committed ensures an ordinary query does not see another transaction's uncommitted writes. In many implementations, each statement obtains a fresh committed view, so two selects in one transaction can return different results.
This level is a widely used default because it balances concurrency and safety. It still requires explicit protection for read-modify-write logic and multi-statement assumptions.
Read Committed Example
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT price FROM products WHERE product_id = 7001;
-- Another transaction commits a price update here.
SELECT price FROM products WHERE product_id = 7001;
COMMIT;
The second query may return a different committed price. If the operation requires a stable value, lock the row, use a stronger snapshot, or encode the expected value in a conditional update.
PostgreSQL Read Committed
PostgreSQL's default Read Committed level gives each command a snapshot as of that command's start. A query sees committed rows from before its snapshot plus its own earlier changes. Later statements can see newly committed data.
An updating statement can wait for a concurrent writer and then re-evaluate its predicate against the updated row. Understanding this behavior matters for conditional updates.
Repeatable Read
Repeatable Read aims to keep data read by a transaction stable. Lock-based engines may retain shared locks, while MVCC engines often provide one transaction snapshot. This supports coherent multi-query calculations and prevents ordinary non-repeatable reads.
The name does not guarantee identical phantom or write-skew behavior across databases. Always consult product semantics.
Repeatable Read Example
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE account_id = 101;
-- Another transaction commits an update.
SELECT balance FROM accounts WHERE account_id = 101;
COMMIT;
The repeated read normally returns the same value within this transaction. A later write may still block, conflict, or fail depending on the engine and concurrent changes.
Repeatable Read and Phantoms
The SQL standard associates Repeatable Read with possible phantoms, but PostgreSQL's snapshot-based implementation prevents phantoms within the transaction snapshot. InnoDB's behavior combines consistent reads with next-key locking for locking queries and has its own nuances.
This is why a generic anomaly chart cannot replace vendor documentation and two-session tests.
Serializable
Serializable is the strongest standard level. Transactions may run concurrently, but committed results must be equivalent to some valid serial order. Engines achieve this through strict locking, predicate or range protection, serializable snapshot validation, or combinations.
More transactions may wait, deadlock, or abort. The application must treat serialization failures as expected control flow and retry safely.
Serializable Example
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*)
FROM doctor_schedule
WHERE shift_id = 80 AND on_call = TRUE;
UPDATE doctor_schedule
SET on_call = FALSE
WHERE shift_id = 80 AND doctor_id = 17;
COMMIT;
If concurrent transactions could leave no doctor on call, a serializable engine may block or reject one. The application then rolls back and retries from the beginning.
Serializable Retries
A retry must open a new transaction, reread current state, and execute the complete business operation. Retrying only the failed statement can preserve assumptions from an invalid snapshot.
Use bounded exponential backoff with jitter, classify vendor error codes, preserve one idempotency key across attempts, and return a controlled conflict when the retry limit is exhausted.
Snapshot Isolation
Snapshot isolation gives a transaction a stable committed snapshot and usually rejects conflicting writes to the same row. Readers avoid many locks, which improves concurrency for mixed workloads.
Snapshot isolation is not always serializable because write skew can occur across different rows. Some products offer both snapshot and serializable snapshot modes with stronger dependency detection.
MVCC
Multiversion concurrency control stores or reconstructs multiple row versions so readers can see an appropriate snapshot while writers create newer versions. It reduces reader-writer blocking but adds version storage, cleanup, and transaction-age concerns.
Long-running transactions can retain old versions and delay vacuum or cleanup. MVCC changes how isolation is implemented; it does not remove the need to choose a correct level.
Database Locks
Locks coordinate access to rows, keys, ranges, pages, or tables. Shared locks protect reads in some modes, exclusive locks protect writes, and range locks can prevent phantoms. Lock duration and compatibility depend on the engine and isolation level.
Indexes influence locking because they help the database locate a narrow key range. Poor access paths can touch and lock more data, increasing contention.
Pessimistic Locking
Pessimistic locking assumes conflict is likely and obtains a lock before making the decision. A query such as SELECT ... FOR UPDATE can reserve rows for a later update in products that support it.
Keep the transaction short, lock resources in a consistent order, and avoid user interaction while holding locks. Pessimistic locking trades waiting and deadlock risk for explicit coordination.
Optimistic Locking
Optimistic locking assumes conflicts are uncommon and detects them during update. A version column is read with the row and included in the write predicate.
UPDATE products
SET price = 39.99,
version = version + 1
WHERE product_id = 7001
AND version = 14;
If zero rows are affected, another transaction changed the row. The application reloads, merges or rejects the change, and never treats the no-op as success.
Deadlocks
A deadlock occurs when transactions wait in a cycle. Stronger isolation and longer lock duration can increase opportunities, but deadlocks can occur at common levels whenever resources are acquired in inconsistent order.
The database aborts a participant to break the cycle. Retry the complete idempotent transaction with bounded backoff, and reduce recurrence through consistent lock order, selective indexes, and short boundaries.
Isolation and Performance
Stronger isolation can add blocking, version retention, conflict checks, and retries. Weaker isolation may reduce some overhead but shift complexity into application logic and increase correctness risk.
Measure realistic contention, not only single-session throughput. An isolation change that improves a benchmark but allows duplicate allocation is not an optimization.
PostgreSQL Isolation Levels
PostgreSQL accepts the four standard names, but Read Uncommitted behaves as Read Committed. Repeatable Read uses a transaction snapshot and prevents phantom reads, while Serializable adds Serializable Snapshot Isolation and can abort transactions with serialization failures.
Applications must retry those failures. Statement errors also normally leave a transaction aborted until rollback or rollback to an appropriate savepoint.
MySQL InnoDB Isolation Levels
InnoDB supports the standard levels and commonly defaults to Repeatable Read. Consistent nonlocking reads use snapshots, while locking reads and writes use locks that can include next-key ranges. Read Committed creates fresher read views and changes some locking behavior.
Results also depend on query type, index, and storage engine. Nontransactional engines do not provide the same guarantees.
SQL Server Isolation Levels
SQL Server commonly defaults to lock-based Read Committed. The database option READ_COMMITTED_SNAPSHOT can implement Read Committed reads with row versions. Explicit SNAPSHOT isolation requires configuration and detects update conflicts.
SQL Server also provides Repeatable Read and Serializable lock-based levels. Table hints can modify behavior but should not replace a coherent transaction design.
Oracle Isolation Levels
Oracle provides Read Committed, Serializable, and Read Only transaction modes rather than implementing every standard name as a separate mode. Read consistency uses undo information so queries see a consistent view as of their statement or transaction snapshot.
Oracle Serializable can reject updates when a row changed after the transaction began. Applications should handle the product-specific error and retry where appropriate.
SQLite Isolation Behavior
SQLite serializes writes through its locking and journaling model while allowing read concurrency according to rollback-journal or WAL mode. Separate connections normally do not see uncommitted changes. PRAGMA read_uncommitted has limited effect and requires shared-cache conditions for dirty visibility.
Transaction start modes such as deferred, immediate, and exclusive influence when locks are obtained. SQLite should be tested as its own concurrency model rather than mapped mechanically to a server database.
Predicate and Range Protection
Row locks protect rows that already exist, but some invariants concern the absence of a row or every row matching a condition. Preventing another transaction from inserting a new matching row requires predicate-aware conflict detection or range locking rather than only locking the current result set.
Serializable engines solve this differently. A lock-based engine may lock index ranges, while a serializable snapshot engine can track read-write dependencies and abort an unsafe transaction. Useful indexes narrow protected ranges and reduce unnecessary contention. Application code should express the real predicate clearly so the database can apply the appropriate mechanism.
Read-Only Transactions
A read-only transaction communicates that no writes are intended and can support consistent reporting snapshots. Some databases can optimize or enforce the mode, while others treat it primarily as a transaction characteristic. It does not automatically make a long query inexpensive.
Choose the isolation level based on whether all report queries must describe one point in time. Close the transaction promptly because long snapshots can retain old versions, delay cleanup, and increase storage pressure even when they acquire few traditional locks.
Changing Isolation Safely
Changing isolation should begin with a specific anomaly or contention problem, followed by a two-session reproduction and a production-like load test. Compare correctness, blocking time, deadlocks, abort rate, version growth, and end-to-end latency. Do not evaluate only average query duration.
Roll out connection or database-default changes gradually and confirm that every application pool, background worker, and migration tool receives the intended setting. A safer pattern is often to use a transaction-specific override for the small number of workflows that require stronger guarantees, leaving unrelated workloads unchanged.
Setting an Isolation Level
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Transaction work here
COMMIT;
Syntax, timing, and session versus transaction scope vary. Some systems require the level to be set before the first statement, and a driver may expose constants instead of raw SQL. Changing a global default affects every workload and requires broader testing.
Isolation and Autocommit
In autocommit mode, each statement is usually its own transaction. A repeatable snapshot has little value when the transaction ends after one query. Multi-statement consistency requires an explicit transaction on one connection.
Autocommit is suitable for independent operations, but it cannot protect an invariant spanning several statements.
Connection Pools
Isolation is often connection state. A pooled connection can retain a level changed by a previous request unless the pool resets it. Unexpectedly stronger isolation can cause blocking; unexpectedly weaker isolation can permit anomalies.
Set transaction requirements explicitly, use framework-managed scopes, and verify pool reset behavior. Always finish the transaction before returning the connection.
Isolation with Java JDBC
try (Connection connection = dataSource.getConnection()) {
connection.setAutoCommit(false);
connection.setTransactionIsolation(
Connection.TRANSACTION_SERIALIZABLE
);
try {
performBusinessOperation(connection);
connection.commit();
} catch (SQLException ex) {
connection.rollback();
throw ex;
}
}
Check driver and database support with metadata. Frameworks may set isolation through transaction annotations, but retry behavior still belongs at a boundary that can rerun the complete operation.
Banking Example
Concurrent withdrawals must not spend the same balance. An atomic conditional update can subtract only when funds are sufficient, or a locked row can serialize balance decisions. A unique transfer identifier prevents duplicate retries.
Serializable isolation may be appropriate for cross-row rules, but a well-designed atomic statement can often protect a single-row balance efficiently.
Inventory and Ticket Booking
Inventory should use a conditional decrement and verify one affected row. Ticket booking should use a unique constraint on event and seat. These write-boundary rules handle races that an earlier availability check cannot prevent.
Isolation supports the workflow, while constraints remain the final defense. The losing request should roll back and return a controlled conflict.
Reporting Workloads
A multi-query financial report may need one transaction snapshot so totals and details describe the same point in time. Read Committed statement snapshots can produce read skew across separate queries.
Long report transactions can retain old versions and increase cleanup pressure. Consider a read replica, exported snapshot, materialized report, or analytical store when reports are expensive.
Choosing the Right Isolation Level
Begin with the invariant and write down harmful schedules. Identify whether dirty reads, changing rows, phantoms, lost updates, or write skew could break it. Then choose the narrowest mechanism that prevents or detects those schedules.
Use constraints and atomic statements where possible, stronger isolation where the rule spans data, and explicit conflict handling in every case. Document product-specific assumptions.
Testing Isolation Levels
Isolation tests require at least two independent connections and controlled synchronization. Start transactions, pause after specific reads or writes, execute the competing operation, then observe blocking, returned values, commit success, or error codes.
Use barriers or latches in automated integration tests instead of timing sleeps. Run against the same database family and configuration as production because mocks and embedded substitutes can behave differently.
Testing Repeatable Reads and Phantoms
For a repeatable-read test, Session A reads one row, Session B commits an update, and Session A reads again. For a phantom test, Session A queries a predicate, Session B inserts a matching row and commits, and Session A repeats the predicate.
Record whether Session B blocks and whether Session A sees the change. Repeat at each supported level to create an engine-specific behavior matrix.
Monitoring Isolation Problems
Track lock waits, deadlocks, serialization failures, optimistic conflicts, retry attempts, transaction age, snapshot retention, and query latency by isolation level. Correlate them with endpoints or jobs using safe request identifiers.
A conflict is not always a defect; it may be the correct prevention mechanism. Alerts should focus on excessive rates, exhausted retries, user impact, and long-held resources.
Common Isolation Mistakes
- Assuming the same level name behaves identically in every database.
- Using Read Uncommitted to fix a locking problem without assessing incorrect results.
- Believing Repeatable Read is always serializable.
- Ignoring lost updates because dirty reads are prevented.
- Retrying only the failed statement after a serialization error.
- Holding locks while calling external services or waiting for users.
- Changing a global default to solve one workflow.
- Returning pooled connections with altered isolation or open transactions.
- Testing concurrency with only one connection.
- Using stronger isolation before optimizing predicates and indexes.
Isolation Best Practices
- Define the business invariant before choosing a level.
- Map each unacceptable outcome to a known anomaly.
- Prefer atomic SQL and constraints for local rules.
- Keep transactions short and connection-scoped.
- Use consistent lock order and selective indexes.
- Handle deadlocks and serialization failures with bounded retries.
- Use idempotency keys across retry attempts.
- Set and reset connection isolation deliberately.
- Test real concurrent schedules on the production database family.
- Monitor blocking, versions, conflicts, and retry exhaustion.
Interview Questions and Answers
What are the four standard isolation levels?
Read Uncommitted, Read Committed, Repeatable Read, and Serializable.
Which level prevents dirty reads?
Read Committed and stronger common implementations prevent ordinary dirty reads.
Is Snapshot Isolation the same as Serializable?
No. Snapshot isolation can permit write skew unless the database adds serializable dependency detection.
Why does Serializable require retries?
The database may abort a transaction whose concurrent execution cannot be placed into a valid serial order.
How can lost updates be prevented?
Use atomic conditional updates, row locks, optimistic version checks, or an isolation mode that detects the conflict.
Practical Exercises
- Use two sessions to demonstrate a non-repeatable read at Read Committed.
- Repeat the experiment at Repeatable Read and record the outcome.
- Test a predicate query for phantom behavior at each supported level.
- Build a lost-update example and fix it with a version column.
- Create an atomic inventory decrement with an affected-row check.
- Model write skew and test snapshot versus serializable behavior.
- Capture the database error used for serialization failure.
- Implement a bounded whole-transaction retry in JDBC.
- Verify that your connection pool resets isolation state.
- Document the default and supported levels in your project database.
Quick Reference
| Concept | Meaning |
|---|---|
| Read Uncommitted | Weakest standard isolation |
| Read Committed | Ordinary reads see only committed data |
| Repeatable Read | Previously read state remains stable by product rules |
| Serializable | Committed result matches a serial execution |
| Snapshot | Reads a consistent set of committed row versions |
| MVCC | Maintains versions to coordinate readers and writers |
| Pessimistic lock | Reserves data before the decision |
| Optimistic lock | Detects a concurrent change at write time |
Isolation Selection Workflow
- Define the business invariant and transaction boundary.
- List the reads and writes used to enforce it.
- Construct harmful concurrent schedules.
- Name the resulting anomaly or conflict.
- Add constraints and atomic statements first.
- Select the database isolation behavior that closes remaining gaps.
- Implement deadlock, serialization, and optimistic-conflict handling.
- Test with coordinated independent connections.
- Measure blocking, version retention, retries, and latency.
- Document engine-specific assumptions and connection settings.
- Monitor production contention and correctness signals.
- Revisit the choice when workloads or invariants change.
Key Takeaway
SQL isolation levels define which concurrent schedules a transaction can observe and which conflicts the database prevents or reports. Read Uncommitted, Read Committed, Repeatable Read, and Serializable are useful names, but their real guarantees come from each engine's locking, MVCC, snapshot, and validation behavior.
Choose isolation from concrete business anomalies, not from a universal ranking. Combine the selected level with constraints, atomic SQL, short transactions, correct indexing, idempotent retries, connection-state management, and real concurrency tests. The result is a design that balances throughput with the exact correctness the workflow requires.