TransactionalDataPortal Isolation level.

[ReneElizondo]

Hello,

We have a WPF application running under .NET Framework 4.7.2, CSLA 4.2.2 and SQL Server 2022. We recently started having issues with locks in the database (readers were being blocked too often for too long). To alleviate the problem, we enabled snapshot isolation in the database (READ_COMMITTED_SNAPSHOT ).

Unfortunately, this did not work as expected. It turns out that it appears that the CSLA TransactionalDataPortal (which we use) is based on the TransactionScope and that class appears to default the database isolation level to serializable which does not have the desired effect.

To fix this, I modified the Create, Fetch, Update and Delete functions (basically all of the functions) in the TransactionalDataPortal class from doing this:

        public DataPortalResult Fetch(Type objectType, object criteria, DataPortalContext context)
        {
            DataPortalResult result;
            using (TransactionScope tr = new TransactionScope(TransactionScopeOption.Required, TimeSpan.FromSeconds(3600)))
            {
                var portal = new DataPortalSelector();
                result = portal.Fetch(objectType, criteria, context);
                tr.Complete();
            }
            return result;
        }

To doing this:

        public DataPortalResult Fetch(Type objectType, object criteria, DataPortalContext context)
        {
            var options = new TransactionOptions
            {
                IsolationLevel = IsolationLevel.ReadCommitted,
                Timeout = TimeSpan.FromSeconds(3600)
            };

            DataPortalResult result;
            using (TransactionScope tr = new TransactionScope(TransactionScopeOption.Required, options))
            {
                var portal = new DataPortalSelector();
                result = portal.Fetch(objectType, criteria, context);
                tr.Complete();
            }
            return result;
        }

This appears to be working as expected but I don't like it because I had to modify the CSLA source code which is something I did not want to do.

So, I was wondering if someone could help me and answer the following two questions:

1. Is there a way to set the isolation level on the TransactionalDataPortal via a configuration setting? I looked and was not able to find anything but I hope I am missing something.
2. Do you have experience with the SQL server snapshot isolation level and the effect that has in the CSLA? For example, is doing what I did an acceptable solution or am I bound to run into issues / gotchas?

Thanks.

[hurcane]

If you were originally using TransactionScope, that puts the transaction control in .NET. The only way to modify that is by changing the .NET code, as you did.

If you are using stored procedures, you could remove the TransactionScope and implement transactions with the SQL stored procedure.

When we receive reports of excessive blocking from users, the first instinct is always to work on improving the query performance. Can the query be optimized to run faster? Locks are good for data accuracy and integrity. Long locks are never good.

I recommend familiarizing yourself with the Microsoft documentation on isolation levels. With ReadCommitted, you run the risk of phantom reads. That may be acceptable for some queries, but not others. For example, if you run a stored procedure that returns a header result that sums all the rows of a table and the second result returns the detail contents of the table, the header's sum and the detail content may not match if a new row was added between the two Sql queries within the procedure.

[hurcane]

My original answer here was naive because I did not have a strong understanding of the current Csla codebase. Please see my later answer, which I think is a lot better.

[rockfordlhotka]

I'm pretty sure that at some point we added the ability to change those values via configuration, specifically to help avoid the need to change the framework code itself.

[hurcane]

I am working -- finally -- on a project to upgrade code from CSLA 5, and this question popped up during my research. To document a specific answer, there are two techniques you can use to configure an isolation level that is different from the default.

One option is to use the following for an attribute to use something other than the default. This is useful when you have a specific operation that should override the isolation level.

[Transactional(TransactionalTypes.TransactionScope, TransactionIsolationLevel.ReadCommitted)]

In a larger context, you may want to change the isolation level default for all transactions. This can be set up with configuration options when adding Csla to the services collection. Here's an example:

services.AddCsla(o => 
    o.Data(d => 
        d.DefaultTransactionIsolationLevel(TransactionIsolationLevel.ReadCommitted)));

Rocky was correct in noting that this ability was added to Csla. Hopefully, these specifics will be helpful to others.