Handle locked database

[squaregoldfish]

If one process is writing to a database, nothing else can read from it because it's locked. This results in an SQLite.SQLiteException("database is locked"). We can get round this by catching the exception and trying again after a few milliseconds, but would it be desirable to build this functionality into the library?

There's a number of ways it could be done, but the most obvious is to add optional arguments for handleLockedDatabase=true|false and a timeout=x, which is a number of seconds to keep retrying before giving up. I guess handleLockedDatabases should be false by default. Although it would be useful to be able to set a global option somehow to save setting it every time.

I'm willing to do some work on this if you think it's a good idea.

[quinnj]

We could potentially provide a macro retry that implemented the logic you've mentioned. I kind of like the idea of having a macro that people would then use like @retry SQLite.query(db, "...") because it's pretty lightweight and doesn't mess with global settings/config or anything. With global settings, I always worry because of situations where a package you're using ends up using SQLite for something and they're setting their own global settings and now they're messing with yours, etc. A quick macro seems like a way users can easily get the functionality w/o affecting others.

Does that sound ok? I'm happy to help discuss what the implementation would look like if necessary (depending on your comfort level with macros).

[squaregoldfish]

A macro sounds like a perfect solution! I've never written a proper one before, but I'll do some reading and see if I can make something.

[tosone]

we should set this busy_timeout definition, @retry is not a good solution.

https://www.sqlite.org/pragma.html#pragma_busy_timeout

[hellemo]

Please have a look at #218 adding support for busy_timeout

[quinnj]

I don't quite understand how busy_timeout works, but for those that do, I'd gladly welcome any additional PRs that could provide nice a nice API for handling a locked database.