WAL Mode (Write-Ahead Logging)

WAL Mode (Write-Ahead Logging) is a SQL statement in the SQLite Specific category. SQLite: WAL mode allows concurrent reads and writes. Much better performance for concurrent access. The syntax is PRAGMA journal_mode = WAL;. It returns wAL mode enabled. A typical example: PRAGMA journal_mode = WAL; -- Mode changes from 'delete' to 'wal' -- Benefits: -- 1. Readers don't block writers -- 2. Writers don't block readers -- 3. Faster write performance -- 4. Better concurrency -- Disadvantages: -- 1. Slightly larger file (-wal and -shm files) -- 2. Requires VACUUM or checkpoint to reclaim space PRAGMA wal_checkpoint; -- Manually checkpoint WAL to main DB A close relative is AUTOINCREMENT (SQLite), which sQLite: unlike AUTO_INCREMENT in MySQL, only INTEGER PRIMARY KEY columns auto-increment. AUTOINCREMENT guarantees unique increasing IDs. A close relative is ROWID / _ROWID_, which sQLite: every row has an implicit 64-bit ROWID. INTEGER PRIMARY KEY becomes an alias for ROWID.