Non-ACID behaviour in Sqlite driver.
LennDG opened this issue · 1 comments
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
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: #2099 (comment)