Skip to content
Tutorials Center
My learning

Topics

Lesson 63 of 125 ยท Changing Data

Isolation Levels and Locking

Understand SQL isolation levels. Dirty, non-repeatable and phantom reads, lost updates, locking vs MVCC, SELECT FOR UPDATE, deadlocks, and how SQLite handles concurrency.

A database serving a real application never has just one user. Hundreds of transactions may read and write the same tables at the same moment: two customers buy the last tent, a report sums up balances while transfers are running, two admins edit the same product. Isolation, the "I" in ACID, decides how much those concurrent transactions can see of, and interfere with, each other.

Perfect isolation would make every transaction behave as if it ran alone, one after another. That's safe but slow, so databases offer several isolation levels that trade a little safety for a lot of speed. To choose well you need to know which anomalies each level allows, how databases enforce isolation (locks or row versions), and which coding patterns stay correct regardless of the level. That's what this lesson covers.

The examples below run in one browser connection, so they can't start two real sessions. Instead, they replay concurrent timelines step by step, which shows exactly how each problem arises and how to prevent it.

The anomalies

Each anomaly describes something a transaction can observe when others run at the same time. In the timelines, T1 and T2 are two transactions in different sessions, and time flows downward.

Dirty read

T1 reads data that T2 has changed but not committed yet. If T2 then rolls back, T1 has acted on a value that never officially existed.

Step T1 T2
1 UPDATE accounts SET balance = 0 WHERE id = 1;
2 SELECT balance ... WHERE id = 1; โ†’ 0
3 ROLLBACK; (balance is back to 1000)

Non-repeatable read

T1 reads the same row twice and gets different values, because T2 committed a change in between.

Step T1 T2
1 SELECT price ... WHERE id = 6; โ†’ 189.00
2 UPDATE products SET price = 199.00 WHERE id = 6; COMMIT;
3 SELECT price ... WHERE id = 6; โ†’ 199.00

Phantom read

T1 runs the same query twice and gets a different set of rows, because T2 inserted or deleted matching rows in between. The new rows are the "phantoms".

Step T1 T2
1 SELECT COUNT(*) FROM orders WHERE status = 'pending'; โ†’ 24
2 INSERT INTO orders (..., status) VALUES (..., 'pending'); COMMIT;
3 same query โ†’ 25

Lost update

Two transactions read the same value, each computes a new value from it, and both write. The second write silently overwrites the first. This one causes real-world bugs every day, so here it is as a runnable replay. Two sessions sell headlamps; each reads the stock into its own memory (simulated by a table) and then writes back what it computed:

SQL Replaying a lost update

Press Run (or Ctrl + Enter) to see the result here.

Three headlamps were sold, so the stock should be 7, but it's 8. Session A's sale is lost. Notice that each session's own logic was perfectly correct; the bug only exists because they overlapped.

Write skew

The subtlest anomaly. Two transactions read overlapping data, then each updates a different row based on what it read. Neither overwrote the other, yet together they break a rule. Classic example: a hospital requires at least one doctor on call. Two doctors on call each check "is someone else on call?", both see yes, and both go off call. Now nobody is on call. Only the strictest level, SERIALIZABLE, prevents this.

The four isolation levels

The SQL standard defines four levels by the anomalies they must prevent:

Level Dirty read Non-repeatable read Phantom read
READ UNCOMMITTED possible possible possible
READ COMMITTED prevented possible possible
REPEATABLE READ prevented prevented possible
SERIALIZABLE prevented prevented prevented

SERIALIZABLE goes further: the outcome must be the same as running the transactions one at a time in some order, which rules out lost updates and write skew too.

Real databases often do better than the minimum. PostgreSQL never allows dirty reads, even if you ask for READ UNCOMMITTED, and its REPEATABLE READ also prevents phantoms. Defaults differ as well:

Database Default level How it isolates
PostgreSQL Read Committed MVCC; Serializable uses serializable snapshot isolation (SSI)
MySQL (InnoDB) Repeatable Read MVCC snapshots for reads, plus record and gap locks for writes
SQL Server Read Committed locking by default; MVCC with READ_COMMITTED_SNAPSHOT (on by default in Azure SQL)
Oracle Read Committed MVCC; offers Read Committed and Serializable
SQLite Serializable one writer at a time for the whole database

You choose a level per transaction (or per session):

SQL
-- PostgreSQL: inside the transaction
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- ...
COMMIT;

-- MySQL: before the transaction starts
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

-- SQL Server: for the session
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

How databases isolate: locking vs MVCC

There are two main techniques, and most databases mix them.

Locking. Before reading a row, a transaction takes a shared lock; before writing, an exclusive lock. Many transactions can share-lock a row, but an exclusive lock waits until all other locks are released. Holding locks until commit (two-phase locking) gives strong isolation, but readers and writers block each other, and long reports can stall the whole application.

MVCC (multi-version concurrency control). Instead of overwriting a row, an update creates a new version of it and keeps the old one for a while. Each transaction reads from a consistent snapshot: it sees the versions that were committed when its snapshot was taken, and ignores newer ones. Readers never block writers and writers never block readers. Writers still lock the rows they change, so two transactions updating the same row still wait for each other.

This replay shows the idea behind MVCC. Each row version records which transaction created it and which one replaced it, and a snapshot only sees versions that existed at its moment:

SQL How MVCC snapshots see rows

Press Run (or Ctrl + Enter) to see the result here.

A transaction whose snapshot was taken at moment 110 still sees 1,000, even though later transactions changed the row twice; one at 125 sees 700, and one at 140 sees the current 450. Real databases also track which transactions committed, and a cleanup process (PostgreSQL's VACUUM, InnoDB's purge) removes versions no snapshot needs anymore.

The level decides when the snapshot is taken. Under READ COMMITTED, each statement gets a fresh snapshot, so two SELECTs in one transaction can disagree (a non-repeatable read). Under REPEATABLE READ in PostgreSQL and MySQL, the snapshot is taken once per transaction, so every read in it is consistent.

Patterns that prevent lost updates

Isolation levels help, but the most reliable fixes are in how you write your statements. They work at any level.

1. Let the database do the arithmetic

The lost update happened because the application read, computed and wrote back. A single UPDATE that computes from the current value is atomic: the database locks the row, reads the latest value and writes the result in one step. A second session waits for the lock and then sees the first session's result.

SQL Atomic updates

Press Run (or Ctrl + Enter) to see the result here.

Stock is 7, as it should be. Whenever you can express a change as SET x = x + ..., do.

2. Put the business rule in the WHERE clause

What if a sale must never push stock below zero? Checking with a SELECT first has the same race as before. Instead, make the rule part of the update and look at the result:

SQL Conditional update ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first update succeeds and leaves 4 units. The second returns no rows: the condition no longer holds, so nothing changed, and the application tells the second customer the item is sold out. No overselling, no matter how the two requests overlap.

3. Optimistic locking with a version column

When a user edits a record in a form for several minutes, you can't hold a transaction open the whole time. Instead, give the row a version number. Each save checks that the version is still the one the user loaded, and increments it:

SQL Optimistic locking

Press Run (or Ctrl + Enter) to see the result here.

Kim's save returns the row with version 2. Lu's returns nothing, so the application knows someone else changed the article and can show "this article was modified, please review the changes" instead of silently overwriting Kim's work. Many frameworks (Hibernate, Entity Framework, Rails) support this pattern out of the box. It's called optimistic because it assumes conflicts are rare and only detects them at the end.

4. Pessimistic locking with SELECT ... FOR UPDATE

When you must read a row, make a decision in application code and then write, you can lock the row as you read it. Other transactions that try to lock or change it wait until you commit:

SQL
-- PostgreSQL, MySQL, Oracle
BEGIN;

SELECT balance
FROM accounts
WHERE id = 1
FOR UPDATE;          -- row is locked until COMMIT or ROLLBACK

-- application checks the balance, then:
UPDATE accounts SET balance = balance - 300 WHERE id = 1;

COMMIT;

Variants: FOR SHARE (others can read-lock but not change), NOWAIT (fail immediately instead of waiting) and SKIP LOCKED (skip rows other sessions have locked, perfect for job queues where several workers grab the next free job). SQL Server uses table hints such as WITH (UPDLOCK, ROWLOCK) instead. SQLite has no FOR UPDATE; there you start the transaction with BEGIN IMMEDIATE, which takes the database's write lock up front.

Deadlocks

Locks introduce a new risk. Suppose T1 locks row A and then wants row B, while T2 has locked row B and wants row A. Each waits for the other, forever:

Step T1 T2
1 UPDATE accounts ... WHERE id = 1; (locks 1)
2 UPDATE accounts ... WHERE id = 2; (locks 2)
3 UPDATE accounts ... WHERE id = 2; waits for T2
4 UPDATE accounts ... WHERE id = 1; waits for T1: deadlock

Databases detect the cycle and break it by rolling back one transaction, the "victim", with an error: deadlock detected (PostgreSQL, SQLSTATE 40P01), error 1213 in MySQL, error 1205 in SQL Server. The other transaction then continues normally.

You can't rule deadlocks out entirely, but you can make them rare and harmless:

  • Lock in a consistent order. If every transfer updates the lower account id first, the cycle above can't form.
  • Keep transactions short so locks are held briefly.
  • Index the columns in your WHERE clauses, so updates lock only the rows they need instead of scanning and locking many.
  • Retry. Treat deadlocks (and serialization failures, SQLSTATE 40001) as normal events: catch the error and run the whole transaction again.

Blocking, livelock and lock hints

Not every wait is a deadlock. Three situations look similar from the application's side ("my query is stuck") but have different causes and cures.

Situation What happens Does the database fix it?
Blocking T2 wants a lock that T1 holds, so T2 waits until T1 commits or rolls back Yes, as soon as T1 finishes. A long T1 means a long wait
Deadlock T1 and T2 each hold a lock the other needs: a cycle Yes: the engine detects the cycle and kills one victim
Livelock Transactions keep running and retrying but never make progress, for example two workers that each back off and retry in lock step, or a writer starved by an endless stream of readers No: nothing is blocked, so nothing is detected. You need randomized back-off, fair queuing or a smaller critical section

Blocking is normal and healthy in short bursts; it is how locks protect your data. It becomes a problem when a transaction stays open (a forgotten BEGIN, a user thinking in the middle of a form, a slow report) and everyone behind it queues up. To investigate, ask the engine who is blocking whom: pg_locks with pg_stat_activity in PostgreSQL, sys.dm_exec_requests (the blocking_session_id column) in SQL Server, performance_schema.data_lock_waits in MySQL.

Choosing what to do when a row is locked

Pessimistic locking with SELECT ... FOR UPDATE (see above) lets you decide what happens when another session already holds the row:

Behaviour PostgreSQL / MySQL 8 / Oracle SQL Server
Wait (the default) FOR UPDATE default locking
Fail immediately FOR UPDATE NOWAIT WITH (NOWAIT) or SET LOCK_TIMEOUT 0 (error 1222)
Skip locked rows FOR UPDATE SKIP LOCKED WITH (READPAST)

SKIP LOCKED and READPAST are the tools for job queues: several workers each claim "the next queued job", and a worker simply skips jobs another worker is already handling instead of waiting for it. They are wrong for ordinary reads, because the result silently omits rows.

The claim itself must be atomic: choose and mark the job in one statement. SQLite has no row locks, but its single writer plus UPDATE ... RETURNING shows the shape:

SQL Claiming the next job atomically

Press Run (or Ctrl + Enter) to see the result here.

In PostgreSQL you would add FOR UPDATE SKIP LOCKED inside the subquery, so two workers running at the same moment cannot pick the same row and cannot wait for each other either:

SQL
-- PostgreSQL / MySQL 8 / Oracle
UPDATE jobs SET status = 'running', worker = 'A'
WHERE id = (
  SELECT id FROM jobs
  WHERE status = 'queued'
  ORDER BY id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id, task;

SQL Server: NOLOCK and snapshot isolation

SQL Server readers take shared locks by default, so a long update can block reports. A popular "fix" is the WITH (NOLOCK) table hint, which is the same as READ UNCOMMITTED:

SQL
-- SQL Server
SELECT COUNT(*) FROM orders WITH (NOLOCK);

It never waits, but it pays for that with dirty reads, and also with rows that appear twice or not at all when pages are split during the scan. Use it only where approximate numbers are acceptable. The correct fix for "readers block writers" is row versioning, which gives readers a consistent snapshot without locks:

SQL
-- SQL Server
ALTER DATABASE shop SET READ_COMMITTED_SNAPSHOT ON;   -- Read Committed now uses row versions
ALTER DATABASE shop SET ALLOW_SNAPSHOT_ISOLATION ON;  -- allows SET TRANSACTION ISOLATION LEVEL SNAPSHOT

With READ_COMMITTED_SNAPSHOT every statement sees the data as of its own start; with SNAPSHOT isolation the whole transaction sees the data as of its start, like PostgreSQL's Repeatable Read. Writers still block writers, and the row versions live in tempdb, so watch its size. This is the same MVCC idea described earlier, and it is what Azure SQL Database uses by default.

SQLite's concurrency model

SQLite is designed for one application process (or a few) using a local file, and its model is much simpler:

  • One writer at a time for the whole database file. Writes never interleave, so every transaction is effectively SERIALIZABLE.
  • In the default rollback-journal mode, a writer needs exclusive access when it commits, so readers and the writer briefly block each other.
  • In WAL mode (PRAGMA journal_mode = WAL;), readers read from a snapshot and don't block the writer, and the writer doesn't block readers. Most applications should enable it.
  • A second writer gets SQLITE_BUSY ("database is locked"). Set PRAGMA busy_timeout = 5000; so it waits up to 5 seconds instead of failing at once, and use BEGIN IMMEDIATE for transactions that will write, so the lock is taken at the start rather than in the middle.
SQL SQLite's settings

Press Run (or Ctrl + Enter) to see the result here.

The journal mode is delete, SQLite's default rollback-journal mode, which deletes the journal file after each commit. This in-browser database has no other connections to wait for, so the busy timeout is left at 0. read_uncommitted is 0; it only matters for connections that share a cache, the one case where SQLite allows dirty reads.

Choosing a level

  • Start with your database's default (usually Read Committed) and write statements that are safe under it: atomic updates, conditions in WHERE, unique constraints, optimistic version checks.
  • Use Repeatable Read (or a snapshot) for reports that must see one consistent picture across several queries.
  • Use Serializable for complex rules that span several rows (like the on-call example), and be ready to retry on serialization failures.
  • Avoid Read Uncommitted except for rough monitoring queries where wrong numbers are acceptable.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. T1 reads a product's price twice in one transaction and gets 189 the first time and 199 the second, because T2 committed a change in between. What is this called?

    The same row read twice gave different committed values. A dirty read would involve uncommitted data; a phantom involves new or removed rows.
  2. 2. Stock is 10. Sessions A and B both run UPDATE products SET stock = stock - 1 WHERE id = 1; at the same time under Read Committed. What is the final stock?

    Each UPDATE reads the latest committed value while holding the row lock, so the second one waits and then subtracts from 9. Atomic updates don't lose writes.
  3. 3. Which isolation level must prevent dirty reads but may allow non-repeatable reads?

    Read Committed only shows committed data, but each statement may see newer commits than the previous one.
  4. 4. An optimistic-locking save runs UPDATE articles SET title = 'New', version = version + 1 WHERE id = 1 AND version = 4 RETURNING id; and returns no rows. What does that mean?

    No row matched id = 1 with version 4, so another save changed the version (or the row is gone). The application should reload instead of overwriting.
  5. 5. How does the database usually resolve a deadlock?

    Deadlock detection picks a victim and rolls it back. The application should retry that transaction.

Summary

  • Isolation controls what concurrent transactions see of each other.
  • Anomalies: dirty read (uncommitted data), non-repeatable read (a row changes between reads), phantom read (new or removed rows), lost update and write skew.
  • Levels: Read Uncommitted, Read Committed, Repeatable Read, Serializable. Most databases default to Read Committed; MySQL to Repeatable Read; SQLite is always serializable.
  • Locking makes transactions wait; MVCC gives each transaction a snapshot so readers and writers don't block each other.
  • Prevent lost updates with atomic SET x = x - 1, conditions in WHERE, optimistic version columns, or SELECT ... FOR UPDATE.
  • Deadlocks are resolved by rolling back a victim: lock in a consistent order, keep transactions short and retry.
  • Blocking is a normal wait for a lock, livelock is endless retrying without progress. Use NOWAIT to fail fast and SKIP LOCKED (READPAST in SQL Server) for job queues; prefer snapshot isolation over NOLOCK.
  • SQLite allows one writer at a time; use WAL mode, busy_timeout and BEGIN IMMEDIATE.

Next: Importing and Exporting Data, where you'll move data in and out of a database in bulk.