Sqlite Single Writer

SQLite Single-Writer Pattern

Problem

SQLite supports one writer at a time. When multiple processes (not threads) attempt concurrent writes, you get SQLITE_BUSY errors. This skill provides a cross-platform locking pattern that:

• Serializes writes across processes
• Keeps reads concurrent and fast
• Provides diagnostic errors on timeout
• Automatically releases locks on crash

Architecture

┌─────────────┐  ┌─────────────┐  ┌─────────────┐
│  Process 1  │  │  Process 2  │  │  Process 3  │
└──────┬──────┘  └──────┬──────┘  └──────┬──────┘
       │                │                │
       └────────────────┼────────────────┘
                        │
              ┌─────────▼─────────┐
              │  Write Lock File  │  ← flock/LockFileEx
              │   (db.lock)       │
              └─────────┬─────────┘
                        │
              ┌─────────▼─────────┐
              │  SQLite (WAL)     │  ← Concurrent reads OK
              │   (data.db)       │
              └───────────────────┘

Implementation Steps

1. Enable WAL Mode

WAL (Write-Ahead Logging) allows concurrent reads while writes are serialized.

PRAGMA journal_mode=WAL;
PRAGMA busy_timeout=500;      -- Fallback timeout (ms)
PRAGMA synchronous=NORMAL;    -- Faster, still safe with WAL

Run these on every database open.

2. Create Lock File

Use a separate lock file (not the database file) for coordination:

project/
├── data.db        # SQLite database
├── data.db-wal    # WAL file (auto-created)
├── data.db-shm    # Shared memory (auto-created)
└── db.lock        # Write lock file

3. Lock Acquisition Pattern

acquire(timeout):
    open("db.lock", CREATE | RDWR)
    deadline = now + timeout
    backoff = 5ms
    
    loop:
        if try_exclusive_lock():      # Non-blocking
            write_holder_info()       # PID + timestamp
            return SUCCESS
        
        if now > deadline:
            holder = read_holder_info()
            return TIMEOUT_ERROR(holder)
        
        sleep(backoff)
        backoff = min(backoff * 2, 50ms)  # Exponential, capped

4. Platform-Specific Locking

Unix (flock):

flock(fd, LOCK_EX | LOCK_NB)  // Acquire
flock(fd, LOCK_UN)            // Release

Windows (LockFileEx):

LockFileEx(handle, LOCKFILE_EXCLUSIVE_LOCK | LOCKFILE_FAIL_IMMEDIATELY, ...)
UnlockFileEx(handle, ...)

Both auto-release on process exit/crash.

5. Holder Info for Diagnostics

Write to lock file when acquired:

pid:12345
time:2025-01-15T10:30:45Z

On timeout, read and report:

write lock timeout after 500ms
  holder: pid:12345 since 2025-01-15T10:30:45Z
  try again or check if holder process is stuck

6. Wrap Write Operations

Every write method should:

1. Acquire lock (with timeout)
2. Execute write
3. Release lock (in finally/defer)

Language-Specific References

See references for implementation details:

• go.md - Go implementation with flock/LockFileEx
• rust.md - Rust implementation with fs2/windows-sys
• python.md - Python implementation with fcntl/msvcrt
• javascript.md - Node.js implementation with proper-lockfile

Key Parameters

Failure Modes

Testing

Spawn N processes doing M writes each. Verify:

1. All N×M writes succeed (no lost writes)
2. Timeout errors include diagnostic info
3. Lock released after crash (kill -9 a holder)

# Example stress test
for i in {1..5}; do
    (for j in {1..10}; do ./app write "p$i-w$j"; done) &
done
wait
./app count  # Should be 50

Common Mistakes

1. Locking the database file - Use separate lock file; SQLite manages its own locks
2. Forgetting WAL mode - Without WAL, readers block on writers too
3. Too long timeout - 500ms is plenty; fail fast, let caller retry
4. No holder info - Without diagnostics, debugging contention is painful
5. Thread locks for processes - Mutex/RwLock don't work across processes; use flock
6. Network filesystems - flock may not work on NFS/SMB; use local filesystems only
7. Ignoring EINTR - Unix flock can be interrupted by signals; retry on EINTR