locked
Appropriate Transaction Isolation Level RRS feed

  • Question

  • Hi every body,

    I'm not sure if this is the right place to start this topic, If not, please direct me to the correct forum.

    Here's my scenario...

    1) Starting a time-consuming transaction which will result to add a lot of data into one of tables in my database.

    2) Starting another transaction which will read data from the table that other transaction is going to insert into.

    Here's my problem:

    in the second transaction, I know if I use a select statement it will not read volatile data with having the transaction isolation level set to read committed. and this is what I exactly need, but the problem comes up when I want to execute a select statement with a where clause. the constraint in the where clause is so that the volatile data won't be in the result set but still the table is locked. what kind of isolation level should I use to be able to execute the select statement successfully? to clarify the point:

     

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    /* A bunch of insert statements which will take about 10 minutes to finish executing, for example one is as below:

    insert into X A, B VALUES (0, 1),  assuming that all the other insert statements are for the same table and B is set to 1 for all of them*/

     

    In another connection to database I have:

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    SELECT * FROM X WHERE B = 3 /* This exclude all the rows that are going to be inserted from other transaction */

     

    the above select statement doesn't execute until the first transaction completes. If I set isolation level to read uncommitted then the above statement executes successfully but having the volatile data in my result set isn't what I really want. You may say that by default volatile data won't appear in the result set due to B = 3 but this constraint will dynamically change in different conditions and in some conditions may get equal to 1


    learn to learn
    Monday, June 14, 2010 11:47 AM

Answers

  • Consider changing the database to READ_COMMITTED_SNAPSHOT.  This will use row versioning instead of locking to implement the READ COMMITTED isolation level.  Be aware that this will increase tempdb usage due to the row version store requirements.

    ALTER DATABASE MyDatabase
    SET READ_COMMITTED_SNAPSHOT ON;

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Monday, June 14, 2010 12:20 PM

All replies

  • Cannot tested right now, but do not use SELECT * , issue SELECT b FROM X WHERE B = 3  ,assumed that B has noclustered index
    Monday, June 14, 2010 12:04 PM
    Answerer
  • Cannot tested right now, but do not use SELECT * , issue SELECT b FROM X WHERE B = 3  ,assumed that B has noclustered index
    Monday, June 14, 2010 12:04 PM
    Answerer
  • Consider changing the database to READ_COMMITTED_SNAPSHOT.  This will use row versioning instead of locking to implement the READ COMMITTED isolation level.  Be aware that this will increase tempdb usage due to the row version store requirements.

    ALTER DATABASE MyDatabase
    SET READ_COMMITTED_SNAPSHOT ON;

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Monday, June 14, 2010 12:20 PM
  • Thank you both for your replies,

    Dear Uri Dimant, your solution works fine when I use SQL Management Studio to execute queries but the problem is that I'm using LINQ to execute queries, having the following command executed in management studio without using sp_executesql will return what I really expect but as LINQ uses sp_executesql the following line of code will stop until another transaction which has inserted a row into Cluster table commits its changes. notice that the insert statement is so that the cluster ID is 140.

    exec sp_executesql N'SELECT [t0].[ID], [t0].[_clusterTypeID] AS [_clusterTypeID], [t0].[_switchID] AS [_switchID], [t0].[_registrarID] AS [_registrarID], [t0].[Filename], [t0].[HashCode], [t0].[RegistrationDate]
    FROM [dbo].[Cluster] AS [t0]
    WHERE [t0].[ID] <> @p0',N'@p0 int',@p0=140

     

    Dear Dan Guzman, you're right, changing database to READ_COMMITTED_SNAPSHOT will solve my problem but as you know ALTER DATABASE MYDB SET READ_COMMITTED_SNAPSHOT ON needs to close all other connections to the database which is not what I want.

    Isn't there any solution so that when I execute a query it simply just skip volatile data and return committed ones without locking my query (I mean except snapshot isolation level, its overhead is unacceptable for my project)?

     

    Thanks a million.


    learn to learn
    Tuesday, June 15, 2010 6:48 AM
  • Dear Dan Guzman, you're right, changing database to READ_COMMITTED_SNAPSHOT will solve my problem but as you know ALTER DATABASE MYDB SET READ_COMMITTED_SNAPSHOT ON needs to close all other connections to the database which is not what I want.

    You only need to set READ_COMMITTED_SNAPSHOT once during a short maintenance window.  The operation only takes a few seconds and will be remembered.
    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Tuesday, June 15, 2010 12:16 PM