Is it safe to use the database at the same time in background and foreground (workmanager)

[biessek]

Hi.

I'm having problems, in production. Some times I get the database is locked error, I thing its not a issue, only, using the library in the right way.

The problem occurs, I guess, by the concurrency. I have an open connection in the app and I also have a workmanager that runs every 15 minutes, opens another connection and reads and writes. I believe that the lock occurs there, I ask. What is the best strategy to avoid this? (Knowing that the workmanager runs regardless of the app. That is, it uses another connection with the bank)

[simolus3]

I recently saw a similar issue (#1042). I think it should be safe to just retry when you get that exception. You could write an implementation of QueryExecutor that wraps another executor (typically a VmDatabase) and retries if operations fail with that exception.
Also, you might want to consider enabling WAL mode if you need concurrency. With WAL, a writer does not block readers (and vice-versa). Multiple concurrent writes still aren't allowed, but this might help a bit.

[biessek]

Got it, thanks, just a question about "Multiple concurrent writes still aren't allowed" means that I can't be running two writes at the same time or depends on the table / row? in case I need this "almost simultaneous writing" Is implementing a custom QueryExecutor the best option then? right?

[simolus3]

I can't be running two writes at the same time or depends on the table / row

I'm actually not sure about that, but I think sqlite3 always locks the whole database.

case I need this "almost simultaneous writing" Is implementing a custom QueryExecutor the best option then? right?

So far, I'm afraid there isn't anything better (or at least I'm not aware of anything). If you can communicate between the worker and the UI in real time, you could setup a single moor isolate to handle writes. That will avoid concurrent writes by serializing them. You can still run reads on the "local" (worker / UI) isolate with a MultiExecutor.

But apart from that, I think your best bet is catching the "database locked" errors in a custom QueryExecutor and retrying in that case.

[biessek]

Perfect @simolus3. Understood! I will work with the custom QueryExecutor