NOLOCK, transactions, and LINQ in RTM VS2008

Question

We have a large ecommerce web app that uses NOLOCK on a large subset of our queries. We're ready to start adding LINQ to our application. I understand that there is no way to put locking hints in LINQ, and that we must use transactions with an isolation level of "read uncommitted" to get equivalent performance. I can create a transaction on the data context, but I get the following exception at run time:

 

ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.  The Transaction property of the command has not been initialized.

 

Here's the basic setup and code path:

• Use SQLMetal to generate data layer.
• Create data context (new data context object for each query).
• Open the connection in the data context.
• Inside the data context, create a transaction using
  • myTrans = Connection.BeginTransaction(IsolationLevel.ReadUncommitted)
• Create LINQ query using this data context.
• Use Query.ToList() to run the query and dump results into a List<T>.
• InvalidOperationException occurs.

If I go through an alternate path that doesn't create a transaction on the data context, it works, so the problem is specific to the fact that I've created a transaction. I'm using the Connection member of the data context to create the transaction. I don't know of any way to attach the transaction to the command for the Reader (and I probably shouldn't have to reach inside to do that, anyway).

 

Any suggestions?

 

Thanks,

Diane

Answer 1

Diane,

 

This is an interesting issue. Thank you for bringing it up. The problem comes from the fact that in ADO.NET the transaction must be associated with the command so that you can have parallel transactions in a single connection.

 

In Entity Framework, we made a decision to keep things simple – we impose a limitation that an EntityConnection may have only one active EntityTransaction. That relieves you from the responsibility of associating each command with a transaction. When an EntityCommand is executed it reuses the active transaction from its connection. So this scenario should work with LINQ to Entities, ObjectQuery<T>, and EntityClient. Please try those technologies.

 

Meanwhile, I’ll file a bug for LINQ to SQL to enable this scenario. (No guarantee when/whether it will be resolved.)

 

 

Zlatko Michailov

Program Manager, Entity Services

Microsoft Corp.

http://blogs.msdn.com/esql

 

Answer 2

I'm having the same problem.  This is a huge issue if we can't specify transaction levels.  Any update?  Is the product team going to fix this?  If I know that they will, I can start implementing.

 

Thanks,

John

 

Answer 3

Consider using System.Transactions.Transaction (and/or TransactionScope).

Here is a link to your local MSDN: ms-help://MS.MSDNQTR.v90.en/fxref_system.transactions/html/b1958051-9337-3e80-f3e9-c26893f74248.htm

 

Answer 4

I don't have MSDN installed locally.  Here is the code I'm executing. 

 

Dim sdc As New SysproObjects.SysproDataContext

Return (From c As SysproObjects.ArCustomer In sdc.ArCustomers Where c.Customer = CustomerCode Select c.TermsCode).FirstOrDefault

 

I want to make sure that this read has an isolation level of ReadUncommitted (nolock).  Can you quickly give me the code to make this happen?  Thx.

Answer 5

I've been meaning to get back here and post a solution. Yes, you can do NOLOCK as well as explicit transaction control.

 

After generating your data access layer using SQLMetal, you get a partial class that subclasses the DataContext, and maps all your tables to classes. What I've done is create a partial class of the same type that gives me the control I need. Note particularly the static methods that implement "factories" that return a tailored DataContext. "NoLockDC()" returns a DataContext with LINQ's internal caching turned off, and transaction isolation level set to ReadUncommitted, which is equivalent to NoLock. "TransActDC" returns a DataContext with an open transaction set to ReadCommitted.

 

Code Snippet

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Text;

namespace NW.Data
{
    public enum DCTransType
    {
        Normal,
        ReadOnlyTrans,
        NoLock,
        Explicit,
        Rollback,
        Commit
    }
    public partial class Northwind : System.Data.Linq.DataContext
    {
        private DCTransType myTransType = DCTransType.Normal;

        public DCTransType TransType
        {
            get
            {
                return myTransType;
            }
            set
            {
                if (myTransType != DCTransType.Rollback)
                {
                    myTransType = value;
                }
            }
        }

        public static Northwind DC()
        {
            Northwind newDC = new Northwind("Data Source=.;Initial Catalog=Northwind;Integrated Security=True");
            return newDC;
        }

        public static Northwind NolockDC()
        {
            Northwind newDC = Northwind.DC();
            newDC.ObjectTrackingEnabled = false;
            newDC.TransType = DCTransType.NoLock;
            newDC.SetUpConnection();
            return newDC;
        }

        public static Northwind ReadOnlyDC()
        {
            Northwind newDC = Northwind.DC();
            newDC.ObjectTrackingEnabled = false;
            newDC.TransType = DCTransType.ReadOnlyTrans;
            newDC.SetUpConnection();
            return newDC;
        }

        public static Northwind TransActDC()
        {
            Northwind newDC = Northwind.DC();
            newDC.TransType = DCTransType.Explicit;
            newDC.SetUpConnection();
            return newDC;
        }

        private void SetUpConnection()
        {
            if (myTransType == DCTransType.NoLock)
            {
                Connection.Open();
                Transaction = Connection.BeginTransaction(IsolationLevel.ReadUncommitted);
            }
            else if (myTransType == DCTransType.Explicit)
            {
                Connection.Open();
                Transaction = Connection.BeginTransaction(IsolationLevel.ReadCommitted);
            }
        }

        public new void Dispose()
        {
            switch (myTransType)
            {
                case DCTransType.NoLock:
                case DCTransType.Explicit:
                case DCTransType.Commit:
                    Transaction.Commit();
                    break;
                case DCTransType.Rollback:
                    Transaction.Rollback();
                    break;
            }
            base.Dispose();
        }
    }
}

 

 

 

 

The NolockDC is a throw-away; you can use it inline with your LINQ statement and create a new DataContext every time, if you want.

 

To use the TransActDC, you'd save it in a local variable, do your transaction, commit or rollback your transaction, and dispose of the DataContext. Dispose will close the transaction, but you do want to call it explicitly rather than wait for garbage collection to do it. The code would look like this:

 

Code Snippet

Northwind myDC = Northwind.TransActDC();

try

{

// do your transaction stuff using myDC as your DataContext

myDC.TransType = TransType.Commit;

}

catch (Exception ex)

{

myDC.TransType = TransType.Rollback;

}

finally

{

myDC.Dispose();

myDC = null;

}

 

 

 

That should do it. If you run into problems, please post back here.

Answer 6

 

This is looking really good.  Can you convert it to VB.NET?  Thx!

Answer 7

Never mind.  I converted it myself.  It works great!

Answer 8

Hi,

 

I am struckling with this? Do you have a sample project which I can download? How to set up this correctly?

 

Answer 9

I believe I am accomplishing the same thing with the following code:

    <System.Runtime.CompilerServices.Extension()> _
    Public Sub SetNoLockOn(ByVal thisDataContext As DataContext)
        thisDataContext.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")
    End Sub

    <System.Runtime.CompilerServices.Extension()> _
    Public Sub SetNoLockOff(ByVal thisDataContext As DataContext)
        thisDataContext.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ COMMITTED")
    End Sub

but maybe I am misunderstanding something?

I just call the following right before I query MyDataContext:

MyDataContext.SetNoLockOn

and this is generic to work with any Datacontext.  I don't have to edit or extend each of the generated DataContexts individually.  But please feel free to point out the error in my approach?

---

Mike