Транзакции

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

Параллелизм

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

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

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

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

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

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

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

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

Microsoft.Data.Sqlit рассматривает IsolationLevel, переданные в 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();
}