What should I do to refresh data with concurrent sqlite 3 access

Question

I have two sqlite windows clients concurrently using a sqlite 3 base on the network. When one appends data the record is successfully appended (there's no the journal file at the moment). But another client doesn't see this record even when a fresh SELECT query is fired and starts to see this record of this table after some SELECT queries from another table.

So the question is: is there the "official" way to reset internal buffers of sqlite completely to refresh whole data? Disconnecting from the DB would be a best bet, but maybe there's a way to do this without full disconnection?

Answer 1

Using SQLite over a network share is a dangerous practice due to the buggy implementations of network file sharing software on both UNIX and Windows machines (causing SQLite locking mechanism to fail sometimes).

Don't be surprised if your database gets corrupted this way.

So - regardless of your current problem, I'd advise you change your software to prevent using a network share with SQLite.

If you still want to use the network share solution - I'd suggest you add Synchronous=Full pragma (or its equivalent) to the connection string you are using in order to make SQLite flush everything to disk synchronously.

Liron Levi (author of the SQLite Compare diff/merge utility)