destroy_security_findings in EE::Ci::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.

Currently this is happening when deleting job artifacts in batch at https://gitlab.com/gitlab-org/gitlab/-/blob/82398ffa5f317227a001c0c92a4b8fe8b8496713/ee/app/services/ee/ci/job_artifacts/destroy_batch_service.rb#L13.

How to test this

This failure has been added to either spec/support/database/cross-database-modification-allowlist.yml or an explicit allow_cross_database_modification_within_transaction has been wrapped around the offending code. Removing the corresponding allowlist item and running any RSpec test that executes this code path should reveal the problem.

• Comment out the allow_cross_database_modification_within_transaction in ee/app/services/ee/ci/job_artifacts/destroy_batch_service.rb
• Run bin/rspec spec/models/ci/job_artifact_spec.rb

How to fix this

This should be possible to fix this just the same as !74583 (merged) by moving destroy_security_findings into after_batch_destroy_hook.

TBC

See possible solutions for this at https://docs.gitlab.com/ee/development/database/multiple_databases.html#removing-cross-database-transactions .

You may also be able to search for fixes to similar problems or you can ask in #g_sharding if something is unclear.

cc @tkuah as you fixed a very similar issue in !74583 (merged) and we can probably just do the same thing for this.