Document disabling retries in SQLite

[alexthornton1]

Presently, Microsoft.Data.Sqlite automatically retries commands when they fail due to SQLite indicating that the database is busy or locked. I would like to have the ability to turn this behavior off explicitly.

As to the question of why I'm interested in this feature, I'm presently plugging Microsoft.Data.Sqlite (among other SQL implementations) into a higher-level database library that has a couple of characteristics that are germane to my request:

1. It is centered around the idea of running transactions that each consist of a sequence of commands.
2. It provides automatic support for retrying transactions when they fail due to transient errors, such as concurrency issues (deadlocks, etc.). In this situation, the goal is to retry them in their entirety.

To best support multiple concurrent readers with SQLite, I'm using deferred transactions. This can lead to transactions failing in mid-stream, in which case the database library will automatically handle retrying the transaction, but only after waiting for Microsoft.Data.Sqlite to retry the doomed command until the timeout expires. I'd like for Microsoft.Data.Sqlite to simply fail and let my database library handle the failure itself.

My first solution was to set the command timeout to 1, which appeared to be the smallest value that would give me what I wanted, but that meant there would be a one-second penalty whenever this situation occurred. After looking at the code in Microsoft.Data.Sqlite, I discovered that I could likely set the command timeout to a negative value instead, though this appears to be an accident of the way the feature is implemented more than being a documented approach to achieve what I need. (My concern is a future re-implementation could remove this workaround.) On top of that, Dapper is also part of this implementation, which introduces the possibility of the command timeout being adjusted on a command-by-command basis, with a radically different meaning on SQLite than on other database engines, so it would be better to be able to simply turn the automatic retries off altogether.

Here's what I would like to see:

• By default, Microsoft.Data.Sqlite continues to behave as it does, retrying commands automatically when the database is busy or locked.
• By either setting a keyword in the connection string, a property on the SqliteConnection, or a property on the SqliteCommand, it would be possible to turn this behavior off, so that the first failure of a command would result in an exception being thrown.

I've taken the liberty of implementing a first attempt at this change already and will raise a pull request.

Thank you for your consideration.

[ajcvickers]

/cc @bricelam

[bricelam]

Using a timeout of -1 will disable any retries.

[bricelam]

This design is intentional, and we have tests for it. Agreed that we should document it better.

[alexthornton1]

Great news! As long as there's a well-defined way to avoid the retries, I'm a happy guy.

Thank you!