Transactions

Transactions let you group multiple SQL statements into a single unit of work that is committed to the database as one atomic unit. If any statement in the transaction fails, changes made by the previous statements can be rolled back. The initial state of the database when the transaction was started is preserved. Using a transaction can also improve performance on SQLite when making numerous changes to the database at once.

Concurrency

In SQLite, only one transaction is allowed to have changes pending in the database at a time. Because of this, calls to BeginTransaction and the Execute methods on SqliteCommand may time out if another transaction takes too long to complete.

For more information about locking, retries, and timeouts, see Database errors.

Isolation levels

Transactions are serializable by default in SQLite. This isolation level guarantees that any changes made within a transaction are completely isolated. Other statements executed outside of the transaction aren't affected by the transaction's changes.

SQLite also supports read uncommitted when using a shared cache. This level allows dirty reads, nonrepeatable reads, and phantoms:

• A dirty read occurs when changes pending in one transaction are returned by a query outside of the transaction, but the changes in the transaction are rolled back. The results contain data that was never actually committed to the database.

• A nonrepeatable read occurs when a transaction queries the same row twice, but the results are different because it was changed between the two queries by another transaction.

• Phantoms are rows that get changed or added to meet the where clause of a query during a transaction. If allowed, the same query could return different rows when executed twice in the same transaction.

Microsoft.Data.Sqlite treats the IsolationLevel passed to BeginTransaction as a minimum level. The actual isolation level will be promoted to either read uncommitted or serializable.

The following code simulates a dirty read. Note, the connection string must include 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();
}

Deferred transactions

Starting with Microsoft.Data.Sqlite version 5.0, transactions can be deferred. This defers the creation of the actual transaction in the database until the first command is executed. It also causes the transaction to gradually upgrade from a read transaction to a write transaction as needed by its commands. This can be useful for enabling concurrent access to the database during the 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();
}

Warning

Commands inside a deferred transaction can fail if they cause the transaction to be upgraded from a read transaction to a write transaction while the database is locked. When this happens, the application will need to retry the entire transaction.

Savepoints

Version 6.0 of Microsoft.Data.Sqlite supports savepoints. Savepoints can be used to create nested transactions. Savepoints can be rolled back without affecting other parts of the transaction, and even though a savepoint may be committed (released), its changes may later be rolled back as part of its parent transaction.

The following code illustrates using the Optimistic Offline Lock pattern to detect concurrent updates and resolve conflicts within a savepoint as part of a larger transaction.

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