TransactionScope in business layer for dirty async read

Question

I'm developing a .NET Core web api service and have the following method in BL:

public async Task<SetParams> GetParams(CreateRequest request)
        {
            var user = await _userRepository.GetUserByLogin(request.Login);  
            var client = await _clientRepository.GetClientByCode( request.ClientCode);

            // many other getters here 

            return new SetParams
            {
                IdUser = user.IdUser,
                ClientName = client.Name,
                // and so forth...
            };
        }

I have a requirement to get all the entities in "dirty read" mode.

So, I was trying to use TransactionScope this way:

public async Task<SetParams> GetParams(CreateRequest request)
        {
            using (var ts = new TransactionScope(
                TransactionScopeOption.Required,
                new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted }))
            {
                var user = await _userRepository.GetUserByLogin(request.Login);
                var client = await _clientRepository.GetClientByCode(request.ClientCode);

                // many other getters here 

                ts.Complete();

                return new SetParams
                {
                    IdUser = user.IdUser,
                    ClientName = client.Name,
                    // and so forth...
                };
            }
        }

But: 1) this has read committed mode (I know from this post that I have to start a transaction but I don't have a session or db context here, because I'm in BL not in DAL)

and 2) ending up with an exception A TransactionScope must be disposed on the same thread that it was created.

It's not a good idea to do loads of repository gets if you are only using a few fields from each. Implement a DL function that retrieves just the fields you require in the BL. — Neil, Sep 03 '18 at 08:11
.NET 4.5.1 added the option to pass a `TransactionScopeAsyncFlowOption` to the constructor, which should address 2. — Damien_The_Unbeliever, Sep 03 '18 at 08:40
https://particular.net/blog/transactionscope-and-async-await-be-one-with-the-flow — Mrinal Kamboj, Sep 03 '18 at 08:52

score 1 · Answer 1 · answered Sep 03 '18 at 09:37

You need Transaction for the set of Read queries (dirty Read)

I assume you have high concurrency to read the uncommitted data. That's probably the only use case for using the transaction in the Select queries. I assume they are still interwoven with the few DML operations

Where to begin Transaction, BL / DAL, does it needs DbContext ?

This depends on your use case, if your design is such that multiple business queries, which shall be part of a Transaction are dispatched from the BL, then its a valid starting point, especially using the Ambient transaction (TransactionScope). An explicit Session or Context to initiate a Transaction is one of the mechanism, since all the shared queries are seamlessly enlisted in the same Transaction Context, but similar thing is possible using TransactionScope or CommittableTransaction, you can utilize TransactionScopeOption to ensure participation of various queries:

Using the Requires New to initiate the ambient transaction at the BL and at the DAL level using the Required to ensure the same transaction context is used for the enlistment. Database connection shall auto enlist in the available scope.
Using the CommittableTransaction, same object created in the BL is passed to the DAL repositories and there Connection objects are explicitly enlisted
If your requirement is little more specific, where No Commit is feasible till the point all the Child operations on separate threads are completed, then review DependantTransaction, which is also part of System.Transaction, but it does much more tight binding, mostly for the multi threaded scenario, you may not need it for the Async Await calls, since you complete only when all awaits are finished.

Issue related to A TransactionScope must be disposed on the same thread that it was created.

Solution is here, this was introduced in the .Net4.5.1 and ensure usage of TransactionScope across Async calls

Sql Server Isolation level for the queries

What you are referring is the default for most of the systems ReadCommitted, but you can certainly tweak the settings to ensure lower IsolationLevel, though also be prepared of the implications of reading dirty data in the applications

TransactionScope in business layer for dirty async read

1 Answers1