SQLite Forum

Snapshot isolation not working?
Login

Snapshot isolation not working?

(1) By Nico (cryptonector) on 2020-10-08 21:49:09 [source]

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.

(2.1) By Keith Medcalf (kmedcalf) on 2020-10-08 23:34:34 edited from 2.0 in reply to 1 [link] [source]

Looks like you are opening the database then forking. This does not work. You must fork then open the database.

(3) By Nico (cryptonector) on 2020-10-09 01:17:35 in reply to 2.1 [link] [source]

The parent opens the DB, forks a child that then also opens the DB. Perhaps SQLite3 builds state that is not fork-safe in the process?

Changing the test to fork and only then open DB in the parent and child (and also fixing a couple of bugs along the way) solved the problem. Thanks!

(4) By Keith Medcalf (kmedcalf) on 2020-10-09 03:59:47 in reply to 3 [link] [source]

It is documented thusly here https://sqlite.org/howtocorrupt.html#_carrying_an_open_database_connection_across_a_fork_:

Do not open an SQLite database connection, then fork(), then try to use that database connection in the child process. All kinds of locking problems will result and you can easily end up with a corrupt database. SQLite is not designed to support that kind of behavior. Any database connection that is used in a child process must be opened in the child process, not inherited from the parent.

Do not even call sqlite3_close() on a database connection from a child process if the connection was opened in the parent. It is safe to close the underlying file descriptor, but the sqlite3_close() interface might invoke cleanup activities that will delete content out from under the parent, leading to errors and perhaps even database corruption.