Issue Overview: Concurrent Write Conflicts in SQLite WASM via OPFS

The core challenge revolves around managing concurrent write operations to an SQLite database stored on the Origin Private File System (OPFS) across multiple browser tabs in a WebAssembly (WASM) environment. When two tabs attempt to write to the same database (demo.db or demo2.db) simultaneously, the second tab encounters an error: "attempt to write a readonly database" (SQLITE_READONLY, extended result code 8). This error arises due to limitations in OPFS concurrency support and SQLite’s locking mechanisms in WASM environments. The problem is exacerbated by long-running write transactions in the first tab, which monopolize access to the database file, preventing other tabs from acquiring write locks.

Key technical constraints include:

1. OPFS Locking Behavior: OPFS enforces exclusive write locks on files. When a file is opened for writing in one tab, other tabs cannot acquire a write lock until the first tab releases it.
2. SQLite WASM Architecture: The SQLite WASM build uses a synchronous C API, which conflicts with OPFS’s asynchronous nature. The current OPFS VFS implementation in SQLite WASM does not fully support cross-tab coordination.
3. WASMFS Limitations: When using Emscripten’s WASMFS with OPFS, there is no built-in mechanism for concurrent file access coordination between tabs. Each tab operates as an independent process with no shared memory or lock state synchronization.

The workflow described in the problem involves:

• Tab 1: Initiates a long-running write transaction (e.g., bulk inserts) and keeps the database connection open.
• Tab 2: Attempts to perform writes while Tab 1’s transaction is ongoing, triggering the readonly error.
• Goal: Allow Tab 2 to write independently without waiting for Tab 1’s transaction to complete, while ensuring transactional integrity.

Possible Causes: OPFS Locking and Transaction Coordination Failures

1. OPFS File Handle Monopolization

When Tab 1 opens the database file via OPFS using sqlite3_open_v2(), it acquires an exclusive lock on the file. This lock persists until the connection is explicitly closed with sqlite3_close(). In the provided code, Tab 1 does not close the database connection after completing its operations, causing the lock to remain active indefinitely. OPFS does not allow concurrent write handles to the same file, so Tab 2 is forced into read-only mode.

2. WASMFS Concurrency Limitations

The Emscripten WASMFS OPFS backend lacks awareness of SQLite’s transactional requirements. Key issues include:

• No Lock Escalation: WASMFS cannot downgrade a write lock to a read lock during transaction pauses.
• No Cross-Tab Notifications: There is no mechanism for Tab 2 to detect when Tab 1 releases its lock.
• Persistent Handles: Keeping database connections open between transactions (common in ORM patterns) exacerbates locking conflicts.

3. Missing Application-Level Lock Coordination

SQLite’s default locking mechanisms (e.g., SQLITE_BUSY_TIMEOUT) are designed for single-process environments. In multi-tab scenarios:

• No Native Cross-Tab Locking: The SQLite library has no built-in way to communicate lock states between browser tabs.
• Unlock Notify Limitations: The sqlite3_unlock_notify() API only works within the same process and cannot propagate notifications across tabs.

4. Browser-Specific OPFS Implementations

Chromium’s experimental OPFS features (e.g., synchronous access via FileSystemSyncAccessHandle) offer better concurrency but are not yet standardized. The SQLite WASM build avoids vendor-specific solutions, leading to suboptimal locking behavior in current browser versions.

Troubleshooting Steps, Solutions & Fixes

Step 1: Immediate Code Corrections for Lock Release

Problem: The database connection in Tab 1 is never closed, causing perpetual locking.

Solution: Explicitly close the database after each transaction.

Modified Code:

void createDB() {
  // ... existing code ...
  
  // After finalizing statements and before exiting:
  if(SQLITE_OK != sqlite3_close(mp_db)){
    printf("Error closing database: %s\n", sqlite3_errmsg(mp_db));
  }
  mp_db = nullptr; // Prevent accidental reuse
  std::cout << "createDB finished" << std::endl;
}

Effect: Forces Tab 1 to release the OPFS lock when done, allowing Tab 2 to acquire a write handle. This is a prerequisite for any further concurrency improvements.

Step 2: Implementing Cross-Tab Lock Coordination

Option A: Web Locks API

The Web Locks API allows tabs to coordinate access to shared resources.

Implementation:

1. Acquire Lock Before DB Operations:

// In Emscripten-generated JS glue code:
async function executeWithLock(callback) {
  const lockName = 'sqlite-demo2.db-lock';
  await navigator.locks.request(lockName, {mode: 'exclusive'}, async lock => {
    await callback();
  });
}

// Modify C++ code to trigger JS lock acquisition:
EM_ASYNC_JS(void, acquire_db_lock, (), {
  await executeWithLock(() => {
    // Proceed with C++ database operations
    Module._createDB(); // Call compiled C++ function
  });
});

// In main():
acquire_db_lock(); // Instead of directly calling createDB()

2. Handle Lock Contention:

// Retry logic in C++ with EM_ASYNC_JS wrappers
void createDBWithRetry() {
  int retries = 0;
  while(retries < MAX_RETRIES) {
    int rc = createDB(); // Modified to return error codes
    if(rc == SQLITE_OK) break;
    if(rc == SQLITE_READONLY) {
      emscripten_sleep(100); // Non-blocking sleep
      retries++;
    } else {
      // Handle fatal errors
      break;
    }
  }
}

Limitations: Requires integrating JavaScript async logic with C++ via Emscripten’s EM_ASYNC_JS, which complicates the build process.

Option B: BroadcastChannel for State Notification

Use BroadcastChannel to signal lock release between tabs.

Implementation:

// In JS glue code:
const dbChannel = new BroadcastChannel('sqlite-locks');

dbChannel.onmessage = (event) => {
  if(event.data === 'lock-released') {
    // Trigger C++ retry logic
    Module._retryPendingOperations();
  }
};

// After closing database in Tab 1:
dbChannel.postMessage('lock-released');

C++ Integration:

EM_JS(void, notify_lock_release, (), {
  if(typeof BroadcastChannel !== 'undefined') {
    new BroadcastChannel('sqlite-locks').postMessage('lock-released');
  }
});

void createDB() {
  // ... existing code ...
  sqlite3_close(mp_db);
  notify_lock_release(); // Signal other tabs
}

Step 3: Architectural Changes for OPFS Concurrency

Approach 1: Short-Lived Transactions

Restructure the application to use brief, atomic transactions:

Before (Problematic):

// Long-running transaction with open connection
createDB(); // Opens connection, performs multiple operations, never closes

After (Fixed):

void insertData(const std::vector<std::string>& names) {
  sqlite3* db = nullptr;
  sqlite3_open_v2(..., &db);
  // Perform single insert transaction
  sqlite3_exec(db, "BEGIN IMMEDIATE");
  for(auto& name : names) {
    // Bind and insert
  }
  sqlite3_exec(db, "COMMIT");
  sqlite3_close(db);
}

Key Changes:

• Open/close the database for each discrete operation.
• Use BEGIN IMMEDIATE to acquire write locks early.
• Keep transactions under 50ms to minimize lock contention.

Approach 2: Worker-Based Database Proxy

Offload database operations to a SharedWorker acting as a write coordinator:

Worker Implementation (worker.js):

let db = null;
const ports = [];

onconnect = (e) => {
  const port = e.ports[0];
  ports.push(port);
  
  port.onmessage = async (event) => {
    if(event.data.type === 'exec') {
      const result = await executeSql(event.data.sql);
      port.postMessage({id: event.data.id, result});
    }
  };
};

async function executeSql(sql) {
  if(!db) {
    db = await sqlite3.open('demo2.db');
  }
  return db.exec(sql);
}

Tab Integration:

// Use Emscripten's JS proxy to communicate with worker
EM_ASYNC_JS(const char*, executeSqlInWorker, (const char* sql), {
  const worker = new SharedWorker('worker.js');
  return new Promise((resolve) => {
    worker.port.onmessage = (e) => {
      resolve(e.data.result);
    };
    worker.port.postMessage({
      type: 'exec',
      sql: UTF8ToString(sql)
    });
  });
});

// C++ wrapper
void createDB() {
  const char* sql = "INSERT INTO users ...";
  const char* result = executeSqlInWorker(sql);
  // Process result
}

Advantages:

• Centralizes write operations to avoid OPFS lock contention.
• Allows tabs to queue write requests without direct DB access.

Step 4: Evaluating Alternative SQLite WASM Implementations

Option A: SQLite Official OPFS VFS

Switch from WASMFS to SQLite’s JavaScript OPFS VFS:

Migration Steps:

1. Remove WASMFS dependencies from the build:

   emcc main.cpp sqlite3-wasm.c -o app.html # No WASMFS flags
   

2. Use SQLite’s JavaScript VFS initialization:

   // In HTML file:
   import sqlite3InitModule from './sqlite3.mjs';
   sqlite3InitModule().then((sqlite3) => {
     const db = new sqlite3.oo1.OpfsDb('/persistent/tmp/demo2.db');
     // Expose db to C++ via Emscripten
   });
   

3. Modify C++ code to use the JavaScript VFS:

   // Open database through JS VFS wrapper
   EM_JS(sqlite3*, open_opfs_db, (const char* name), {
     return Module.opfsDbHandles[UTF8ToString(name)];
   });
   
   void createDB() {
     sqlite3* db = open_opfs_db("/persistent/tmp/demo2.db");
     // Proceed with operations
   }
   

Concurrency Improvements:

• The official OPFS VFS uses cooperative locking with timeouts.
• Write transactions can be interleaved if kept brief (<100ms).

Option B: wa-sqlite Integration

Use the wa-sqlite project for enhanced concurrency:

Implementation:

1. Replace SQLite amalgamation with wa-sqlite:

   emcc main.cpp -Iwa-sqlite -s MODULARIZE=1 -o app.html
   

2. Leverage wa-sqlite’s async VFS:

   import { VFS } from "@rhashimoto/wa-sqlite";
   import { OPFSVFS } from "@rhashimoto/wa-sqlite/src/opfs";
   
   const vfs = new OPFSVFS();
   VFS.register("opfs", vfs);
   

3. Modify C++ open calls:

   int openResult = sqlite3_open_v2(
     "file:demo2.db?vfs=opfs", &mp_db,
     SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nullptr
   );
   

Features:

• Implements sqlite3_unlock_notify()-style retries across tabs.
• Uses Service Workers for background lock acquisition.
• Supports concurrent readers when no writer is active.

Step 5: Advanced Techniques for Long-Running Writes

Method 1: Chunked Commit Strategy

Break large transactions into smaller chunks with intermediate commits:

Implementation:

void bulkInsert(sqlite3* db, const std::vector<User>& users) {
  sqlite3_exec(db, "BEGIN IMMEDIATE");
  for(size_t i=0; i<users.size(); ++i) {
    // Insert single user
    if(i % 100 == 0) { // Commit every 100 records
      sqlite3_exec(db, "COMMIT");
      sqlite3_exec(db, "BEGIN IMMEDIATE");
    }
  }
  sqlite3_exec(db, "COMMIT");
}

Optimization:

• Use PRAGMA journal_mode = WAL to allow reads during writes.
• Adjust chunk size based on observed lock contention.

Method 2: Differential Pending Writes Queue

Implement a secondary storage mechanism for pending writes:

Architecture:

1. Main Database: demo2.db in OPFS.
2. Pending Writes: pending.db (SQLite) or pending.json (OPFS file).
3. Reconciliation Process:
   • Tab 2 writes to pending.db when main DB is locked.
   • Tab 1 checks pending.db after completing its transaction.
   • Merge conflicts are resolved via application logic.

Code Skeleton:

void writeToPending(const User& user) {
  sqlite3* pendingDb = nullptr;
  sqlite3_open_v2("pending.db", &pendingDb, ...);
  // Insert into pending_users table
  sqlite3_close(pendingDb);
}

void reconcilePending(sqlite3* mainDb) {
  // Attach pending DB
  sqlite3_exec(mainDb, "ATTACH 'pending.db' AS pending");
  // Merge records with conflict resolution
  sqlite3_exec(mainDb, 
    "INSERT OR REPLACE INTO main.users "
    "SELECT * FROM pending.users "
    "WHERE id NOT IN (SELECT id FROM main.users)");
  sqlite3_exec(mainDb, "DETACH pending");
}

Conflict Resolution:

• Use SQLite’s ON CONFLICT clauses.
• Track write timestamps for last-write-wins semantics.

Final Recommendations

1. Immediate Fix:

   • Close database connections immediately after use.
   • Implement Web Locks API for basic coordination.

2. Medium-Term Solution:

   • Migrate to SQLite’s official JavaScript OPFS VFS.
   • Structure transactions to be short (<100ms).

3. Long-Term Strategy:

   • Monitor browser support for OPFS concurrent access APIs.
   • Consider wa-sqlite if cross-tab notifications are critical.
   • Implement a worker-based write queue for heavy workloads.

By systematically addressing OPFS locking behaviors, adjusting transaction granularity, and leveraging modern browser APIs, robust multi-tab write access to SQLite databases in WASM environments becomes achievable despite current platform limitations.