Reapply "Merge branch 'smh-reapply-tx-jobs' into 'master'"

What does this MR do and why?

Reapply "Merge branch 'smh-reapply-tx-jobs' into 'master'"

This reverts commit b31f6470.

The CI jobs were reverted again due to an unrelated master broken incident. After the revert, discussion sparked again on whether or not we can run the jobs on each MR. Introduce the jobs again but run the non-transactional variants in the maintenance pipeline.

This implements #463236