playbooks

home / skills / tursodatabase / turso / transaction-correctness

transaction-correctness skill

/.claude/skills/transaction-correctness

This skill explains Turso's transaction correctness with WAL, checkpointing, and recovery to ensure durable, isolated, and consistent SQL data.

npx playbooks add skill tursodatabase/turso --skill transaction-correctness

Review the files below or copy the command above to add this skill to your agents.

Files (1)
SKILL.md
3.0 KB
---
name: transaction-correctness
description: How WAL mechanics, checkpointing, concurrency rules, recovery work in tursodb
---
# 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)

Overview

This skill explains transaction correctness in Turso by detailing WAL mechanics, checkpointing, concurrency rules, and recovery behavior. It focuses on how Turso implements SQLite-compatible WAL semantics in an in-process, single-process environment and what invariants guarantee durable, atomic, and isolated transactions.

How this skill works

WAL writes append page frames to a .db-wal file; a COMMIT is encoded as a frame with a non-zero db_size marking transaction end. Readers take a read mark (mxFrame) and consult the WAL up to that frame before falling back to the main .db file, producing consistent snapshots. Checkpointing transfers WAL pages back into the main DB using PASSIVE, FULL, RESTART, or TRUNCATE strategies, while Turso replaces the disk shm index with an in-memory frame cache and atomic read marks.

When to use it

  • When designing or debugging transaction behavior for embedded Turso databases.
  • When you need to understand how readers observe consistent snapshots while writers append to WAL.
  • When tuning checkpoint behavior and choosing PASSIVE vs FULL vs RESTART vs TRUNCATE.
  • When verifying crash recovery and ensuring committed transactions are durable.
  • When implementing concurrency controls or integrating Turso into single-process environments.

Best practices

  • Ensure COMMITs are fsynced before reporting success to clients to preserve durability.
  • Limit long-running readers to avoid blocking checkpoint progress; use PASSIVE checkpoints where possible.
  • Keep write transactions short to reduce contention on the single-writer lock.
  • Monitor WAL growth and checkpoint frequency (default trigger ~1000 pages) to avoid large WAL files.
  • Rely on Turso’s in-memory WAL-index semantics only for single-process use; do not assume multi-process shared-memory behavior.

Example use cases

  • A local desktop app using Turso wants consistent reads while background writes append to WAL.
  • A WebAssembly host embeds Turso and needs deterministic recovery semantics after process restart.
  • A developer investigates why checkpointing stalls due to an active long-lived reader and adjusts reader lifetime.
  • A system validates that commits persisted across crashes by inspecting WAL replay on exclusive lock acquisition.

FAQ

How does Turso differ from SQLite with respect to the WAL index?

Turso does not use a -shm shared-memory file; it keeps the WAL index in-memory (frame_cache and atomic read marks) because multi-process access is not supported.

What happens on crash recovery?

The first connection to open the DB gets an exclusive lock, replays all valid commits from the WAL into the main DB, then releases the lock so normal operations can resume.