Issue Overview: ReadOnly Connections and WAL/SHM File Retention Behavior

When working with SQLite databases in Write-Ahead Logging (WAL) mode, a common observation arises: closing a ReadWrite connection typically deletes the associated WAL (Write-Ahead Logging) and SHM (Shared Memory) files, while closing a ReadOnly connection leaves these files intact. This behavior can lead to confusion, as developers might assume the presence of WAL/SHM files indicates an active database connection or incomplete cleanup.

The WAL file contains uncommitted changes to the database, while the SHM file coordinates access to shared memory for concurrent connections. In ReadWrite mode, SQLite automatically manages these files by creating them on first write operation and deleting them when the last connection closes. However, ReadOnly connections exhibit different behavior due to their inherent constraints. A ReadOnly connection cannot modify the database file or its associated WAL/SHM files. This limitation extends to file deletion operations, which are considered modifications.

The core issue stems from SQLite’s design philosophy for ReadOnly connections: they operate as passive observers of the database state. When a ReadOnly connection closes, SQLite does not attempt to delete WAL/SHM files because (1) ReadOnly connections lack write permissions to alter the filesystem, and (2) other active connections might still require these files. This behavior is intentional and aligns with SQLite’s concurrency model in WAL mode, where multiple connections (both ReadOnly and ReadWrite) can coexist.

A critical misunderstanding arises when developers interpret retained WAL/SHM files as evidence of an orphaned connection or software defect. In reality, these files persist because SQLite delegates cleanup responsibility to the last ReadWrite connection that closes. If all connections to the database are ReadOnly, no cleanup occurs, as none have the authority to delete files. This design ensures data integrity but requires developers to understand SQLite’s file management semantics in WAL mode.

Possible Causes: Why ReadOnly Connections Preserve WAL/SHM Files

1. File System Permission Constraints
ReadOnly connections open the database with the SQLITE_OPEN_READONLY flag, which explicitly denies write operations to the database file and its associated WAL/SHM files. File deletion constitutes a write operation at the filesystem level, as it modifies directory metadata. Even if the WAL/SHM files are empty, SQLite cannot delete them without write permissions to the directory containing the database. This permission constraint applies regardless of whether the operating system user account has general write access to the directory.

2. Shared Resource Management in WAL Mode
In WAL mode, the SHM file acts as a coordination point for all database connections. ReadOnly connections must read the SHM file to determine the current state of the database (e.g., committed transactions). However, they cannot update the SHM file’s contents. When a ReadOnly connection closes, SQLite cannot safely assume it is the last active connection. Premature deletion of WAL/SHM files would disrupt other active connections, potentially causing crashes or data corruption. Only ReadWrite connections maintain the necessary metadata (e.g., transaction counters) to determine when cleanup is safe.

3. Checkpointing Behavior Differences
Automatic checkpointing – the process of transferring data from the WAL file to the main database – occurs only during write operations. Since ReadOnly connections cannot trigger checkpoints, they leave the WAL file untouched. Even if a ReadWrite connection previously executed a checkpoint, the WAL file may still exist if subsequent ReadOnly connections kept it open for read consistency.

4. Connection Closure Semantics
SQLite distinguishes between connection closure and file cleanup. Closing a connection releases resources associated with that specific connection (e.g., prepared statements, transaction locks) but does not necessarily trigger filesystem operations. File cleanup is a separate phase that occurs only when permitted by the connection type (ReadWrite) and when safe (no active connections).

5. Filesystem Monitoring Misinterpretation
Developers monitoring the database directory might observe WAL/SHM file persistence and erroneously attribute it to connection leaks. This misinterpretation arises from conflating SQLite’s internal resource management with filesystem state. SQLite does not guarantee immediate file deletion upon connection closure, even for ReadWrite connections, as background threads or other processes might briefly retain access.

Troubleshooting Steps, Solutions & Fixes: Managing WAL/SHM File Retention

A. Validating Connection Types and Permissions

1. Audit Connection Flags: Ensure all connections intended to be ReadWrite use SQLITE_OPEN_READWRITE without SQLITE_OPEN_READONLY. In some language bindings (e.g., Python’s sqlite3 module), the default might be ReadOnly unless explicitly specified.
2. Verify Filesystem Permissions: Confirm the directory housing the database grants write permissions to the process running SQLite. ReadOnly connections require read access to the database and WAL/SHM files but cannot function without read access to the SHM file.
3. Check for Mixed Connection Types: Use PRAGMA journal_mode to confirm the database remains in WAL mode. If another process switched the journal mode to DELETE (rollback journal), WAL/SHM files would not be used.

B. Forcing WAL/SHM Cleanup

1. Open/Close a ReadWrite Connection: The simplest method to delete residual WAL/SHM files is to open a ReadWrite connection and immediately close it. This triggers a checkpoint (if automatic checkpointing is enabled) and cleans up files if no other connections exist.

   sqlite3* db;
   sqlite3_open_v2("database.db", &db, SQLITE_OPEN_READWRITE, NULL);
   sqlite3_close(db);
   

2. Manual Checkpoint Execution: Execute PRAGMA wal_checkpoint(FULL); from a ReadWrite connection to force a checkpoint and WAL truncation. This reduces the WAL file size but does not delete it unless all transactions are committed and no connections are active.
3. File Deletion Precautions: Manually deleting WAL/SHM files while any connection is active risks database corruption. If absolutely necessary, ensure all connections are closed and use filesystem operations to remove the files:

   rm database.db-wal database.db-shm
   

C. Architectural Adjustments for Predictable Behavior

1. Avoid ReadOnly Connections for WAL Cleanup: If your application requires deterministic WAL/SHM cleanup, prefer ReadWrite connections even for read-only workloads. Combine with SQLITE_OPEN_NOMUTEX to allow concurrent reads.
2. Use Exclusive Locking Mode: Configure the database to use exclusive locking mode (PRAGMA locking_mode=EXCLUSIVE;) if single-process access is guaranteed. This disables WAL mode but eliminates SHM file usage.
3. Periodic Maintenance Connections: Schedule a background process to periodically open/close a ReadWrite connection, ensuring WAL/SHM files get cleaned up. This is particularly useful in long-running applications with intermittent write activity.

D. Monitoring and Diagnostics

1. Connection Tracking: Maintain an application-level registry of open connections. Increment/decrement counters on sqlite3_open()/sqlite3_close() to detect leaks.
2. WAL File Size Monitoring: Use PRAGMA wal_checkpoint(TRUNCATE); to keep WAL file size manageable. Monitor file size trends to identify abnormal retention patterns.
3. SQLITE_CONFIG_LOG: Enable SQLite’s error logging to detect unexpected connection states or permission errors during file operations.

E. Alternative Approaches

1. In-Memory Databases: For read-heavy workloads requiring no persistence, consider :memory: databases. These avoid WAL/SHM files entirely but lose data on process termination.
2. Database Copy Operations: Create a copy of the database in DELETE journaling mode when distributing read-only replicas. This eliminates WAL/SHM dependencies:

   sqlite3 source.db "VACUUM INTO 'copy.db';"
   

3. File System Notifications: Integrate with OS-specific file change notifications (e.g., inotify on Linux) to alert when WAL/SHM files persist beyond expected timeframes.

By understanding SQLite’s intentional design choices around ReadOnly connections and WAL/SHM file management, developers can architect systems that either embrace file retention as a harmless side effect or implement rigorous cleanup protocols through ReadWrite coordination. The key lies in recognizing that WAL/SHM file presence does not inherently indicate resource leaks but rather reflects SQLite’s concurrency safeguards.