Readonly SQLite Connections in WAL Mode Leave Residual -wal and -shm Files

Understanding Readonly WAL Mode Behavior and Residual File Retention

The interaction between SQLite’s write-ahead logging (WAL) mode and read-only database connections creates operational nuances that frequently surprise developers. When a database connection explicitly specifies the -readonly flag (or equivalent API parameters), residual -wal and -shm files may persist after connection closure despite the absence of explicit write operations. This contrasts with read-write connections in WAL mode, where automatic checkpointing typically removes these files when transactions complete. The discrepancy arises from SQLite’s concurrency model, journaling mechanics, and operating system file ownership constraints. Developers working with multi-process architectures – particularly web applications with privilege-separated database readers – encounter practical challenges when residual files interfere with primary processes or violate permission models.

Foundational Mechanics of WAL Mode and Readonly Constraints

Write-Ahead Logging Fundamentals

SQLite’s WAL mode decouples write operations from read operations by buffering changes in a separate -wal file rather than modifying the main database file directly. The -shm (shared memory) file coordinates access to the WAL index among concurrent processes. This architecture enables simultaneous readers and writers without blocking, but introduces file management responsibilities. A key requirement is that all database connections – including readonly ones – must interact with the WAL and SHM files to maintain transactional consistency, even when no local writes occur.

Readonly Mode Implications

A connection opened with SQLITE_OPEN_READONLY (or the -readonly CLI flag) prohibits schema modifications and data manipulation via INSERT, UPDATE, or DELETE. However, WAL mode imposes additional requirements:

1. WAL File Validation: Every connection must verify the integrity of existing WAL files, requiring read access at minimum.
2. SHM File Coordination: The shared memory file mediates cross-process visibility of uncheckpointed transactions.
3. Checkpoint Inhibition: Readonly connections cannot initiate automatic checkpoints to merge WAL contents into the main database, as this constitutes a write operation.

These constraints force readonly connections to retain WAL/SHM files when opened against a database already in WAL mode. The files persist until a read-write connection performs a checkpoint and deems them obsolete. This behavior becomes problematic when readonly connections operate under different user contexts or lack filesystem permissions to manage these auxiliary files.

Operational Conflicts in Multi-User and Multi-Process Environments

Permission Mismatch During File Creation

When a low-privilege user (e.g., nobody or a restricted service account) executes a readonly query against a database owned by a high-privilege process (e.g., www-data), SQLite may attempt to create new WAL/SHM files if none exist. This fails when the directory lacks write permissions for the low-privilege user, producing errors like unable to open database file. Conversely, if the directory permits file creation, the new WAL/SHM files inherit the low-privilege user’s ownership, potentially causing conflicts when the high-privilege process attempts subsequent writes.

Residual File Retention Post-Connection

Readonly connections cannot perform checkpointing – the process that would allow safe deletion of empty WAL/SHM files. Even if the WAL file contains no new transactions, a readonly connection lacks authority to truncate or remove it. This leaves cleanup dependent on a subsequent read-write connection, which may not occur in systems where readonly access dominates. Over time, residual files accumulate unless explicitly managed.

Cross-Process Coordination Challenges

The SHM file implements a locking mechanism that relies on shared memory semantics. When multiple processes (even readonly ones) access the database, each must have consistent visibility into the WAL state. A readonly connection that cannot access or update the SHM file (due to permissions or filesystem restrictions) may default to exclusive locking, defeating WAL’s concurrency advantages or causing query failures.

Mitigation Strategies for Residual WAL/SHM File Management

Configuring Journal Mode Compatibility

Assess whether WAL mode is strictly necessary for the application’s readonly workload. Alternatives like journal_mode=DELETE (the default) may be preferable for readonly-heavy access patterns:

PRAGMA journal_mode=DELETE; -- Set in main application before readonly access

This mode avoids WAL/SHM file creation entirely, using rollback journals instead. However, this sacrifices WAL’s concurrent read/write benefits. Evaluate tradeoffs between concurrency needs and file management complexity.

Explicit WAL Persistence Configuration

SQLite’s SQLITE_FCNTL_PERSIST_WAL file control (via sqlite3_file_control()) allows intentional retention of WAL files across connections. When persistent WAL is enabled, the database retains the WAL file even after all connections close, reducing churn from frequent file creation/deletion:

// C API example to persist WAL
sqlite3_file_control(db, "main", SQLITE_FCNTL_PERSIST_WAL, &persist);

For command-line usage, this requires application-level integration. Persistent WAL is advantageous when multiple readonly connections repeatedly access the database, as they reuse an existing WAL file instead of generating new ones.

File Permission and Ownership Harmonization

Align filesystem permissions between primary (read-write) and secondary (readonly) processes:

1. Group-Based Ownership: Set the database directory’s group ownership to a common group (e.g., db-access), with chmod g+rwx permissions. Ensure both www-data and the low-privilege user belong to this group.
2. Sticky Bit for WAL/SHM Files: Configure the directory with chmod +t to ensure files created within inherit the directory’s ownership, preventing disjoint user-owned residuals.
3. Access Control Lists (ACLs): Apply filesystem ACLs to grant specific read/write permissions on WAL/SHM files without broad directory write access:

setfacl -m u:lowprivuser:rw /path/to/db/-wal
setfacl -m u:lowprivuser:rw /path/to/db/-shm

Forced Checkpointing via Read-Write Connections

Schedule periodic checkpoints from authorized write connections to purge obsolete WAL files:

PRAGMA wal_checkpoint(TRUNCATE); -- Truncate WAL to zero size after checkpoint

Automate this via application logic or external cron jobs. Combine with SQLITE_FCNTL_PERSIST_WAL to retain WAL files across restarts while still allowing periodic cleanup.

Readonly Connection URI Parameters

Use URI-style connections to enforce strict readonly semantics and WAL mode handling:

sqlite3 "file:/path/to/db?mode=ro&journal_mode=WAL" -- No WAL/SHM creation if main db not in WAL

This configuration prevents readonly connections from inadvertently switching the journal mode to WAL, which could trigger unnecessary file creation. Note that if the main database is already in WAL mode, the readonly connection must still interact with existing WAL/SHM files.

Application-Level File Cleanup

Implement a monitored cleanup process that removes WAL/SHM files when safe:

1. Size-Based Cleanup: Scripts can remove -wal files smaller than a threshold (e.g., 0 bytes) when no active connections exist.
2. Connection Monitoring: Use lsof or inotify to detect when all database connections close, triggering cleanup.
3. Startup/Shutdown Hooks: Integrate cleanup routines into application startup/shutdown sequences to handle residual files from crashed processes.

Alternative: In-Memory Databases with Backup Strategies

For high-volume readonly queries, consider serving from an in-memory database populated via backups from the primary disk-based database:

-- Primary connection
ATTACH DATABASE 'file:memdb?mode=memory&cache=shared' AS mem;
BACKUP mem FROM main;
-- Readonly connections access 'memdb' with no WAL files

This approach eliminates WAL/SHM file concerns entirely but requires careful memory management and backup scheduling.

Conclusion

Residual WAL/SHM file retention in readonly SQLite connections stems from WAL mode’s design requirements rather than a software defect. Successful mitigation hinges on understanding the interplay between journaling modes, filesystem permissions, and connection semantics. Developers must choose between adjusting journaling strategies, harmonizing file access controls, or implementing auxiliary cleanup mechanisms – each with distinct tradeoffs in concurrency, complexity, and operational overhead. Rigorous testing under representative multi-user conditions remains essential to validate the chosen approach against the application’s specific failure modes and performance requirements.