在.NET 7中使用Microsoft.Data.Sqlite无法设置SQLite WAL模式

问题根源 #

核心问题是：PRAGMA journal_mode = WAL 不能在事务中执行。SQLite规定，日志模式的修改必须在事务启动前完成——一旦事务开始，数据库的日志模式就被锁定，此时执行的journal_mode设置不会被持久化，甚至可能直接无效。而wal_autocheckpoint属于运行时配置参数，在事务中设置可以生效，这就是为什么只有WAL模式没生效的原因。

修复步骤 #

1. 将WAL模式设置移到事务之外：在连接打开后、启动事务前，单独执行PRAGMA journal_mode = WAL。
2. 验证设置结果：执行PRAGMA后读取返回值，确认是否成功切换到WAL（避免因其他连接占用数据库导致设置失败）。

修改后的代码示例 #

var conn = new SqliteConnection(_connectionString);
await using(conn.ConfigureAwait(false))
{
    await conn.OpenAsync().ConfigureAwait(false);

    // 先设置WAL模式，必须在事务启动前执行
    var walCommand = conn.CreateCommand();
    await using(walCommand.ConfigureAwait(false))
    {
        walCommand.CommandText = "PRAGMA journal_mode = WAL;";
        // 读取返回值确认是否成功切换
        var result = await walCommand.ExecuteScalarAsync().ConfigureAwait(false);
        if (result.ToString() != "wal")
        {
            // 处理设置失败的情况，比如日志记录
        }
    }

    // 再启动事务执行其他操作
    var trans = await conn.BeginTransactionAsync().ConfigureAwait(false);
    await using(trans.ConfigureAwait(false))
    {
        var checkpointCommand = conn.CreateCommand();
        await using(checkpointCommand.ConfigureAwait(false))
        {
            checkpointCommand.CommandText = "PRAGMA wal_autocheckpoint=1000;";
            await checkpointCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
        }

        // 执行其他数据库操作...

        trans.Commit();
    }
}

额外说明 #

• 只要成功设置过一次WAL模式，SQLite会将这个配置持久化到数据库文件中，后续打开连接时会自动使用WAL模式，除非有其他连接显式将其改回DELETE模式。
• 如果设置WAL失败，大概率是因为当前有其他连接以DELETE模式打开了该数据库，需要确保所有连接都关闭后再尝试设置。

内容的提问来源于stack exchange，提问作者Iaman Swtrse