Что такое уровни изоляции транзакций?

Уровни изоляции транзакций определяют, до какой степени одна транзакция может видеть промежуточные (незафиксированные) изменения данных, внесенные другими одновременно выполняющимися транзакциями. Они помогают управлять конфликтами при параллельном доступе к данным.

Стандарт SQL определяет четыре уровня:

1. Read Uncommitted: Самый низкий уровень. Транзакция может видеть данные, которые другая транзакция еще не зафиксировала (эффект "грязного" чтения - dirty read).

2. Read Committed: Транзакция видит только данные, которые были зафиксированы до начала ее выполнения, а также изменения, внесенные ею самой. Предотвращает "грязное" чтение, но могут возникнуть "неповторяющееся" чтение (non-repeatable read) и "фантомы" (phantom read).

3. Repeatable Read: Гарантирует, что если транзакция читает строку несколько раз, она всегда получает одно и то же значение для этой строки. Предотвращает "неповторяющееся" чтение, но могут возникнуть "фантомы".

4. Serializable: Самый высокий уровень изоляции. Гарантирует полную изоляцию транзакций, как если бы они выполнялись последовательно. Предотвращает все три типа проблем: "грязное" чтение, "неповторяющееся" чтение и "фантомы". Обычно реализуется с помощью блокировок или multi-version concurrency control (MVCC).

Типы проблем при параллельном доступе:

• Грязное чтение (Dirty Read): Чтение данных, которые были изменены другой транзакцией, но еще не зафиксированы. Если меняющая транзакция откатится, прочитанные данные окажутся неверными.
• Неповторяющееся чтение (Non-repeatable Read): Одна и та же строка читается дважды в одной транзакции, но между чтениями другая транзакция изменяет и фиксирует эту строку. Второе чтение возвращает другое значение.
• Фантомы (Phantom Read): В одной транзакции выполняется два одинаковых запроса, возвращающих набор строк. Между запросами другая транзакция добавляет или удаляет строки, которые соответствуют условию запроса. Второй запрос возвращает другой набор строк (появляются "новые" или "исчезают" строки).

Выбор уровня изоляции - компромисс между целостностью данных и производительностью. Более высокие уровни изоляции обеспечивают лучшую целостность, но могут снижать параллелизм из-за накладных расходов на блокировки или управление версиями. В Python с базами данных часто используются библиотеки, такие как psycopg2 для PostgreSQL или sqlite3 для SQLite, которые позволяют установить уровень изоляции для соединения или транзакции.

import sqlite3

# Подключение к базе данных
conn = sqlite3.connect('mydatabase.db')

# Установка уровня изоляции REPEATABLE READ
# В SQLite по умолчанию PRAGMA read_uncommitted = false, что соответствует Repeatable Read
# Для явной настройки (если доступно и поддерживается RDBMS)
# conn.isolation_level = 'REPEATABLE READ' # Пример синтаксиса (не для sqlite3)

try:
    cursor = conn.cursor()

    # Начало транзакции (автоматически при первом запросе, или явно begin)
    # cursor.execute("BEGIN") # Пример явного начала

    # Выполнение запросов
    cursor.execute("SELECT * FROM users WHERE id = 1")
    user1 = cursor.fetchone()
    print(f"First read: {user1}")

    # В этот момент могла бы выполниться другая транзакция и изменить строку с id=1
    # Или добавить новую строку, если изоляция ниже Repeatable Read

    cursor.execute("SELECT * FROM users WHERE id = 1")
    user2 = cursor.fetchone()
    print(f"Second read: {user2}")
    # При Repeatable Read user2 будет тем же, что и user1

    # cursor.execute("SELECT COUNT(*) FROM users")
    # count1 = cursor.fetchone()[0]
    # print(f"First count: {count1}")
    # # Если другая транзакция добавила новую строку,
    # # на более низких уровнях изоляции (Read Committed или ниже)
    # # следующий запрос мог бы вернуть другое количество (фантом)
    # cursor.execute("SELECT COUNT(*) FROM users")
    # count2 = cursor.fetchone()[0]
    # print(f"Second count: {count2}")
    # # При Serializable FANTOM read не произойдет

    # Фиксация изменений
    conn.commit()

except Exception as e:
    print(f"An error occurred: {e}")
    # Откат изменений в случае ошибки
    conn.rollback()

finally:
    # Закрытие соединения
    conn.close()