Discover and forbid usage of 2PC pattern

Problem

2PC commit is a pattern used when a many transactions are done across distinct data sets. In case of decompisition is the transaction that is done across two databases, where we cannot confidently rollback the other transaction.

Lets consider the following example for a given structure that gonna result in having to resolve 2PC commit:

class Project < ::ApplicationRecord # uses Main DB
end

class Ci::Variable < Ci::ApplicationRecord # uses CI DB
end

Example 1 - user-controlled transactions

Project.transaction do
  project.save!

  project.variables.create!(key: 'key', value: 'value')
end

Rails when doing .save! and .create! will re-use transactions, since there's already new open:

• When a single DB is used, if the .create! fails the changes done by .save! will be rolled back.
• In case of many DBs, we can likely manually rollback changes made by .save!.

Example 2 - explicit transactions

Project.transaction do
  ...

  Ci::Variable.transaction do
    ...
  end

  project.save!
end

Here, we open two nested transactions:

• When a single DB is used the changes made in Ci::...transaction would be rolled back if .save! fails.
• If many DBs are used we are unable to rollback changes made by CI transaction if .save! fails

Example 3 - implicit transactions

project.variables.build(key: 'secret', value: 'value')
project.shared_runners_enabled = false
project.save!

Here, the one/two implicit transactions are opened:

• we gonna open a transaction around project
• we gonna open a transaction to save variables (if many DBs, it will be nested transaction resembling 2PC)
• we will be unable to rollback changes to variables, if UPDATE projects fails as it was another transaction open across different DB

Example X

Bring your own example here :)

Proposal

We:

• forbid any usage that will result in opening many transactions across different databases
• this behavior is enforcing when running in development / test (we block and raise exception)
• this behavior is permissive when running in production (we don't want to generate 500's initially)
• we require all code to be changed to follow a serialized model where changes are done sequentially to at most one database at time, and are manually revertible - this will effective forbid usage of any of the mentioned examples

Implementation

We likely can implement that by tracking transaction counter, and forbidding opening transaction if there's another one open. Likely this can be done by tracking in a current context what transaction is currently open.