Read Committed Snapshot Isolation (RCSI)

Questions
1

Once in a while we get deadlocks on our database(s). I’ve read something about Read Committed Snapshot Isolation (RCSI).

Does anyone have expierence with this? And so, is it something to look into?

Thanks in advance.

Dave

2

That’s how SQL Azure and Umbraco Cloud run by default, so should be no problem. Not sure if we should document it actually, could be good to create an issue at Issues · umbraco/UmbracoDocs · GitHub to get it confirmed.

3

Hi @DynamicDave

Umbraco uses its own read/write lock for database interactions, so I’d work out where the deadlocks are actually coming from before changing the isolation level.

If you’ve got custom code reading Umbraco tables, or you’re holding transactions open while using the Umbraco APIs, that’s where I’d look. From experience, keep locks held to the absolute minimum and access and update data in the same order everywhere.

Justin

4

Very good point @justin-nevitech - it’s more important to figure out where the deadlocks are coming from rather than hoping a config change might fix them! :+1:

5

I think you are inferring ensure usage of IScopeProvider

and explicit scope.Complete()

using (var scope = _scopeProvider.CreateScope())
        {
            // Access the NPoco database instance via the scope
            var db = scope.Database;

            // Perform multiple database operations
            db.Execute("UPDATE myTable SET Status = @0 WHERE Id = @1", "Active", 1);
            db.Insert(new MyCustomEntity { Name = "New Record" });

            // CRITICAL: Mark the scope as complete, otherwise it rolls back on dispose
            scope.Complete();
        }

or use of autocomplete

using (var scope = _scopeProvider.CreateScope(autoComplete: true))
{
    var db = scope.Database;
    // Read operations or actions that commit automatically on successful block exit
}

and here’s an article that might be of use?

6

Sorry for the late response @sebastiaan, @justin-nevitech & @mistyn8,

I will have a look at all the possible uses of IScopeProvider in our project!

Thanks! #h5yr!