Upgrading transaction with statements using triggers and contentless fts5 causes SQLITE_BUSY timeouts

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Upgrading transaction with statements using triggers and contentless fts5 causes SQLITE_BUSY timeouts

Mikal H Henriksen
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users