SQLite Transactions, WAL, and Locking Cheatsheet

BEGIN;
INSERT INTO audit_log(action) VALUES ('start');
UPDATE counters SET value = value + 1 WHERE key = 'jobs';
COMMIT;

# Bundle multiple statements atomically.

BEGIN IMMEDIATE;

# Reserve the write lock up front.

ROLLBACK;

# Undo uncommitted changes.

SAVEPOINT import_batch;
INSERT INTO tags(name) VALUES ('sqlite');
ROLLBACK TO import_batch;
RELEASE import_batch;

# Create a nested rollback point within a transaction.

PRAGMA journal_mode = WAL;

# Switch the database to write-ahead logging mode.

PRAGMA busy_timeout = 5000;

# Wait briefly when the database is locked.

PRAGMA wal_checkpoint(TRUNCATE);

# Merge WAL contents back into the main database file.

PRAGMA locking_mode;

# See or set the database locking mode.