What to do in case of nested transactions?

I have not so big experience in system design, and when faced with a situation, doubts were raised as to her right to allow.
There is a table Role - user roles.
ON divided into two levels - user and api code (the same code, but highlighted separately as the core of the system).
In the API there is a method AddRoleToUser - pass the method ID of user, ID, role -> method checks whether the role and the user -> in case of success assigns the role by writing in a couple of secondary tables, and then send e-mails. Queries inside AddRoleToUser wrapped in a transactionScope (scope1).
Custom code does the following:
1. Creates its own transactionScope (scope2) mode Requires Serializable;
2. Writes the data in the secondary tables Table1 and Table2;
3. Writes directly to the table Role, creating a role there.
4. Causes AddRoleToUser, passing it the ID of the newly created role and the ID of some user (obviously existing);
5. Out of scope2 without commit (that is, a rollback).

I have a misunderstanding of how to act inside AddRoleToUser reading data from the database. You may encounter the following scenarios:
1) scope1 mode requires the isolation level to any, the role is read without problems, the data is written, the letter is sent, but after the rollback rolled back in scope2 all records created in AddRoleToUser. Summary: role no role assignment no the email is.
2) scope1 in the suppress mode, the read uncommitted isolation level - the role is read, the data is written, the role assignment is created, the email is sent, rollback, nothing is failing. The result: no role, the role assignment is there, there is a letter.
3) scope1 in the suppress mode, the isolation level to any, except read uncommitted - the role is not readable, the request falls on timeout. The result: no role, no purpose, no letter.

Scenario 3 looks the most "clean" from the point of view of consequences, but is it true?
Perhaps the first committing scope2, then call AddRoleToUser? But what if the logic inside scope2 depend on the result of the work AddRoleToUser?
One of the obvious conclusions - Role must also be created within the api methods. Yes, ideally, but now the situation is not the same.
March 23rd 20 at 19:29
1 answer
March 23rd 20 at 19:31
Google pattern Unit of Work
or here to begin with - Pattern Unit of Work
UoW is in EntityFramework, you can reuse it.

if you microcerinae of architecture, then Google pattern Saga
or here esteem - Pattern: Saga

in General, a letter about the change of role must send a separate micro service, and not the code that writes to the database.
event-based, so ideally, it is necessary to raise the bus (RabbitMQ) and link them asynchronously.
Saga looks very attractive, but bloody time consuming. Yes, it is justified to microservices, but how to be with a monolithic app?
Let's assume that we have no requests to the database, there is only a change of state of entities. That is, to rollback all changes through rollback-a transaction will not work.
It appears that there is a set of atomic operations to be performed sequentially. In the end, they all must be saved or cancelled. It's like a Saga, similar to UoW. At UoW it seems even more than all the rest - in the end all changes should be analyzed and applied. Or canceled. But it is also the pattern looks like a Keeper. - shanelle_Stokes39 commented on March 23rd 20 at 19:34
@shanelle_Stokes39if you have a monolith - it is UoW.

In this case you will have repositories that work directly every with its essence.
Not one at all! But three entities - three repository.

> Let's assume that we have no requests to the database

okay, but then each entity must have a Stable state that cannot be rolled back, and a Temporary state that can be rolled back to the latest Stable.

Implementation of UoW does only change the flag from Temporary status to Stable. And nothing more. She doesn't need to know what fields changed, what is the essence of who depends on whom, etc.

The scheme is as follows:
1. Each Repository takes from memory/DB entity. Her condition is Stable.
2. Then you pull each entity according to the business logic.
3. You give them back to the Repository, and they expose them to a Temporary condition.
In fact, in the memory starts hanging 2 copies of an Entity (Stable old and Temporary new)
4. You give the order UoW implementation to apply the changes. - There is a substitution of reference - a Stable version of the entity point to the new Temporary condition. The old Stable - is killed.

In this case, each entity had within itself a Collection of Events (Send an email, etc.)
UoW implementation makes the Event distribution after the application of the new state. - ezequiel_Beck commented on March 23rd 20 at 19:37
@shanelle_Stokes39if the Commit all changes to UoW or Repository will fail, that's OK. GC or some other service will step Temporary entity, because soon they will not have links. As soon as using (UnitOfWork... - ezequiel_Beck commented on March 23rd 20 at 19:40

Find more questions by tags SQL ServerDesigning software.NET