Locking Hints and LINQ

It is possible to do that in DLINQ by specifying your custom commands. In eSQL, since you are working against client views, it doesn't make sense to have them there. You may want those during submitting changes - I feel if this CTP doesn't have that, future CTPs probably will (just my guess).

Sahil Malik
http://blah.winsmarts.com

SahilMalik at 2007-10-7 >

The ability to set an isolation level to a query is incredibly important. I can't really stress this enough. Every project I have worked on in the past 8 years has used at some point either the nolock table hint or set the isolation level at the begining of the query.

Why not simply allow people to append a string to the front of the generated query? If you place "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;" at the front of a query it is the equivalent of using table hint NOLOCK. And I'm pretty sure that this would work across all databases.

Valdair at 2007-10-7 >

Question:

So imagine you want to read say a Customer row, and you want to do this with an update lock. How much "non LINQ" code would you then have to write to accomplish the same as the simple LINQ code below:

northwindDataContext.Customers.Single(c => c.CustomerID == "1") // plus UPDATELOCK

NielslaCour at 2007-10-7 >

System.Transactions support is available in the just released EF Beta 2 and will be part of our first version RTM (early next year).

- Danny

DanielSimmons-MSFT at 2007-10-7 >

Hi,

Though the code snippet doesn't show the other operations performed on the associated connection, the error message suggests that you have pending results associated with another query (open datareader) on the same context/connection. Since this thread doesn't specify any requirements, let me list out various options here to get around this problem:

1. Let's say, you want to deal with two queries within one context, you need to make sure MARS setting is enabled in the connection string (supported with MS SQL Server 2005 or higher). Make sure you enumerate or read through all the results associated with previous query before accessing a new query in the same context.

Even when the MARS setting is enabled, if there are pending results left over on the SQL Server end, you would run into an exception as listed below:

Message: Inner Exception -> The operation failed because the session is not single threaded.
Source: .Net SqlClient Data Provider
Type: System.Data.SqlClient.SqlException

This is "by design" behavior of SQL Server.

2. Let's say, your intention wasn't to keep the other reader open, please make sure you close it out (in other words, enumerate all the results associated with the previous query) in which case you don't need to enable MARS setting.

If you're interested in knowing more about connection management and transactions related to EntityFramework (EF), please let me know. From the code snippet above, looks like you're using DLinq in which case I could ask some DLinq expert to help you with the internals on the connection management and trans.

Hope this helps,

Kavitha Jonnakuti

Microsoft Corportation

KavithaJonnakuti-MSFT at 2007-10-7 >

Hello Q,

I can't see any obvious reasons for the exception here but here are some additional things beyond what Kavitha has already covered:

1. For SQL2000, the default is unfortuantely distributed transaction. So if you are using SQL2000, you may need to use a workaround (see below)

2. On SQL 2005 Express, we have some recent reports of unexpected promotion. We are investigating this further. The example we have involves use of the same connection after the using(){ } block is complete and the transaction has failed. I doubt this is applicable to your case but it is still worth eliminating.

What database version and edition are you using? That will help us narrow down this further.

Workaround: this unfortunately involves sidestepping the nice TransactionScope programming model -

You can create your own SqlTransaction and assign it to DataContext.Transaction. This was primarily designed for interop scenarios where you want to use SqlClient API and use the same transaction for LINQ to SQL operations.

Thanks,

Dinesh

DineshKulkarni-MSFT at 2007-10-7 >

Hi Dinesh, Hi all,

thank you for all your comments.

I summarize that we all agree that locking hints/or translevels are important.

DLINQ only supports translevels. However you need to side-step the TransactionScope model and assign a SqlTransaction to the DataContext manually. This will be supported in RTM of DLINQ with Orcas.

Note: EF will

This shall work for most scenarios.

Thank you

Tim

Dinesh, correct me if wrong.

TimF.Fischer at 2007-10-7 >