Database Connection Lock Issues with Web Workers

[KoenRijpstra]

Problem Description: I’m using two web workers that perform long tasks and update a queue in the database, while the main thread reads the progress from the same database. Currently, each creates its own database connection, which seams to leads to lock issues.

OPFS syncer: xLock() async error: GetSyncHandleError: Error getting sync handle for xLock(). 6 attempts failed. database.db : NoModificationAllowedError: Failed to execute 'createSyncAccessHandle' on 'FileSystemFileHandle': Access Handles cannot be created if there is another open Access Handle or Writable stream associated with the same file.

I am using PRAGMA journal_mode = WAL;

Question: Is it possible to share the main thread's database connection with the web workers?

[DallasHoff]

That is a SQLite error because you are running into a limitation with SQLite: only one connection can have an open write transaction on the database at once. SQLite will wait a bit and retry a few times (i.e. the "6 attempts failed" mentioned in that error message), but if a long-running write transaction blocks a second write transaction for too long, SQLite will throw an error. This is why it is recommended that you keep writing transactions short and close them quickly.

I assume your long tasks are waiting on network data to arrive while they have the transactions open. If so, you should see if you can close the transactions between batches of writes or otherwise break up your database queries. Having those 2 workers attempt to have long-running transactions on the database at the same time is creating contention for the database file.

Regarding your question: No, SQLite connections cannot be shared between threads. They are necessarily per-thread handles.

[KoenRijpstra]

Thank you so much! I'll see if I can minimize the writing time. Sent you some lunch money! PS: Should every connection set the journal_mode to WAL or is it enough to only do this on the main thread?

[DallasHoff]

Hey thanks for the donation! To answer your question, PRAGMA settings only affect the connection they are run on, so yes, you should set journal_mode and any other PRAGMA settings on every connection.

[KoenRijpstra]

Thanks for your help! I’ve resolved most of the lock errors with the optimizations. Is there a way to adjust the number of retries? Also, how long is the wait time between each retry?

[DallasHoff]

Is there a way to adjust the number of retries? Also, how long is the wait time between each retry?

No, the logic for that is hard-coded into sqlite-wasm, so it's not adjustable. It will wait 300ms before the first retry, and each subsequent retry will wait for 300ms longer (600ms for retry 2, 900ms for retry 3, etc) up to a maximum of 6 retries.