SQLite WAL Mode - Non-Blocking Reads During Writes

intermediate

SQLite default mode (rollback journal) blocks readers during writes and blocks writers during reads. WAL (Write-Ahead Logging) mode allows one writer and multiple readers simultaneously. Writers don't block readers, readers don't block writers. This is critical for mobile apps where UI reads data while background sync writes data. WhatsApp uses WAL mode to allow reading messages while syncing new messages from server.

-- Check current journal mode

PRAGMA journal_mode;

-- Result: delete (default rollback journal mode)

-- Enable WAL mode (Write-Ahead Logging)

PRAGMA journal_mode=WAL;

-- Result: wal

-- Creates two files: mydb.sqlite-wal (write-ahead log), mydb.sqlite-shm (shared memory)

-- Create messages table

CREATE TABLE messages (

message_id INTEGER PRIMARY KEY AUTOINCREMENT,

chat_id INTEGER NOT NULL,

sender_id INTEGER NOT NULL,

content TEXT NOT NULL,

timestamp INTEGER NOT NULL,

is_read INTEGER DEFAULT 0

);

CREATE INDEX idx_chat_timestamp ON messages(chat_id, timestamp);

-- Thread 1: UI reads messages (reader)

-- Runs continuously while user scrolls

BEGIN;

SELECT message_id, sender_id, content, timestamp

FROM messages

WHERE chat_id = 123

ORDER BY timestamp DESC

LIMIT 50;

-- Result: 50 messages

-- In WAL mode: Reads from database file + WAL file

-- Not blocked by Thread 2's writes

-- Thread 2: Background sync writes new messages (writer, concurrent)

-- Syncs messages from server

BEGIN;

INSERT INTO messages (chat_id, sender_id, content, timestamp)

VALUES

(123, 456, 'Hello!', 1704067200),

(123, 789, 'Hi there!', 1704067201),

(123, 456, 'How are you?', 1704067202);

-- Writes to WAL file (not main database file)