database-transactions-isolationlisted
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