Sort bulk update VALUES lists by primary key

Michał Zającrequested to merge
606404-sort-bulk-updatable-task-values-by-pk into master

What does this MR do and why?

Sorts every Gitlab::Ingestion::BulkUpdatableTask bulk UPDATE's VALUES list by the model's primary key, so concurrent ingestion transactions acquire row locks in a consistent order.

Concurrent ingestion transactions (advisory-driven Continuous Vulnerability Scanning and pipeline security-report ingestion) can update the same rows and deadlock when their bulk UPDATE ... FROM (VALUES ...) statements lock rows in different orders. This was fixed piecemeal per task (IngestFindings by uuid, IngestIdentifiers by fingerprint, the IngestVulnerabilities orchestrator by vulnerability_id in !244454 (merged)), but IngestVulnerabilityReads::Update was still unsorted, and any future task would repeat the mistake.

A literal "sort all finding_maps once at the start of the flow" (as asked in the follow-up issue) isn't feasible: vulnerability_id/finding_id are only assigned mid-flow, and different tasks lock different tables by different keys. Instead this centralizes the sort at the one choke point every bulk update goes through — BulkUpdatableTask#values — where the join (and therefore lock) key is always model.primary_key. This covers all 8 current includer tasks in both IngestReportSliceService and IngestCvsSliceService, plus any future ones.

Safe because after_update consumers key off RETURNING values, not input positions (unlike BulkInsertableTask, whose after_ingest maps by index — which is why insert-side tasks keep their own sorts).

Database Review

No schema changes. The only change to emitted SQL is the ordering of rows inside the (VALUES ...) list of the existing bulk UPDATE statements — same rows, same plan shape.

References

How to set up and validate locally

bundle exec rspec ee/spec/lib/gitlab/ingestion/bulk_updatable_task_spec.rb
bundle exec rspec ee/spec/services/security/ingestion/tasks/ingest_vulnerability_reads/update_spec.rb

Both new ordering specs fail when the sort_by in BulkUpdatableTask#values is removed.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Merge request reports