agent flagged 300 'duplicate' queries that were actually retries after a deadlock, not an N+1 loop

TL;DR

Three hundred identical queries after a deadlock are the retry layer doing its job, not an N+1 bug. Before flagging duplicates, the agent must check whether the repeats carry retry markers: same transaction, rising attempt counts, a deadlock error between attempts. Fix the detector to group retries as one logical operation. Fix the deadlock separately if the retry rate itself is the problem.

The query

agent flagged 300 'duplicate' queries that were actually retries after a deadlock, not an N+1 loop

Use this when

• A duplicate-query alert fires on queries that repeat inside transaction retries.
• The repeated queries cluster around deadlock or serialization errors in the log.
• The "N+1" has no per-row loop in the code to explain it.

Not for

• Genuine N+1 loops in request handlers (per-row queries with no retry involved).
• Duplicate queries from missing eager loading.
• Retry storms caused by the application's own retry policy (that is a real problem, just not an N+1).

Steps

Step 1: Pull the query log with errors around the duplicates

grep -B 2 -A 2 "deadlock detected" query-log.sql | head -40

Expected output: deadlock error lines interleaved with the repeated queries. If every burst of duplicates follows a deadlock error, the repeats are retries, and the N+1 label is wrong.

Step 2: Check the retry wrapper for attempt markers

grep -rn "retry" app/db/transaction.py | head -10
grep -o "attempt=[0-9]*" app.log | sort | uniq -c | head -10

Expected output: the retry policy (max attempts, backoff) and log lines showing attempt counters rising across the duplicate queries. Identical query text with rising attempt numbers is one logical operation, not 300 operations.

Step 3: Verify there is no per-row loop in the code

grep -rn "for .* in " app/handlers/[suspect handler].py | head -10

Expected output: no loop issuing the flagged query per row. A true N+1 has a loop. Retries have a retry wrapper. The code shape settles it when the logs are ambiguous.

Step 4: Teach the detector to collapse retries

duplicate_detector:
  group_by: [query_shape, transaction_id, retry_window]
  collapse_retries: true

Expected output: queries with identical text inside one transaction's retry window now count as a single logical operation. The 300 "duplicates" become 1 retried operation, and the false alert stops.

Step 5: Alert on retry rate, not duplicate count

Add a monitoring rule: alert when the deadlock-retry rate exceeds 5 percent of transactions.

Expected output: the monitoring now tracks what actually matters. A high retry rate means the deadlock itself needs fixing (lock ordering, smaller transactions). A low retry rate with collapsed duplicates means everything is fine.

Step 6: Fix the deadlock if the retry rate is the real problem

grep -o "DETAIL:.*" postgres.log | sort | uniq -c | sort -rn | head -5

Expected output: the most frequent deadlock details, showing which two queries contend. Fix lock ordering in the app (consistent lock acquisition order, shorter transactions) and the retries, and the duplicate-looking queries, go away at the source.

Variant phrasings

agent flagged an N+1 that wasn't real, the duplicate queries had different WHERE clauses

Same detector naivety, different axis. The fix is normalizing query shapes before counting, not just retry collapsing.

agent counted query-builder log lines, not round trips

The detector measured logging, not database work. Count round trips at the connection level.

profiler flagged duplicates that were actually GraphQL dataloader batching

Same family: the detector ignored the batching layer. Understand the data-access layer before counting.

Why it happens

Naive duplicate detection counts query text and ignores control flow. Retries are a recovery mechanism that legitimately replays queries, so a deadlock followed by a retry looks exactly like an N+1 loop to a counter that cannot see the error between the attempts. The agent flagged the symptom of the recovery (repeated queries) instead of the cause of the recovery (the deadlock), and prescribed the wrong fix for both.

Edge cases

• A retry policy with no backoff and unlimited attempts can turn one deadlock into thousands of queries. That is a real retry-storm problem. Collapse them in the detector, then fix the policy.
• If the deadlock errors are not in the query log the detector reads, the agent cannot see the retry markers. Join the error log into the analysis before concluding.
• Serializable isolation produces serialization failures that retry the same way deadlocks do. The collapse rule must cover both, not just deadlock errors.
• Do not "fix" this by disabling retries. The retries are correct. The detector was wrong.
• When retries span multiple services (saga-style), the transaction_id grouping in step 4 needs a distributed trace ID instead. Same principle, wider scope.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_EX-iOR0I6W7-0r9tYsiZxA