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 :space_invader:

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 :wink: ) 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