Preventing Concurrent Modification Race Conditions in SQLite Virtual Tables

Understanding the Core Challenge: Virtual Table Updates With External Data Mutations

The central issue revolves around maintaining data consistency when executing UPDATE operations on SQLite virtual tables (vtabs) while underlying records may be modified concurrently through non-SQL pathways. This scenario occurs in multithreaded environments where:

1. A virtual table interfaces with external data structures (e.g., concurrent_map<int, Data*>)
2. Threads modify Data fields both via SQL statements (UPDATE vtab SET a = a + 1 WHERE key=123 AND a=3) and direct memory operations
3. The virtual table implementation must coordinate SQLite’s transactional logic with external synchronization primitives

SQLite’s default transaction handling cannot automatically detect changes made outside its control to objects managed through virtual tables. When a virtual table’s xUpdate method modifies a row identified by a volatile key (e.g., WHERE key=100), another thread might alter or invalidate that key’s associated data before SQLite completes the update cycle. The original poster’s solution involved intercepting rowid generation logic to implement lock-check-retry patterns directly in SQLite’s virtual machine opcodes – an effective but fragile approach requiring core code modification.

Key technical relationships creating this challenge include:

• Virtual tables acting as bridges between SQLite’s transactional model and external data stores
• The dual mutation pathways (SQL vs direct code) operating on shared Data objects
• SQLite’s read-uncommitted isolation level behavior for virtual tables without explicit synchronization
• The temporal gap between SQLite’s WHERE clause evaluation and actual row modification

Critical Failure Points in Concurrent Virtual Table Operations

1. Transaction Scope Mismatch Between SQLite and External Data Stores

Virtual tables that proxy external data structures inherit their host environment’s concurrency characteristics rather than SQLite’s ACID guarantees. Consider a Data object with fields a and version managed through both SQL and direct memory access:

struct Data {
    std::atomic<int> a;
    std::atomic<uint64_t> version;
    std::mutex mtx;
};

SQLite transactions wrapping vtab updates cannot automatically synchronize with external mutex locks on Data::mtx. This creates race conditions where:

The version mismatch at t4 demonstrates how SQLite’s transaction isolation fails to prevent write-write conflicts when external modifications occur outside its control.

2. Lack of Two-Phase Locking in Virtual Table Implementations

Native SQLite tables employ strict two-phase locking with write-ahead logging (WAL), but virtual tables delegate locking semantics to their implementations. Common anti-patterns include:

• Check-then-Act Race Conditions: Evaluating WHERE clauses without holding locks through subsequent modification phases
• Row Identification Through Volatile Keys: Using externally mutable values (like Data::a) as row identifiers without versioned snapshots
• Implicit Connection Sharing: Multiple threads operating on the same database connection despite SQLite’s thread safety modes

The original poster’s key=100 example demonstrates this perfectly – between identifying the rowid for key=100 and applying the update, another thread could modify or delete the key, leading to lost updates or constraint violations.

3. Cross-Paradigm Synchronization Overhead

Merging SQLite’s transactional model with lock-free data structures requires careful coordination. A concurrent_map might use fine-grained locking or atomic operations for read efficiency, but SQLite’s update process introduces multi-step workflows:

1. xFilter/xBestIndex to locate candidate rows
2. xColumn calls to gather current values
3. xRowid to obtain unique identifiers
4. xUpdate to apply changes

Each phase presents opportunities for state changes when external modifications interleave with these steps. Traditional double-checked locking patterns become necessary but aren’t natively supported by SQLite’s virtual table API.

Comprehensive Synchronization Strategy for Hybrid Data Access

1. Virtual Table Transaction Integration

Implement SQLite’s transaction hooks in your virtual table module to synchronize with external data stores:

typedef struct ConcurrentMapVTab ConcurrentMapVTab;
struct ConcurrentMapVTab {
    sqlite3_vtab base;
    ExternalDataStore* store;
    uint64_t transaction_version;
};

static int xBegin(sqlite3_vtab *pVTab) {
    ConcurrentMapVTab* p = (ConcurrentMapVTab*)pVTab;
    p->transaction_version = p->store->begin_transaction();
    return SQLITE_OK;
}

static int xSync(sqlite3_vtab *pVTab) {
    ConcurrentMapVTab* p = (ConcurrentMapVTab*)pVTab;
    p->store->commit_transaction(p->transaction_version);
    return SQLITE_OK;
}

static int xRollback(sqlite3_vtab *pVTab) {
    ConcurrentMapVTab* p = (ConcurrentMapVTab*)pVTab;
    p->store->rollback_transaction(p->transaction_version);
    return SQLITE_OK;
}

Pair this with versioned snapshots in your external data store:

class ExternalDataStore {
    std::atomic<uint64_t> global_version{0};
    std::shared_mutex snapshot_mutex;
    
public:
    uint64_t begin_transaction() {
        std::shared_lock lock(snapshot_mutex);
        return global_version.load(std::memory_order_acquire);
    }
    
    void commit_transaction(uint64_t version) {
        std::unique_lock lock(snapshot_mutex);
        global_version.store(version + 1, std::memory_order_release);
    }
};

This creates happens-before relationships between SQL transactions and external modifications.

2. Optimistic Concurrency Control with Versioned Rows

Augment your virtual table’s row access methods with automatic version checking:

static int xColumn(
    sqlite3_vtab_cursor *pCursor,
    sqlite3_context *ctx,
    int col
) {
    ConcurrentMapCursor *pCsr = (ConcurrentMapCursor *)pCursor;
    Data* data = pCsr->current_item->data;
    
    // Capture version atomically
    uint64_t version = data->version.load(std::memory_order_acquire);
    
    switch(col) {
        case 0: // key
            sqlite3_result_int(ctx, pCsr->current_item->key);
            break;
        case 1: // value
            sqlite3_result_int(ctx, data->a.load(std::memory_order_relaxed));
            break;
        case 2: // version (hidden column)
            sqlite3_result_int64(ctx, version);
            break;
    }
    
    // Store version in cursor for later validation
    pCsr->last_version = version;
    return SQLITE_OK;
}

Then enforce version consistency in xUpdate:

static int xUpdate(
    sqlite3_vtab *pVTab,
    int argc,
    sqlite3_value **argv,
    sqlite3_int64 *pRowid
) {
    ConcurrentMapVTab *p = (ConcurrentMapVTab *)pVTab;
    int key = sqlite3_value_int(argv[1]);
    
    Data* data = p->store->find(key);
    uint64_t expected_version = sqlite3_value_int64(argv[3]); // Hidden version column
    
    std::unique_lock lock(data->mtx);
    if(data->version != expected_version) {
        return SQLITE_CONSTRAINT; // Triggers transaction rollback
    }
    
    // Proceed with update
    data->a = sqlite3_value_int(argv[2]);
    data->version++;
    
    return SQLITE_OK;
}

Expose the version as a hidden column in your virtual table definition:

CREATE TABLE concurrent_map(key INT PRIMARY KEY, value INT HIDDEN, version HIDDEN);

This pattern enables SQLite to automatically handle retries through its transaction system without core code modifications.

3. Hybrid Pessimistic-Optimistic Locking Protocol

Combine short-duration mutex locks with version validation for high contention scenarios:

1. Filter Phase: Collect candidate rows with shared locks
2. RowID Phase: Upgrade to exclusive locks and validate versions
3. Update Phase: Hold locks until transaction commit

Implement through cursor lifecycle management:

typedef struct ConcurrentMapCursor {
    sqlite3_vtab_cursor base;
    std::shared_ptr<ExternalDataIterator> iterator;
    Data* current_data;
    uint64_t locked_version;
} ConcurrentMapCursor;

static int xNext(sqlite3_vtab_cursor *pCursor) {
    ConcurrentMapCursor *pCsr = (ConcurrentMapCursor *)pCursor;
    
    // Release previous lock
    if(pCsr->current_data) {
        pCsr->current_data->mtx.unlock_shared();
    }
    
    pCsr->iterator->next();
    pCsr->current_data = pCsr->iterator->get();
    
    if(pCsr->current_data) {
        pCsr->current_data->mtx.lock_shared();
    }
    
    return SQLITE_OK;
}

static int xRowid(sqlite3_vtab_cursor *pCursor, sqlite3_int64 *pRowid) {
    ConcurrentMapCursor *pCsr = (ConcurrentMapCursor *)pCursor;
    
    // Upgrade to exclusive lock
    pCsr->current_data->mtx.unlock_shared();
    pCsr->current_data->mtx.lock();
    
    // Validate version matches initial read
    if(pCsr->current_data->version != pCsr->locked_version) {
        pCsr->current_data->mtx.unlock();
        return SQLITE_BUSY_SNAPSHOT;
    }
    
    *pRowid = pCsr->iterator->current_key();
    return SQLITE_OK;
}

static int xClose(sqlite3_vtab_cursor *pCursor) {
    ConcurrentMapCursor *pCsr = (ConcurrentMapCursor *)pCursor;
    
    if(pCsr->current_data) {
        if(pCsr->base.pVtab->zErrMsg) {
            // Update in progress - keep lock until commit
        } else {
            pCsr->current_data->mtx.unlock();
        }
    }
    
    return SQLITE_OK;
}

This approach minimizes lock contention by:

• Using shared locks during scanning
• Upgrading to exclusive locks only for candidate rows
• Integrating lock lifetime with SQLite’s cursor management

4. Connection Management and Thread Isolation

Enforce strict thread-to-connection affinity with connection pooling:

class ThreadLocalConnection {
    static thread_local std::unique_ptr<sqlite3, decltype(&sqlite3_close)> conn;
    
public:
    static sqlite3* get() {
        if(!conn) {
            sqlite3* db;
            sqlite3_open(":memory:", &db);
            conn.reset(db);
            
            // Enable WAL for concurrent access
            sqlite3_exec(db, "PRAGMA journal_mode=WAL;", nullptr, nullptr, nullptr);
        }
        return conn.get();
    }
};

// Usage in thread
void worker_thread() {
    auto* db = ThreadLocalConnection::get();
    // Use db exclusively in this thread
}

Configure SQLite in serialized mode to allow safe connection sharing (though not recommended):

sqlite3_config(SQLITE_CONFIG_SERIALIZED);

Combine with SQLITE_OPEN_NOMUTEX flags for connection-specific locking:

sqlite3_open_v2("file:data.db?mode=memory", &db, 
    SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX, nullptr);

5. Shadow Table Synchronization

Mirror volatile external data in SQLite-managed shadow tables for transactional consistency:

-- Virtual table declaration
CREATE VIRTUAL TABLE ex_data USING extern_module;

-- Shadow table for transaction isolation
CREATE TABLE _shadow_ex_data (
    key INTEGER PRIMARY KEY,
    value INTEGER,
    version INTEGER
);

-- Update trigger
CREATE TRIGGER ex_data_before_update INSTEAD OF UPDATE ON ex_data
BEGIN
    INSERT INTO _shadow_ex_data(key, value, version)
    VALUES (NEW.key, NEW.value, NEW.version)
    ON CONFLICT(key) DO UPDATE SET
        value = excluded.value,
        version = excluded.version;
END;

Use compound queries to merge live and shadow data:

WITH live_data AS (
    SELECT key, value, version FROM ex_data
    WHERE key = ?
),
shadow_data AS (
    SELECT key, value, version FROM _shadow_ex_data
    WHERE key = ?
)
SELECT 
    COALESCE(sd.key, ld.key) AS key,
    COALESCE(sd.value, ld.value) AS value,
    COALESCE(sd.version, ld.version) AS version
FROM live_data ld
FULL OUTER JOIN shadow_data sd ON ld.key = sd.key;

6. SQLite Extension Hooks for External Synchronization

Leverage SQLite’s extension API to intercept transaction boundaries without modifying core code:

static void xCommitHook(void *pArg) {
    ExternalDataStore* store = (ExternalDataStore*)pArg;
    store->commit();
}

sqlite3_commit_hook(db, xCommitHook, store);

static int xRollbackHook(void *pArg) {
    ExternalDataStore* store = (ExternalDataStore*)pArg;
    store->rollback();
    return 0;
}

sqlite3_rollback_hook(db, xRollbackHook, store);

Combine with update hooks for granular change tracking:

static void xUpdateHook(
    void *pArg,
    int op,
    char const *dbName,
    char const *tableName,
    sqlite3_int64 rowid
) {
    if(op == SQLITE_UPDATE && strcmp(tableName, "ex_data") == 0) {
        ExternalDataStore* store = (ExternalDataStore*)pArg;
        store->notify_update(rowid);
    }
}

sqlite3_update_hook(db, xUpdateHook, store);

7. Hybrid CAS (Compare-and-Swap) Operations

Implement atomic updates through custom SQL functions:

static void cas_value(
    sqlite3_context *context,
    int argc,
    sqlite3_value **argv
) {
    int key = sqlite3_value_int(argv[0]);
    int expected = sqlite3_value_int(argv[1]);
    int desired = sqlite3_value_int(argv[2]);
    
    ExternalDataStore* store = (ExternalDataStore*)sqlite3_user_data(context);
    bool success = store->compare_and_swap(key, expected, desired);
    
    sqlite3_result_int(context, success ? 1 : 0);
}

// Register function
sqlite3_create_function(db, "cas_value", 3, SQLITE_UTF8, store, cas_value, 0, 0);

Execute updates atomically:

UPDATE ex_data
SET value = desired
WHERE key = 123 AND cas_value(123, current_value, desired);

This moves the concurrency control into the virtual table implementation while maintaining standard SQL syntax.

8. Version-Aware Query Rewriting

Intercept SQL statements at parse time to inject version checks:

static int xAuthorizer(
    void *pUserData,
    int actionCode,
    const char *param1,
    const char *param2,
    const char *dbName,
    const char *triggerName
) {
    if(actionCode == SQLITE_UPDATE && strcmp(param1, "ex_data") == 0) {
        // Rewrite UPDATE to include version check
        inject_version_check((sqlite3*)pUserData);
    }
    return SQLITE_OK;
}

sqlite3_set_authorizer(db, xAuthorizer, db);

Rewrite engine example:

void inject_version_check(sqlite3* db) {
    const char* sql = "UPDATE ex_data SET value = ?1 WHERE key = ?2 AND version = ?3";
    sqlite3_stmt* stmt;
    sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr);
    // Replace default UPDATE handling with versioned variant
}

This approach maintains query expressiveness while enforcing concurrency checks transparently.

9. Lock Escalation in Virtual Table Scans

Implement progressive locking strategies in xFilter and xNext methods:

static int xFilter(
    sqlite3_vtab_cursor *pCursor,
    int idxNum, const char *idxStr,
    int argc, sqlite3_value **argv
) {
    ConcurrentMapCursor *pCsr = (ConcurrentMapCursor *)pCursor;
    
    // Acquire intention-shared lock on entire store
    pCsr->store->lock_shared();
    
    // Proceed with scan
    return SQLITE_OK;
}

static int xClose(sqlite3_vtab_cursor *pCursor) {
    ConcurrentMapCursor *pCsr = (ConcurrentMapCursor *)pCursor;
    
    // Release IS lock
    pCsr->store->unlock_shared();
    
    return SQLITE_OK;
}

Combine with row-level locks in xRowid:

static int xRowid(sqlite3_vtab_cursor *pCursor, sqlite3_int64 *pRowid) {
    ConcurrentMapCursor *pCsr = (ConcurrentMapCursor *)pCursor;
    
    // Upgrade to exclusive row lock
    pCsr->current_item->mutex.lock();
    
    // Validate version
    if(pCsr->current_item->version != pCsr->cached_version) {
        pCsr->current_item->mutex.unlock();
        return SQLITE_BUSY;
    }
    
    *pRowid = pCsr->current_item->id;
    return SQLITE_OK;
}

10. Cross-Process Synchronization with Advisory Locks

Extend coordination beyond single-process threads using SQLite’s advisory locks:

SELECT ex_data_lock(123); -- Implemented via xBestIndex/xFilter

UPDATE ex_data SET value = value + 1 WHERE key = 123;

SELECT ex_data_unlock(123);

Virtual table implementation:

static int xBestIndex(
    sqlite3_vtab *pVTab,
    sqlite3_index_info *pIdxInfo
) {
    // Detect ex_data_lock/ex_data_unlock special functions