Транзакции

Транзакции позволяют сгруппировать несколько инструкций SQL в одну единицу работы, которая фиксируется в базе данных в виде одной атомарной единицы. Если любая инструкция в транзакции завершается ошибкой, изменения, внесенные предыдущими инструкциями, можно откатить. Начальное состояние базы данных сохраняется при начале транзакции. Использование транзакции также может повысить производительность SQLite при одновременном внесении многочисленных изменений в базу данных.

Конкурентность

В SQLite только одна транзакция может быть изменена в базе данных одновременно. Из-за этого вызовы методов BeginTransaction и Execute на SqliteCommand могут завершаться по таймауту, если выполнение другой транзакции занимает слишком много времени.

Дополнительные сведения о блокировке, повторных попытках и истечении времени ожидания см. в статье об ошибках базы данных.

Уровни изоляции

Транзакции по умолчанию сериализуются в SQLite. Этот уровень изоляции гарантирует, что любые изменения, внесенные в транзакцию, полностью изолированы. Другие инструкции, выполняемые за пределами транзакции, не затрагиваются изменениями транзакции.

SQLite также поддерживает чтение без коммутации при использовании общего кэша. Этот уровень допускает грязные чтения, неповторяемые чтения и фантомные чтения.

• Грязное чтение происходит, когда изменения, находящиеся в процессе в одной транзакции, возвращаются запросом за пределами транзакции, но затем изменения в этой транзакции откатываются. Результаты содержат данные, которые никогда не были зафиксированы в базе данных.

• Неповторяющееся чтение происходит, когда транзакция запрашивает одну и ту же запись дважды, но результаты отличаются, так как они были изменены другой транзакцией между двумя запросами.

• Фантомы — это строки, которые изменяются или добавляются, чтобы соответствовать условию запроса в ходе транзакции. Если разрешено, один и тот же запрос может возвращать разные строки при выполнении дважды в одной транзакции.

Microsoft.Data.Sqlite обрабатывает переданный в BeginTransaction уровень изоляции как минимальный. Фактический уровень изоляции будет повышен либо до уровня чтения без фиксации, либо до уровня сериализации.

Следующий код имитирует грязное чтение. Обратите внимание, что строка подключения должна включать Cache=Shared.

using (var firstTransaction = firstConnection.BeginTransaction())
{
    var updateCommand = firstConnection.CreateCommand();
    updateCommand.CommandText =
    @"
        UPDATE data
        SET value = 'dirty'
    ";
    updateCommand.ExecuteNonQuery();

    // Without ReadUncommitted, the command will time out since the table is locked
    // while the transaction on the first connection is active
    using (secondConnection.BeginTransaction(IsolationLevel.ReadUncommitted))
    {
        var queryCommand = secondConnection.CreateCommand();
        queryCommand.CommandText =
        @"
            SELECT *
            FROM data
        ";
        var value = (string)queryCommand.ExecuteScalar();
        Console.WriteLine($"Value: {value}");
    }

    firstTransaction.Rollback();
}

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

Начиная с Microsoft.Data.Sqlite версии 5.0 транзакции могут быть отложены. Это откладывает создание фактической транзакции в базе данных до выполнения первой команды. Она также вызывает постепенное обновление транзакции с чтения на запись в зависимости от требований команд. Это может быть полезно для включения параллельного доступа к базе данных во время транзакции.

using (var transaction = connection.BeginTransaction(deferred: true))
{
    // Before the first statement of the transaction is executed, both concurrent
    // reads and writes are allowed

    var readCommand = connection.CreateCommand();
    readCommand.CommandText =
    @"
        SELECT *
        FROM data
    ";
    var value = (long)readCommand.ExecuteScalar();

    // After a the first read statement, concurrent writes are blocked until the
    // transaction completes. Concurrent reads are still allowed

    var writeCommand = connection.CreateCommand();
    writeCommand.CommandText =
    @"
        UPDATE data
        SET value = $newValue
    ";
    writeCommand.Parameters.AddWithValue("$newValue", value + 1L);
    writeCommand.ExecuteNonQuery();

    // After the first write statement, both concurrent reads and writes are blocked
    // until the transaction completes

    transaction.Commit();
}

Предупреждение

Команды внутри отложенной транзакции могут завершиться ошибкой, если они вызывают обновление транзакции с транзакции чтения до транзакции записи во время блокировки базы данных. В этом случае приложению потребуется повторить всю транзакцию.

Точки сохранения

Версия 6.0 Microsoft.Data.Sqlite поддерживает точки сохранения. Точки сохранения можно использовать для создания вложенных транзакций. Точки сохранения можно откатить, не затрагивая другие части транзакции, и даже если точка сохранения может быть подтверждена (освобождена), её изменения могут быть отменены в рамках родительской транзакции.

Следующий код иллюстрирует использование шаблона оптимистичной автономной блокировки для обнаружения параллельных обновлений и разрешения конфликтов в точке сохранения в рамках более крупной транзакции.

using (var transaction = connection.BeginTransaction())
{
    // Transaction may include additional statements before the savepoint

    var updated = false;
    do
    {
        // Begin savepoint
        transaction.Save("optimistic-update");

        var insertCommand = connection.CreateCommand();
        insertCommand.CommandText =
        @"
            INSERT INTO audit
            VALUES (datetime('now'), 'User updates data with id 1')
        ";
        insertCommand.ExecuteScalar();

        var updateCommand = connection.CreateCommand();
        updateCommand.CommandText =
        @"
            UPDATE data
            SET value = 2,
                version = $expectedVersion + 1
            WHERE id = 1
                AND version = $expectedVersion
        ";
        updateCommand.Parameters.AddWithValue("$expectedVersion", expectedVersion);
        var recordsAffected = updateCommand.ExecuteNonQuery();
        if (recordsAffected == 0)
        {
            // Concurrent update detected! Rollback savepoint and retry
            transaction.Rollback("optimistic-update");

            // TODO: Resolve update conflicts
        }
        else
        {
            // Update succeeded. Commit savepoint and continue with the transaction
            transaction.Release("optimistic-update");

            updated = true;
        }
    }
    while (!updated);

    // Additional statements may be included after the savepoint

    transaction.Commit();
}