HardConcept

What are transaction isolation levels in SQL and what problems do they prevent?

Technology:
SQL
Experience:
Senior

Quick answer

Isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) control how much concurrent transactions can see of each other's changes, trading consistency against concurrency to prevent dirty reads, non-repeatable reads and phantom reads.

Detailed explanation

A dirty read means reading data another transaction has not committed. A non-repeatable read means reading the same row twice in one transaction and getting different values because someone committed in between. A phantom read means running the same query twice and getting a different set of rows because rows were inserted or deleted.

Read Uncommitted allows all three. Read Committed prevents dirty reads (the default in PostgreSQL, SQL Server and Oracle). Repeatable Read also prevents non-repeatable reads (the default in MySQL InnoDB, and it also prevents most phantoms there). Serializable prevents all three by making transactions behave as if they ran one after another, at the highest cost in locking or retries.

Choose the lowest level that is correct for the business rule, and use explicit locking (SELECT ... FOR UPDATE) or optimistic concurrency with a version column for specific hot spots such as inventory or balances.

Key points

  • Higher isolation = fewer anomalies but less concurrency
  • Defaults differ by database
  • Use row locks or version columns for critical updates

Follow-up questions

  • What is a deadlock and how do you prevent it?
  • What is MVCC?
  • SQLMedium

    What are ACID properties in a database?

    ACID stands for Atomicity, Consistency, Isolation and Durability: a transaction either fully succeeds or fully fails, keeps the data valid, does not interfere with other transactions, and survives crashes once committed.

    Mid-level 路 Senior 路 Database
  • System DesignMedium

    When should you use a SQL database vs a NoSQL database?

    Choose SQL when your data is relational and you need transactions, joins and strong consistency; choose NoSQL when you need flexible schemas, very high write throughput or horizontal scaling for a specific access pattern.

    Mid-level 路 Senior 路 Database
  • SQLHard

    How do you optimize a slow SQL query?

    Read the execution plan with EXPLAIN to find full scans and expensive joins, add or fix indexes for the filter and join columns, select only the columns and rows you need, and rewrite predicates so indexes can be used.

    Senior 路 Performance