Non-ACID behaviour in Sqlite driver.

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

launchbadge / sqlx