Database Backup Fails Amid Frequent WAL-Mode Updates in SQLite

Understanding Backup Interruptions During High-Frequency WAL-Mode Transactions

Mechanics of WAL-Mode Checkpoints and Backup Conflicts

SQLite’s Write-Ahead Logging (WAL) mode introduces unique behaviors that directly impact backup operations. When a database operates in WAL mode, all write transactions append data to the -wal file instead of overwriting the main database file. Checkpoints periodically transfer data from the WAL file to the main database. The original poster’s strategy involved disabling automatic checkpoints (PRAGMA wal_autocheckpoint=0) and forcing a full checkpoint (PRAGMA wal_checkpoint(FULL)) before initiating a backup via sqlite3_backup_init(). However, the backup process repeatedly restarted despite these steps.

The root cause lies in how SQLite’s backup API interacts with WAL files. Even after a full checkpoint, the WAL file remains active if concurrent write transactions are ongoing. The backup API does not operate on a static snapshot of the database; instead, it dynamically copies pages from the current state of the source database, which includes both the main file and the active WAL. If new transactions modify the WAL during the backup, the backup process detects these changes and restarts to maintain internal consistency. This occurs because the backup mechanism prioritizes data integrity over speed, ensuring that the destination database reflects a valid state, even if that state evolves during the backup.

The VACUUM INTO command initially appeared to resolve this issue because it creates a discrete copy of the database by reconstructing it from scratch. However, the error "cannot VACUUM - SQL statements in progress" revealed a critical oversight: unfinalized SQL statements on the same database connection block the VACUUM operation. This highlights the importance of connection state management when using blocking commands like VACUUM.

Key Factors Contributing to Backup Failures and Blocked VACUUM Operations

1. Active WAL File During Backup Initialization
   Disabling automatic checkpoints does not freeze the WAL file. Concurrent write transactions continue appending to the WAL, even after a manual FULL checkpoint. The backup API monitors the WAL’s state and restarts if the file grows or changes during the backup.

2. Backup API’s Dynamic Page Tracking
   The sqlite3_backup_step() function iterates through database pages, but it does not lock the database. If the source database is modified during this process, the backup restarts to account for new or altered pages. This is inherent to the API’s design and unavoidable without additional safeguards.

3. Connection Contention in VACUUM Operations
   The VACUUM INTO command requires exclusive access to the database connection. Any unfinalized prepared statements, pending transactions, or concurrent operations on the same connection will block VACUUM. This includes unclosed cursors or forgotten sqlite3_finalize() calls on prior queries.

4. Misunderstanding WAL File Isolation
   The assumption that disabling autocheckpoints would "freeze" the main database file is incorrect. While checkpoints transfer data from the WAL to the main file, the WAL itself remains writable. Concurrent updates alter the WAL, which the backup process still recognizes as part of the active database state.

Resolving Backup Instability and VACUUM Errors

Step 1: Validate Connection and Transaction States

Before initiating any backup or VACUUM operation:

• Ensure all prior SQL statements on the backup/VACUUM connection are finalized using sqlite3_finalize().
• Verify that no transactions are active (sqlite3_get_autocommit() == 1).
• Use separate database connections for backup/VACUUM and write operations to avoid contention.

Step 2: Implement a Stable Backup Strategy

Option A: Snapshot-Based Backup Using sqlite3_backup

1. Acquire a Write Lock: Use BEGIN IMMEDIATE to block incoming write transactions temporarily.
2. Checkpoint and Disable WAL:

   PRAGMA wal_checkpoint(TRUNCATE);  -- Truncate WAL after checkpoint
   PRAGMA journal_mode = DELETE;     -- Temporarily disable WAL
   

   This forces SQLite to revert to rollback journal mode, eliminating WAL activity during the backup.

3. Execute sqlite3_backup_init(): Perform the backup while holding the write lock.
4. Re-enable WAL: After backup completion, restore WAL mode and release the lock.

Option B: File-Level Copy with WAL Handling

1. Checkpoint and Lock:

   PRAGMA wal_checkpoint(FULL);
   BEGIN IMMEDIATE;
   

2. Copy Files: Manually copy the main database file, the WAL file, and the shared-memory file (-shm).
3. Release Lock: Commit the transaction to allow writes to resume.

Option C: Use VACUUM INTO with Connection Isolation

1. Dedicate a Connection: Reserve a connection exclusively for VACUUM operations.
2. Prevent Contention: Ensure no other threads or processes use this connection.
3. Execute VACUUM INTO 'backup.db': This creates a transactionally consistent copy.

Step 3: Mitigate Performance Impacts

• Incremental Backups: Use the backup API’s ability to resume partial backups (sqlite3_backup_step() with smaller page counts) to reduce contention.
• Schedule Backups During Low Activity: Coordinate with update cycles to minimize conflicts.
• Monitor WAL Growth: Use PRAGMA wal_checkpoint(PASSIVE) periodically to prevent unbounded WAL file expansion.

Step 4: Debugging and Validation

• Check WAL State: Query PRAGMA wal_checkpoint before and after backups to confirm checkpoint success.
• Log Backup Progress: Track sqlite3_backup_remaining() to identify restarts or stalls.
• Use SQLite Error Codes: Check for SQLITE_BUSY or SQLITE_LOCKED errors during backup/VACUUM, which indicate unresolved contention.

By addressing the interplay between WAL checkpoints, connection states, and backup methodologies, developers can achieve stable backups even in high-frequency update environments. The choice between sqlite3_backup, file-level copies, and VACUUM INTO depends on tolerance for downtime, performance requirements, and transactional consistency needs.