## What to do Shrink the slice transaction by moving the aggregate counter tasks out of it. `IncreaseCountersTask`, `IngestVulnerabilityStatistics` and `IngestVulnerabilityNamespaceStatistics` each update a singleton row — one per project, one per project, and one per namespace respectively — acquired mid-transaction and held until commit. `IngestVulnerabilityNamespaceStatistics` is the worst of the three: jobs for different projects in the same group serialize on a single row. These are aggregates rather than part of the finding write, so they can move to `after_commit`, to their own transaction, or run once per job instead of once per slice. ## Why | | | |---|---| | Problem area | Timeouts | | Evidence | All three hold singleton rows until commit; `IngestVulnerabilityNamespaceStatistics` writes one row per ancestor namespace, so the root row serialises the whole group tree | | Effort | Medium–large | | Priority in the RCA | 4 of 11 | ## How the statement timeout happens All 18 tasks in `Security::Ingestion::IngestReportSliceService::SEC_DB_TASKS` run inside a single `SecApplicationRecord.transaction`, opened by `IngestSliceBaseService#run_tasks_in_sec_db`. Two of those tasks set up the contention: - `IngestIdentifiers` is task 1. It upserts `vulnerability_identifiers`, taking exclusive row locks that are held until the transaction commits. - `IngestVulnerabilityNamespaceStatistics` calls `Vulnerabilities::NamespaceStatistics::UpdateService`, which expands `traversal_ids`. A project whose `traversal_ids` are `{1,2,3}` upserts rows for namespaces 1, 2 and 3. Every project anywhere in a group hierarchy therefore writes that hierarchy's root namespace row. That root row is the serialisation point, and it spans the whole group tree — not just sibling projects under one parent. The chain from there: 1. Job A, ingesting for project P1, runs its slice transaction and reaches the namespace statistics task. It takes an exclusive row lock on the root namespace row and holds it until commit. 1. Job B, ingesting for project P2 in the same group hierarchy, has already completed task 1, so it is holding exclusive row locks on its own `vulnerability_identifiers` rows — also until commit. 1. Job B reaches the namespace statistics task and blocks, waiting for the root namespace row that Job A holds. 1. While blocked, Job B's transaction stays open and keeps holding all of its P2 identifier row locks. 1. A second job for P2 — another pipeline for the same project, or continuous vulnerability scanning — issues `INSERT INTO vulnerability_identifiers ... ON CONFLICT (project_id, fingerprint) DO UPDATE` for the same rows. It waits behind Job B, and its `statement_timeout` (15 seconds in production) expires first. To be precise about what the namespace lock does and does not do: it never directly blocks another project's identifier insert. `vulnerability_identifiers` has a unique index on `(project_id, fingerprint)`, so two different projects never contend on the same identifier row. What the cross-project serialisation does is keep each sibling's transaction open for longer, which turns a same-project collision that would have been brief into one that exceeds the statement timeout. ### Local reproduction PostgreSQL 17.8, GDK `gitlabhq_development_sec` database, against the real `vulnerability_identifiers` and `vulnerability_namespace_statistics` tables. Neither table has foreign keys, so no fixtures were needed. Three concurrent `psql` sessions: - Session A: `BEGIN;` then `UPDATE vulnerability_namespace_statistics SET total = total WHERE namespace_id = 1;`, then held open. - Session B: `BEGIN;` then `UPDATE vulnerability_identifiers SET name = name WHERE project_id = 1 AND fingerprint = '\x7198...';` then `UPDATE vulnerability_namespace_statistics SET total = total WHERE namespace_id = 1;`, which blocked. - Session C: `INSERT INTO vulnerability_identifiers (...) VALUES (...) ON CONFLICT (project_id, fingerprint) DO UPDATE SET name = EXCLUDED.name, updated_at = now();` on the same key Session B holds. Session B acquired the namespace lock only after Session A's transaction ended, which confirms the serialisation. Session C was then run three times with different timeout settings: | Timeout setting on session C | Error | | --- | --- | | `statement_timeout` only, at 3s — same settings shape as production | `ERROR: 57014: canceling statement due to statement timeout`, with `CONTEXT: while inserting index tuple (0,1) in relation "vulnerability_identifiers"` | | `lock_timeout = 2s` also set | `ERROR: 55P03: canceling statement due to lock timeout` | | `transaction_timeout = 3s` instead, a PostgreSQL 17 setting | `FATAL: 25P04: terminating connection due to transaction timeout`, which also closes the connection | Production sets `statement_timeout` and leaves `lock_timeout` and `transaction_timeout` at 0, so the error we get is the first one: a statement timeout, SQLSTATE 57014. That matches the 364 SQLSTATE 57014 cancellations on `INSERT INTO vulnerability_identifiers` recorded in the linked root cause analysis. It is not a transaction timeout. ### Worth checking in the logs The reproduction's error carries `CONTEXT: while inserting index tuple ... in relation "vulnerability_identifiers"`. That context line distinguishes a statement cancelled while waiting on a row lock from a statement that was merely slow. If the production 57014 errors carry the same context line, that is direct evidence the cancellations are lock contention rather than slow inserts. ### The interim reorder !256593 reordered the tasks so the three aggregate tasks run last: `IncreaseCountersTask` moved from position 4 to 14, and the two statistics tasks from 12 and 13 to 15 and 16. That shortens how long the root namespace lock is held, because it is now taken just before commit. The trade-off is that a job which blocks on the namespace row now does so while holding every identifier and finding lock from tasks 1 to 15, where previously it blocked earlier holding fewer. Hold time is what drains the waiting queue, so the net effect is still favourable, but it is a trade rather than a pure win. Moving the tasks out of the transaction entirely — what this issue proposes — is what removes the serialisation. ## References - Root cause analysis, with the measurements behind this: https://gitlab.com/gitlab-org/gitlab/-/work_items/629530 - Parent epic: https://gitlab.com/groups/gitlab-org/-/epics/23596