On Mon, 05 Mar 2007, Malcolm Rowe wrote:
> On Mon, Mar 05, 2007 at 12:34:06PM -0500, Daniel Berlin wrote:
> > >In my (albeit limited) experience of SQLite, it doesn't seem to attempt a
> > >great deal of concurrency (until recently, you could deadlock by deleting
> > >rows that you were currently iterating through with a SELECT).
> >
> > They don't recommend it mainly for the same reasons nobody recommends
> > doing anything that requires locking over NFS:
> > Too many buggy locking implementations.
> >
> > If you actually take a look at what sqlite does code wise, it requires
> > that exactly the same lock mechanisms work that we require work for
> > FSFS, so we're fine if FSFS would work.
>
> Well, it's not quite the same. FSFS only requires that whole-file
> advisory locking works, while SQLite seems to require 'proper' byte
> range locking. I don't know if there's a difference in the quality of
> client implementations in practice.
DannyB indicated that last time he looked at SQLite's code, that it
was only doing whole-file locking.
While I haven't looked at its code, after reading "File Locking And
Concurrency In SQLite Version 3" <http://www.sqlite.org/lockingv3.html>
(the document Malcolm referenced in his original post), I'd guess that
SQLite is taking out POSIX advisory locks on its journal (probably in
addition to its DB files), and using the journal to coordinate
byte-range locking on the actual database file. Again, I haven't
confirmed this...
> I'm also interested to know what SQLite's concurrency is like: do we
> have to do anything special to ensure that multiple readers (on different
> threads, processes, machines) can co-exist happily with writers?
Section 2.0 and 3.0 of lockingv3.html indicate that SQLite v3 locking
is per-thread (or process), and effectively works much like FSFS for
writes.
Writer starvation, defined as no thread ever being able to make
changes to the database because of inability to acquire a write lock,
was a concurrency problem in SQLite v2. This was caused by there
never being a time when all readers were inactive, and is handled in
v3 by a new PENDING lock state, which temporarily blocks new read
locks from being acquired, allowing the writer to (eventually) persist
its changes to the DB (see section 5.1 of lockingv3.html).
> (By the way, don't take this as a dig at SQLite at all - I think it's
> great, and I use it myself for single-user applications, but I don't
> know what the characteristics are for multi-user or shared filesystem
> access).
For sure -- let's air these potential issues now.
- application/pgp-signature attachment: stored
Received on Mon Mar 5 22:04:49 2007