From everything I've read, here and elsewhere, the reason seems to be that changing multiple aggregates in one transaction creates the requirement that they are stored on the same database host. (Let's not even consider two-phase commit.)
This introduces a trade-off, one that makes this a consideration rather than a rule.
Is your bounded context in question ever going to be divided among multiple database hosts? Not multiple databases - that is no problem. Database hosts.
Unless you process incredible volumes or you are bad at choosing table indexes, most contexts are only ever going to use a single database host. This lets you have database transactions around multiple aggregate changes (within one bounded context). This makes consistency guarantees so much easier.
All things considered, I prefer the decision making process like this:
- Is it enough for the aggregates to be eventually consistent? If so, use eventual consistency.
- Can we reasonably expect the aggregates to always live on a single database host? If so, allow them to share the same database transaction. (Note that if the aggregates are in different bounded contexts, the answer here is probably 'no'.)
- We need multiple hosts and guaranteed consistency. Only now do we have to jump through hoops, because our requirements are heavy. Solve the problem through design.
To give an example of #3:
- The Balance context tracks the balance of each tenant.
- A tenant's balance must not be negative.
- The Payment context wants to spend some of a tenant's balance. The deduction must be immediately consistent (to guarantee the previous rule). Should the payment fail, then the balance must eventually go back up.
- The Balance context exposes in its API a method that returns a new Reservation, reducing the balance by a requested amount, or returning a failure if that amount is not available.
- The Balance context consumes events from the Payment context.
- Certain events increase a tenant's balance.
- Other events pertain to a decrease in the tenant's balance, and are always linked to a prior Reservation. They confirm that Reservation.
- Reservations are valid for a short time, e.g. 5 minutes. Reservations not confirmed within that time are reversed, increasing the balance to compensate.
Note that this example requires the guarantee that every event is handled exactly once. Particularly, no event must ever be skipped! That is doable, but it is challenging to get right. Most tools are not airtight in this regard. The most easily identified point of failure is if the application server crashes after updating its database, but before publishing its event(s). Guaranteeing exactly-once delivery of events is a worthwhile discussion in its own right.