blog post

Complete Guide To Transaction Isolation Levels in SQL

Sponsor my blogs

Understanding transaction isolation levels in SQL is crucial for maintaining data integrity and performance in databases. This blog post explores the various isolation levels, their impact on data consistency and system performance, and how to implement them with code examples.

Introduction

When multiple transactions execute concurrently in a database, various concurrency issues may occur, such as dirty reads, non-repeatable reads, and phantom reads. To manage these issues, SQL databases have different transaction isolation levels, which control what data changes are visible for transactions that are running concurrently.

SQL transactions support the following isolation levels:

  1. Read Uncommitted
    • Description: The lowest level of isolation. Transactions may read changes made by other transactions even before they are committed, potentially leading to dirty reads.
    • Use Case: Suitable in scenarios where accuracy is not critical and performance is a most priority.
  2. Read Committed
    • Description: Default isolation level. Ensures that a transaction can only read data that has been committed before the transaction was started, preventing dirty reads but not non-repeatable reads.
    • Use Case: Commonly used for most of the applications to prevent dirty reads.
  3. Repeatable Read
    • Description: Prevents non-repeatable reads by ensuring that if a transaction reads a row, other transactions cannot modify or delete that row until the first transaction completes.
    • Use Case: Useful in applications requiring consistent reads during a transaction but can lead to phantom reads.
  4. Serializable
    • Description: The highest level of isolation. It simulates transactions being executed serially, thus preventing dirty reads, non-repeatable reads, and phantom reads.
    • Use Case: Critical for applications that require complete isolation and data integrity, but it can severely impact performance due to intense locking.

How To Set an Isolation Level

Let's have a look on examples how to set each isolation level for a transaction in different RDBMS.

MS SQL Server

sql
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;
-- Your SQL statements here
COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
-- Your SQL statements here
COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
-- Your SQL statements here
COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
-- Your SQL statements here
COMMIT TRANSACTION;

PostgreSQL

It is worth mentioning that PostgreSQL doesn't support READ UNCOMMITTED isolation level. As stated in official docs: PostgreSQL's Read Uncommitted mode behaves like Read Committed.

sql
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Your SQL statements here
COMMIT;

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Your SQL statements here
COMMIT;

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Your SQL statements here
COMMIT;

MySQL

sql
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
-- Your SQL statements here
COMMIT;

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- Your SQL statements here
COMMIT;

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
-- Your SQL statements here
COMMIT;

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
-- Your SQL statements here
COMMIT;

Advantages and Disadvantages of Isolation Levels

Now, as we briefly discussed each isolation level and their use cases, let's make a deep dive and explore the advantages and disadvantages of each isolation level.

  1. Read Uncommitted
    • Advantages:
      • Offers the highest performance among all isolation levels by allowing transactions to access uncommitted data, which minimizes lock contention and increases throughput.
    • Disadvantages:
      • Prone to dirty reads, where a transaction may read data that another transaction has written but not yet committed. This can lead to inconsistent and unreliable results if the other transaction is rolled back.
  2. Read Committed
    • Advantages:
      • Prevents dirty reads, ensuring that only committed data is visible to a transaction. Typically, offers better performance than higher isolation levels because it holds locks for a shorter duration.
    • Disadvantages:
      • Is susceptible to non-repeatable reads, where data read once in a transaction can change if read again due to other committed transactions.
  3. Repeatable Read
    • Advantages:
      • Prevents non-repeatable reads within the same transaction, ensuring that records read multiple times return the same data.
    • Disadvantages:
      • Can experience phantom reads, where new rows can be added by other transactions and be visible before the initial transaction completes. More locking overhead than lower isolation levels, potentially reducing concurrency.
  4. Serializable
    • Advantages:
      • Provides complete isolation from other transactions, preventing dirty reads, non-repeatable reads, and phantom reads.
    • Disadvantages:
      • Significantly reduces concurrency by locking large portions of the table or database, leading to potential performance bottlenecks. Highest potential for transaction serialization failures, requiring applications to handle transaction retries. Such transactions are vulnerable to deadlocks if two transactions wait for each other to unlock the same data.

Practical Examples of Isolation Levels Behavior

To demonstrate how different isolation levels behave in a practical scenario, let's consider two transactions working on the same data in a SQL database. Let's consider the following example with accounts table that has account_id and balance columns:

sql
CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    balance DECIMAL(10, 2)
);

INSERT INTO accounts VALUES (1, 1000.00);
INSERT INTO accounts VALUES (2, 2000.00);

Now, let's explore how two simultaneous transactions interact with each other under different isolation levels.

All SQL queries in the examples below were tested in the Postgres database. While the same SQL syntax can be used in other databases.

1. Read Uncommitted

Read Uncommitted is the lowest isolation level and can suffer from dirty reads. A dirty read occurs when a transaction may read data that another transaction has written but not yet committed.

Scenario: Transaction 1 reads the balance for account_id = 1, while Transaction 2 is updating the balance.

sql
-- Transaction 1
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 1;  -- Might read uncommitted data

-- Transaction 2
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
COMMIT;

-- Transaction 1 continues
SELECT balance FROM accounts WHERE account_id = 1;  -- Reads updated data
COMMIT;

Outcome: Transaction 1 may see the uncommitted change made by Transaction 2, leading to a dirty read. Uncommitted changes are the changes applied by INSERT, UPDATE, DELETE SQL commands, but are not committed to the database yet.

Read Committed

Read Committed isolation level solves dirty reads but can suffer from non-repeatable reads. A non-repeatable read occurs when data read once in a transaction can change if read again due to other committed transactions.

Scenario: Transaction 1 reads the same row multiple times, while Transaction 2 updates it between the reads of the 1st transaction.

sql
-- Transaction Transaction 1
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 1;  -- Reads committed data

-- Transaction Transaction 2
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
COMMIT;

-- Transaction Transaction 1 continues
SELECT balance FROM accounts WHERE account_id = 1;  -- Reads new committed data
COMMIT;

Outcome: Transaction 1 reads the balance twice and might see two different values if Transaction 2 commits between Transaction 1 SELECT statements. This is non-repeatable reads.

Repeatable Read

Repeatable Read isolation level solves dirty and non-repeatable reads but can suffer from phantom reads.

A phantom read occurs when a transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another committed transaction. This is different from a non-repeatable read in the number of rows returning from the query, rather than changes to the rows themselves.

Scenario: Transaction 1 reads multiple times all accounts that have balance more than or equal to 1000. Between these reads - Transaction 2 inserts a new account that has balance 2000.

sql
-- Transaction 1 starts
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts WHERE balance >= 1000; -- Returns 1 and 2 accounts

-- Meanwhile, Transaction 2 starts and commits
BEGIN;
INSERT INTO accounts (account_id, balance) VALUES (3, 1100.00);
COMMIT;

-- Transaction 1 continues and runs the same query again
SELECT * FROM accounts WHERE balance >= 1000; -- Now returns 1, 2 and 3 accounts
COMMIT;

Outcome: in Transaction 1, the second query unexpectedly includes an additional account, that was not part of the initial result set. This is a classic example of a phantom read. Transaction 1 reads a set of rows twice and finds more rows the second time due to the insertion committed by Transaction 2.

Serializable

Repeatable Read is the highest isolation level that solves dirty, non-repeatable reads and phantom reads. Serializable isolation level effectively locks the range of records accessed, preventing new records from being added that match the queries of the ongoing transactions. But it can significantly decrease the overall performance of the database queries because of hard locked data rows.

Scenario: let's explore a previous example when a phantom read occurred.

sql
-- Transaction Transaction 1
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM accounts WHERE balance >= 1000; -- Returns 1 and 2 accounts

-- Meanwhile, Transaction 2 starts and commits
BEGIN;
INSERT INTO accounts (account_id, balance) VALUES (3, 1100.00);
COMMIT;

-- Transaction 1 continues and runs the same query again
SELECT * FROM accounts WHERE balance >= 1000; -- Still returns 1 and 2 accounts
COMMIT;

Outcome: Both transactions operate as if they were executed serially and doesn't affect each other.

Summary

What Isolation Level to Select:

  1. Read Uncommitted. This isolation level offers the highest performance among all isolation levels. Use it when performance is crucial and when dirty reads won't have any impact on your application.
  2. Read Committed. This isolation level is enabled by default level and is recommended for most of the use cases as it prevents dirty reads and is highly performant.
  3. Repeatable Read. This isolation level is useful to prevent use cases when another transaction can modify rows of the currently running transaction. Consider that this isolation level is less performant than Read Committed.
  4. Serializable. This isolation level offers the highest data isolation and locking to prevent other transactions from adding new rows or modifying data. Consider this isolation level when it is critical for applications to have complete isolation and data integrity, but keep in mind that it can severely impact performance due to intense locking.

Consider the following advices when selecting an isolation level:

  • Evaluate Needs: Assess the criticality of data integrity versus system performance to choose the appropriate isolation level.
  • Testing: Always test the impact of changing isolation levels in a controlled environment before deploying changes to production.
  • Monitoring: Regularly monitor transaction performance and look for locks or other issues that could impact user experience.

Hope you find this blog post useful. Happy coding!

Improve Your .NET and Architecture Skills

Join my community of 1800+ developers and architects.

Each week you will get 2 practical tips with best practises and architecture advice.