← ClaudeAtlas

database-transactions-isolationlisted

How databases store, version and isolate data: B+tree vs LSM engines, WAL and recovery, MVCC and vacuum, row locks and SKIP LOCKED queues, real vendor isolation semantics (PostgreSQL, InnoDB, Oracle, SQL Server, CockroachDB), anomalies as concrete histories, and their fixes. Load when concurrent transactions misbehave, picking an isolation level, or reasoning about storage engines (query tuning lives in sql-query-tuning).
rustcircle/c101 · ★ 0 · API & Backend · score 65
Install: claude install-skill rustcircle/c101
# Database transactions and isolation ## 1. Scope This skill covers how the engine stores, versions and isolates rows. Index design and plan reading live in `sql-query-tuning`; DDL lock levels and online schema changes in `zero-downtime-migrations`; replicated and distributed consistency (and Elle-style history checking) in `distributed-consensus-consistency`. Behaviour below is stated for PostgreSQL and MySQL 8 InnoDB unless another vendor is named; verify edge cases against the Hermitage suite for your version. ## 2. Storage engines | Engine | Writes | Reads | Space | Examples | |---|---|---|---|---| | B+tree, update in place | random page writes plus WAL | one tree walk, good ranges | page slack from splits | InnoDB, PostgreSQL indexes, SQLite, WiredTiger | | LSM, leveled compaction | sequential, high write amplification (about fanout x levels) | bloom filters for points; ranges merge levels | low | RocksDB default, LevelDB | | LSM, tiered/universal | lower write amplification | higher read amplification | up to about 2x during compaction | Cassandra STCS, RocksDB universal, ScyllaDB | | Bitcask (hash index + log) | append only | one seek | all keys must fit in RAM | Riak | | Columnar | batch appends | fast scans, slow point lookups | compressed | Parquet, ClickHouse, DuckDB | RUM: an engine trades read, update and memory (space) amplification; improving two worsens the third. Pages: PostgreSQL 8 KB, InnoDB 16 KB. PostgreSQL stores tables as heaps with separate indexe