我在同一个表中插入多线程时遇到问题。从错误消息中似乎正在复制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 db 不应该有这个问题，因为它在带有标志THREADSAFE=1的serialised模式下运行。它应该将多个插入排队，并为每个插入提供不同的 PK。
我还使用以下PRAGMA：
PRAGMA synchronous=NORMAL;
PRAGMA journal_mode=WAL;
PRAGMA foreign_keys=ON;
我正在使用 .NET 程序集的 SQLite v3.27.2。
更新
似乎与FOREIGN KEY有关(感谢蒂姆·比格莱森。我怎么会不这么想？我已经做了一些检查，并且之前添加了外键的记录。一些调试输出：
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
可以看出，很久以前就添加了另一个Obj id 9，但失败仍然发生了。
从错误消息中可以看出，失败实际上与Logs表中的外键有关：
外键约束失败
失败很可能是因为您的一个(或多个)插入正在使用OtherObjId值，该值在OtherObjs表中没有映射Id对应项。 若要解决此问题，应调查OtherObjId值的来源，并确保它们在OtherObjs中具有父记录。 也许存在多线程问题，或者可能有其他原因。