ActiveRecord::QueryCanceled: on SBOM ingestion due to deadlock
https://sentry.gitlab.net/gitlab/gitlabcom/issues/4091997/?referrer=gitlab_plugin
PG::QueryCanceled: ERROR: canceling statement due to statement timeout
CONTEXT: while inserting index tuple (1716,4) in relation "sbom_components"
lib/gitlab/database/load_balancing/connection_proxy.rb:121:in `public_send'
connection.public_send(...)
lib/gitlab/database/load_balancing/connection_proxy.rb:121:in `block in write_using_load_balancer'
connection.public_send(...)
lib/gitlab/database/load_balancing/load_balancer.rb:127:in `block in read_write'
yield connection
lib/gitlab/database/load_balancing/load_balancer.rb:205:in `retry_with_backoff'
return yield attempt # Yield the current attempt count
lib/gitlab/database/load_balancing/load_balancer.rb:116:in `read_write'
retry_with_backoff(attempts: attempts) do |attempt|
...
(193 additional frame(s) were not displayed)
ActiveRecord::QueryCanceled: PG::QueryCanceled: ERROR: canceling statement due to statement timeout
CONTEXT: while inserting index tuple (1716,4) in relation "sbom_components"
PG::QueryCanceled: ERROR: canceling statement due to statement timeout
CONTEXT: while inserting index tuple (1716,4) in relation "sbom_components"
These queries are timing out due to a query deadlock. See: #404630 (comment 1341149626)
We should order the records prior to insertion in order to avoid having circular locking.
Implementation plan
Order the records by their unique attributes in each of the ingestion classes:
- ee/app/services/sbom/ingestion/tasks/ingest_components.rb
- ee/app/services/sbom/ingestion/tasks/ingest_component_versions.rb
- ee/app/services/sbom/ingestion/tasks/ingest_sources.rb
- ee/app/services/sbom/ingestion/tasks/ingest_occurrences.rb
Edited by Brian Williams