What's the best way to handle "database is locked" ?

[darachm]

Hey, neato plugin. I may have missed it, but I was wondering how I should deal with rapid writes to a database where there's an inconsistent failure where it seems very clear with:

ERROR ~ [SQLITE_BUSY] The database file is locked (database is locked)

Is there a feature I missed that can just wait and retry several times? Is that needed/appropriate? Am I using it wrong?

I very much may have just read past it, sorry for the hassle if that's the case. I'm known as a space cadet 👾

I assume I can't handle this in the SQL create/insert templates.

Application note FYI

I'm trying to stuff records from hundreds of processes into one sqlite database to gather it. Right now I can just relaunch it, and that usually fixes it, but eventually I want this thing to be hands-off, eventually.

What next

I like this plugin, but I'm also wondering if I could alternatively whip it up in a subworkflow pointing to an absolute path (from a parameter), and then I could setup the waiting logic, and bypass using this plugin.

Alternatively, if you think this is something that should be coded up, I'm interested in learning more about plugins.
( I haven't listened to the podcast episode yet, but I hear they're all the rage 😉 )
I don't know Java/Groovy except in making Nextflow do weird things like loops, but I'd be interested in doing a pull request - if y'all think it is useful (and if it isn't already in the documentation as a feature!!!).

If it was wanted, I am thinking it would be a retry: parameter that setups a loop of tries, and you break if it is successful.

[darachm]

I've went ahead and instead used a workflow/process to deconflict this. So that looks a bit ugly, but it's pulling all the desired tuples into a single val input for a process, then using inline groovy to collect that into a list of sqlite3 BASH commands and executing that bash script.

I also found that as I was arranging the tuples of keys and values, because I had a lot of them flowing through channels then I got an infrequent (individually) but common (for the whole pipeline) collision where variables are being modified by multiple closures. So adding a bunch of def everywhere seems to have solved that for now.

Anyways, I'm no longer using this plugin. Even without the locking issues, I suppose my design is inappropriate for this plugin because I have subsequent step that uses the sqlite database to generate a static report. But I think it's still useful, for the future, if I'm using different methods for generating reports dynamically or whatnot

[dmacguigan]

Hello, I've encountered the same issue with my own pipeline. Is there any solution to this, aside from hacky bash scripts? I'd rather not abandon the plugin, as it makes NF SQL interaction much cleaner.

[pditommaso]

Likely this happens because there's a concurrent access to the SQLite db.