Hi, I've an application that uses SQLite3, and a test that evinces
failure to provide snapshot isolation. The test does the following:
- fork()s to create a child process, then the parent and child take turns
(synchronizing over a pair of pipes) to:
- INSERT two rows in the parent (all of this in autocommit mode)
- then execute a SELECT of all rows in that table and step it twice
- then INSERT one more row in the parent
- then step the SELECT in the child once more
The test program tests LMDB and SQLite3. Using LMDB the child correctly does not see the third row, the one added after the child began executing its iterator (SELECT), but using SQLite3 the child does see it.
Currently I've not written a standalone test outside this application,
so I don't yet have a minimal reproducer, but you can see a test here:
- https://github.com/heimdal/heimdal/blob/master/lib/hdb/test_concurrency.c
that a minimal reproducer could be fashioned from. The application code
calling SQLite3's C API is here:
- https://github.com/heimdal/heimdal/blob/master/lib/hdb/hdb-sqlite.c
I've tested this with SQLite3 3.17.0 (which is included in Heimdal's
master branch at this time) and with the current SQLite3 release
(3.33.0), and both produce the same behavior:
```
$ ./test_concurrency
Testing SQLite3 HDB backend
Writing two entries into HDB
Starting reader process
Reader process opening HDB
test_concurrency: hdb-sqlite: prepare busy
Reader process iterated one entry; telling writer to write more
Reader process waiting for writer
Reader process iterating another entry
Reader process iterated another entry
--->test_concurrency: HDB sqlite:testhdb-LtYtsB sees writes committed since starting iteration (foobar): Success
Reader process done; telling writer to go
Reader process exiting
Testing LMDB HDB backend
Writing two entries into HDB
Starting reader process
Reader process opening HDB
Reader process iterated one entry; telling writer to write more
Reader process waiting for writer
Reader process iterating another entry
Reader process iterated another entry
Reader process done; telling writer to go
Reader process exiting
$
```
(The application is not entirely trivial because it's using SQLite3 as
one backend to a pluggable interface to a simple key/value store
where the values as encodings of a complex structure. Ideally that
store would be relational and SQL-based only, with SQLite3 and
PostgreSQL backends, but it's not, not yet.)
I'm not sure what we're doing wrong, if anything.
I'll understand if you want me to put together a minimal reproducer
that's completely independent of the application.