dotnet / dotnet/EntityFramework.Docs

Document disabling retries in SQLite

Open
#3,470 4 comments 0 reactions 0 assignees View on GitHub
area-sqlite
Dominant language
Mermaid
Stars
1.7k
Forks
2k
Avg merge
7d 23h
Merged PRs (30d)
16

Description

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.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.