CODEWITHSUNDEEP
c#sql-serverentity-frameworktransactionscopeisolation-level
MBender
MBender

Reputation: 5650

Snapshot isolation in SQL / code blocking reads

I suspect I don't understand fully what's going on or something weird is happening. (The first case is more likely I guess.)

The big picture:

Here's a sample of the code which I'm currently using to test out the transactions (using Entity Framework 5 model first):

        using (var transaction = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.RequiresNew, new System.Transactions.TransactionOptions() { IsolationLevel = System.Transactions.IsolationLevel.Snapshot }))
        {
            var db = new DataModelContainer();
            Log test = new Log();
            test.Message = "TEST";
            test.Date = DateTime.UtcNow;
            test.Details = "asd";
            test.Type = "test";
            test.StackTrace = "asd";
            db.LogSet.Add(test);
            db.SaveChanges();
            using (var suppressed = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Suppress))
            {
                var newDb = new DataModelContainer();
                var log = newDb.LogSet.ToArray();  //deadlock here... WHY?
            }
            test = db.LogSet.Where(l => l.Message == "TEST").Single();
            db.LogSet.Remove(test);
            db.SaveChanges();
            transaction.Complete();
        }

The code creates a simple Log entry in the database (yeah, I'm playing around at the moment so the values are rubbish). I've set the SQL database to allow snapshot isolation, and to my knowledge reads should still be permitted (these are being tested in this code by using a new, suppressed transaction and a new DataModelContainer). However, I cannot query the LogSet in the suppressed transaction or in SQL Management Studio - the whole table is locked!

So... why? Why is it locked if the transaction scope is defined as such? I've also tried other isolation levels (like ReadUncommited) and I still cannot query the table.

Could someone please provide an explanation for this behavior?

Upvotes: 1

Views: 1526

Answers (1)

Moho
Moho

Reputation: 16543

In SSMS, set your current isolation level to SNAPSHOT and see if that corrects your problem - it's probably set to READ COMMITTED and would therefore still block due to pending updates.

Update:

You can allow READ COMMITTED to access versioned rows DB-wide by altering the following option (and avoiding having to constantly set the current isolation level to SNAPSHOT):

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON

Upvotes: 1

Related Questions