SQLite Forum

In-memory database from sqlite file
> This is an interesting point. Does this mean that by doing this the risk is limited to losing some records (or not being able to read anything at all)? Or is this completely undefined behaviour (so we could read garbage data that is not present in the DB)?

I would assume so yes. Let's see if we can construct a problem.

We've got a 1 table database. table1 is made of a single leaf page on page 2. sqlite_master is on page 1 with a record saying table1 starts on page 2.

You're now inserting into table1, and it's grown enough that it needs a new page to hold data. Since it's now more than 1 page long, we're gonna need a new trunk page as well as another leaf page. Let's put the trunk page on page 3, and the new leaf page on page 4.

So we're at least gonna have to update page 1 with the new root page of table1 which is now page 3. We're gonna have to update page 3 with the pointers to pages 2 and 4. And we're gonna have to update page 4 with the new insert into. If we're inserting out of rowid order we might need to update page2 with the new info, and move some records to page 4.

Depending on the order these things happen and when you jump in there, you can see all sorts of potential problems. The SQLite library doing the inserts doesn't care, it has the file locked, so from its point of view no one else should be reading.

If we update sqlite_master first before appending to the file then we're pointing to a page that doesn't exist, and you won't see any records. If we move records from page 2 to page 4 before we update sqlite_master, then you could miss those moved records because you've got the wrong root page. Pick any order you want and you can find a spot to jump in and see an incomplete or broken view.

Depending on the circumstances the time windows for getting a bad view might be tiny or they might be huge, but they're definitely there.