deleting security findings with JobArtifacts::DestroyBatchService causes CrossDatabaseModificationAcrossUnsupportedTablesError
Per https://docs.gitlab.com/ee/development/database/multiple_databases.html#removing-cross-database-transactions we do not allow you to write to 2 different databases within the context of a single transaction. When calling JobArtifacts::DestroyBatchService we're opening a transaction and deleting from ci_job_artifacts as well as security_findings which violates this rule:
$ bin/rspec ./ee/spec/services/ee/ci/job_artifacts/destroy_batch_service_spec.rb
Failures:
1) Ci::JobArtifacts::DestroyBatchService.execute destroys all expired artifacts
Failure/Error: raise CrossDatabaseModificationAcrossUnsupportedTablesError, message
Gitlab::Database::QueryAnalyzers::PreventCrossDatabaseModification::CrossDatabaseModificationAcrossUnsupportedTablesError:
Cross-database data modification of 'gitlab_ci, gitlab_main' were detected within a transaction modifying the 'ci_job_artifacts, security_findings' tables.Please refer to https://docs.gitlab.com
/ee/development/database/multiple_databases.html#removing-cross-database-transactions for details on how to resolve this exception. For now we have added this violation to the allowlist https://gitlab.com/gitlab-org/gitlab/-/blob/master/spec/support/database/cross-database-modification-allowlist.yml .
Possible solutions
The cleanup could be moved to a sidekiq worker or it could be done after commit.