Transacciones

Las transacciones permiten agrupar varias instrucciones SQL en una misma 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. Se conservará el estado inicial de la base de datos cuando se inició la transacción. El uso de una transacción también puede mejorar el rendimiento en SQLite cuando se realizan varios cambios en la base de datos a la vez.

simultaneidad

En SQLite, solo una transacción puede tener cambios pendientes en la base de datos en un momento determinado. 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 más información sobre los bloqueos, los reintentos y los tiempos de espera, vea Errores de base de datos.

Niveles de aislamiento

Las transacciones se serializan de forma predeterminada en SQLite. Este nivel de aislamiento garantiza que los cambios realizados en una transacción estén completamente aislados. El resto de instrucciones ejecutadas fuera de la transacción no se verán afectadas por los cambios de la transacción.

SQLite también admite la lectura no confirmada cuando se usa una memoria caché compartida. Este nivel permite lecturas de datos sucios, lecturas no repetibles y filas fantasma:

• 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 confirmaron realmente en la base de datos.

• Una lectura no repetible es aquella que se produce cuando una transacción consulta la misma fila dos veces, pero los resultados son diferentes porque otra transacción cambió la fila entre una consulta y la otra.

• 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 si se ejecutase dos veces en la misma transacción.

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

En el siguiente código se simula una lectura de datos sucios. Fíjese en 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 la versión 5.0 de Microsoft.Data.Sqlite, las transacciones se pueden diferir. 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 de manera gradual de una transacción de lectura a una de escritura, según las necesidades de 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 de escritura mientras la base de datos está bloqueada. Cuando esto ocurre, la aplicación tendrá que reintentar la transacción completa.

Puntos de retorno

La versión 6.0 de Microsoft.Data.Sqlite admite puntos de retorno. 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();
}