Управление транзакциями в mssql-django

В этой статье описывается, как настроить управление транзакциями и уровни изоляции для приложений Django с использованием серверной части mssql-django для SQL Server.

Поведение по умолчанию

По умолчанию Django работает в режиме автоматического подключения. Каждый запрос базы данных выполняется в собственной транзакции и немедленно фиксируется. Это поведение можно изменить с помощью AUTOCOMMIT параметра или API управления транзакциями Django.

Параметр AUTOCOMMIT

Установите значение AUTOCOMMITFalse в конфигурации базы данных, чтобы отключить режим автоматического подключения:

DATABASES = {
    "default": {
        "ENGINE": "mssql",
        "NAME": "<your-database>",
        "USER": "<your-username>",
        "PASSWORD": "<your-password>",
        "HOST": "<your-server>",
        "PORT": "1433",
        "AUTOCOMMIT": False,
        "OPTIONS": {
            "driver": "ODBC Driver 18 for SQL Server",
        },
    },
}

Note

Отключение автоматической фиксации означает, что необходимо явно фиксировать или откатывать транзакции. Большинство приложений Django оставляют автокоммит включённым и используют transaction.atomic() для определённых операций.

Используйте transaction.atomic()

Оберните операции с базой данных в transaction.atomic(), чтобы гарантировать, что они выполняются в рамках одной транзакции:

from django.db import transaction
from myapp.models import Account

def transfer_funds(from_account_id, to_account_id, amount):
    with transaction.atomic():
        sender = Account.objects.select_for_update().get(pk=from_account_id)
        receiver = Account.objects.select_for_update().get(pk=to_account_id)

        sender.balance -= amount
        receiver.balance += amount

        sender.save()
        receiver.save()

Если внутри блока atomic() возникает исключение, вся транзакция откатывается.

Вложенные транзакции

Django поддерживает вложенные блоки atomic() с помощью точек сохранения SQL Server:

from django.db import transaction

with transaction.atomic():
    # Outer transaction
    Product.objects.create(name="Widget A", price=9.99)

    try:
        with transaction.atomic():
            # Inner savepoint
            Product.objects.create(name="Widget B", price=14.99)
            raise ValueError("Simulated error")
    except ValueError:
        pass  # Inner savepoint is rolled back, outer continues

    # Widget A is committed, Widget B is not

Уровни изоляции транзакций

Настройте уровень изоляции транзакций с помощью isolation_level параметра в конфигурации базы данных:

DATABASES = {
    "default": {
        "ENGINE": "mssql",
        "NAME": "<your-database>",
        "USER": "<your-username>",
        "PASSWORD": "<your-password>",
        "HOST": "<your-server>",
        "PORT": "1433",
        "OPTIONS": {
            "driver": "ODBC Driver 18 for SQL Server",
            "isolation_level": "READ COMMITTED",
        },
    },
}

Поддерживаемые уровни изоляции

Уровень изоляции	Description
`READ UNCOMMITTED`	Разрешает грязные чтения. Минимальная изоляция, максимальная параллельность.
`READ COMMITTED`	SQL Server по умолчанию. Предотвращает грязное чтение.
`REPEATABLE READ`	Предотвращает грязные и неповторяемые чтения.
`SNAPSHOT`	Использует версионирование строк для согласованного чтения без блокировок. Требуется включить изоляцию моментальных снимков на уровне базы данных.
`SERIALIZABLE`	Самая высокая изоляция. Предотвращает фантомные чтения.

Включение изоляции SNAPSHOT

Чтобы использовать SNAPSHOT изоляцию, сначала включите ее в базе данных:

ALTER DATABASE [<your-database>]
SET ALLOW_SNAPSHOT_ISOLATION ON;

Затем настройте его в settings.py:

DATABASES = {
    "default": {
        "ENGINE": "mssql",
        "NAME": "<your-database>",
        "USER": "<your-username>",
        "PASSWORD": "<your-password>",
        "HOST": "<your-server>",
        "PORT": "1433",
        "OPTIONS": {
            "driver": "ODBC Driver 18 for SQL Server",
            "isolation_level": "SNAPSHOT",
        },
    },
}

Используйте декоратор @transaction.atomic

Применение транзакций ко всем функциям представления:

from django.db import transaction
from django.http import JsonResponse

@transaction.atomic
def create_order(request):
    # All database operations in this view run in a single transaction
    order = Order.objects.create(customer_id=request.user.id)
    for item in request.POST.getlist("items"):
        OrderItem.objects.create(order=order, product_id=item)
    return JsonResponse({"order_id": order.pk})

Чтение данных без блокировки (эквивалент NOLOCK)

Часто просят выполнять запросы к SQL Server с подсказкой NOLOCK или с уровнем изоляции READ UNCOMMITTED, чтобы избежать блокировок при работе с загруженными таблицами. ORM Django не создает табличные подсказки, но у вас есть два варианта.

Вариант 1. Настройка ПАРАМЕТРА READ UNCOMMITTED для каждого подключения

Задайте уровень READ UNCOMMITTED изоляции для выделенного псевдонима базы данных только для чтения, чтобы применить его ко всем запросам по этому подключению:

DATABASES = {
    "default": {
        "ENGINE": "mssql",
        "NAME": "<your-database>",
        "HOST": "<your-server>",
        "PORT": "1433",
        "OPTIONS": {
            "driver": "ODBC Driver 18 for SQL Server",
        },
    },
    "read_uncommitted": {
        "ENGINE": "mssql",
        "NAME": "<your-database>",
        "HOST": "<your-server>",
        "PORT": "1433",
        "OPTIONS": {
            "driver": "ODBC Driver 18 for SQL Server",
            "isolation_level": "READ UNCOMMITTED",
        },
    },
}

Затем перенаправьте запросы к псевдониму read_uncommitted :

# Read with NOLOCK-equivalent behavior
products = Product.objects.using("read_uncommitted").filter(active=True)

# Writes still go through the default connection
Product.objects.create(name="Widget", price=9.99)

Вариант 2. Использование необработанного SQL с NOLOCK

Для целевых запросов в определенных таблицах используйте необработанный SQL с указанием NOLOCK таблицы:

from django.db import connection

with connection.cursor() as cursor:
    cursor.execute("SELECT id, name, price FROM myapp_product WITH (NOLOCK) WHERE active = %s", [1])
    rows = cursor.fetchall()

Caution

И READ UNCOMMITTED, и NOLOCK допускают грязное чтение, что означает, что запросы могут возвращать данные из незафиксированных транзакций. Используйте эти методы только для запросов отчетов или аналитики, где абсолютная согласованность не требуется.

Вариант 3. Вместо этого используйте изоляцию SNAPSHOT

SNAPSHOT изоляция обеспечивает согласованное чтение без блокировок и без грязных чтений. Это рекомендуемая альтернатива NOLOCK для большинства рабочих нагрузок:

DATABASES = {
    "default": {
        "ENGINE": "mssql",
        "NAME": "<your-database>",
        "HOST": "<your-server>",
        "PORT": "1433",
        "OPTIONS": {
            "driver": "ODBC Driver 18 for SQL Server",
            "isolation_level": "SNAPSHOT",
        },
    },
}

SNAPSHOT требует настройки на уровне базы данных. См. раздел "Включить изоляцию МОМЕНТАЛЬНОГО СНИМКА".

Блокировка на уровне строк с помощью select_for_update()

Django select_for_update() полностью поддерживается серверной частью mssql-django . SQL Server реализует это с помощью табличных подсказок вместо предложения, используемого FOR UPDATE другими базами данных.

Основное использование

from django.db import transaction

with transaction.atomic():
    product = Product.objects.select_for_update().get(pk=1)
    product.stock -= 1
    product.save()

Серверная часть создает: SELECT ... FROM [myapp_product] WITH (ROWLOCK, UPDLOCK) WHERE ...

NOWAIT и SKIP LOCKED

Поддерживаются оба параметра: nowait и skip_locked

from django.db import transaction

# Raise DatabaseError immediately if the row is already locked
with transaction.atomic():
    product = Product.objects.select_for_update(nowait=True).get(pk=1)

# Skip rows that are locked by other transactions
with transaction.atomic():
    available = Product.objects.select_for_update(skip_locked=True).filter(
        reserved=False
    )[:10]

Parameter	Подсказка таблицы SQL Server
По умолчанию	`WITH (ROWLOCK, UPDLOCK)`
`nowait=True`	`WITH (NOWAIT, ROWLOCK, UPDLOCK)`
`skip_locked=True`	`WITH (ROWLOCK, UPDLOCK, READPAST)`