[meta] Drop implicit autocommit on Transaction destruction

Problem/Motivation

As described in numerous issues (see related), it is desirable to drop the auto-commit of database transactions during the destruction of Drupal's Transaction objects.

This meta issue is to track the overall plan to achieve that.

Proposed resolution

1. Start allowing explicit COMMIT/RELEASE SAVEPOINT from the Transaction objects: ✨ Allow explicit COMMIT in Transaction objects Active
2. Deprecate auto-commit on Transaction destruction, and convert core to always explicitly commit/release: 📌 [PP-1] Convert all transactions to explicit COMMIT Postponed
3. Ensure the event dispatcher is available before the container is ready: 📌 Explore injecting the event dispatcher in the database service Active
4. Move post transaction callback execution to an event-based process and deprecate execution on Transaction destruction: issue tbd
5. Clean up (Drupal 12 or 13)

Remaining tasks

User interface changes

Introduced terminology

API changes

Data model changes

Release notes snippet