File Locking — The OS Layer

SQLite trusts the filesystem; the filesystem doesn't always deserve it

SQLite's concurrency model relies on the host OS's file locking primitives — fcntl(F_SETLK) on POSIX, LockFileEx on Windows. When those work correctly, SQLite is bulletproof. When they don't, you get corruption.

Networked filesystems are the worst offenders:

NFS — file locks are advisory and often unreliable. Concurrent writes lose updates.
SMB / CIFS — locking depends on client/server config.
FUSE filesystems — depends entirely on the FUSE driver's implementation.
Dropbox / iCloud / OneDrive sync folders — sync apps touch and rewrite files; never put a live database there.

Safe places to put a SQLite database:

Local SSD on the machine running the writer.
An ext4 / APFS / NTFS volume that's not network-mounted.
A managed service that handles SQLite (Turso, Cloudflare D1) and abstracts away the storage layer.

Warning: Pippa's ~/pippa-db/ lives outside Dropbox specifically because Dropbox sync corrupted earlier iterations. The 'sync your database file via cloud storage' anti-pattern is one of the top corruption causes Dad has personally hit.

Code

Confirm the filesystem supports SQLite·bash

# Quick smoke test: create, write, close, reopen on the candidate volume
DB=/path/to/candidate/test.db
rm -f "$DB"
sqlite3 "$DB" 'CREATE TABLE t(x); INSERT INTO t VALUES (1); INSERT INTO t VALUES (2);'
sqlite3 "$DB" 'SELECT count(*) FROM t'   # expect 2
sqlite3 "$DB" 'PRAGMA integrity_check'   # expect ok

Exercise

Run a small concurrent-writer test on three different filesystems available to you (local SSD, an external USB drive, optionally a Dropbox / iCloud folder if you have one — but treat the result as a warning, not a recommendation). For each, attempt parallel writes and run integrity_check after. Note which environments are safe and which produced surprises.

File Locking — The OS Layer

SQLite trusts the filesystem; the filesystem doesn't always deserve it

Code

External links

Exercise

Progress

Comments 0