事务

项目
04/06/2023

通过事务可以将多个 SQL 语句组合成一个工作单元，并将其作为一个原子单元提交到数据库。如果事务中的任何语句失败，可以回滚前面语句所做的更改。事务启动时的数据库初始状态会予以保留。当一次对数据库进行大量修改时，使用事务也可以提高 SQLite 上的性能。

并发

在 SQLite 中，数据库中一次只允许有一个事务具有挂起的更改。因此，如果另一个事务需要很长时间才能完成，则对 SqliteCommand 调用 BeginTransaction 和 Execute 方法可能会超时。

有关锁定、重试和超时的详细信息，请参阅数据库错误。

隔离级别

默认情况下，在 SQLite 中，事务是可序列化的。此隔离级别可确保完全隔离事务中所做的任何更改。在事务之外执行的其他语句不受事务更改的影响。

SQLite 还支持在使用共享缓存时读取未提交的内容。此级别允许脏读、不可重复读和幻像：

当事务外部的查询返回一个事务中挂起的更改，但事务中的更改被回滚时，会发生“脏读” 。结果包含从不实际提交到数据库的数据。
如果某一事务两次查询相同的行，但由于在两个查询之间另一个事务进行了更改导致结果不同，这时会发生“不可重复读”。
“幻像” 在事务过程中为满足查询的 where 子句而更改或添加的行。如果允许的话，同一查询在同一事务中执行两次时可能返回不同的行。

Microsoft.Data.Sqlite 将传递给 BeginTransaction 的 IsolationLevel 视为最低级别。实际隔离级别将提升为读取未提交内容或可序列化。

下面的代码模拟脏读。请注意，连接字符串必须包含 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();
}

延迟的事务

从 Microsoft.Data.Sqlite 版本 5.0 开始，可以延迟事务。这会延迟在数据库中创建实际事务，直到执行第一个命令。还会根据该事务的命令需求，使该事务逐步从读取事务升级到写入事务。这有助于在事务期间并发访问数据库。

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

警告

如果延迟的事务中的命令导致事务在数据库锁定时从读取事务升级到写入事务，则这些命令可能会失败。发生这种情况时，应用程序将需要重试整个事务。

保存点

Microsoft.Data.Sqlite 版本 6.0 支持保存点。保存点可用于创建嵌套事务。保存点可以在不影响事务的其他部分的情况下回滚，即使保存点可能已提交（释放），其更改也可能在稍后作为其父事务的一部分回滚。

以下代码演示了如何使用“乐观离线锁”模式来检测并发更新，并将保存点内的冲突解析为更大的事务的一部分。

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

通过

事务

并发

隔离级别

延迟的事务

保存点

反馈

其他资源