Non-ACID behaviour in Sqlite driver.

[LennDG]

Bug Description

While doing some testing for using SQLite in a server environment, I discovered behaviour that is non-atomic when doing writes and reads consecutively.

When in WAL mode with synchronous set the Full, a write to a table that returns the id (primary key) followed immediately by a get on that table for that id fails to fetch the row. Obviously this is not good code, but it would be easy to write this accidentally.

In further testing, I found that using rollback journal mode fixes this for the case where you do it once, but not if you repeat this a couple thousand times, a non-deterministic row does not get found after a certain amount of write/read operations.

Setting the synchronous mode to Normal or Off also does not completely prevent this issue, it only shows up after x amount of operations.

Putting a short pause (~20ms with tokio::sleep) between writes and reads reduces the amount of times the issue happens, but does not completely eliminate as far as my testing shows. This may vary between machines.

I suspect that this is not an issue in libsqlite3 , as they are very extensively tested, but rather something to do with the interface to the library.

There seem to be some related issues like #2099 and #3080. My reproduction hopefully contributes to eventually tackling this problem.

Minimal Reproduction

Here is a repo that has a minimal reproduction that can be easily edited for producing different behaviours.

Info

• SQLx version: 0.7
• SQLx features enabled: [ "macros", "runtime-tokio", "sqlite", "migrate"]
• Database server and version: SQLite
• Operating system: Ubuntu
• rustc --version: 1.76

[code-ape]

Thanks for filing this @LennDG! Just wanted to voice that I've hit something very similar this week, very concerning to see a local SQL database failing ACID.

UPDATE: I appear to have debugged this down to the fact that .fetch_one() and .fetch_optional() return eagerly before commit is complete for SQLite. Full details in comment on different issue in this repo here: https://github.com/launchbadge/sqlx/issues/2099#issuecomment-2010772576