[OpenAFS] experience of SQLite on AFS

[Ken Dreyer]

Thought I would add another datapoint to the discussion of SQLite on AFS.

We have several Solaris 10 servers running OpenAFS client 1.4.x and
PHP 5.2.x. We really wanted to get PHP's PDO-SQLite extension working
with AFS. (PHP bundles SQLite 3.3.x). We anticipate that most of our
uses would be read-only, but a few applications would need to make
occasional INSERTs and so on.

SQLite has an option in os_unix.c (SQLITE_ENABLE_LOCKING_STYLE) to
automatically figure out the database's filesystem type and use the
most appropriate locking mechanism for that filesystem. Adam Megacz
wrote a patch to SQLite back in 2006 that added AFS to this list of
filesystems SQLite could detect. I'm not certain, but I think this
only works for OSX (Adam, correct me if I'm wrong :-)

Additionally, SQLite also has the (undocumented?) ability to define a
fixed locking style at compile-time with SQLITE_FIXED_LOCKING_STYLE.
This is the variable that we manipulated in our tests when we compiled
PDO-SQLite.

fnctl - Byte-range locking. Since we're using OpenAFS 1.4, this
pretends to work but actually doesn't lock anything.
flock - Whole-file locking. This completely hung on Solaris when
opening sqlite files on read-only mounts. Even if the SQLite
operations were read-only (SELECT, etc), PHP hung.
dot-lock - did not work, as SQLite wanted to write dot-files to our
read-only mounts.
no locks - worked

I did not expect the flock-style locking to hang when opening files
that were mounted read-only, but it was 100% repeatable. We figured
that read-access was better than nothing, so we use no locks currently
and warn our PHP developers. We hope we can make use of byte-range
locking some day when OpenAFS supports this on *nix.

- Ken