That is why the Sqlite locking is not a good fit for a threaded server.
Why not use thread locks instead and achieve the synchronization with
minimum overhead and latency? You do miss out on a couple of Sqlite
features doing that (the pending and reserved locks which help with
concurrency and write starvation) so you need to balance the benefits of
them against the detrimental effects of polling.
In our older embedded Sqlite threaded applications we just serialized
Sqlite access using a mutex because concurrency was not a prime issue,
but use read/write locks in a higher traffic Sqlite based multi-threaded
application server.
After experimentation, which included some erroneous attempts at cache
sharing we have a strategy in place which uses Sqlite shared cache and
assigns a rwlock to each open database. Each thread has its own DB
connection with a pointer to the locking structure for the open
database. That gives good throughput since it holds each database open
while the server runs and maintains one cache per database, accelerating
reads. The downside is that we have to figure out a replacement for the
FTS2 accesses used for text searching.
Since we no longer user POSIX file locking we compile Sqlite without it
to trim some redundant overhead.
It looks like we can replace FTS by user functions using a text indexing
method recycled from another product.
The server in question services AJAX style WWW pages where there are
large numbers of short read transactions and minimum latency is required
to achieve a snappy response. It manages to achieve sub-millisecond
responses to database RPC's from the WWW browser.
BTW, with help from this forum we realized that our attempts to achieve
shared cache and FTS were doomed to fail for fundamental architecture
reasons and abandoned the effort. In retrospect we were trying to
implement PostgreSQL with Sqlite and that was not a rational project.
The Sqlite based application server allows a central site to support
many databases, each one specific to sets of users located globally.
Sqlite's single file databases make this very simple to administer.
Each database does not have a large number of users, relieving the
concurrency load.
For further background on using Sqlite this way look at the way Mozilla
implements it using shared cache.
Finally, it is important to recognize that Sqlite id not Oracle, it is a
well conceived kit of tools to permit a developer to embed SQL database
capability into an application and to make it fit transparently. The
developer has the source and nothing is chiselled in stone.
Ken wrote:
John,
The sqlite api won't block, it will return a sqlite_busy type error to any
other transactions that are attempted? Correct, so there is no sqlite blocking
which is a good thing when writing a server. The clients will always block
waiting upon a response from the server. The server simply keeps the client
requests enqueued until it can service them some time later.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------