Ensuring SQLite Coroutine Safety in Single-Threaded Non-Thread-Safe Builds

Understanding SQLite’s Non-Thread-Safe Build in Single-Threaded Coroutine Environments

Issue Overview: Coroutine Safety in SQLite Without Mutexes

The core issue revolves around using SQLite in a single-threaded, multi-coroutine environment where the SQLite library is compiled without thread safety (i.e., SQLITE_THREADSAFE=0 or -DSQLITE_THREADSAFE=0). In this setup, multiple coroutines operate within the same thread, each managing its own SQLite database connection. The coroutines yield execution during disk I/O operations or when encountering SQLITE_BUSY errors. The primary question is whether this configuration is safe, given that SQLite’s documentation explicitly addresses thread safety but does not mention coroutines.

Key technical considerations include:

1. Coroutine Scheduling: All coroutines run in a single thread, meaning there is no true concurrency. However, coroutines can yield execution at arbitrary points (e.g., during I/O waits or explicit SQLITE_BUSY checks).
2. Non-Thread-Safe Build: SQLite is compiled without internal mutexes, which eliminates synchronization primitives designed for multi-threaded environments. This reduces overhead but assumes no concurrent access to shared resources.
3. Database Connection Isolation: Each coroutine uses a distinct SQLite database connection, theoretically isolating their operations. However, SQLite shares certain global resources (e.g., memory allocators, VFS layers) even across connections.
4. Yield Points: The user plans to yield coroutines at specific points:
   • When sqlite_step() returns SQLITE_BUSY.
   • Inside sqlite3_progress_handler callbacks.
   • During disk I/O operations (e.g., via a custom VFS layer).

The risks arise from unprotected critical sections in SQLite’s internal logic. Even in a single-threaded environment, yielding a coroutine while SQLite is modifying global or connection-specific state could lead to data corruption, undefined behavior, or resource leaks. For example:

• If a coroutine yields during a write operation, another coroutine might read an inconsistent database state.
• Shared resources like the page cache or memory allocator might be accessed in an unsafe order if coroutines interleave their operations.
• Callbacks (e.g., sqlite3_progress_handler) might modify state that another coroutine relies on if not properly isolated.

Potential Risks in Coroutine-Driven SQLite Workflows

The absence of thread safety mechanisms does not inherently make SQLite unsafe in single-threaded coroutine environments. However, the following risks must be evaluated:

1. Unprotected Global State

Even in non-thread-safe builds, SQLite uses global variables for:

• Memory allocation statistics (sqlite3_memory_used()).
• Default VFS and mutex implementations.
• Registered extensions (e.g., user-defined functions, collations).

If two coroutines modify these globals in an interleaved manner (e.g., one coroutine registers a UDF while another is executing a query), the behavior is undefined. For example, a coroutine yielding during sqlite3_create_function() could leave the global function registry in an incomplete state.

2. Connection-Specific State Contention

While each coroutine uses a separate database connection, connections share underlying resources:

• Memory Allocators: SQLite’s default allocator uses global counters for tracking allocations. If two coroutines allocate/free memory in an interleaved way, these counters could become inconsistent.
• VFS Layer: Disk I/O operations (e.g., file locks, sector writes) are managed by the VFS. If a coroutine yields during a file lock acquisition, another coroutine might attempt to lock the same file, leading to deadlocks or corruption.

3. Yield Point Safety

Yielding at SQLITE_BUSY or in a progress handler is not inherently unsafe, but it depends on what SQLite is doing internally when the yield occurs. For example:

• Yielding during a sqlite_step() call that has partially written a B-tree page to disk could leave the database in a corrupted state if another coroutine reads the same page.
• Progress handler callbacks are invoked during long-running operations (e.g., VACUUM). Yielding here might interrupt atomic operations.

4. Stack Size and Reentrancy

Coroutines with small stacks (256KB–1MB) risk stack overflow if SQLite’s recursive functions (e.g., parser logic) exceed the available space. While SQLite is designed to be stack-light, complex queries or deeply nested JSON functions might push these limits.

Strategies for Safe SQLite Usage in Coroutine Environments

Step 1: Validate Global Resource Isolation

• Disable Shared Caches: Ensure each database connection uses a separate page cache by setting PRAGMA locking_mode=EXCLUSIVE and avoiding SQLITE_OPEN_SHAREDCACHE.
• Use Dedicated Memory Allocators: Override SQLite’s default allocator with a coroutine-specific allocator. Implement sqlite3_config(SQLITE_CONFIG_MALLOC, ...) to provide isolated memory pools.
• Isolate VFS Instances: Assign a unique VFS name to each coroutine’s database connection. This prevents file handle contention in the VFS layer.

Step 2: Control Yield Points

• Avoid Yielding in Callbacks: Do not yield inside sqlite3_progress_handler, sqlite3_commit_hook, or other callbacks. These are often invoked during critical operations (e.g., transaction commits).
• Yield Only at SQLITE_BUSY: When sqlite_step() returns SQLITE_BUSY, the coroutine is already in a safe state to yield. SQLite guarantees that no partial changes are made at this point.
• Instrument SQLite Source Code: Add logging to SQLite’s internal functions (e.g., sqlite3BtreeBeginTrans, sqlite3PagerWrite) to identify unsafe yield points. Avoid yielding while these functions are executing.

Step 3: Stress-Test Coroutine Interactions

• Concurrent Write/Read Tests: Run a test where multiple coroutines perform simultaneous writes and reads on separate databases. Monitor for SQLITE_CORRUPT errors or assertion failures.
• Stack Overflow Checks: Use tools like valgrind or compiler stack guards to detect stack exhaustion in coroutines executing complex queries.
• Custom VFS Testing: If using a custom VFS, simulate disk I/O delays and verify that yielding does not leave files in a locked or inconsistent state.

Step 4: Compile-Time and Runtime Configurations

• Enable Debugging Features: Compile SQLite with -DSQLITE_DEBUG to enable internal consistency checks. Use sqlite3_test_control(SQLITE_TESTCTRL_ASSERT) to crash on invariants.
• Disable Unsafe Features: Turn off extensions like JSON1 or FTS5 if they introduce reentrancy risks. Re-enable them incrementally after testing.
• Use Thread-Safe Builds Proactively: Even in single-threaded environments, compiling with SQLITE_THREADSAFE=1 adds negligible overhead and ensures mutexes are available (though no-ops in single-threaded mode).

Step 5: Implement Coroutine Scheduling Guards

• Wrap SQLite API Calls: Create a wrapper around sqlite3_step(), sqlite3_exec(), and other functions that disallow yielding until SQLite returns a "safe" status (e.g., SQLITE_DONE, SQLITE_BUSY).
• Use Async I/O Extensions: Replace synchronous disk I/O with SQLite’s asynchronous I/O module (async.c). This allows coroutines to yield during I/O without blocking the event loop.

By systematically addressing global state isolation, yield point safety, and rigorous testing, developers can mitigate risks in single-threaded coroutine environments using non-thread-safe SQLite builds. The absence of thread safety does not inherently preclude coroutine safety, but it demands meticulous validation of SQLite’s internal assumptions about execution continuity.