An AI assistant asks the database three reasonable questions:
- how many incidents are open?
- which services are affected?
- which incidents are oldest?
While those calls run, one incident closes and another is created.
Every query can be correct on its own while the final answer contradicts itself.
The fix is not to keep a transaction open for the entire AI conversation. Model reasoning and user clarification can turn seconds into minutes.
Instead, define a short bounded data operation. Compose related reads inside one transaction, return a structured result, close the transaction, and let the model reason afterward.
Make the consistency promise explicit:
- single statement
- single snapshot
- watermark-aligned sources
- best-effort live reads
- unknown
If several tool calls cannot share a snapshot, return an observation time and watermark for each result. Do not let the final prose quietly upgrade them into one point-in-time claim.
Retries matter too. After a serialization failure, deadlock, failover, or timeout, rerun the complete logical operation in a clean transaction. Never combine half of attempt one with half of attempt two.
A snapshot receipt should carry identity, scope, isolation mode, source/replica, observation boundary, filters, limits, retry attempts, completion state, and trace ID.
The conversation is not the transaction.
Full guide: MCP database answers need multi-query snapshot consistency
Top comments (0)