代码中SQLite备份文件被锁定，无法上传该如何排查？

我通过以下代码备份SQLite数据库并上传至Azure Blob容器，但执行上传时总是报错文件被其他进程占用，直到应用关闭才解锁。

备份与上传代码：

public static async Task<string> CreateBackupAsync()
{
    // 生成带时间戳的备份文件名
    var timestamp = DateTime.UtcNow.ToString("yyyyMMddHHmmss");
    var backupFileName = $"backup_{timestamp}.db";
    var backupFilePath = Path.Combine("C:\\Backups", backupFileName);

    // 确保备份目录存在
    Directory.CreateDirectory("C:\\Backups");

    using (var location = new SqliteConnection($"Data Source={LocalDbPath}"))
    using (var destination = new SqliteConnection($"Data Source={backupFilePath}"))
    {
        await location.OpenAsync();
        await destination.OpenAsync();
        location.BackupDatabase(destination);
    }

    return Path.GetFullPath(backupFilePath);
}

public static async Task UploadFileAsync(string filePath)
{
    var containerClient = new BlobContainerClient(new Uri(ConnectionString));
    var blobClient = containerClient.GetBlobClient($"database/backup/{Path.GetFileName(filePath)}");

    try
    {
        // 将文件上传至Azure Blob存储
        await using var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.None);
        await blobClient.UploadAsync(fs, true);
    }
    catch (Exception ex)
    {
        Debug.WriteLine($"Exception: {ex.Message}");
        Debug.WriteLine($"Stack Trace: {ex.StackTrace}");
    }
}

执行逻辑：

var backup = await DatabaseSynchronizer.CreateBackupAsync();
await DatabaseSynchronizer.UploadFileAsync(backup);

报错信息：

System.IO.IOException: 'The process cannot access the file 'C:...\Debug\net8.0-windows10.0.19041.0\backup_20240702114910.db' because it is being used by another process.'

问题原因 #

查看BackupDatabase的底层实现后发现，连接关闭逻辑存在漏洞：

• 该方法仅在自身主动打开目标连接时（即flag为true），才会在finally块中关闭连接。
• 但在CreateBackupAsync中，我们提前手动调用了await destination.OpenAsync();，导致flag为false，最终目标连接（指向备份文件的SqliteConnection）没有被关闭，SQLite因此一直持有备份文件的锁，后续FileStream无法访问。

解决办法 #

方案一：移除手动打开目标连接的代码 #

让BackupDatabase方法自行处理目标连接的打开与关闭：

public static async Task<string> CreateBackupAsync()
{
    // 生成带时间戳的备份文件名
    var timestamp = DateTime.UtcNow.ToString("yyyyMMddHHmmss");
    var backupFileName = $"backup_{timestamp}.db";
    var backupFilePath = Path.Combine("C:\\Backups", backupFileName);

    // 确保备份目录存在
    Directory.CreateDirectory("C:\\Backups");

    using (var location = new SqliteConnection($"Data Source={LocalDbPath}"))
    using (var destination = new SqliteConnection($"Data Source={backupFilePath}"))
    {
        await location.OpenAsync();
        // 移除手动打开目标连接的代码
        location.BackupDatabase(destination);
    }

    return Path.GetFullPath(backupFilePath);
}

此时BackupDatabase会检测到目标连接未打开，自动打开并在finally中关闭，确保备份文件的锁被释放。

方案二：手动确保目标连接关闭 #

如果需要保留手动打开连接的逻辑，在备份完成后主动关闭目标连接：

public static async Task<string> CreateBackupAsync()
{
    // 生成带时间戳的备份文件名
    var timestamp = DateTime.UtcNow.ToString("yyyyMMddHHmmss");
    var backupFileName = $"backup_{timestamp}.db";
    var backupFilePath = Path.Combine("C:\\Backups", backupFileName);

    // 确保备份目录存在
    Directory.CreateDirectory("C:\\Backups");

    using (var location = new SqliteConnection($"Data Source={LocalDbPath}"))
    using (var destination = new SqliteConnection($"Data Source={backupFilePath}"))
    {
        await location.OpenAsync();
        await destination.OpenAsync();
        try
        {
            location.BackupDatabase(destination);
        }
        finally
        {
            // 手动关闭目标连接
            await destination.CloseAsync();
        }
    }

    return Path.GetFullPath(backupFilePath);
}

可选优化：放宽文件共享权限 #

上传文件时，将FileShare.None改为FileShare.Read，允许其他进程同时读取文件，减少锁冲突概率：

await using var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.Read);

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