Transactions and Isolation: Difference between revisions
(Created page with "Every SQL statement runs in an implicit transaction unless the session already has an explicit <code>BEGIN</code>/<code>COMMIT</code>/<code>ROLLBACK</code> transaction. The default cursor-stability mode applies writes directly with undo masks, gives statement atomicity, and rolls back on errors. MemCP also contains an ACID mode with snapshot visibility and optimistic commit conflict detection. Savepoints are used internally for nested trigger/error recovery. Transactio...") |
Wikiservice (talk | contribs) (Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference) |
||
| (One intermediate revision by the same user not shown) | |||
| Line 1: | Line 1: | ||
<!-- Copyright (C) 2026 Carl-Philip Haensch --> | |||
<!-- SPDX-License-Identifier: GPL-3.0-or-later --> | |||
= Transactions and Isolation = | |||
Every SQL statement runs in an implicit transaction unless its session is already inside an explicit <code>BEGIN</code>/<code>COMMIT</code>/<code>ROLLBACK</code> 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. | |||
MemCP does not claim every MySQL isolation level, XA, or | 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: | |||
<pre> | |||
INSERT INTO counters (name, value) VALUES ('jobs', 1) | |||
ON DUPLICATE KEY UPDATE value = value + 1; | |||
</pre> | |||
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 <code>safe</code> tables, WAL synchronization occurs at commit. <code>logged</code> writes WAL without fsync; <code>sloppy</code>, <code>memory</code>, and <code>cache</code> 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]]. | |||
Latest revision as of 12:13, 28 August 2026
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.