The problem with queuing all writes is that you're playing fast and loose
with the isolation of the transactions. Imagine two threads (A and B).
Each reads the same value, increments it, and then writes it (Ar Aw, and Br
Bw). If the operations interleave properly (Ar Aw Br Bw), the final values
will be correct). If they don't (Ar Br Aw Bw), then A's write will be lost.
By queuing the writes, you've lost the association with the reads they were
based on, and the database doesn't have a chance to enforce the isolation of
the transactions. Don't forget: the whole reason the SQLite locks work the
way they do it to guarantee the proper transaction semantics.
To make a single writer thread work, you'd need to put the *entire*
transaction, including the reads, onto the writer thread. If you're going
to do reads on one thread, and then writes on another, you might was well
avoid the threading mess, and structure your operations like this:
begin;
/* do all the reads */
commit;
do {
begin;
/* do all the writes */
commit;
} until the commit succeeds;
The best solution is to rollback entire transactions when the database is
busy. This means structuring your code so that all transactions can
rollback and retry. In my experience, this is necessary (though *far* less
frequently) even with the "big boy" databases. See, for example,
http://staff.newtelligence.net/clemensv/PermaLink,guid,826bc7c9-8b0f-4df6-aa
be-e6c5377a9446.aspx
--Ned.
-----Original Message-----
From: Andrew Piskorski [mailto:[EMAIL PROTECTED]
Sent: Monday, March 14, 2005 8:23 PM
To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
Subject: Re: [sqlite] Re: [unclassified] Re: [sqlite] getting rid of dirty
SQLITE_BUSY workaround
On Tue, Mar 15, 2005 at 12:21:15AM +0100, Thomas Lotterer wrote:
> On Sun, Mar 13, 2005, jed wrote:
>
> > [...] web applications fit well into the model of "many readers, one
> > writer", sqlite does this very well.
> >
> Well, there might be web applications which are read-only from the web's
> view. But to be honest, most of them also call for occasional writes.
> Think of a simple address book. Also I think of uses like tracking
> session cookies which also use occasional writes. In all those cases
I have not tried this with SQLite, but if I was using SQLite for such
an app, I assume I would need to serialize all such writes through a
single thread or process. E.g., in my web server, the connection
thread servicing the user request would send a message to my one
single db writer thread, saying, "Heh, please insert this data for
me!". Then the conn thread would simply block until the db writer
thread replies with, "Yup, your transaction is committed."
That is a uglier than what you'd do with a real server-based RDBMS
like Oracle or PostgreSQL, but it should scale fine until you have
either: One, very large numbers of hits on your simple and efficient
web app. Or two, a complicated web app with many potentially long
running transactions, etc. In the real world, the second concern is
much more likely to bite you than the first, and cries out for a more
capable, more general purpose database than SQLite.
It would be nice if SQLite had MVCC, which would let it scale much
further up into the PostgreSQL-like realm normally dominated by
client/server databases, but given the "simple, small, embedded" niche
that Dr. Hipp intended for SQLite, it's easy to see why adding MVCC
isn't any sort of priority, even if it could be done without making
the code much more complicated.
--
Andrew Piskorski <[EMAIL PROTECTED]>
http://www.piskorski.com/
