Small. Fast. Reliable.
Choose any three.

Sqlite uses advisory byte-range locks on the database file to coordinate safe, concurrent access by multiple readers and writers [1]. The five file locking states (UNLOCKED, PENDING, SHARED, RESERVED, EXCLUSIVE) are implemented as POSIX read & write locks over fixed set of locations (via fsctl), on AFP and SMB only exclusive byte-range locks are available via fsctl with _IOWR('z', 23, struct ByteRangeLockPB2) to track the same 5 states. To simulate a F_RDLCK on the shared range, on AFP a randomly selected address in the shared range is taken for a SHARED lock, the entire shared range is taken for an EXCLUSIVE lock):

	PENDING_BYTE        0x40000000
	RESERVED_BYTE       0x40000001
 	SHARED_RANGE        0x40000002 -> 0x40000200

This works well on the local file system, but shows a nearly 100x slowdown in read performance on AFP because the AFP client disables the read cache when byte-range locks are present. Enabling the read cache exposes a cache coherency problem that is present on all OS X supported network file systems. NFS and AFP both observe the close-to-open semantics for ensuring cache coherency [2], which does not effectively address the requirements for concurrent database access by multiple readers and writers [3].

To address the performance and cache coherency issues, proxy file locking changes the way database access is controlled by limiting access to a single host at a time and moving file locks off of the database file and onto a proxy file on the local file system.

Using proxy locks


  sqlite3_file_control(db, dbname, SQLITE_SET_LOCKPROXYFILE, (char *)proxy_path> | ":auto:")
  sqlite3_file_control(db, dbname, SQLITE_GET_LOCKPROXYFILE, (char **)proxy_path)

SQL pragmas

  PRAGMA [database.]lock_proxy_file=<proxy_path> | :auto:
  PRAGMA [database.]lock_proxy_file

Specifying ":auto:" means that if there is a conch file with a matching host ID in it, the proxy path in the conch file will be used, otherwise a proxy path based on the LOCKPROXYDIR (or default temp dir) will be used and the actual proxy file name is generated from the name and path of the database file. For example:

        For database path "/Users/me/foo.db"
        The lock path will be "<tmpdir>/sqliteplocks/_Users_me_foo.db:auto:")

Open flag

Include the SQLITE_OPEN_AUTOPROXY flag to sqlite3_open_v2 to automatically enable proxy locking on non-local file systems, using the ":auto:" style lock proxy file naming convention.

Once a lock proxy is configured for a database connection, it can not be removed, however it may be switched to a different proxy path via the above APIs (assuming the conch file is not being held by another connection or process).

How proxy locking works

Proxy file locking relies primarily on two new supporting files:

The conch file - to use a proxy file, sqlite must first "hold the conch" by taking an sqlite-style shared lock on the conch file, reading the contents and comparing the host's unique host ID (see below) and lock proxy path against the values stored in the conch. The conch file is stored in the same directory as the database file and the file name is patterned after the database file name as ".<databasename>-conch". If the conch file does not exist, or it's contents do not match the host ID and/or proxy path, then the lock is escalated to an exclusive lock and the conch file contents is updated with the host ID and proxy path and the lock is downgraded to a shared lock again. If the conch is held by another process (with a shared lock), the exclusive lock will fail and SQLITE_BUSY is returned.

The proxy file - a single-byte file used for all advisory file locks normally taken on the database file. This allows for safe sharing of the database file for multiple readers and writers on the same host (the conch ensures that they all use the same local lock file).

Requesting the lock proxy does not immediately take the conch, it is only taken when the first request to lock database file is made. This matches the semantics of the traditional locking behavior, where opening a connection to a database file does not take a lock on it. The shared lock and an open file descriptor are maintained until the connection to the database is closed.

The proxy file and the lock file are never deleted so they only need to be created the first time they are used.

Configuration options

Sqlite must be built with the SQLITE_ENABLE_LOCKING_STYLE macro defined to enable proxy locking.

Other compile-time options

As mentioned above, when compiled with SQLITE_PREFER_PROXY_LOCKING, setting the environment variable SQLITE_FORCE_PROXY_LOCKING to 1 will force proxy locking to be used for every database file opened, and 0 will force automatic proxy locking to be disabled for all database files (explicity calling the SQLITE_SET_LOCKPROXYFILE pragma or sqlite_file_control API is not affected by SQLITE_FORCE_PROXY_LOCKING).