Transaction support: no BeginTransaction API, DbCommand.Transaction never assigned, dead defaultIsolation parameter

[DJGosnell]

Finding from the 2026-07-07 multi-agent deep review (runtime-API perspective), adversarially verified end-to-end. Identified as the single biggest adoption blocker in the runtime API for a library competing with Dapper (where tx is a first-class argument on every call).

Current state (verified)

• QuarryContext exposes no BeginTransaction/Commit/Rollback API.
• QueryExecutor contains zero occurrences of Transaction — no execution path ever assigns DbCommand.Transaction. The only cmd.Transaction assignments in the tree are in the migration subsystem (MigrationRunner.cs, MigrateCommands.cs). All raw-SQL paths in QuarryContext likewise never set it.
• The constructor's defaultIsolation parameter is stored (QuarryContext.cs:36, 52, 99) and never read by anything — repo-wide grep finds only pass-through constructors and a generator test asserting the parameter exists. A user passing IsolationLevel.Serializable silently gets nothing.
• The consequence is acknowledged inside the repo: Quarry.Tests/QueryTestHarness.cs:221-237 documents that SqlClient requires every command on a connection with an open SqlTransaction to have command.Transaction assigned, "which makes BeginTransaction() incompatible", and works around it with a raw BEGIN TRANSACTION command; :174-185 sets MySqlConnector's IgnoreCommandTransaction=True for the same reason and notes "Production consumers who attach Quarry to a pooled connection-with-transaction will hit the same need." Also documented in llm-testing.md:80-81.
• Net effect: a user who opens an explicit transaction on db.Connection gets an immediate provider exception on SQL Server and MySQL when running any chain query (Npgsql/SQLite are permissive).

Proposed work

• First-class transaction API on QuarryContext, e.g.:

  await using var tx = await db.BeginTransactionAsync();           // uses _defaultIsolation
  await using var tx = await db.BeginTransactionAsync(IsolationLevel.Serializable);
  ...
  await tx.CommitAsync();   // RollbackAsync; implicit rollback on dispose without commit

  Context tracks the current transaction; every command construction site assigns command.Transaction = ctx.CurrentTransaction when set.
• Wire all command sites: QueryExecutor paths, generated carrier terminals (CarrierEmitter/TerminalBodyEmitter emit the assignment or route command creation through a context helper that applies it), raw-SQL paths, batch insert, Patch update.
• Honor defaultIsolation as the default for BeginTransactionAsync() — or, if transactions are deliberately out of scope, remove the dead parameter and document the workarounds prominently (current state advertises a knob that does nothing).
• Ambient/external transaction interop: decide behavior when the user began a transaction directly on the connection (detect-and-adopt vs document-as-unsupported). At minimum, document.
• Thread-safety/docs: one context per unit of work already documented; state that the transaction is context-scoped, not thread-safe.
• Tests: cross-dialect commit/rollback tests through the chain API on all four backends (the harness workarounds in QueryTestHarness can then be retired for these paths); a SqlClient/MySqlConnector regression test proving chain queries work inside BeginTransactionAsync().
• Docs: usage article + llm.md section; EF Core migration table row (Database.BeginTransactionAsync → equivalent).

Severity: high (API gap blocking common production usage), not a correctness bug in existing code.