gpt4 book ai didi

c# - SQLite多线程插入到同一表导致FOREIGN KEY约束失败

转载 作者:行者123 更新时间:2023-12-03 19:12:29 27 4
gpt4 key购买 nike

我在同一个表中有多线程插入问题。从错误消息看来,PK已被复制:

错误:


外键约束失败


表:

-- Logs
CREATE TABLE "Logs" (
"Id" INTEGER PRIMARY KEY,
"Time" DATETIME NOT NULL,
"Message" TEXT NOT NULL,
"OtherObjId" INTEGER NOT NULL,
FOREIGN KEY(OtherObjId) REFERENCES OtherObjs(Id) ON DELETE CASCADE
);
CREATE INDEX IDX_Logs ON Logs (OtherObj);


代码(可能从多个线程称为异步代码):

public async Task AddLogAsync(Log log) {
using (var cmd = _conn.CreateCommand()) {
cmd.CommandText = $"INSERT INTO Logs (Time, Message, OtherObjId) VALUES ('{DateTime.UtcNow}', @message, @OtherObjId);";
cmd.Parameters.AddWithValue("@message", log.Message);
cmd.Parameters.AddWithValue("@otherObjId", log.OtherObj.Id);
await cmd.ExecuteNonQueryAsync();
}
}


我的理解是,由于Sqlite数据库在 serialised模式下运行且带有标志 THREADSAFE=1,因此不应出现此问题。它应该将多个插入队列排队,并为每个插入分配不同的PK。

我还使用以下 PRAGMA

PRAGMA synchronous=NORMAL;
PRAGMA journal_mode=WAL;
PRAGMA foreign_keys=ON;


我正在使用.NET程序集使用SQLite v3.27.2。

更新资料

看来它与 FOREIGN KEY有关(谢谢Tim Biegeleisen。我怎么可能不这么认为?)。我已经做了一些检查,外键的记录也早已添加。一些调试输出:

Log about to be added for otherObj id 1
otherObj added with id 2
Log about to be added for otherObj id 2
otherObj added with id 3
Log about to be added for otherObj id 3
otherObj added with id 4
Log about to be added for otherObj id 4
otherObj added with id 5
Log about to be added for otherObj id 5
otherObj added with id 6
Log about to be added for otherObj id 6
otherObj added with id 7
Log about to be added for otherObj id 7
otherObj added with id 8
Log about to be added for otherObj id 8
otherObj added with id 9
Log about to be added for otherObj id 9
otherObj added with id 10
Log about to be added for otherObj id 10
otherObj added with id 11
Log about to be added for otherObj id 11
otherObj added with id 12
Log about to be added for otherObj id 12
otherObj added with id 13
Log about to be added for otherObj id 13
otherObj added with id 14
Log about to be added for otherObj id 14
otherObj added with id 15
Log about to be added for otherObj id 15
otherObj added with id 16
Log about to be added for otherObj id 16
Log about to be added for otherObj id 1
Log about to be added for otherObj id 2
Log about to be added for otherObj id 3
otherObj added with id 17
Log about to be added for otherObj id 17
Log about to be added for otherObj id 4
otherObj added with id 18
Log about to be added for otherObj id 18
Log about to be added for otherObj id 5
otherObj added with id 19
Log about to be added for otherObj id 19
Log about to be added for otherObj id 6
otherObj added with id 20
Log about to be added for otherObj id 20
Log about to be added for otherObj id 7
Log about to be added for otherObj id 8
otherObj added with id 461
Log about to be added for otherObj id 461
SQLite error (787): abort at 21 in [INSERT INTO Logs (Time, Message, otherObjId) VALUES ('25/04/2019 11:35:27', @message, @otherObjId);]: FOREIGN KEY constraint failed
Exception thrown: 'System.Data.SQLite.SQLiteException' in System.Private.CoreLib.dll
Log about to be added for otherObj id 9
Exception thrown: 'System.Data.SQLite.SQLiteException' in System.Private.CoreLib.dll
An exception of type 'System.Data.SQLite.SQLiteException' occurred in System.Private.CoreLib.dll but was not handled in user code
constraint failed
FOREIGN KEY constraint failed


可以看出,otherObj id 9是在很久以前添加的,但是失败仍然发生。

最佳答案

从错误消息中可以看到,失败实际上与Logs表中的外键有关:


外键约束失败


失败很可能是因为您的一个(或多个)插入使用的是OtherObjId值,而在Id表中没有对应的OtherObjs映射。要解决此问题,您应该调查OtherObjId值的来源,并确保它们在OtherObjs中具有父记录。也许存在多线程问题,或者可能还有其他原因。

关于c# - SQLite多线程插入到同一表导致FOREIGN KEY约束失败,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/55846774/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com