Transactions and Isolation - Revision history

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

2026-08-28T10:13:53Z

Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

← Older revision		Revision as of 12:13, 28 August 2026
Line 17:		Line 17:
	Prefer one atomic SQL statement where possible. For example, an upsert counter avoids a client-side read/modify/write race:		Prefer one atomic SQL statement where possible. For example, an upsert counter avoids a client-side read/modify/write race:

	<~~syntaxhighlight lang="sql"~~>		<pre>
	INSERT INTO counters (name, value) VALUES ('jobs', 1)		INSERT INTO counters (name, value) VALUES ('jobs', 1)
	ON DUPLICATE KEY UPDATE value = value + 1;		ON DUPLICATE KEY UPDATE value = value + 1;
	</~~syntaxhighlight~~>		</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.		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.

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

2026-08-28T09:59:22Z

Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

@@ Line 1: / Line 1: @@
-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.
+<!-- Copyright (C) 2026 Carl-Philip Haensch -->
-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.
+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.
-Transaction state is tracked per shard. Visibility includes main rows, delta rows, insert/delete overlays, transaction snapshot, and the active shard generation. Commit locks and publishes touched shards; rollback reverses staged or directly applied changes. Nested shard work has a bounded fanout to avoid deadlock and goroutine explosion.
+== Visibility and conflict handling ==
-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 therefore inherits the guarantees and risks of each table's ENGINE.
+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 distributed transaction feature. Test conflicts and failure recovery required by the application.
+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.

Carli: Created page with "Every SQL statement runs in an implicit transaction unless the session already has an explicit `BEGIN`/`COMMIT`/`ROLLBACK` 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..."

2026-08-21T11:01:00Z

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..."

New page

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.

Transaction state is tracked per shard. Visibility includes main rows, delta rows, insert/delete overlays, transaction snapshot, and the active shard generation. Commit locks and publishes touched shards; rollback reverses staged or directly applied changes. Nested shard work has a bounded fanout to avoid deadlock and goroutine explosion.

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 therefore inherits the guarantees and risks of each table's ENGINE.

MemCP does not claim every MySQL isolation level, XA, or distributed transaction feature. Test conflicts and failure recovery required by the application.