C# SQLite数据库锁定问题：多标签图片插入时报错

我有一个包含Tag列表的ImageData类，仅当图片拥有2个及以上标签时会触发SQLite database locked错误，0个或1个标签（无论单张或百张图片）均正常运行。已使用using语句确保资源释放，以下是相关插入方法代码及表创建语句，其他循环插入场景无此问题。

插入方法代码 #

public static int addImages(List<ImageData> images)
{
    int rows = 0;
    using (SQLiteConnection con = new SQLiteConnection(ConnectionString()))
    {
        con.Open();
        foreach (ImageData img in images)
        {
            using (SQLiteCommand cmd = new SQLiteCommand($"INSERT INTO images(Hash, Extension, Name) VALUES(@IHash,@IExtension, @IName)", con))
            {
                cmd.Parameters.AddWithValue("@IHash", img.Hash);
                cmd.Parameters.AddWithValue("@IExtension", img.Extension);
                cmd.Parameters.AddWithValue("@IName", img.Name);
                rows += cmd.ExecuteNonQuery();
            }
            foreach (Tag tag in img.Tags)
            {
                using (SQLiteCommand cmd = new SQLiteCommand($"INSERT INTO ImagesTags(ImageHash, TagName) VALUES(@IHash,@IName)", con))
                {
                    cmd.Parameters.AddWithValue("@IHash", img.Hash);
                    cmd.Parameters.AddWithValue("@IName", tag.Name);
                    cmd.ExecuteNonQuery();
                }
            }
        }
        con.Close();
    }
    
    return rows;
}

表创建语句 #

string[] createTable =
{
    "CREATE TABLE images(Hash TEXT PRIMARY KEY, Extension TEXT, Name TEXT)",
    "CREATE TABLE tags(NAME TEXT PRIMARY KEY NOT NULL,DESCRIPTION TEXT,COLLECTIONNAME TEXT)",
    "CREATE TABLE ImagesTags(ImageHash TEXT,TagName TEXT,Primary KEY (ImageHash, TagName),FOREIGN KEY (ImageHash) REFERENCES images(Hash),FOREIGN KEY (TagName) REFERENCES tags(Name))"
};

1. 隐式事务引发锁竞争：SQLite默认会为每个ExecuteNonQuery()操作创建隐式事务并立即提交。当图片有2个及以上标签时，短时间内多次触发小事务提交，数据库锁的频繁获取与释放极易引发冲突，导致锁定错误。
2. 外键约束叠加开销：每次插入ImagesTags记录时，SQLite会验证外键关联的images和tags表数据，频繁的约束检查会延长锁的持有时间，进一步加剧冲突概率。

1. 用显式事务包裹全流程 #

将所有插入操作放在一个显式事务中，仅在全部操作完成后提交一次，大幅减少事务次数和锁竞争。同时复用SQLiteCommand对象，降低资源创建开销。

修改后的代码示例：

public static int addImages(List<ImageData> images)
{
    int rows = 0;
    using (SQLiteConnection con = new SQLiteConnection(ConnectionString()))
    {
        con.Open();
        // 开启显式事务
        using (SQLiteTransaction transaction = con.BeginTransaction())
        {
            // 复用图片插入命令
            using (SQLiteCommand imgCmd = new SQLiteCommand("INSERT INTO images(Hash, Extension, Name) VALUES(@IHash,@IExtension, @IName)", con, transaction))
            {
                // 预先定义参数
                imgCmd.Parameters.Add("@IHash", DbType.String);
                imgCmd.Parameters.Add("@IExtension", DbType.String);
                imgCmd.Parameters.Add("@IName", DbType.String);

                // 复用标签关联插入命令
                using (SQLiteCommand tagCmd = new SQLiteCommand("INSERT INTO ImagesTags(ImageHash, TagName) VALUES(@IHash,@IName)", con, transaction))
                {
                    tagCmd.Parameters.Add("@IHash", DbType.String);
                    tagCmd.Parameters.Add("@IName", DbType.String);

                    foreach (ImageData img in images)
                    {
                        // 设置图片参数并执行插入
                        imgCmd.Parameters["@IHash"].Value = img.Hash;
                        imgCmd.Parameters["@IExtension"].Value = img.Extension;
                        imgCmd.Parameters["@IName"].Value = img.Name;
                        rows += imgCmd.ExecuteNonQuery();

                        // 插入当前图片的所有标签
                        foreach (Tag tag in img.Tags)
                        {
                            tagCmd.Parameters["@IHash"].Value = img.Hash;
                            tagCmd.Parameters["@IName"].Value = tag.Name;
                            tagCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
            // 提交所有操作
            transaction.Commit();
        }
        con.Close();
    }
    return rows;
}

2. 启用WAL模式优化并发 #

在SQLite连接字符串中添加Journal Mode=WAL，WAL（Write-Ahead Logging）模式可提升读写并发性能，减少锁冲突概率。示例连接字符串：

private static string ConnectionString()
{
    return "Data Source=your_database.db;Journal Mode=WAL;";
}

3. 确保标签预存在于tags表 #

由于ImagesTags表的外键约束要求TagName必须存在于tags表中，若插入不存在的标签会触发约束错误，间接导致锁资源异常。建议在插入标签关联前，先确保tags表中已存在对应标签。

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