On 8/24/15, Daniel Coles 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
