Database Transaction Isolation Flakiness Prompt

This prompt is for backend test engineers and SDETs who are investigating test failures that appear intermittently and are suspected to be caused by database transaction isolation violations. The job-to-be-done is to take raw evidence—such as transaction logs, database metrics, connection pool configurations, and concurrent access patterns—and produce a structured root cause analysis. The ideal user has access to database logs, understands the application's transaction boundaries, and can correlate failure timestamps with database activity. The prompt is designed to distinguish genuine isolation bugs (e.g., non-repeatable reads, phantom reads, serialization anomalies) from environmental flakiness caused by connection pool exhaustion, lock timeouts, or network blips.

Do not use this prompt when you lack transaction-level logs or when the failure is already confirmed to be a deterministic code defect. It is also inappropriate for diagnosing flakiness in non-transactional data stores, such as simple key-value caches or eventually consistent NoSQL systems where isolation guarantees differ fundamentally. The prompt assumes a relational database with configurable isolation levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE) and expects the user to provide concrete evidence. Without transaction IDs, lock wait times, or concurrent execution traces, the model will produce speculative output that should not be trusted. For high-severity production incidents, always require human review of the model's root cause hypothesis before applying any isolation level or connection configuration changes.

Before using this prompt, gather the required inputs: transaction logs showing conflicting operations, the declared isolation level for each connection, connection pool settings (max size, timeout, validation query), and a timeline of concurrent test executions. The prompt's value comes from its ability to cross-reference these signals and identify specific isolation anomalies. After receiving the output, validate the proposed root cause by reproducing the failure under controlled concurrency, and use the remediation recommendations as a starting point for a code or configuration change, not as a final verdict. If the model cannot confidently attribute the flakiness to an isolation violation, it should recommend further instrumentation rather than guessing.

This prompt template is designed to be copied directly into your AI harness or testing tool. It accepts structured inputs about your database configuration, test execution logs, and failure patterns, then produces a root cause analysis with specific isolation level and connection configuration recommendations. Every placeholder is marked with square brackets—replace them with your actual data before sending the prompt to the model.

textCopy
You are a database reliability engineer specializing in transaction isolation anomalies and test flakiness diagnosis.

Analyze the following test execution data to identify isolation-related flakiness root causes.

## INPUT DATA

### Test Context
- Test name: [TEST_NAME]
- Test framework: [TEST_FRAMEWORK]
- Database engine and version: [DB_ENGINE_VERSION]
- Current transaction isolation level: [CURRENT_ISOLATION_LEVEL]
- Connection pool configuration: [CONNECTION_POOL_CONFIG]

### Failure Pattern
- Failure frequency: [FAILURE_FREQUENCY] (e.g., 3/10 runs)
- Failure timing: [FAILURE_TIMING] (e.g., only under parallel execution, only after deployment)
- Observed symptoms: [OBSERVED_SYMPTOMS] (e.g., stale reads, missing rows, duplicate inserts, deadlocks)

### Transaction Logs

[TRANSACTION_LOGS]

codeCopy

### Concurrent Access Patterns
- Number of concurrent test workers: [CONCURRENT_WORKERS]
- Shared tables or rows accessed: [SHARED_RESOURCES]
- Test data lifecycle: [DATA_LIFECYCLE] (e.g., created per test, shared fixture, cleaned after suite)

### Code Context

[RELEVANT_CODE_SNIPPETS]

codeCopy

## OUTPUT REQUIREMENTS

Produce a structured root cause analysis with these sections:

1. **Isolation Violation Classification**: Identify the specific anomaly type (dirty read, non-repeatable read, phantom read, serialization anomaly, write skew, lost update) and explain how the evidence supports this classification.

2. **Root Cause Chain**: Trace the failure from concurrent operations through the isolation level gap to the observed symptom. Include a sequence diagram in text form showing the conflicting transaction timeline.

3. **Isolation Level Recommendation**: Recommend a specific isolation level change (e.g., READ COMMITTED → REPEATABLE READ) with justification. Include trade-offs: performance impact, deadlock risk, and compatibility with existing code.

4. **Connection Configuration Changes**: Specify exact connection pool settings, session variables, or ORM configuration changes needed. Include code examples for the recommended configuration.

5. **Test-Level Mitigations**: Propose test code changes that add resilience without masking genuine defects (e.g., retry with exponential backoff, explicit locking, data partitioning per test worker).

6. **Verification Steps**: List specific actions to confirm the fix works: what to observe in logs, what failure rate reduction to expect, and how to detect regression.

## CONSTRAINTS
- Do not recommend changing isolation levels without explaining the trade-offs.
- If the evidence is insufficient to classify the anomaly, state what additional data is needed.
- Distinguish between application-level race conditions and database isolation violations.
- Flag any recommendation that requires a production database configuration change for human review.
- If connection pool exhaustion is a contributing factor, separate it from the isolation analysis.

## OUTPUT FORMAT
Return a JSON object with this schema:
{
  "anomaly_classification": {
    "type": "string",
    "confidence": "high|medium|low",
    "evidence_summary": "string"
  },
  "root_cause_chain": {
    "narrative": "string",
    "transaction_timeline": "string"
  },
  "isolation_recommendation": {
    "current_level": "string",
    "recommended_level": "string",
    "justification": "string",
    "trade_offs": ["string"],
    "requires_production_change": true|false
  },
  "connection_config_changes": {
    "settings": [{"parameter": "string", "value": "string", "rationale": "string"}],
    "code_example": "string"
  },
  "test_mitigations": [{"action": "string", "expected_effect": "string"}],
  "verification_steps": ["string"],
  "additional_data_needed": ["string"]
}

Adaptation guidance: Replace each [PLACEHOLDER] with your actual test data. The [TRANSACTION_LOGS] and [RELEVANT_CODE_SNIPPETS] placeholders accept multi-line text—paste your logs and code directly. If you lack certain inputs (e.g., connection pool config), replace the placeholder with Not available rather than removing the field, so the model can flag missing evidence. The output schema is strict JSON; validate the response against this schema before acting on recommendations. For production database changes, always route the isolation_recommendation through a human approval step before applying.

This prompt is designed to operate as a diagnostic step inside a flaky-test investigation workflow, not as a standalone chatbot query. The typical integration point is a CI/CD post-mortem script or a test-infrastructure analysis service that collects transaction logs, isolation-level configurations, connection-pool metrics, and concurrent-access traces from a failed test run, assembles them into the [TRANSACTION_LOG], [ISOLATION_CONFIG], [CONNECTION_POOL_LOG], and [CONCURRENT_ACCESS_LOG] placeholders, and then calls the LLM. The output is a structured root-cause analysis that downstream tooling can parse and route to the appropriate team.

Validation and output contracts are critical here because a misdiagnosis can send an engineer on a wild-goose chase or, worse, lead to a production isolation-level change that introduces data corruption. Wrap the LLM call in a harness that validates the response against a strict JSON schema before accepting it. At minimum, check that root_cause_category is one of the allowed enum values (isolation_level_mismatch, connection_pool_reuse, lock_contention, snapshot_staleness, deadlock_retry, phantom_read, other), that confidence_score is a float between 0.0 and 1.0, and that every evidence entry references a specific log line or timestamp. If validation fails, retry once with the validation errors appended to the prompt as [PREVIOUS_VALIDATION_ERRORS]. If the second attempt also fails, escalate to a human reviewer with the raw logs and both failed responses attached.

Model choice and tool use matter. This prompt benefits from models with strong reasoning over structured logs and database semantics. For high-stakes production incidents, prefer a model with a large context window that can ingest full transaction logs without truncation. If your logs exceed the context window, preprocess them with a log-clustering tool or a summarization prompt before passing them to this prompt. The prompt does not require function calling or external tools during inference, but the harness should log every invocation—including the full prompt, the raw response, the validation result, and any retries—to an observability system. This audit trail is essential when an isolation-level change is later questioned during a postmortem.

Human review gates are non-negotiable when the prompt recommends a production isolation-level change (recommended_isolation_level differs from the current setting) or when confidence_score is below 0.7. In these cases, the harness should create a ticket in your issue tracker with the full analysis, the raw logs, and a checklist for the reviewing engineer: verify the evidence, reproduce the isolation violation in a staging environment, and approve or reject the recommendation. Never allow an automated pipeline to apply an isolation-level change directly from the LLM output. The prompt's requires_human_review boolean field should be enforced by the harness, not trusted on faith.

Retry and error handling should distinguish between transient LLM failures and persistent quality problems. If the model returns a malformed response, retry with the same inputs and a stronger formatting instruction appended. If the model returns a valid response but with confidence_score below 0.5, do not retry automatically—escalate immediately. Low confidence usually means the evidence is ambiguous, and retrying with the same logs is unlikely to help. Instead, the harness should prompt the operator to collect additional data (e.g., enabling log_statement = 'all' in PostgreSQL for the next test run) and re-run the analysis.

Integration with test infrastructure typically looks like this: a test harness detects a flaky failure, collects the relevant logs from the test environment, calls this prompt through your LLM gateway, validates the response, and either posts the analysis to the test report or escalates to a human. If you're using a test observability platform, store the structured output as a custom annotation on the test result so that future flakiness trends can be correlated with isolation-level issues over time. Avoid calling this prompt on every test run—reserve it for tests that have already been flagged as flaky by a cheaper heuristic, such as a pass-after-retry detector.