-
-
Notifications
You must be signed in to change notification settings - Fork 393
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Encountering SQLITE_BUSY (or SQLITE_BUSY_SNAPSHOT) on multi process concurrent queries only when using transactions #1067
Comments
I did a quick peek (not an expert on that topic): better-sqlite3 passes the timeout to better-sqlite3/src/objects/database.lzz Line 177 in ae23e69
but it never calls
But in addition the docs also say:
So even with properly configured timeout you can run into a case where you immediately get SQLITE_BUSY. This could be the case for you, you need to provide a minimal repo for that. But in either case this would be a SQLite issue because better-sqlite3 itself does not actually implement any timeout or retry logic. @JoshuaWise am I wrong with my conclusion or does the |
Also see #155 |
Ok - in short this then means that irrespective of any better-sqlite3 internal timeout handling, there can always be cases of such an exception on writes, which applications that can't afford to just error, need to handle by implementing their own rollback+retry. I also conclude from your observation of internal timecode handling, that the higher incidence of such an error when using a wrapping transaction is either coincidental or sqlite3 internal behaviour. Thank you very much for the clarification. |
In a scenario where two parallel Node processes using better-sqlite3 perform the same UPDATE queries wrapped in a transaction (either using BetterSQLite API, or manually using BEGIN, COMMIT) concurrently, one UPDATE occasionally throws an exception SQLITE_BUSY (journal DELETE) or SQLITE_BUSY_SNAPSHOT (WAL) well before the 5s retry timeout has passed.
This behaviour disappears if the enclosing transaction is removed.
My understanding from the documentation would be that even with an enclosing transaction, better-sqlite3 should perform a retry automatically within the timeout specified. If this understanding is incorrect, I would appreciate some insight (and possibly extension in the documentation) as to how the transaction scenario is different and how to handle such a situation gracefully in the better-sqlite3 using code.
The text was updated successfully, but these errors were encountered: