On 8/24/15, Daniel Coles <daniel.coles at astadev.com> wrote: > Hello, > > Our application is to use SQLite to store its data. It uses a > multi-threaded server, though in most cases SQLite would be accessed by one > thread at a time. The application runs on Microsoft Windows. > > Our initial SQLite implementation followed our interpretation of the > guidelines laid out here > (http://www.sqlite.org/cvstrac/wiki?p=MultiThreading), in that: > * We use "multi-thread" mode > * Threads create connections ('sqlite3' objects) to the database when > needed, and then close them again once an operation is complete > * The 'sqlite3' objects are not shared between threads > > Implementing encryption through the SQLite Encryption Extension (SEE) is > causing me to question whether this is the correct strategy. I would like > to know: > * Is there any sharing of information between the 'sqlite3' objects? In > particular, the in-memory unencrypted database page-caches; are those > shared?
No, and no. Unless you turn on shared-cache mode (https://www.sqlite.org/sharedcache.html) and stop constantly closing and reopening your database connections, then the answer would be Yes and Yes. > * What governs the lifetime of the page-caches? The page cache is discarded for lots of reasons: (1) you close the database connection. (2) another process modifies the database file, rendering the cache stale. (3) You invoke PRAGMA shrink_memory. Etc. > * We do not want multiple application instances to modify the same file at > once. SQLite automatically serializes writes. You do not have to do anything to make this happen. > * What happens if an application unexpectedly exits while having a SQLite > file exclusively locked? Is the lock automatically released by the > underlying OS? Yes. And any uncommitted changes are rolled back. > > My current suspicion is that I will need to change our SQLite threading so > that we have one persistent 'sqlite3' connection that is shared between > threads. Does that sound advisable? > That depends on so many things that it is hard to say. You could certainly try it and see how it works out! But I think if you just avoid constantly opening and closing connections, things will go better. -- D. Richard Hipp drh at sqlite.org
