I've hit a problem that causes simple parallel inserts to hit the 30 second
busy timeout. Here's the setup, using suggested trigger setup from the fts5 doc
page:
CREATE TABLE resource(id, title, data);
-- Full-text search (fts) for resources
CREATE VIRTUAL TABLE resource_fts USING fts5 (
title, data, content = 'resource'
);
-- Weigh title matches 10 times as much as other columns
INSERT INTO resource_fts(resource_fts, rank) VALUES('rank', 'bm25(10.0)');
-- Auto-update fts index - https://sqlite.org/fts5.html#external_content_tables
CREATE TRIGGER insert_into_resource_fts AFTER INSERT ON resource BEGIN
INSERT INTO resource_fts(rowid, title, data) VALUES (NEW.rowid, NEW.title,
NEW.data);
END;
CREATE TRIGGER delete_from_resource_fts AFTER DELETE ON resource BEGIN
INSERT INTO resource_fts(resource_fts, rowid, title, data) VALUES ('delete',
OLD.rowid, OLD.title, OLD.data);
END;
CREATE TRIGGER update_resource_fts AFTER UPDATE OF title, data ON resource BEGIN
INSERT INTO resource_fts(resource_fts, rowid, title, data) VALUES ('delete',
OLD.rowid, OLD.title, OLD.data);
INSERT INTO resource_fts(rowid, title, data) VALUES (NEW.rowid, NEW.title,
NEW.data);
END;
The statements I'm running are:
BEGIN; INSERT INTO resource VALUES ('guid', 'title text', 'some random data');
COMMIT;
If I run this simultaneously on a handful of threads (in a single process; 2
threads is enough to reproduce), then all the connections will hang for the
sqlite busy timeout duration of 30 seconds, and then some will succeed while
the rest throw SQLITE_BUSY. It also happens with UPDATE and DELETE.
I've found some things that work around this:
- Using BEGIN IMMEDIATE. This suggests that upgrading the read transaction to
write is part of the problem.
- Using implicit transaction, i.e. no BEGIN or COMMIT. I guess this works out
to the same as above since it's an INSERT.
- Using an explicit statement in the transaction instead of creating the
triggers. This suggests that the triggers are also part of the problem.
- Running the operations sequentially, i.e. single threaded.
I have a minimum repro gist written as a .NET Core 2.0 test project here:
https://gist.github.com/MHHenriksen/b3edb0f4020e4c304b92463c3c9223a5 with code
that is pretty close to the real app that is suffering from this. It
demonstrates the failing case, and also the workaround cases. It IS possible
that the issue is in the Microsoft.Data.Sqlite library, of course, but I don't
have the knowledge nor the dev environment to reproduce this in a plain c app
in order to rule that out. I've reported a related issue with them to make it
possible to use BEGIN IMMEDIATE here:
https://github.com/aspnet/Microsoft.Data.Sqlite/issues/416 (and I plan to just
write custom code to do this easily for now) but my gut feeling is that there's
a deeper cause in Sqlite itself.
This is tested with Microsoft.Data.Sqlite 1.1.0, which uses Sqlite 3.13.0, but
if I did it right I also reproduced it with 3.20.1.
Can you help me get to the bottom of this? And if nothing else, since this is
using the example triggers straight from the fts5 documentation, the very least
that needs to be done is to update that with a warning (only if it's
reproducible with plain c, of course).
- Mikal
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
