Transactions and Isolation

Transactions and Isolation

Every SQL statement runs in an implicit transaction unless its session is already inside an explicit BEGIN/COMMIT/ROLLBACK transaction. This provides statement-level error rollback without requiring every client to manage a transaction for a single statement.

Visibility and conflict handling

The default cursor-stability mode applies writes with undo information, provides statement atomicity, and rolls changes back on errors. MemCP also contains an ACID path with snapshot visibility and optimistic conflict detection at commit. Savepoints are used internally for nested trigger and error recovery.

Transaction state is tracked per shard. A read combines main rows, delta rows, insertion/deletion overlays, its transaction snapshot, and the active shard generation. Commit locks and publishes touched shards; rollback reverses staged or directly applied work. Nested shard work uses bounded fanout to avoid unbounded goroutines and lock cycles.

Applications should test their actual contention patterns. MemCP does not claim every MySQL isolation level, XA, distributed transactions, or every locking clause. An unsupported guarantee should be treated as unsupported rather than inferred from accepted syntax.

Atomic write patterns

Prefer one atomic SQL statement where possible. For example, an upsert counter avoids a client-side read/modify/write race:

INSERT INTO counters (name, value) VALUES ('jobs', 1)
ON DUPLICATE KEY UPDATE value = value + 1;

Use an explicit transaction when several statements must succeed or fail as a unit. Keep it short: long transactions retain visibility state, increase conflict probability, and can delay maintenance.

Durability is a separate axis

Isolation describes which concurrent changes a transaction observes; durability describes what survives a failure. For safe tables, WAL synchronization occurs at commit. logged writes WAL without fsync; sloppy, memory, and cache skip WAL durability. A transaction spanning tables inherits the guarantees and risks of every table ENGINE involved.

See Advanced SQL Tutorial, Persistency and Performance Guarantees, Triggers, and Supported SQL.