On Mar 5, 2007, at 2:07 PM, Daniel Rall wrote:
> At least on *nix, SQLite uses runtime detection of file system type,
> and (possibly) even actual fcntl() behavior, to determine the type of
> locking to use (e.g. POSIX fcntl() locking vs. dot locking). See the
> sqlite3DetectLockingStyle() and sqlite3TestLockingStyle() routines of
> <http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/
> os_unix.c&v=1.118>.
Hey! I wrote that code! Specifically, the code was written for Apple
in the context of the Tiger release. It is relatively conservative,
choosing .lock style all-or-none locking on NFS even when fcntl() may
actually work. A number of NFS servers completely lie about their
locking capabilities, thus the defensive posture of that code.
It has sense been updated as the locking code in SQLite has evolved.
In general, you want to avoid using SQLite in the many-writers-one-
database situation. It works relatively well on a local filesystem,
but can be somewhat inefficient. For network filesystems, the
performance drops significantly if you have to back off to completely
exclusive locks -- only one reader or writer at a time -- or it may
fail entirely if the network filesystem lies about capabilities.
Dealing with the transactions associated with "unsticking" locks is
not difficult once you understand how it works.
As stated, SQLite uses fcntl() based byte range locks to escalate
locks through a range from read only to exclusive to support both
multiple readers and a queue of potential writers.
The problem happens when:
- client A begins a transaction and does a select (thus grabbing a
read lock)
- client B begins a transaction and does a select (thus grabbing a
read lock)
- client A does an insert or update, thus escalating the lock to
PENDING (i.e. indicates that it wants exclusive, but doesn't have it
yet)
- client B tries to do an insert / update and fails to grab the
PENDING because A is already PENDING
- client A can't commit the transaction between a write requires the
PENDING to be escalated to EXCLUSIVE
- neither A or B end their transaction, each looping waiting for their
locks to be escalated
- user is unhappy
So, manage your transactions carefully and back the hell off when you
can't escalate a lock!
b.bum
Received on Mon Mar 5 23:39:09 2007