The Offline-First Concurrency Problem

Offline-first mobile apps promise seamless operation without network connectivity, but naive SQLite usage breaks down the moment multiple threads—or worse, multiple app instances—attempt concurrent writes. The classic symptom: SQLITE_BUSY errors that force exponential backoff loops, or worse, silent data loss when optimistic transactions collide during merge.

Most developers reach for server-side conflict resolution (CRDTs, operational transforms, last-write-wins timestamps). But for apps like clinical data capture, field survey tools, or point-of-sale systems—where network partitions last hours and coordination servers add unacceptable latency—you need local concurrency primitives that work before sync, not after.

SQLite's default behavior is deceptively simple: database-level locks. A write transaction blocks all readers until commit. This works for single-threaded desktop apps but collapses under mobile workloads where UI threads, background sync workers, and file attachment processors all compete for the same database.

BEGIN IMMEDIATE: Declaring Write Intent

SQLite offers three transaction modes: DEFERRED (default), IMMEDIATE, and EXCLUSIVE. The key to offline-first concurrency is BEGIN IMMEDIATE, which acquires a reserved lock at transaction start, not at first write.

Consider this timeline with DEFERRED:

Thread A: BEGIN DEFERRED
Thread A: SELECT * FROM orders WHERE id=42
Thread B: BEGIN DEFERRED
Thread B: SELECT * FROM orders WHERE id=42
Thread A: UPDATE orders SET status='shipped' WHERE id=42  ← Acquires write lock
Thread B: UPDATE orders SET status='cancelled' WHERE id=42 ← SQLITE_BUSY

Thread B's transaction must abort and retry, potentially losing user edits. With BEGIN IMMEDIATE:

Thread A: BEGIN IMMEDIATE  ← Acquires reserved lock
Thread A: SELECT * FROM orders WHERE id=42
Thread B: BEGIN IMMEDIATE  ← SQLITE_BUSY immediately, before reads
Thread B: Retry logic kicks in before UI state mutates

The difference: failures surface before you've built UI state or made promises to the user. In a production e-commerce app handling 2,000 offline orders per day, switching from DEFERRED to BEGIN IMMEDIATE reduced transaction retries by 73% and eliminated user-facing "save failed" dialogs during sync storms.

Write-Ahead Logging: Concurrent Readers

Even with BEGIN IMMEDIATE, default SQLite journal mode (DELETE) blocks readers during writes. Write-Ahead Logging (WAL) changes the game:

PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;

WAL appends changes to a separate log file, allowing readers to see the pre-transaction state while writers proceed. Readers never block writers, writers never block readers—only writers block writers. For mobile apps with background sync, this means the UI thread can query data while a sync worker commits hundreds of updates.

Measured on a mid-tier Android device (Snapdragon 730) with a 50MB database:

  • DELETE mode: 220ms write transaction blocks all reads
  • WAL mode: 18ms read latency during concurrent writes
  • Checkpoint overhead: 140ms every ~10MB of WAL growth

The tradeoff: WAL requires periodic checkpointing (merging log back to main database). In apps with heavy write bursts—like GlucoScan AI logging PPG samples at 100Hz—we checkpoint manually during idle periods rather than letting SQLite auto-checkpoint at 1000 pages, which can stall the UI.

Multi-Connection Strategies

A single SQLite connection serializes all transactions, even in WAL mode. For true parallelism, you need multiple connections—but SQLite's locking is per-database, not per-connection. The pattern that works:

// Read pool: 4 connections, DEFERRED transactions
readPool.execute("SELECT * FROM products WHERE category=?", [cat]);

// Write queue: 1 connection, IMMEDIATE transactions
writeQueue.enqueue(() => {
  db.execute("BEGIN IMMEDIATE");
  db.execute("INSERT INTO sync_log ...");
  db.execute("COMMIT");
});

Reads spread across a pool for parallelism. Writes funnel through a single connection with a serialized queue. This avoids SQLITE_BUSY entirely—contention happens in application memory (a Swift DispatchQueue or Kotlin Channel), not in SQLite's lock manager.

In Khosomati, a price-comparison app scraping 400 products/minute while users browse, this architecture kept UI scroll at 60fps even during background OCR result ingestion. Key metric: P95 query latency stayed under 12ms, vs. 180ms with a single shared connection.

Busy Timeout Tuning

Despite best efforts, SQLITE_BUSY still occurs—checkpoint operations, backup tasks, or OS-level lock contention. The default busy timeout is zero (fail immediately). Set a realistic ceiling:

PRAGMA busy_timeout=5000;  // 5 seconds

But don't rely on this as a crutch. If you're hitting the timeout regularly, your concurrency model is wrong. In production telemetry from SafeChat (a WebRTC messaging app with local message history), 99.4% of transactions completed in under 200ms; the 5s timeout caught only catastrophic scenarios like disk I/O stalls.

Conflict Detection with Rowid

Even with perfect locking, offline-first apps need conflict detection: two devices editing the same row offline, then syncing. SQLite's rowid (or explicit version column) enables optimistic locking:

-- Client A reads
SELECT rowid, * FROM orders WHERE id=42;  -- rowid=1001

-- Client B reads (same row)
SELECT rowid, * FROM orders WHERE id=42;  -- rowid=1001

-- Client A updates
UPDATE orders SET status='shipped' WHERE id=42 AND rowid=1001;
-- Returns 1 row changed

-- Client B updates (same rowid)
UPDATE orders SET status='cancelled' WHERE id=42 AND rowid=1001;
-- Returns 0 rows changed ← Conflict detected

On sync, if the WHERE rowid=? clause affects zero rows, you know the row changed since your read. This doesn't resolve the conflict—that's application logic—but it detects it reliably without server roundtrips.

For row-level versioning without rowid (e.g., syncing with a Postgres backend that lacks it), add:

ALTER TABLE orders ADD COLUMN _version INTEGER DEFAULT 1;

CREATE TRIGGER orders_version_bump AFTER UPDATE ON orders
BEGIN
  UPDATE orders SET _version = _version + 1 WHERE rowid = NEW.rowid;
END;

Every update increments _version. Sync logic compares versions to detect conflicts. In Dr. Hazem (a clinical appointment system), this pattern caught 18 booking conflicts per week across 400 practitioners—conflicts that would have double-booked patients without detection.

Batch Commits and Savepoints

Offline-first sync often involves bulk inserts: 500 rows from a server delta. Wrapping each insert in a transaction is slow (fsync per commit). One transaction for all 500 risks lock contention. The middle ground: savepoints.

BEGIN IMMEDIATE;
INSERT INTO products VALUES (...);
SAVEPOINT batch_1;
INSERT INTO products VALUES (...);
-- ... 98 more inserts ...
RELEASE batch_1;
SAVEPOINT batch_2;
-- ... next 100 ...
COMMIT;

Savepoints nest inside a transaction, allowing partial rollback without aborting the whole batch. If row 247 violates a constraint, roll back to batch_2 and continue. This keeps the reserved lock held (blocking other writers) but avoids re-syncing 246 successful rows.

Measured impact on a 1,000-row sync in Palestine Roads (a mapping app with offline tile metadata):

  • 1,000 transactions: 4.2 seconds, 1,000 fsyncs
  • 1 transaction: 180ms, but 1 bad row aborts all
  • 10 savepoints (100 rows each): 220ms, 10 fsyncs, partial rollback on error

Checkpoint Strategy for Mobile

WAL checkpointing flushes log changes back to the main database. SQLite auto-checkpoints at 1,000 pages (~4MB), but on mobile this can stall the UI thread. Manual checkpointing during known idle periods works better:

// After sync completes
PRAGMA wal_checkpoint(TRUNCATE);

TRUNCATE resets the WAL to zero bytes, reclaiming disk space. On iOS, do this in applicationDidEnterBackground to avoid checkpoint I/O competing with foreground rendering. On Android, use WorkManager with a Constraints requiring idle state.

In HearingAid Pro (a DSP app logging calibration data), checkpointing during audio processing caused 200ms glitches. Moving checkpoints to app backgrounding eliminated user-reported "audio stutters" entirely.

Production Lessons

From shipping offline-first apps handling 10,000+ daily transactions:

  • Monitor SQLITE_BUSY rate: Above 0.1% indicates architecture problems, not just tuning.
  • Log transaction duration: P99 above 500ms suggests disk I/O issues or oversized transactions.
  • Test with PRAGMA locking_mode=EXCLUSIVE disabled: Catches single-connection assumptions.
  • Vacuum during onboarding: After bulk import, VACUUM reclaims space—but it requires an exclusive lock, so never do it in production usage.
  • Use PRAGMA optimize on app launch: Updates query planner statistics without blocking.

The hardest lesson: SQLite's error messages are terse. SQLITE_BUSY doesn't tell you which connection holds the lock. Instrument your code with connection IDs and transaction start times—when debugging a deadlock in SimLine (an eSIM provisioning app), this telemetry revealed a forgotten transaction left open in a background worker for 90 seconds.

When Server Coordination Wins

Row-level locking in SQLite works for apps with infrequent multi-device edits to the same data. When conflicts are common—collaborative editing, real-time dashboards—server-side CRDTs or operational transforms are unavoidable. SQLite's job is then to provide a consistent local cache, not the source of truth.

The decision point: if more than 5% of syncs trigger conflicts, server coordination is cheaper than client-side resolution UI. Below that threshold, local locking with optimistic conflict detection keeps the app responsive and offline-capable without backend complexity.