[MILESTONE] Support explicit transaction control (BEGIN / COMMIT / ROLLBACK) end-to-end

[longbinlai]

Background

Today, every statement submitted to NeuG is implicitly wrapped in a single-statement transaction by the connection layer. Users have no way to:

• Open a transaction explicitly with BEGIN TRANSACTION (or BEGIN TRANSACTION READ ONLY).
• Bundle multiple statements into one atomic unit (e.g. create a node and a relationship based on it without committing the create in between).
• Explicitly COMMIT or ROLLBACK an in-flight transaction.
• Get atomicity guarantees across CREATE + MATCH + DELETE chains that span multiple queries.

The Cypher grammar and parser already accept the keywords (see Cypher.g4:203-207 and parser/transform/transform_transaction.cpp), and the binder/planner emit a BoundTransactionStatement for them. The gap is everything below the compiler: session/connection lifecycle, runtime execution, the Python binding, and the HTTP service.

This milestone tracks the end-to-end delivery of explicit transaction control so users can drive long-running, multi-statement transactions safely.

Scope

In scope

1. Session-level transaction state on Connection (and Python Session):
   • Default auto_commit = true; BEGIN switches the session to an active transaction.
   • Subsequent statements participate in the active transaction until COMMIT / ROLLBACK / connection close.
   • Connection::Close() rolls back any in-flight transaction (no silent partial commit).
2. Multi-statement execution path that supports BEGIN; stmt1; stmt2; ...; COMMIT; in a single Query/execute call.
   • Detect TransactionAction statements while iterating statements and steer session state accordingly.
   • Reject mixing read-only txn control with DDL/DML (per [BUG] Read-only queries misclassified as write/schema when Cypher comments contain DDL/DML keywords #760 follow-up).
3. Runtime execution of BoundTransactionStatement:
   • BEGIN_WRITE / BEGIN_READ: open a Transaction on the storage layer, set the active read/write timestamp, attach to the connection's ClientContext.
   • COMMIT: drive the two-phase commit (per Refactor UpdateTransaction #86 staging), flush WAL, publish the new snapshot.
   • ROLLBACK: discard staged mutations, restore prior snapshot, no WAL durability required.
4. Read-only enforcement: BEGIN TRANSACTION READ ONLY rejects any statement classified as write/schema.
5. AP/TP integration (with Refactor UpdateTransaction #86, refactor: Correct the behavior of manual checkpoint for AP and TP #762):
   • Respect CheckpointCoordinator invariants — explicit COMMIT may force a staging checkpoint if forceCheckpoint is set.
   • Coordinate with Avoid unnecessary checkpointing when starting TP service after AP open without changes #715 (TP-from-AP) so an open explicit txn blocks mode flips.
6. Error handling: any exception inside an active txn triggers an automatic ROLLBACK and the error is surfaced to the user; the session is left in a clean state for the next transaction.
7. Python binding & HTTP service:
   • Python Connection.begin(), commit(), rollback() methods.
   • Python Connection.execute("BEGIN; ...; COMMIT;") multi-statement path.
   • HTTP POST /transaction/begin|commit|rollback or carry a transaction_id header on existing endpoints.
   • HTTP server must keep the same session pinned to the same transaction across requests.
8. Test coverage:
   • Unit tests for Connection lifecycle and rollback-on-error.
   • E2E tests in tests/e2e/ for explicit txn with concurrent reader isolation.
   • Crash-recovery tests: kill process after BEGIN + some writes + before COMMIT → reopen and confirm no partial state.
9. Documentation:
   • doc/source/transaction/transaction.md — add a section on explicit transaction semantics, isolation, AP/TP interaction.
   • Python API reference and Node.js API reference updates.
   • Migration note for users previously relying on per-statement auto-commit.

Phased delivery

Phase 1 — Compiler plumbing

• Close out Support explicit transaction in compiler #774 (parse + bind + plan) for the four transaction statements.
• Define TransactionAction ↔ Connection semantics in a new CompilerTransactionStatement execution operator.
• Deliverable: prepared statements for BEGIN/COMMIT/ROLLBACK round-trip through the compiler without yet touching storage.

Phase 2 — Storage / session layer

• Add Connection::active_transaction_ and the auto-commit flag.
• Implement beginTransaction(readOnly), commitTransaction(), rollbackTransaction() against ClientContext + UpdateTransaction (consume Refactor UpdateTransaction #86's two-phase API).
• Wire Connection::Close to rollback on pending txn.
• Deliverable: C++ embedded mode can drive a multi-statement explicit txn round-trip; rollback-on-error unit test passes.

Phase 3 — Python binding & service mode

• Add Connection.begin|commit|rollback to tools/python_bind.
• Expose a Session.execute_script that streams a multi-statement string through the multi-statement path.
• HTTP service: add a transaction handle (cookie or X-Transaction-Id header) and pin the session to the active txn across requests.
• Deliverable: from neug import Connection user can run begin → write → write → commit end-to-end, and the HTTP service supports the same flow.

Phase 4 — Read-only enforcement & isolation

• Add the READ ONLY runtime guard: any write/schema classification inside the txn aborts with a clear error.
• Reuse the snapshot isolation already provided by ClientContext's read txn (from Refactor UpdateTransaction #86) — explicit txn must not break concurrent reader visibility.
• Deliverable: isolation test that a long-running explicit txn does not block concurrent read txns (post-Refactor UpdateTransaction #86).

Phase 5 — Recovery & docs

• Crash-recovery test: kill mid-transaction, reopen, verify clean state.
• Documentation updates in doc/source/transaction/transaction.md and the Python/Node API references.
• Add a "Migration from auto-commit" section.
• Deliverable: docs merged; e2e test in CI.

Acceptance criteria

• BEGIN TRANSACTION; <writes>; COMMIT; works in embedded mode, Python binding, and HTTP service.
• BEGIN TRANSACTION READ ONLY; <read>; COMMIT; rejects writes with a clear error.
• An exception inside an explicit txn automatically rolls back; the session is reusable.
• Connection.close() rolls back any open txn.
• Multi-statement execution: begin; stmt; stmt; commit; in a single call works.
• HTTP service can span an explicit txn across multiple requests via a transaction handle.
• Crash mid-transaction leaves the database in a consistent state on reopen.
• Documentation and Python/Node API references updated.
• No regression in the implicit auto-commit path (existing e2e suite green).

Open questions

• Should BEGIN TRANSACTION (without mode) default to read-write, or be ambiguous? (Proposal: read-write, matches SQL.)
• Does the HTTP service keep the txn alive across keep-alive connections only, or do we need a long-poll / explicit handle?
• For Python: do we want a context-manager API with conn.transaction(): ... in addition to explicit begin/commit/rollback?

Labels

transaction, enhancement, compiler, python, service

[github-actions]

🤖 Auto-triage: could not confidently classify this issue under any current v0.2 umbrella.

Reason: The issue is explicitly labeled as a [MILESTONE] and tracks end-to-end delivery of explicit transaction control across multiple subsystems, making it a tracking/umbrella issue itself.

Auto-applied: type → Feature; labels → executor, client

Maintainers: please pick a parent manually, or close if out of scope.