Validation errors in Security::SyncProjectPolicyWorker causing MRAP to be not linked to project

Summary

The Security::SyncProjectPolicyWorker Sidekiq job experiences race conditions on GitLab Dedicated tenants when security policies are updated, causing validation errors and SLO violations on the catchall shard. This issue was previously reported and closed in #538759 (closed), but continues to affect Dedicated tenants.

Affected Platform: GitLab Dedicated (specifically observed on tenant defiant_coffee_whitefish)

Error Pattern: ActiveRecord::RecordInvalid: Validation failed: Rule idx has already been taken

Impact

Availability & Performance

SLO Violation: SidekiqServiceSidekiqExecutionErrorSLOViolationSingleShard triggered
Error Rate: Sidekiq execution error rate momentarily reached ~50% on catchall shard (threshold: typically <1%)
Alert Flapping: 5 PagerDuty alerts fired and resolved over 8-hour period
Duration: November 7, 2025, 15:25 UTC - 23:11 UTC (~8 hours)

Operational Cost

On-call Time: ~2-3 hours of SRE investigation time
Alert Fatigue: 5 page events during incident response period
False Signal: Self-resolved without intervention after flapping, but required investigation to rule out customer impact

Customer Impact

Severity: Limited - catchall shard jobs are not time-critical
User Experience: No direct customer-facing impact observed
Data Integrity: Policy synchronization eventually succeeded despite repeated failures

INC-5557: defiant_coffee_whitefish - SidekiqServiceSidekiqExecutionErrorSLOViolationSingleShard
GitLab Incident Issue #2161

Visual Evidence

Error Rate Spike on Catchall Shard:

Graph A:

Graph B:

Log Evidence:

Graph C:

Backtrace

Log D:

activerecord (7.1.5.2) lib/active_record/validations.rb:84:in `raise_validation_error',
activerecord (7.1.5.2) lib/active_record/validations.rb:55:in `save!',
activerecord (7.1.5.2) lib/active_record/transactions.rb:313:in `block in save!',
activerecord (7.1.5.2) lib/active_record/transactions.rb:365:in `block in with_transaction_returning_status',
activerecord (7.1.5.2) lib/active_record/connection_adapters/abstract/database_statements.rb:342:in `transaction',
lib/gitlab/database/load_balancing/connection_proxy.rb:127:in `public_send',
lib/gitlab/database/load_balancing/connection_proxy.rb:127:in `block in write_using_load_balancer',
lib/gitlab/database/load_balancing/load_balancer.rb:145:in `block in read_write',
lib/gitlab/database/load_balancing/load_balancer.rb:232:in `retry_with_backoff',
lib/gitlab/database/load_balancing/load_balancer.rb:135:in `read_write',
lib/gitlab/database/load_balancing/connection_proxy.rb:126:in `write_using_load_balancer',
lib/gitlab/database/load_balancing/connection_proxy.rb:78:in `transaction',
activerecord (7.1.5.2) lib/active_record/transactions.rb:361:in `with_transaction_returning_status',
activerecord (7.1.5.2) lib/active_record/transactions.rb:313:in `save!',
activerecord (7.1.5.2) lib/active_record/suppressor.rb:56:in `save!',
activerecord (7.1.5.2) lib/active_record/associations/collection_association.rb:371:in `insert_record',
activerecord (7.1.5.2) lib/active_record/associations/has_many_association.rb:63:in `insert_record',
activerecord (7.1.5.2) lib/active_record/associations/collection_association.rb:358:in `block (2 levels) in _create_record',
activerecord (7.1.5.2) lib/active_record/associations/collection_association.rb:462:in `replace_on_target',
activerecord (7.1.5.2) lib/active_record/associations/collection_association.rb:278:in `add_to_target',
activerecord (7.1.5.2) lib/active_record/associations/collection_association.rb:357:in `block in _create_record',
app/models/concerns/cross_database_modification.rb:91:in `block in transaction',
activerecord (7.1.5.2) lib/active_record/connection_adapters/abstract/transaction.rb:535:in `block in within_new_transaction',
activesupport (7.1.5.2) lib/active_support/concurrency/null_lock.rb:9:in `synchronize',
activerecord (7.1.5.2) lib/active_record/connection_adapters/abstract/transaction.rb:532:in `within_new_transaction',
activerecord (7.1.5.2) lib/active_record/connection_adapters/abstract/database_statements.rb:344:in `transaction',
lib/gitlab/database/load_balancing/connection_proxy.rb:127:in `public_send',
lib/gitlab/database/load_balancing/connection_proxy.rb:127:in `block in write_using_load_balancer',
lib/gitlab/database/load_balancing/load_balancer.rb:145:in `block in read_write',
lib/gitlab/database/load_balancing/load_balancer.rb:232:in `retry_with_backoff',
lib/gitlab/database/load_balancing/load_balancer.rb:135:in `read_write',
lib/gitlab/database/load_balancing/connection_proxy.rb:126:in `write_using_load_balancer',
lib/gitlab/database/load_balancing/connection_proxy.rb:78:in `transaction',
activerecord (7.1.5.2) lib/active_record/transactions.rb:212:in `transaction',
lib/gitlab/database.rb:415:in `transaction',
app/models/concerns/cross_database_modification.rb:82:in `transaction',
activerecord (7.1.5.2) lib/active_record/associations/collection_association.rb:314:in `transaction',
activerecord (7.1.5.2) lib/active_record/associations/collection_association.rb:355:in `_create_record',
activerecord (7.1.5.2) lib/active_record/associations/has_many_association.rb:147:in `_create_record',
activerecord (7.1.5.2) lib/active_record/associations/association.rb:211:in `create!',
activerecord (7.1.5.2) lib/active_record/associations/collection_proxy.rb:366:in `create!',
ee/app/services/security/scan_result_policies/approval_rules/create_service.rb:62:in `create_scan_result_policy',
ee/app/services/security/scan_result_policies/approval_rules/create_service.rb:41:in `create_rule',
ee/app/services/security/scan_result_policies/approval_rules/create_service.rb:17:in `block (2 levels) in execute',
ee/app/services/security/scan_result_policies/approval_rules/create_service.rb:16:in `each',
ee/app/services/security/scan_result_policies/approval_rules/create_service.rb:16:in `each_with_index',
ee/app/services/security/scan_result_policies/approval_rules/create_service.rb:16:in `block in execute',
activerecord (7.1.5.2) lib/active_record/relation/delegation.rb:100:in `each',
activerecord (7.1.5.2) lib/active_record/relation/delegation.rb:100:in `each',
ee/app/services/security/scan_result_policies/approval_rules/create_service.rb:10:in `execute',
ee/app/services/security/security_orchestration_policies/sync_project_approval_policy_rules_service.rb:104:in `create_approval_rules',
ee/app/services/security/security_orchestration_policies/sync_project_approval_policy_rules_service.rb:14:in `create_rules',
ee/app/services/security/security_orchestration_policies/base_project_policy_service.rb:38:in `link_policy',
ee/app/services/security/security_orchestration_policies/sync_project_service.rb:19:in `execute',
ee/app/workers/security/sync_project_policy_worker.rb:87:in `handle_policy_changes',
ee/app/workers/security/sync_project_policy_worker.rb:58:in `handle_change',
ee/app/workers/security/sync_project_policy_worker.rb:44:in `perform',
ee/app/workers/concerns/geo/skip_secondary.rb:14:in `perform',
vendor/gems/sidekiq/lib/sidekiq/processor.rb:220:in `execute_job',
vendor/gems/sidekiq/lib/sidekiq/processor.rb:185:in `block (4 levels) in process',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:180:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
ee/lib/gitlab/sidekiq_middleware/set_session/server.rb:21:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
lib/gitlab/sidekiq_middleware/identity/restore.rb:12:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
lib/gitlab/sidekiq_middleware/resource_usage_limit/middleware.rb:16:in `perform',
lib/gitlab/sidekiq_middleware/resource_usage_limit/server.rb:8:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
lib/gitlab/database/load_balancing/sidekiq_server_middleware.rb:35:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
lib/gitlab/sidekiq_middleware/skip_jobs.rb:51:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
lib/gitlab/sidekiq_middleware/concurrency_limit/middleware.rb:37:in `perform',
lib/gitlab/sidekiq_middleware/concurrency_limit/server.rb:11:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
lib/gitlab/sidekiq_middleware/throttling/middleware.rb:18:in `perform',
lib/gitlab/sidekiq_middleware/throttling/server.rb:8:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
lib/gitlab/sidekiq_middleware/pause_control/strategies/base.rb:31:in `perform',
lib/gitlab/sidekiq_middleware/pause_control/strategy_handler.rb:22:in `perform',
lib/gitlab/sidekiq_middleware/pause_control/server.rb:8:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
lib/gitlab/sidekiq_middleware/duplicate_jobs/strategies/until_executed.rb:17:in `perform',
lib/gitlab/sidekiq_middleware/duplicate_jobs/duplicate_job.rb:44:in `perform',
lib/gitlab/sidekiq_middleware/duplicate_jobs/server.rb:8:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
lib/click_house/migration_support/sidekiq_middleware.rb:7:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
lib/gitlab/sidekiq_middleware/worker_context.rb:9:in `wrap_in_optional_context',
lib/gitlab/sidekiq_middleware/worker_context/server.rb:19:in `block in call',
lib/gitlab/application_context.rb:176:in `block in use',
gitlab-labkit (0.40.0) lib/labkit/context.rb:36:in `with_context',
lib/gitlab/application_context.rb:176:in `use',
lib/gitlab/application_context.rb:98:in `with_context',
lib/gitlab/sidekiq_middleware/worker_context/server.rb:17:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
lib/gitlab/sidekiq_status/server_middleware.rb:7:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
lib/gitlab/sidekiq_versioning/middleware.rb:9:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
lib/gitlab/sidekiq_middleware/query_analyzer.rb:7:in `block in call',
lib/gitlab/database/query_analyzer.rb:83:in `within',
lib/gitlab/sidekiq_middleware/query_analyzer.rb:7:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
lib/gitlab/sidekiq_middleware/admin_mode/server.rb:14:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
lib/gitlab/sidekiq_middleware/set_ip_address.rb:7:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
lib/gitlab/sidekiq_middleware/instrumentation_logger.rb:9:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
lib/gitlab/sidekiq_middleware/batch_loader.rb:7:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
lib/gitlab/sidekiq_middleware/extra_done_log_metadata.rb:7:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
lib/gitlab/sidekiq_middleware/server_metrics.rb:111:in `block in call',
lib/gitlab/sidekiq_middleware/server_metrics.rb:139:in `block in instrument',
lib/gitlab/metrics/background_transaction.rb:33:in `run',
lib/gitlab/sidekiq_middleware/server_metrics.rb:139:in `instrument',
lib/gitlab/sidekiq_middleware/server_metrics.rb:110:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
lib/gitlab/sidekiq_middleware/request_store_middleware.rb:8:in `block in call',
gems/gitlab-safe_request_store/lib/gitlab/safe_request_store.rb:66:in `enabling_request_store',
gems/gitlab-safe_request_store/lib/gitlab/safe_request_store.rb:59:in `ensure_request_store',
lib/gitlab/sidekiq_middleware/request_store_middleware.rb:7:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
gitlab-labkit (0.40.0) lib/labkit/middleware/sidekiq/server.rb:21:in `block in call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:180:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
gitlab-labkit (0.40.0) lib/labkit/middleware/sidekiq/context/server.rb:16:in `block in call',
gitlab-labkit (0.40.0
) lib/labkit/context.rb:36:in `with_context',
gitlab-labkit (0.40.0) lib/labkit/middleware/sidekiq/context/server.rb:15:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:173:in `invoke',
gitlab-labkit (0.40.0) lib/labkit/middleware/sidekiq/server.rb:20:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
lib/gitlab/sidekiq_middleware/monitor.rb:10:in `block in call',
lib/gitlab/sidekiq_daemon/monitor.rb:46:in `within_job',
lib/gitlab/sidekiq_middleware/monitor.rb:9:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
lib/gitlab/sidekiq_middleware/shard_awareness_validator.rb:10:in `block in call',
lib/gitlab/sidekiq_sharding/validator.rb:42:in `enabled',
lib/gitlab/sidekiq_middleware/shard_awareness_validator.rb:9:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
lib/gitlab/sidekiq_middleware/size_limiter/server.rb:13:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
marginalia (1.11.1) lib/marginalia/sidekiq_instrumentation.rb:9:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
sentry-sidekiq (5.23.0) lib/sentry/sidekiq/sentry_context_middleware.rb:54:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
vendor/gems/sidekiq/lib/sidekiq/job/interrupt_handler.rb:9:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:183:in `block in traverse',
vendor/gems/sidekiq/lib/sidekiq/metrics/tracking.rb:26:in `track',
vendor/gems/sidekiq/lib/sidekiq/metrics/tracking.rb:134:in `call',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:182:in `traverse',
vendor/gems/sidekiq/lib/sidekiq/middleware/chain.rb:173:in `invoke',
vendor/gems/sidekiq/lib/sidekiq/processor.rb:184:in `block (3 levels) in process',
vendor/gems/sidekiq/lib/sidekiq/processor.rb:145:in `block (6 levels) in dispatch',
vendor/gems/sidekiq/lib/sidekiq/job_retry.rb:118:in `local',
vendor/gems/sidekiq/lib/sidekiq/processor.rb:144:in `block (5 levels) in dispatch',
vendor/gems/sidekiq/lib/sidekiq/rails.rb:27:in `block in call',
activesupport (7.1.5.2) lib/active_support/reloader.rb:77:in `block in wrap',
activesupport (7.1.5.2) lib/active_support/execution_wrapper.rb:92:in `wrap',
activesupport (7.1.5.2) lib/active_support/reloader.rb:74:in `wrap',
vendor/gems/sidekiq/lib/sidekiq/rails.rb:26:in `call',
vendor/gems/sidekiq/lib/sidekiq/processor.rb:139:in `block (4 levels) in dispatch',
vendor/gems/sidekiq/lib/sidekiq/processor.rb:281:in `stats',
vendor/gems/sidekiq/lib/sidekiq/processor.rb:134:in `block (3 levels) in dispatch',
lib/gitlab/sidekiq_logging/structured_logger.rb:21:in `call',
vendor/gems/sidekiq/lib/sidekiq/processor.rb:133:in `block (2 levels) in dispatch',
vendor/gems/sidekiq/lib/sidekiq/job_retry.rb:85:in `global',
vendor/gems/sidekiq/lib/sidekiq/processor.rb:132:in `block in dispatch',
vendor/gems/sidekiq/lib/sidekiq/job_logger.rb:40:in `prepare',
vendor/gems/sidekiq/lib/sidekiq/processor.rb:131:in `dispatch',
vendor/gems/sidekiq/lib/sidekiq/processor.rb:183:in `block (2 levels) in process',
vendor/gems/sidekiq/lib/sidekiq/processor.rb:182:in `handle_interrupt',
vendor/gems/sidekiq/lib/sidekiq/processor.rb:182:in `block in process',
vendor/gems/sidekiq/lib/sidekiq/processor.rb:181:in `handle_interrupt',
vendor/gems/sidekiq/lib/sidekiq/processor.rb:181:in `process',
vendor/gems/sidekiq/lib/sidekiq/processor.rb:86:in `process_one',
vendor/gems/sidekiq/lib/sidekiq/processor.rb:76:in `run',
vendor/gems/sidekiq/lib/sidekiq/component.rb:10:in `watchdog',
vendor/gems/sidekiq/lib/sidekiq/component.rb:19:in `block in safe_thread'

Recommendation

Immediate Actions

Investigate further and collect more evidence (if needed)
Reopen or Create Follow-up to #538759 (closed): The original issue was closed in May 2025, but the race condition persists on GitLab Dedicated environments. A new issue or reopening #538759 (closed) with Dedicated-specific context is recommended.
Add Idempotency Check: Implement a check in Security::SyncProjectPolicyWorker to handle duplicate rule_idx values gracefully:
- Check if rule with same idx already exists before insertion
- If exists and content matches, skip insertion
- If exists with different content, update existing rule
- Log when collision is detected for monitoring
Add Distributed Lock: Implement a distributed lock (using Redis or similar) around the policy sync operation to prevent concurrent executions for the same project/policy combination.

Verification

Check if Issue Still Occurs

OpenSearch Query (run in tenant's OpenSearch):

json.class: "Security::SyncProjectPolicyWorker" 
AND json.exception_class: "ActiveRecord::RecordInvalid" 
AND json.exception_message: *"Rule idx has already been taken"*

Time Range: Last 7 days

Interpretation:

0 results: Race condition not occurring or is fixed
<10 results: Occasional occurrence, likely low priority
>10 results: Frequent occurrence, should be addressed
Results with same correlation_id: Multiple projects affected, higher priority

Additional Context

Trigger Pattern

The race condition appears to be triggered when:

A user merges a security policy update in a policy project
Security::SyncProjectPolicyWorker is enqueued for multiple related projects
Multiple worker instances attempt to create/update the same policy rules concurrently
Database unique constraint on rule_idx causes validation failure

Original Issue Context

Original Report: GitLab #538759 - "Race condition with SyncProjectPolicyWorker"
Status: Closed in May 2025

Edited Nov 19, 2025 by 🤖 GitLab Bot 🤖