Transaction Correctness
Understand Turso WAL commits, checkpoints, and reader/writer rules so local and edge SQLite-style apps stay consistent under concurrency.
Overview
Transaction Correctness is an agent skill most often used in Build (also Ship, Operate) that explains Turso WAL mechanics, checkpointing, and concurrency so solo builders can reason about safe reads and writes.
Install
npx skills add https://github.com/tursodatabase/turso --skill transaction-correctnessWhat is this skill?
- Explains WAL-only mode with `.db` and `.db-wal` files and no `.db-shm` (in-memory WAL index instead)
- Documents write path (append frames, COMMIT via db_size frame) and read path (read marks + consistent snapshots)
- Covers PASSIVE, FULL, RESTART, and TRUNCATE checkpoint types and default ~1000-page checkpoint trigger
- States concurrency: one writer, readers do not block writer, checkpoint stops at active reader pages
- Contrasts Turso frame_cache/read marks vs SQLite shared-memory WAL index for single-process deployments
- Documents 4 checkpoint types: PASSIVE, FULL, RESTART, TRUNCATE
- Default checkpoint trigger described as 1000 pages
- Turso uses `.db` and `.db-wal` without `.db-shm`
Adoption & trust: 696 installs on skills.sh; 19.1k GitHub stars; 3/3 security scanners passed (skills.sh audits).
What problem does it solve?
You are shipping features on Turso but cannot predict what happens when multiple connections read and write, or when the WAL file grows without a clear checkpoint story.
Who is it for?
Indie builders adding Turso to APIs, agents, or edge apps who need a precise WAL mental model before tuning concurrency or debugging weird DB state.
Skip if: Teams that only need high-level ORM usage with no interest in WAL files, recovery, or single-writer constraints.
When should I use this skill?
Debugging Turso transaction behavior, WAL growth, checkpoints, or concurrency when building or operating embedded database-backed apps.
What do I get? / Deliverables
After applying the guide, you can design queries and connection usage around one-writer rules, snapshot reads, and checkpoint behavior—and spot correctness bugs before they reach production.
- Correct mental model for WAL commits and reader snapshots
- Checklist-style reasoning for writer/reader and checkpoint issues
Recommended Skills
Journey fit
Spans multiple journey phases - primary shelf plus alternate fits below.
Backend data-layer decisions land in Build when you wire Turso into apps; the skill is the canonical reference for how transactions actually behave on disk. Persistence, transactions, and recovery are core backend concerns—not frontend or launch work—so backend is the right primary shelf.
Where it fits
Agent drafts a batch importer and you verify each batch uses explicit transactions compatible with a single writer.
Flaky integration tests under parallel jobs get traced to WAL snapshot behavior instead of random retries.
Post-restart odd reads lead you to check whether a checkpoint left readers on stale read marks.
How it compares
Use as a Turso-specific correctness primer, not as a generic SQL tutorial or a hosted-Postgres migration guide.
Common Questions / FAQ
Who is transaction-correctness for?
Solo and indie developers using Turso or Turso-compatible embedded SQLite who want agent help explaining WAL commits, checkpoints, and reader/writer semantics without guessing from generic SQLite docs.
When should I use transaction-correctness?
During Build when modeling transactions and connection pools; in Ship when reviewing concurrency or test flakiness; and in Operate when investigating WAL growth, checkpoint stalls, or inconsistent reads after crashes.
Is transaction-correctness safe to install?
It is documentation-style procedural knowledge with no bundled executables described in the skill body; review the Security Audits panel on this Prism page and the upstream repo before trusting any install source.
SKILL.md
READMESKILL.md - Transaction Correctness
# Transaction Correctness Guide Turso uses WAL (Write-Ahead Logging) mode exclusively. Files: `.db`, `.db-wal` (no `.db-shm` - Turso uses in-memory WAL index) ## WAL Mechanics ### Write Path 1. Writer appends frames (page data) to WAL file (sequential I/O) 2. COMMIT = frame with non-zero db_size in header (marks transaction end) 3. Original DB unchanged until checkpoint ### Read Path 1. Reader acquires read mark (mxFrame = last valid commit frame) 2. For each page: check WAL up to mxFrame, fall back to main DB 3. Reader sees consistent snapshot at its read mark ### Checkpointing Transfers WAL content back to main DB. ``` WAL grows → checkpoint triggered (default: 1000 pages) → pages copied to DB → WAL reused ``` Checkpoint types: - **PASSIVE**: Non-blocking, stops at pages needed by active readers - **FULL**: Waits for readers, checkpoints everything - **RESTART**: Like FULL, also resets WAL to beginning - **TRUNCATE**: Like RESTART, also truncates WAL file to zero length ### WAL-Index SQLite uses a shared memory file (`-shm`) for WAL index. **Turso does not** - it uses in-memory data structures (`frame_cache` hashmap, atomic read marks) since multi-process access is not supported. ## Concurrency Rules - One writer at a time - Readers don't block writer, writer doesn't block readers - Checkpoint must stop at pages needed by active readers ## Recovery On crash: 1. First connection acquires exclusive lock 2. Replays valid commits from WAL 3. Releases lock, normal operation resumes ## Turso Implementation Key files: - [WAL implementation](../../../core/storage/wal.rs) - WAL implementation - [Page management, transactions](../../../core/storage/pager.rs) ### Connection-Private vs Shared **Per-Connection (private):** - `Pager` - page cache, dirty pages, savepoints, commit state - `WalFile` - connection's snapshot view: - `max_frame` / `min_frame` - frame range for this connection's snapshot - `max_frame_read_lock_index` - which read lock slot this connection holds - `last_checksum` - rolling checksum state **Shared across connections:** - `WalFileShared` - global WAL state: - `frame_cache` - page-to-frame index (replaces `.shm` file) - `max_frame` / `nbackfills` - global WAL progress - `read_locks[5]` - read mark slots (TursoRwLock with embedded frame values) - `write_lock` - exclusive writer lock - `checkpoint_lock` - checkpoint serialization - `file` - WAL file handle - `DatabaseStorage` - main `.db` file - `BufferPool` - shared memory allocation ## Correctness Invariants 1. **Durability**: COMMIT record must be fsynced before returning success 2. **Atomicity**: Partial transactions never visible to readers 3. **Isolation**: Each reader sees consistent snapshot 4. **No lost updates**: Checkpoint can't overwrite uncommitted changes ## References - [SQLite WAL](https://sqlite.org/wal.html) - [WAL File Format](https://sqlite.org/walformat.html)