Nested transaction management .NET

Question

I have an asp.net core project that is divided into the following structure

• Controller
  • Store
    • Repository

The controller receives requests, and calls the correct Stores that are responsible for actions against the database (Create Users, Update Records, etc) The way I see it the controller should be agnostic to the database - it should not control the transactions, or even be aware of their existence.

The store creates a transaction, and calls the required repositories, and their required CRUD methods.

This is pretty straight forward. However, often I find myself repeating code - because of the transaction management.

Sometimes I want to add users, and add them to a group - So If I had a GroupsStore, and a UsersStore - I now have a GroupsUsersStore that does exactly the same as those store, but by putting that in one transaction.

This is obviously wrong, but I can't think of a better solution - since transactions can't be nested in sql server, and I want those stores to work independently as well.

How do you solve such issue?

Answer 1

Your Stores sound like a business layer, correct? I would assume that a given single method in the stores might call multiple repos to achieve its work, with everything being rollbackable in the event of an error. If that's the case, then your store should initiate the sql connection and transaction, and hand them down as a dependencies to the repos.

Inside FooStore.cs:

public StoreResult<ResultThing> DoStuff(int stuff) 
{
     using (var connection = GetOpenConnection())
     using (var transaction = connection.BeginTransaction())
     {
          var fooRepo = new FooRepo(connection, transaction);
          fooRepo.DoThing(stuff); // calls connection.Execute(...)
          // add more repo calls here
          transaction.Commit();
          return new StoreResult<ResultThing>();
     }
}

Now, your StoreResult will need to return some kind of success/fail/messaging data to indicate back to the controller that called it what kind of result occurred.

Answer 2

Give your "Store" classes an optional boolean constructor parameter autocommit to control if they shall manage the transactions by themselves (i.e. committing automatically after their work is done), or if they leave this to the caller. The default value for this parameter might be true.

So your GroupsStore will still commit automatically by default, and your UsersStore store, too, when used on its own.

Now, for having a combined "Groups & Users" transaction, you will probably still create a separate GroupsUsersStore class, but this one does not need to contain any duplicated code any more. Instead, it can reuse a GroupsStore and a UsersStore, calling them both with autocommit=false.

Answer 3

Sometimes I want to add users, and add them to a group - So If I had a GroupsStore, and a UsersStore - I now have a GroupsUsersStore that does exactly the same as those store, but by putting that in one transaction.

This is obviously wrong, but I can't think of a better solution - since transactions can't be nested in sql server, and I want those stores to work independently as well.

How do you solve such issue?

I think you are tackling a problem from the opposite end. Transaction control should be excersised on one application layer only. So, if you need to control transactions on a level higher, than you do now, you have several choices:

• Refactor stores (actually, they are services it seems) so that each could execute all operations without going out of its bounds. You might want to merge stores for instance. But watch out for SRP obidience.
• Construct higher-level layer of your application (orchestration layer), that will control transactions and call stores when needed to execute one bussiness operation.

So If I had a GroupsStore, and a UsersStore

I would say, that you need UserManager (or whatever you call it), that can really manage users as a bussiness entity: assign users to groups etc. Because currently your GroupsStore looks nothing more than a slighly god-like repository.