Transacciones

Las transacciones permiten agrupar varias instrucciones SQL en una sola unidad de trabajo que se confirma en la base de datos como una unidad atómica. Si se produce un error en una instrucción de la transacción, los cambios realizados por las instrucciones anteriores se pueden revertir. El estado inicial de la base de datos cuando se inició la transacción se conserva. El uso de una transacción también puede mejorar el rendimiento en SQLite al realizar numerosos cambios en la base de datos a la vez.

Simultaneidad

En SQLite, solo se permite que una transacción tenga cambios pendientes en la base de datos a la vez. Por este motivo, el tiempo de espera de las llamadas a los métodos BeginTransaction y Execute en SqliteCommand se puede agotar si otra transacción tarda demasiado en completarse.

Para obtener más información sobre el bloqueo, los reintentos y los tiempos de espera, consulte Errores de base de datos.

Niveles de aislamiento

Las transacciones son serializables de forma predeterminada en SQLite. Este nivel de aislamiento garantiza que los cambios realizados en una transacción estén completamente aislados. Otras declaraciones ejecutadas fuera de la transacción no están afectadas por los cambios de la transacción.

SQLite también admite la lectura no confirmada cuando se usa una caché compartida. Este nivel permite lecturas sucias, lecturas no repetibles y fantasmas.

• Una lectura de datos sucios es aquella que se produce cuando una consulta fuera de la transacción devuelve los cambios pendientes de una transacción, pero esos cambios en la transacción se revierten. Los resultados contienen datos que nunca se han confirmado realmente en la base de datos.

• Una lectura no replicable se produce cuando una transacción consulta la misma fila dos veces, pero los resultados son diferentes porque se cambió entre las dos consultas por otra transacción.

• Las filas fantasma son filas que se cambian o se agregan para cumplir la cláusula WHERE de una consulta durante una transacción. Si se permite, la misma consulta podría devolver filas diferentes cuando se ejecuta dos veces en la misma transacción.

Microsoft.Data.Sqlite trata el IsolationLevel pasado a BeginTransaction como un nivel mínimo. El nivel de aislamiento real subirá a lectura no confirmada o serializable.

El código siguiente simula una lectura sucia. Tenga en cuenta que la cadena de conexión debe incluir 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();
}

Transacciones diferidas

A partir de Microsoft.Data.Sqlite versión 5.0, las transacciones se pueden aplazar. Esto aplaza la creación de la transacción real en la base de datos hasta que se ejecute el primer comando. También hace que la transacción se actualice gradualmente de una transacción de lectura a una transacción de escritura según sea necesario por sus comandos. Esto puede ser útil para habilitar el acceso simultáneo a la base de datos durante la transacción.

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

Advertencia

Los comandos dentro de una transacción diferida pueden producir un error si hacen que la transacción se actualice de una transacción de lectura a una transacción de escritura mientras la base de datos está bloqueada. Cuando esto sucede, la aplicación tendrá que reintentar toda la transacción.

Puntos de retorno

La versión 6.0 de Microsoft.Data.Sqlite admite puntos de guardado. Los puntos de retorno se pueden usar para crear transacciones anidadas. Los puntos de retorno se pueden revertir sin afectar a otras partes de la transacción y, aunque se pueda confirmar un punto de retorno (liberado), sus cambios pueden revertirse posteriormente como parte de su transacción primaria.

En el código siguiente se muestra el uso del patrón de bloqueo sin conexión optimista para detectar actualizaciones simultáneas y resolver conflictos dentro de un punto de retorno como parte de una transacción mayor.

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