Let a query read data before is committed - Sql Server

Question

I have a Table named ProjectActivity with Primary Key: ProjectCode and ActivityId and Index on ProjectCode Our websites generate query on this table (without involving other tables) and always requesting rows with same ProjectCode

Sometimes we start a job in c# that deletes every rows with a specific ProjectCode and inserts new rows for that specific ProjectCode in a transaction.

            using (var dbContextTransaction = db.Database.BeginTransaction())
        {
            try
            {

                db.Database.ExecuteSqlCommand("delete FROM [TS_Repository].[dbo].[ProjectActivities] where ProjectCode = {0} ", project.Code);

                LogManager.WriteRequestLog("Deleted Existing Project Activities");

                if (ProjectActivities.Count > 0)
                {
                    db.Set<ProjectActivity>().AddRange(ProjectActivities);
                    db.SaveChanges();

                }

                dbContextTransaction.Commit();

                LogManager.WriteRequestLog("Refresh ProjectActivities succesfully for " + project.Title);
            }
            catch (Exception exc)
            {

                dbContextTransaction.Rollback();
                throw exc;
            }
        }

Our issue is that, while this transaction (3/4 minutes) processing, our websites can't perform any query (whole table is locked). We should obtain this scenario: Until transaction being committed, website should query "old" data. Is this scenario possible to implement? I can figure it out that we have to trick with lock and isolation level.

Thank you in advance for any tips.

https://dba.stackexchange.com/questions/5014/what-risks-are-there-if-we-enable-read-committed-snapshot-in-sql-server — iamdave, Apr 24 '18 at 11:15
Perhaps you should reconsider the decision to purge and then repopulate your set of project codes - this doesn't sound like an efficient approach. — SMor, Apr 24 '18 at 11:40
I don't think so. My activities are often updated. I should check if each id has already been inserted and if so, if some columns have been updated. — Mike Custoza, Apr 24 '18 at 12:55

score 0 · Accepted Answer · answered Apr 24 '18 at 11:26

Yes you can execute your queries with a particular ISOLATION LEVEL that can read data while table or row is locked. I will not enter in the discussion about the risks of read uncommited data, I presume you alread knows, and @iamdave shared a good link about that.

As you want to read uncommited data, there are some ways to do it with EF, but a simple and clear way to do it is using a transaction. In TransactionScope constructor you can set the IsolationLevel, like this:

using (new TransactionScope(
           TransactionScopeOption.Required, 
           new TransactionOptions 
           { 
              IsolationLevel = IsolationLevel.ReadUncommitted 
           })) 
{
        // here you put your code
}

Here another good reading about EF and isolation level: entity-framework-and-transaction-isolation-level

Briefly, risks you are talking about involve database consistency or it regards only the fact i read data that could be potentially deleted? — Mike Custoza, Apr 24 '18 at 12:57
About the risk of reading obsolete or deleted data while querying with isolation level set to `ReadUncommitted` — Ricardo Pontual, Apr 24 '18 at 13:10

score 0 · Answer 2 · answered Apr 24 '18 at 14:16

you addressed me to the correct direction, but i guess i need SNAPSHOT isolation level, cause i've specified i need to read old row version. Maybe i didn't explain so good...

So, i've resolved using SNASPHOT:

using (var dbContextTransaction = db.Database.BeginTransaction(System.Data.IsolationLevel.Snapshot))
        {
            try
            {


                db.Database.ExecuteSqlCommand("delete FROM [TS_Repository].[dbo].[ProjectActivities] where ProjectCode = {0} ", project.Code);

                LogManager.WriteRequestLog("Deleted Existing Project Activities");

                if (ProjectActivities.Count > 0)
                {
                    db.Set<ProjectActivity>().AddRange(ProjectActivities);
                    db.SaveChanges();

                }

                dbContextTransaction.Commit();

                LogManager.WriteRequestLog("Refresh ProjectActivities succesfully for " + project.Title);
            }
            catch (Exception exc)
            {

                dbContextTransaction.Rollback();
                throw exc;
            }
        }

it's the same I've suggested you, just a with a different isolation level — Ricardo Pontual, Apr 25 '18 at 19:20

Let a query read data before is committed - Sql Server

2 Answers2