David Gewirtz wrote:
John Stanton wrote:
Perhaps a thread per open database sync'd on an event and driven by a
queue would give you contention-free operation and avoid the need to
ever have the DB locked.
A thread would post its request to the queue then wait on an event
signifying completion.
That's a great approach. The only problem I see to that is what happens
with, say, a user who's getting a portion of his or her results in a query,
like you would with a page of Google search results? When user A wants the
next page of search results, user B's query might already be in progress,
messing up the whole thing.
Unless, of course, I misunderstand the use of the query ID, and that's why
the query ID exists.
While we're on the topic of threads, though, I've also found some
conflicting information in Michael Owens' book. In it, Owens states:
"Starting with 3.3.1, you can pass connections around between threads as
long as they are in the UNLOCKED state at the time of the transfer (the
connection has no transaction open)."
Doesn't this mean that multiple Web threads could interact with a given
database as long as the LOCKED state is well-managed?
I guess I'm confused still.
-- David
My understanding and experience with Sqlite is that the restriction on
open DB handles being used across threads is merely to do with POSIX
file locking deficiencies in some OS implementations. If you operate
within one process using threads you have no need of file locking and
should have no problems with threads. Within the process mutexes and
events give you better control and lower overhead than file locking.
If you use multiple processes it is my conclusion that it is better to
use the file locking than semaphores.
If you are getting pages of data within one query you have a possible
multi-user problem with Sqlite. I imagine that you would avoid it by
making each page a transaction with a LIMIT and a WHERE using a greater
(or less) test on the key. Obviously the key should be indexed.
If you use a thread per open DB you can choose to kill the thread when
you close the DB or cache the threads to limit creation/kill overheads
(which are not great for threads). In a threaded WWW server you can
choose to keep the DB open between connections or open and close with
connections.
In the threaded WWW server I wrote to incorporate Sqlite the DB's are
opened and closed with connections because the applications use
different databases per client. If the predominant usage were a small
number of DBs then I would maintain a pool of open database threads and
use them as necessary, with a least-recently-used allocation so that
dormant DBs are eventually closed and put to sleep.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
