Claude SQL Server MCP: define transaction isolation for repeatable answers

Claude asks SQL Server for a summary, reads the result, requests a drill-down, and then asks for the total again.

If rows changed between those calls, all three results may be individually valid and mutually inconsistent.

The fix is not to keep one database transaction open for the entire AI conversation. The fix is to define what consistency each MCP tool promises.

Start with the SQL Server behavior you actually have

SQL Server's default READ COMMITTED behavior depends on database configuration. With READ_COMMITTED_SNAPSHOT disabled, reads commonly use shared locks. With it enabled, statement-level reads use row versions.

Neither setting creates a conversation-wide snapshot across separate tool calls.

Document the database option, the isolation level used by each approved operation, and whether a result represents one statement, one bounded transaction, or a stored reporting snapshot.

Choose a consistency class per operation

Most MCP tools fit one of four classes:

• live statement: each call sees committed data according to the database configuration;
• bounded transaction: several statements execute inside one short server-side transaction;
• snapshot read: the operation uses snapshot semantics with a documented start time;
• materialized report: the tool reads a versioned reporting result produced at a known cutoff.

Expose that class in the tool contract. Do not leave it for the model to infer from fluent output.

Do not hold a transaction across model turns

An AI conversation can pause for seconds or minutes. A transaction held open while the model reasons can retain row versions, increase tempdb pressure, extend locks, complicate pooling, and fail when the client retries or disconnects.

Instead, keep transactions inside one tool invocation. If a workflow needs multiple related values, expose a bounded operation that computes them together.

incident_summary(
  start_time,
  end_time,
  service_group
)

The server runs the necessary statements under one documented isolation rule and returns one evidence envelope.

Use snapshot semantics deliberately

Snapshot isolation can give a multi-statement operation a stable view, but it is not free. It depends on database configuration, row-version storage, transaction duration, and workload.

Before using it:

• confirm the relevant database options;
• measure tempdb and version-store pressure;
• bound query time and result size;
• handle update conflicts if the operation can write;
• keep the operation read-only when the use case is analysis.

For many reporting questions, a short bounded transaction or an explicit reporting cutoff is simpler than trying to maintain a live conversational snapshot.

Separate pagination from transaction lifetime

A large result may require multiple tool calls. Do not preserve consistency by leaving the original transaction open.

Use a stable continuation contract instead:

• deterministic ordering with a unique tie-breaker;
• an opaque cursor bound to tenant, filters, sort, and query version;
• a high-water mark or materialized result when a stable dataset is required;
• explicit expiration and restart behavior.

See the keyset pagination contract; the same design principle applies even though SQL syntax and engine behavior differ.

Define reporting cutoffs and timezones

Isolation cannot fix an ambiguous time window. “Today,” “current month,” and “last 24 hours” need a reporting timezone, inclusive and exclusive bounds, and a captured cutoff.

Return those normalized values with the answer so a follow-up call can reuse the same window intentionally.

Related: timezone and cutoff contracts for AI database queries.

Return consistency evidence

A useful result envelope includes:

• isolation or consistency class;
• transaction or snapshot start time when relevant;
• source freshness and reporting cutoff;
• normalized parameters and metric version;
• returned count, truncation, and pagination state;
• database or replica identity;
• trace ID.

This does not ask the model to understand database internals. It gives reviewers enough evidence to know whether two answers are comparable.

For the wider evidence pattern, see query provenance for AI database agents.

A concrete tool result

{
  "summary": {...},
  "consistency": {
    "class": "bounded_snapshot",
    "started_at": "2026-07-31T01:07:12Z"
  },
  "window": {
    "start": "2026-07-30T22:00:00Z",
    "end_exclusive": "2026-07-31T01:00:00Z",
    "timezone": "Europe/Copenhagen"
  },
  "source_fresh_through": "2026-07-31T01:06:55Z",
  "trace_id": "..."
}

Test under concurrent writes

1. Insert and update qualifying rows while the operation runs.
2. Repeat the operation under locking read committed and RCSI in a test environment.
3. Run multi-statement reads and confirm the documented consistency class.
4. Cancel the client halfway through and verify the transaction closes.
5. Retry after a timeout and check whether the reporting cutoff remains explicit.
6. Page through results while boundary rows change.
7. Monitor blocking, deadlocks, tempdb, and version-store growth.
8. Verify that the final answer preserves cutoff and freshness evidence.

Keep reads governed

Transaction isolation is not authorization. Derive tenant and role scope from identity, expose approved operations or views, enforce time and row limits, and keep mutation tools separate from analysis.

For a broader SQL Server setup path, see MCP server for SQL Server AI agents.

Where Conexor fits

Conexor provides MCP infrastructure for connecting AI clients to databases and APIs through governed tools. An explicit isolation contract keeps SQL Server answers understandable under concurrent change without turning an AI conversation into a long-running database transaction.

Explore connecting SQL Server to Claude