Transactions

Les transactions vous permettent de regrouper plusieurs instructions SQL en une seule unité de travail validée dans la base de données en tant qu’unité atomique. En cas d’échec d’une instruction de la transaction, les changements apportés par les instructions précédentes peuvent être annulés. L’état initial de la base de données au démarrage de la transaction est conservé. L’utilisation d’une transaction permet également d’améliorer les performances sur SQLite quand vous apportez de nombreux changements à la base de données en une seule fois.

Accès concurrentiel

Dans SQLite, une seule transaction à la fois est autorisée à avoir des changements en attente dans la base de données. Pour cette raison, les appels à BeginTransaction et aux méthodes Execute sur SqliteCommand peuvent expirer si une autre transaction prend trop de temps.

Pour plus d’informations sur le verrouillage, les nouvelles tentatives et les délais d’expiration, consultez Erreurs de base de données.

Niveaux d'isolement

Les transactions sont sérialisables par défaut dans SQLite. Ce niveau d’isolation garantit l’isolement complet de tous les changements apportés au sein d’une transaction. Les autres instructions exécutées en dehors de la transaction ne sont pas affectées par les changements apportés à la transaction.

SQLite prend également en charge la fonctionnalité de lecture non validée en cas d’utilisation d’un cache partagé. Ce niveau autorise les lectures erronées, les lectures non reproductibles et les fantômes :

• Une lecture erronée se produit quand des changements en attente dans une transaction sont retournés par une requête externe à la transaction, et que les changements apportés à la transaction sont restaurés. Les résultats contiennent des données qui n’ont jamais été réellement validées dans la base de données.

• Une lecture non reproductible se produit quand une transaction interroge la même ligne deux fois, mais que les résultats sont différents, car ils ont été changés entre les deux requêtes par une autre transaction.

• Les fantômes sont des lignes qui ont été changées ou ajoutées en réponse à la clause WHERE d’une requête durant une transaction. Si cela est autorisé, la même requête peut retourner des lignes différentes quand elle est exécutée deux fois dans la même transaction.

Microsoft.Data.Sqlite traite le IsolationLevel passé à BeginTransaction en tant que niveau minimal. Le niveau d’isolation réel est promu en lecture non validée ou sérialisable.

Le code suivant simule une lecture erronée. Notez que la chaîne de connexion doit inclure 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();
}

Transactions différées

À partir de Microsoft.Data.Sqlite version 5.0, les transactions peuvent être différées. Cela permet de différer la création de la transaction réelle dans la base de données jusqu’à ce que la première commande soit exécutée. Cela entraîne également la mise à niveau progressive de la transaction d’une transaction de lecture à une transaction d’écriture, selon les besoins des commandes. Cela peut être utile pour activer l’accès simultané à la base de données durant la transaction.

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

Avertissement

Les commandes présentes dans une transaction différée peuvent échouer si elles entraînent la mise à niveau de la transaction d’une transaction de lecture vers une transaction d’écriture alors que la base de données est verrouillée. Dans ce cas, l’application doit retenter l’intégralité de la transaction.

Points de sauvegarde

La version 6.0 de Microsoft.Data.Sqlite prend en charge les points de sauvegarde. Les points de sauvegarde peuvent être utilisés pour créer des transactions imbriquées. Les points de sauvegarde peuvent être restaurés sans affecter les autres parties de la transaction. Même si un point de sauvegarde peut être validé (publié), ses changements peuvent être restaurés plus tard dans le cadre de sa transaction parente.

Le code suivant illustre l’utilisation du modèle de verrou d’accès concurrentiel optimiste hors connexion pour détecter les mises à jour simultanées et résoudre les conflits au sein d’un point de sauvegarde dans le cadre d’une transaction plus importante.

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