[Mono-dev] Equivalent to LockFileEx on Linux

[Jonathan Pryor]

On Tue, 2010-03-02 at 21:03 -0500, Avery Pennarun wrote:
> Probably the question needs to be rephrased a little.  sqlite itself
> obviously works fine on Linux, so clearly Linux has the kind of
> locking that is needed in order to make sqlite work...

Yes, "work."  There's a difference between "working" and working.

Specifically, if you peruse the C source for SQLite [0], you'll find 
sqlite-3.6.22/src/os_unix.c has a long diatribe about the evils of
advisory locking (lines 591-694, starting with "POSIX advisory locks are
broken by design"), and SQLite "work[s] around the problem" (line 615)
by "manag[ing] file locks internally."

In short, SQLite (1) uses advisory file locking (as FileStream.Lock()
provides under Mono), then (2) requires that all SQLite users actually
use libsqlite.so (otherwise they won't use the internal management).

So if someone re-implements SQLite (or just cat(1)'s the file), they
won't go through SQLites lock management, and they'll be none the wiser.

(Likewise nothing stops someone from overwriting the file with garbage.)

In summary, SQLite uses advisory file locking (because it has to), then
has a ton of code to work around all of the shortcomings of advisory
file locking (UTSL).

For those reimplementing SQLite in managed code, there are ~3 solutions
to the file locking issue:

 1. Ignore the problem entirely.  Especially for Silverlight, your
    SQLite DB should be a per-program resource, so nothing else should
    be mucking with it anyway (unless someone runs your SL app from
    multiple different browsers concurrently).

 2. Use FileStream.Lock()/FileStream.Unlock() and ignore the downsides 
    of advisory locks.  This works if you only care about supporting
    programs that pay attention to advisory file locks (like other 
    programs written in managed code).

    Pro: Simple, and still supports the "multiple SL apps instances"
         scenario.
    Con: Other programs can screw things up (if you care).

 3. Reimplement SQLites internal handle management.

    Pro: You properly interoperate with SQLite.
    Con: You need to track SQLite; if they change their algorithm, 
         you need to change yours.

There's still no perfect solution, though.  I'd suggest (1) or (2) for
simplicity reasons.

 - Jon

[0] http://www.sqlite.org/sqlite-3.6.22.tar.gz