Hey, sorry I'm a little late on this one (as usual).
On 2/3/07, David M X Green <[EMAIL PROTECTED]> wrote:
I am new to this but are these issues those of trying to get it to do what sqlite
it > is not designed for. I quote the book
The Definitive Guide to SQLite - Chapter 1 --- Networking
" .... Again, most of these limitations are intentional—they are a result of
SQLite's
design. Supporting high write concurrency, for example, brings with it great
deal of complexity and this runs counter to SQLite's simplicity in design.
Similarly, being an embedded database, SQLite intentionally does
__not__support__networking__ [my emphasis]. This should come as no surprise.
In short, what SQLite can't do is a direct result of what it can. It was
designed to operate as a modular, simple, compact, and easy-to-use embedded
relational database whose code base is within the reach of the programmers
using it. And in many respects it can do what many other databases cannot, such
as run in embedded environments where actual power consumption is a limiting
factor. "
Dennis Cote and Scott Hess have hit the nail on the head, in my
opinion. As you quoted, chapter 1 sites networking support as a big
disclaimer, for the simple reason that I want to make it clear to the
reader what SQLite does not do, so that ideally he/she can make a
decision right up front whether or not if might be useful for their
purposes.
Having said that, p 196 in chapter 5 includes a sidebar on network
file systems and this whole issue. The issue is in theory quite
simple: SQLite supports the requisite locking semantics such that if
the underlying network file system supports locking in its network
files system in the same way it does its local file system (which it
should), then then SQLite will work correctly. Period. That is to say,
SQLite does locking the same way over network file systems as it does
local file systems. Since this locking code works correctly on local
file systems, and network file systems use exactly the same locking
semantics, then one may logically conclude that SQLite works correctly
over network file systems. End theory.
In practice, however, it's a whole other question: How do you know
that a particular network file system implements every part of this
locking protocol accurately under all conditions? Plus, you are
introducing many other variables -- namely the network, both logical
(TCP stack bugs, etc.) and physical (bad NICs, switches, cables,
punchdowns, etc.). Everything becomes much more complex, even if the
NFS implementation is flawless.
But let's consider just the network file system. With respect to it,
the short answer is, no one can tell you definitively that it's not
without bugs. And a single bug is all it takes to cause proper locking
to fail, no fault to SQLite. It comes down to the logical conundrum
that you can't prove a negative. Even the people who implemented a
particular network file system cannot be 100% sure it is not without a
bug somewhere that may adversely affect locking in some particular
scenario (client crash, or network problem).
So the official answer from SQLite, wisely, is that they cannot
certify that a particular network file system is without bugs, and
therefore whether or not SQLite works over a particular NFS
implementation for your particular application. There are simply too
many variables to account for, but more importantly, *all* of these
variables are external to SQLite. SQLite uses standard system calls
for locking. If the NFS implements those locking calls correctly, then
SQLite will work correctly.
So that's the disclaimer. It's not that SQLite lacks special code to
make it work over network file systems. Much to the contrary, it has
everything required for it to work. The disclaimer simply states that
you cannot expect SQLite's developers to speak for code or systems
they did not write. They can tell you for sure that SQLite follows
POSIX locking semantics on UNIX and the equivalent on Windows. The
locking semantics used on local file systems are the same used for
network file systems (that is, they should be transparent --- no
additional coding is required to use them). What SQLite's developers
cannot tell you that Samba is without bugs, or that Linux NFS is
without bugs, which may be connecting to a OS X NFS server, which is
also without bugs, etc. They didn't write the code. Theoretically,
SQLite should work perfectly in all cases, but one small bug in a
single NFS implementation on either client or server, and either a
lock may exist too long, leaving other clients locked out, or may not
happen at all, letting multiple clients write to the same database at
the same time, resulting in almost assured corruption.
So, what can you do? Well, things may still work out fine for you, you
just have to determine empirically whether or not your particular
system's network file system has any serious locking bugs that come
into play for your particular application.
Is it really a good idea to network a data base that relies on the OS file systems
> like this? Is it ever going to be safe enough?
This is a common practice among many other databases already,
especially on Windows applications. Standard file locking over Windows
shares is used by databases such as Pervasive's SQL, and many others.
I just mention this one because my company ran Great Plains Dynamics
accounting system for years using Windows clients running against a
Linux server with Samba. I set it up, tested it, and it worked
perfectly for us. The Windows clients directly access the Pervasive
database files on a Samba share.
Countless other products do the same thing. I could name at least
three other commercial products I have set up before which are
designed to work by this very model. So accessing database files over
a network file system, be they SQLite or otherwise, is far from being
an unorthodox practice. Again, it boils down to whether or not the
system works for your particular application.
-- Mike
--------------------
David M X Green
>>>|||"Alex Roston" (2007-02-02 20:05) wrote: |||>>>
> Scott Hess wrote:
>> On 2/2/07, Dennis Cote <[EMAIL PROTECTED]> wrote:
>>> [EMAIL PROTECTED] wrote:
>>> > The problem is, not many network filesystems work correctly.
>>>
>>> I'm sure someone knows which versions of NFS have working file locking,
>>> at least under Linux.
>>
>> I doubt it is this easy. You need to line up a bunch of things in the
>> right order, with the right versions of nfs, locking services, perhaps
>> the right kernel versions, the right config, etc, etc.
>>
>> IMO the _real_ solution would be a package which you could use to try
>> to verify whether the system you have is actually delivering working
>> file locking. Something like diskchecker (see
>> http://brad.livejournal.com/2116715.html). The basic idea would be to
>> have a set of networked processes exercising the APIs and looking for
>> discrepencies. Admittedly, passing such a test only gets you a
>> statistical assurance (maybe if you'd run the test for ten more
>> minutes, or with another gig of data, it would have failed!), but
>> failing such a test is a sure sign of a problem.
>>
>> -scott
> That's a really useful idea, not only for itself, but also because it
> might lead to debugging some of the network issues, and allowing the
> developers to build a database of stuff that works: "Use Samba version
> foo, with patch bar, and avoid the Fooberry 6 network cards." Or whatever.
>
> My suspicion, in the earlier case with Windows and Linux clients is that
> Windows didn't handle the locking correctly, and that would be worth
> proving/disproving too.
>
> An alternate approach is to use something a little more like a standard
> client-server model, where there's a "server" program which intervenes
> between (possibly multiple) workstations and the database itself. The
> "server" would queue requests to the database, make sure that no more
> than one write request at a time went to the database, and certify that
> writes have been properly made.
>
> The problem with this approach is that it eats quite heavily into
> SQLite's speed advantage, but if you've already put thousands of hours
> into developing your system, it might be a worthwhile hack.
>
> Alex
>
> -----------------------------------------------------------------------------
>
> To unsubscribe, send email to [EMAIL PROTECTED]
> -----------------------------------------------------------------------------
>
>
>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
