Support "nested" transactions in interactive transactions

Problem

Handle nested transaction blocks in an interactive transaction (or in transactions in general). This allows for writing transactions in a more composable manner without having to think about it all the time, but at the risk of allowing too much nesting.

Suggested solution

I'm not sure what the best solution would be, but here are some examples of how this is implemented in other libraries as well as tools in other languages.

1. Ecto. Another article about Ecto (Ecto, Elixir's ORM collapses transactions into a single transaction, I think they use SAVEPOINTS).
2. Ecto's Multi is a supercharged version of Prisma's interactive transactions API, allowing you to cleanly write database interactions almost like a workflow, and these can be nested too. If you're familiar with monads from functional programming, Multi's are like a database transaction monad.
3. pg-promise transactions
4. Sequelize transactions / https://sequelize.org/docs/v6/other-topics/transactions/

Alternatives

The alternative is to not nest transactions, but I'm not sure if that's the best alternative.

Additional context

Initially suggested here

The issue concerns Prisma's interactive transactions API and requests support for nested transaction blocks. Start by reviewing the current interactive transaction behavior and the linked Ecto, pg-promise, and Sequelize references. Done requires an agreed nesting model and implementation that supports nested transactions with defined behavior and coverage for the relevant cases.