Transazioni

Le transazioni consentono di raggruppare più istruzioni SQL in una singola unità di lavoro di cui viene eseguito il commit nel database come un'unica unità atomica. Se un'istruzione nella transazione ha esito negativo, è possibile eseguire il rollback delle modifiche apportate dalle istruzioni precedenti. Viene mantenuto lo stato iniziale del database al momento dell'avvio della transazione. L'uso di una transazione può anche migliorare le prestazioni di SQLite quando si apportano numerose modifiche al database contemporaneamente.

Concorrenza

In SQLite una sola transazione alla volta può avere modifiche in sospeso nel database. Per questo motivo, può verificarsi il timeout delle chiamate a BeginTransaction e ai metodi Execute su SqliteCommandse il completamento di un'altra transazione richiede troppo tempo.

Per altre informazioni su blocco, tentativi e timeout, vedere Errori del database.

Livelli di isolamento

Le transazioni sono serializzabili per impostazione predefinita in SQLite. Questo livello di isolamento garantisce che tutte le modifiche apportate all'interno di una transazione siano completamente isolate. Le altre istruzioni eseguite all'esterno della transazione non sono interessate dalle modifiche della transazione.

SQLite supporta anche la lettura senza commit quando si usa una cache condivisa. Questo livello consente letture dirty, letture non ripetibili e righe fantasma:

• Una lettura dirty si verifica quando le modifiche in sospeso in una transazione vengono restituite da una query all'esterno della transazione, ma viene eseguito il rollback delle modifiche nella transazione. I risultati contengono dati di cui non è mai stato effettivamente eseguito il commit nel database.

• Una lettura non ripetibile si verifica quando una transazione esegue due volte una query sulla stessa riga, ma i risultati sono diversi perché sono stati modificati tra le due query da un'altra transazione.

• Le righe fantasma sono righe che vengono modificate o aggiunte per soddisfare la clausola where di una query durante una transazione. Se consentito, la stessa query potrebbe restituire righe diverse quando viene eseguita due volte nella stessa transazione.

Microsoft.Data.Sqlite considera il valore di IsolationLevel passato a BeginTransaction come livello minimo. Il livello di isolamento effettivo verrà alzato di livello a lettura senza commit o serializzabile.

Il codice seguente simula una lettura dirty. Si noti che la stringa di connessione deve includere 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();
}

Transazioni posticipate

A partire da Microsoft.Data.Sqlite versione 5.0, le transazioni possono essere posticipate. In questo modo viene posticipata la creazione della transazione effettiva nel database fino a quando non viene eseguito il primo comando. La transazione viene inoltre gradualmente aggiornata da transazione di lettura a transazione di scrittura in base alle esigenze dei relativi comandi. Ciò può essere utile per abilitare l'accesso simultaneo al database durante la transazione.

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

Avviso

I comandi all'interno di una transazione posticipata possono non riuscire se causano l'aggiornamento della transazione da una transazione di lettura a una transazione di scrittura mentre il database è bloccato. In questo caso, l'applicazione dovrà ripetere l'intera transazione.

Punti di salvataggio

La versione 6.0 di Microsoft.Data.Sqlite supporta i punti di salvataggio. I punti di salvataggio possono essere usati per creare transazioni annidate. È possibile eseguire il rollback dei punti di salvataggio senza influire su altre parti della transazione e anche se è possibile che venga eseguito il commit di un punto di salvataggio (rilasciato), è possibile eseguire il rollback delle modifiche apportate successivamente come parte della transazione padre.

Il codice seguente illustra l'uso del modello di blocco offline ottimistico per rilevare gli aggiornamenti simultanei e risolvere i conflitti all'interno di un punto di salvataggio come parte di una transazione più grande.

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