Observed (a production field instance, first night on the v38 dedup write path)
Nine 40P01: deadlock detected errors since the payload-dedup migration activated — zero before it. All self-healed on the collector's next retry: no data loss, no sustained failure, no operator action. Real bug, not an active incident.
The box's own PostgreSQL server log (pg.log) captured the full lock graphs for two occurrences (09:29:09 and 09:44:03 UTC, identical pattern): two processes, each waits for ShareLock on transaction; blocked by the other, both executing the SAME statement shape — PayloadDimensions.UpsertSql against collect.query_text_dim — with the mutual tuple waits on two different specific rows ((108,6) and (293,2)).
Mechanism (verified in source, not just from the logs)
The dim upsert is a batch statement: INSERT ... SELECT FROM unnest($1::bytea[], $2::text[]) ... ON CONFLICT (digest) DO UPDATE .... The arrays it binds come from PayloadDimensionBatch.ToArrays, which drains a Dictionary<string, Entry> via entries.Values — enumeration order, which is per-session arrival order, NOT a consistent total order across sessions.
Two collector sessions (fleet concurrency runs several collection cycles at once) each build their own batch. When both batches contain the same two digests in opposite relative order, each session row-locks its first digest and blocks on its second — a classic unordered-multi-row-upsert lock cycle. The within-batch digest dedup (required to avoid SQLSTATE 21000) is orthogonal: it makes each batch conflict-free with ITSELF, not ordered relative to its SIBLINGS.
Scope
collect.query_text_dim — confirmed by lock graphs.collect.query_plan_dim — same shared statement (PayloadDimensions.UpsertSql), same unsorted arrays, identical exposure; fix both via the one central point rather than waiting for its label to show up in a log.collect.module_map — CHECKED AND SAFE, do not "fix" it: its batch upsert (DarlingModuleMap.RefreshSql) carries ORDER BY server_name, sql_handle, collection_time DESC (required by its DISTINCT ON), which is a deterministic ascending order on the conflict key — concurrent runs acquire locks in the same relative order and the cycle cannot form.- Every other
ON CONFLICT site in the store is single-row (config/state/watermark upserts) — no exposure.
Fix
Impose one deterministic total order on the digests every session uses, at the ONE central point all callers share (the statement or its array producer). Any consistent total order works — byte-wise lexicographic on the digest is the natural one. If all concurrent sessions attempt conflict-key locks in the same relative order, the cycle cannot form. This is the standard fix for this exact shape; it is NOT an indexing problem (the digest PK is required by the dedup semantics), and the hour-throttled conditional DO UPDATE is unrelated and stays.
Pin the property so it cannot regress silently: the ordering is the load-bearing safety mechanism, and it is invisible to every behavioural test (an unordered batch upserts identical DATA — only the lock-acquisition order differs).
Severity
Self-healing under the existing retry, ~9/night at a ~24-server fleet's concurrency. Worth fixing promptly because the collision rate scales with fleet size and digest-overlap (shared plans across servers), and every deadlock is a wasted collection write plus log noise on exactly the surface operators are told to watch.
Observed (a production field instance, first night on the v38 dedup write path)
Nine
40P01: deadlock detectederrors since the payload-dedup migration activated — zero before it. All self-healed on the collector's next retry: no data loss, no sustained failure, no operator action. Real bug, not an active incident.The box's own PostgreSQL server log (
pg.log) captured the full lock graphs for two occurrences (09:29:09 and 09:44:03 UTC, identical pattern): two processes, eachwaits for ShareLock on transaction; blocked bythe other, both executing the SAME statement shape —PayloadDimensions.UpsertSqlagainstcollect.query_text_dim— with the mutual tuple waits on two different specific rows ((108,6)and(293,2)).Mechanism (verified in source, not just from the logs)
The dim upsert is a batch statement:
INSERT ... SELECT FROM unnest($1::bytea[], $2::text[]) ... ON CONFLICT (digest) DO UPDATE .... The arrays it binds come fromPayloadDimensionBatch.ToArrays, which drains aDictionary<string, Entry>viaentries.Values— enumeration order, which is per-session arrival order, NOT a consistent total order across sessions.Two collector sessions (fleet concurrency runs several collection cycles at once) each build their own batch. When both batches contain the same two digests in opposite relative order, each session row-locks its first digest and blocks on its second — a classic unordered-multi-row-upsert lock cycle. The within-batch digest dedup (required to avoid SQLSTATE 21000) is orthogonal: it makes each batch conflict-free with ITSELF, not ordered relative to its SIBLINGS.
Scope
collect.query_text_dim— confirmed by lock graphs.collect.query_plan_dim— same shared statement (PayloadDimensions.UpsertSql), same unsorted arrays, identical exposure; fix both via the one central point rather than waiting for its label to show up in a log.collect.module_map— CHECKED AND SAFE, do not "fix" it: its batch upsert (DarlingModuleMap.RefreshSql) carriesORDER BY server_name, sql_handle, collection_time DESC(required by itsDISTINCT ON), which is a deterministic ascending order on the conflict key — concurrent runs acquire locks in the same relative order and the cycle cannot form.ON CONFLICTsite in the store is single-row (config/state/watermark upserts) — no exposure.Fix
Impose one deterministic total order on the digests every session uses, at the ONE central point all callers share (the statement or its array producer). Any consistent total order works — byte-wise lexicographic on the digest is the natural one. If all concurrent sessions attempt conflict-key locks in the same relative order, the cycle cannot form. This is the standard fix for this exact shape; it is NOT an indexing problem (the digest PK is required by the dedup semantics), and the hour-throttled conditional
DO UPDATEis unrelated and stays.Pin the property so it cannot regress silently: the ordering is the load-bearing safety mechanism, and it is invisible to every behavioural test (an unordered batch upserts identical DATA — only the lock-acquisition order differs).
Severity
Self-healing under the existing retry, ~9/night at a ~24-server fleet's concurrency. Worth fixing promptly because the collision rate scales with fleet size and digest-overlap (shared plans across servers), and every deadlock is a wasted collection write plus log noise on exactly the surface operators are told to watch.