Database Isolation Levels, Anomalies, and How to Handle Them with Django

Database Isolation Levels and Anomalies

Isolation levels define the degree to which transactions are isolated from one another, preventing various types of anomalies. This article clarifies these concepts by summarizing the different isolation levels and their associated anomalies [1].

Let’s imagine a production scenario where we have a table representing an account:

CREATE TABLE account (
id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
balance NUMERIC,
name TEXT
);
INSERT INTO account VALUES (1, 0.00, 'Maria');

Uncommitted Read Isolation Level and the Dirty Read Anomaly

The dirty read anomaly occurs when a transaction reads data that has been modified by another transaction but not yet committed. This anomaly is possible only at the uncommitted read isolation level.

-- First transaction: Maria tries to add 100 to her account:
BEGIN TRANSACTION;
UPDATE account SET balance = balance + 100 WHERE name = 'Maria';

-- Second transaction: Reads Maria's balance before Maria's transaction commits
BEGIN TRANSACTION;
SELECT balance FROM account WHERE name = 'Maria'; -- Dirty read: Shows 100

-- First transaction: Fails due to a crash
ROLLBACK;

Outcome: The second transaction sees an updated balance of 100, even though Maria’s transaction was never committed. This is a classic example of a dirty read, where uncommitted data is read, leading to potential inconsistencies.

Committed Read Isolation Level and the Unrepeatable Read Anomaly

At the committed read isolation level, dirty reads are prevented because transactions can only see data that has been committed. However, unrepeatable read anomalies can still occur, where a transaction reads different values in subsequent reads within the same transaction.

-- First transaction: Maria adds 100 to her account
BEGIN TRANSACTION;
UPDATE account SET balance = balance + 100 WHERE name = 'Maria';

-- Second transaction: Reads Maria's balance
BEGIN TRANSACTION;
SELECT balance FROM account WHERE name = 'Maria';  -- Shows 0, because the previous transaction is not committed

-- First transaction: Maria commits the transaction
COMMIT;

-- Second transaction: reads Maria's balance again
SELECT balance FROM account WHERE name = 'Maria'; -- Shows 100

Outcome: The second transaction first reads a balance of 0, but after Maria’s transaction commits, the second read within the same transaction shows a balance of 100. This inconsistency is known as an unrepeatable read, where the same query returns different results within the same transaction.

Repeatable Read Isolation Level and the Phantom Read Anomaly

The repeatable read isolation level resolves the unrepeatable read anomaly by ensuring that if a transaction writes to a row, that row is locked and cannot be read by other transactions until the lock is released. However, it does not prevent the phantom read anomaly, where new rows matching the initial query condition are inserted by other transactions.

-- First transaction: Maria adds 100 to her account, which locks the account even for read operations
BEGIN TRANSACTION;
UPDATE account SET balance = balance + 100 WHERE name = 'Maria';

-- Second transaction: Attempts to read Maria's balance
BEGIN TRANSACTION;
SELECT balance FROM account WHERE name = 'Maria'; -- Will wait until the lock is released

-- First transaction: Commits the transaction, releasing the lock
COMMIT;

-- Second transaction: Finally runs, showing a balance of 100

Outcome: The second transaction read cannot proceed until the update lock on Maria’s account is released. Once Maria commits her transaction, the second transaction completes, reflecting the updated balance of 100. This solves the unrepeatable anomaly.

-- First transaction: Maria adds 100 to her account
BEGIN TRANSACTION;
UPDATE account SET balance = balance + 100 WHERE name = 'Maria';
COMMIT;

-- Second transaction: Fabio creates a new account for Maria with 100 on it
BEGIN TRANSACTION;
INSERT INTO account VALUES (2, 100.00, 'Maria');

-- Third transaction: Reads Maria's balance
BEGIN TRANSACTION;
SELECT balance FROM account WHERE name = 'Maria'; -- Will show 100

-- Second transaction:
COMMIT;

-- Third transaction: Reads Maria's balance
SELECT balance FROM account WHERE name = 'Maria'; -- Will show 200

Outcome: In this scenario, the initial read in the second transaction shows a balance of 100. After Fabio’s transaction commits, adding a new row to the database, the second read shows an updated balance of 200. This is an example of a phantom read anomaly, where new rows that match the initial query condition appear during the transaction, altering the result set.

Serializable Isolation Level: No Anomalies

The serializable isolation level ensures complete isolation of transactions, effectively running them one at a time. This level prevents all anomalies (dirty reads, unrepeatable reads, and phantom reads) but at the cost of performance, as transactions may need to wait for others to complete.

-- First transaction: Maria adds 100 to her account
BEGIN TRANSACTION;
UPDATE account SET balance = balance + 100 WHERE name = 'Maria';
COMMIT;

-- Second transaction: Fabio creates a new account for Maria with 100 in it
BEGIN TRANSACTION;
INSERT INTO account VALUES (2, 100.00, 'Maria');

-- Third transaction: Reads Maria's balance
BEGIN TRANSACTION;
SELECT balance FROM account WHERE name = 'Maria'; -- Will wait until the previous transactions commits or rollback

-- Second transaction:
COMMIT;

-- Third transaction: Finally runs, showing a balance of 200

Outcome: No anomalies occur because each transaction is isolated and executed in sequence. However, performance may degrade due to the strict ordering and isolation of transactions.

The table below summarizes the isolation levels and anomalies mentioned:

The table below summarizes how different isolation levels handle common anomalies:

How to Handle Isolation Levels with Django

Database isolation levels define how transactions interact with each other and ensure data integrity. Different databases have varying default isolation levels, which may not always align with your application’s requirements. In Django, you can control and manage these isolation levels effectively using a few techniques.

Default Isolation Levels

Here’s a table listing the default isolation levels for some of the most commonly used databases [2–7]:

Depending on your application’s needs, you may want to override these defaults. Django provides several ways to manage and enforce the appropriate isolation levels for your queries.

Handling Isolation Levels in Django

1. Multiple Databases with Different Isolation Levels

In Django, you can configure multiple databases within your settings.py file, each with its own isolation level. This allows you to choose the appropriate database and isolation level for specific operations, providing flexibility in handling data consistency and transaction management.

Configuration in settings.py:

from django.db.backends.postgresql.psycopg_any import IsolationLevel

DATABASES = {
    "default": {
        "NAME": "app_data",
        "ENGINE": "django.db.backends.postgresql",
        "USER": "postgres_user",
        "PASSWORD": "s3krit",
        # Default isolation level: READ COMMITTED
    },
    "serializable": {
        "NAME": "app_data",
        "ENGINE": "django.db.backends.postgresql",
        "USER": "postgres_user",
        "PASSWORD": "s3krit",
        "OPTIONS": {
          "isolation_level": IsolationLevel.SERIALIZABLE
        },
    },
}

​The default database connection uses PostgreSQL’s default isolation level, READ COMMITTED, which is suitable for most general-purpose queries.

The serializable database connection is configured with the SERIALIZABLE isolation level, which ensures the highest level of transaction isolation, preventing anomalies like phantom reads.

Once the databases are configured, you can decide which database to use in your Django ORM queries by specifying the database alias in the using() method:

# This query will use the 'default' database with READ COMMITTED isolation level.
Author.objects.all()

# Explicitly using the 'default' database.
Author.objects.using("default").all()

# This query will use the 'serializable' database with SERIALIZABLE isolation level.
Author.objects.using("serializable").all()

​The first two queries run on the default database, using the READ COMMITTED isolation level.

The third query runs on the serializable database, enforcing the SERIALIZABLE isolation level, which offers stronger consistency guarantees at the cost of potential performance trade-offs.

2. Raw Queries

Raw SQL queries allow you to explicitly set the isolation level for a specific operation [10]. This method is highly flexible but requires more manual control. Note that you should avoid mixing different isolation levels within the same transaction, as this can lead to unexpected behavior.

Example:

from django.db import connection

def my_custom_sql(self):
    with connection.cursor() as cursor:
        cursor.execute("BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;")
        cursor.execute("SELECT * FROM accounts ORDER BY id;")
        row = cursor.fetchone()
    return row

In this example, the REPEATABLE READ isolation level is set explicitly for the transaction.

3. Transaction Atomicity

Django’s transaction.atomic decorator or context manager does not change the default isolation level but provides a way to ensure that a block of code is executed within a single transaction. This prevents the database from entering an inconsistent state by committing all queries at once [11].

Example:

from django.db import transaction

@transaction.atomic
def viewfunc(request):
    do_django_orm_stuff()

In this example, all operations within the viewfunc will either complete successfully and be committed, or they will be rolled back in case of an error.

4. Select for Update

The select_for_update() method in Django is used to lock rows during a transaction, effectively providing behavior similar to the REPEATABLE READ isolation level. This ensures that no other transaction can modify rows until the current transaction is completed [12].

Example:

from django.db import transaction

entries = Entry.objects.select_for_update().filter(author=request.user)
with transaction.atomic():
    for entry in entries:
        # Perform operations on the locked entries

In this example, the selected rows are locked for write operations, ensuring consistency until the commit of transaction.

References

1. PostgreSQL 14 Internals — Chapter 2: Isolation. Link to PDF
2. MySQL Documentation: Default Isolation Levels. Link to Documentation
3. PostgreSQL Documentation: Default Isolation Levels. Link to Documentation
4. Microsoft SQL Server Documentation: Default Isolation Levels. Link to Documentation
5. Oracle Documentation: Default Isolation Levels. Link to Documentation
6. SQLite Documentation: Default Isolation Levels. Link to Documentation
7. MariaDB Documentation: Default Isolation Levels. Link to Documentation
8. Django Documentation: Multiple Databases. Link to Documentation
9. Django Documentation: Isolation Level. Link to Documentation
10. Django Documentation: Performing Raw SQL Queries. Link to Documentation
11. Django Documentation: Controlling Transactions Explicitly. Link to Documentation
12. Django Documentation: select_for_update(). Link to Documentation