DEV: Update `DB.after_commit` to be compatible with 'real' transactions (PR #11294)

Previously it matched the behavior of standard ActiveRecord after_commit callbacks. They do not work well within joinable: false nested transactions. Now DB.after_commit callbacks will only be run when the outermost transaction has been committed.

Tests always run inside transactions, so this also introduces some logic to run callbacks once the test-wrapping transaction is reached.