sql
BEGIN;
INSERT INTO audit_log(action) VALUES ('start');
UPDATE counters SET value = value + 1 WHERE key = 'jobs';
COMMIT;Notes
Wrapping related writes in one transaction improves consistency and often performance.
SQLite Transactions, WAL, and Locking Cheatsheet
BEGIN modes, COMMIT and ROLLBACK, savepoints, WAL mode, busy timeouts, and concurrency patterns for SQLite applications.
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
Transactions and savepoints
Control atomic writes and partial rollback behavior.
sql
BEGIN IMMEDIATE;Notes
Useful when you know the transaction will write and want to fail early if the database is busy.
sql
ROLLBACK;Notes
A safety valve when a migration, import, or manual edit goes wrong.
sql
SAVEPOINT import_batch;
INSERT INTO tags(name) VALUES ('sqlite');
ROLLBACK TO import_batch;
RELEASE import_batch;Notes
Helpful for large imports or multi-step workflows where one chunk may fail.
WAL mode and lock handling
Improve write concurrency and handle busy databases gracefully.
sql
PRAGMA journal_mode = WAL;Notes
WAL often improves read/write concurrency for app-style workloads.
sql
PRAGMA busy_timeout = 5000;Notes
A practical first-line defense against transient `database is locked` failures.
sql
PRAGMA wal_checkpoint(TRUNCATE);Notes
Useful in maintenance jobs or after heavy write bursts when you want to reclaim WAL size.
sql
PRAGMA locking_mode;Notes
Useful when debugging exclusive vs normal locking behavior.
More in SQLite
SQLite Triggers, Views, and Generated Columns Cheatsheet
CREATE VIEW, CREATE TRIGGER, audit logging, derived values, generated columns, and automation patterns in SQLite.
SQLite Full-Text Search (FTS5) Cheatsheet
FTS5 virtual tables, MATCH queries, prefix search, phrase search, bm25 ranking, snippets, and highlighting patterns for SQLite search features.
SQLite PRAGMA and Administration Cheatsheet
High-signal PRAGMA commands for foreign keys, cache, synchronous settings, page size, temp storage, health checks, and general SQLite administration.
SQLite JSON Functions Cheatsheet
json_extract, json_set, json_each, JSON operators, generated columns, and practical JSON query/update patterns in SQLite.
SQLite Import, Export, and Backup Cheatsheet
CSV import/export, SQL dumps, VACUUM INTO, backup copies, and repeatable data movement patterns for SQLite databases.
SQLite Indexes and Query Planner Cheatsheet
CREATE INDEX patterns, covering and partial indexes, EXPLAIN QUERY PLAN, ANALYZE, and practical tuning tips for SQLite queries.