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/