Cross DB modifications in Ci::Build within state transitions

What is a cross-database modification

Cross-database modification happens when data is modified in two different databases within an open transaction. This can cause reliability issues later when the CI database tables are moved to a separate database server.

Example: keeping transaction open to wait for a 3rd party call (UPDATE query in a different DB) which cannot be rolled back anyway.

Problem

The Ci::Build model implements a state machine where various state transitions alter the associated deployment record.

Offenses

The Offenses have been ignored with the .allow_cross_database_modification_within_transaction method. You can find them by grepping for the method name and the issue number:

grep -n "allow_cross_database_modification_within_transaction.*338867" app/models/ci/build.rb

See an example here: https://gitlab.com/gitlab-org/gitlab/-/blob/master/app/models/ci/build.rb#L333

You can remove the allow_cross_database_modification_within_transaction block and run the test cases to see the actual failure messages. (rspec spec/models/ci/build_spec.rb)

Once the offenses have been fixed the allow_cross_database_modification_within_transaction method calls can be removed and this issue can be closed.

Proposal

The deployments table will stay in the Main database so the update statements need to be moved out of the state machine's transaction.

Options:

• Use the build.run_after_commit block.
• Use an async job to make the record updates.

Both options are less "safe". We're not going to have the same consistency guarantee as to the version with the transaction so we need to find the best way forward. Let's discuss ideas and the trade-offs in the comment section.