In this lesson, you will learn how to optimize database concurrency by choosing between the traditional Rollback Journal and Write-Ahead Logging (WAL).
When you modify data in an embedded database like SQLite, the system must guarantee ACID compliance, even if the power cuts out mid-transaction. To achieve this, the database needs a "safety net" to revert changes if something goes wrong.
The Rollback Journal (Traditional)
The default approach is the Rollback Journal. When you start a transaction, the database copies the original contents of any pages you are about to modify into a separate file (the journal). If a crash occurs, the database copies those original pages back into the main database file to restore it.
The downside is that this mode is strictly exclusive: while the journal exists, readers cannot access the database because they might read data that is currently in a state of flux. This creates a "Reader-Writer Contention" problem.
The WAL Advantage
Write-Ahead Logging (WAL) flips the script. Instead of writing changes to the main database file, the database appends those changes to a separate "WAL file."
- Concurrent Reads/Writes: Because the main database file remains untouched until a "checkpoint" occurs, readers can continue to read the original data from the main file while the writer appends new data to the WAL.
- Performance: Appending to a log is significantly faster than performing random writes across the main database file.
You can typically switch your database to WAL mode using a simple command:
PRAGMA journal_mode = WAL;
In this mode, the database engine keeps the main file and the WAL file in sync. Once the WAL file grows sufficiently large, the system performs a "checkpoint," moving the changes into the main database file and clearing the log. This architecture is the primary reason why modern embedded applications can handle multiple simultaneous readers without blocking, even when a process is busy writing new data.
Key Takeaway: Use Write-Ahead Logging to allow multiple concurrent readers to access your data without being blocked by ongoing write operations.