トランザクション

[アーティクル]
04/06/2023

トランザクションを使用すると、複数の SQL ステートメントを 1 つの作業単位にまとめて、1 つのアトミック単位としてデータベースにコミットできます。トランザクション内のいずれかのステートメントが失敗した場合は、前のステートメントによって行われた変更をロールバックできます。トランザクションが開始されたときのデータベースの初期状態は保持されます。トランザクションを使用すると、データベースに多数の変更を一度に加えるときの SQLite でのパフォーマンスを向上させることもできます。

コンカレンシー

SQLite では、一度に 1 つのトランザクションだけがデータベース内で変更を保留にすることができます。このため、別のトランザクションの完了に時間がかかりすぎると、SqliteCommand の BeginTransaction メソッドと Execute メソッドの呼び出しがタイムアウトになる場合があります。

ロック、再試行、タイムアウトの詳細については、「データベースエラー」を参照してください。

分離レベル

SQLite では、トランザクションは既定で serializable です。この分離レベルを使用すると、トランザクション内で行われたすべての変更が完全に分離されることが保証されます。トランザクションの外部で実行される他のステートメントは、そのトランザクションの変更の影響を受けません。

SQLite では、共有キャッシュの使用時には、read uncommitted もサポートされます。このレベルでは、ダーティリード、反復不能読み取り、ファントムが可能です。

"ダーティリード" は、1 つのトランザクション内で保留中の変更がそのトランザクションの外部でクエリによって返される一方で、そのトランザクション内の変更がロールバックされるときに生じます。結果には、実際にはデータベースにコミットされなかったデータが含まれます。
"反復不能読み取り" は、1 つのトランザクションで同じ行のクエリが 2 回実行され、その 2 回のクエリの間に別のトランザクションによってその行が変更されたために結果が異なっているときに発生します。
"ファントム" は、トランザクション中にクエリの where 句に合わせて変更または追加された行です。同じクエリが同じトランザクション内で 2 回実行されることが許可されている場合、そのようなクエリで異なる行が返されることがあります。

Microsoft.Data.Sqlite では、BeginTransaction に渡される IsolationLevel は最小レベルとして扱われます。実際の分離レベルは、read uncommitted または serializable に昇格されます。

次のコードは、ダーティリードをシミュレートしています。接続文字列には 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();
}

警告

データベースがロックされているときに、遅延トランザクション内のコマンドによって、トランザクションが読み取りトランザクションから書き込みトランザクションにアップグレードされると、そのコマンドは失敗することがあります。この場合、アプリケーションで、トランザクション全体の再試行を行う必要があります。

セーブポイント

Microsoft.Data.Sqlite のバージョン 6.0 ではセーブポイントがサポートされています。セーブポイントを使用して、入れ子になったトランザクションを作成できます。セーブポイントはトランザクションの他の部分に影響を与えずにロールバックできます。また、セーブポイントがコミット (リリース) された場合でも、その変更は親トランザクションの一部として後でロールバックできます。

次のコードは、オプティミスティックオフラインロックのパターンを使用して、同時更新を検出し、大きなトランザクションの一部としてセーブポイント内の競合を解決する方法を示しています。

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();
}

トランザクション

コンカレンシー

分離レベル

遅延トランザクション

セーブポイント

フィードバック

その他のリソース