locked
Locking Hints and LINQ RRS feed

  • Question

  • Hi there,

    is it possible to do locking hints like NOLOCK or pessimistic locking hints in DLINQ  and LINQ for Entitities as well as in eSQL?

    Thanks

    Tim
    Tuesday, September 5, 2006 2:48 PM

Answers

  • Currently, it is not possible to pass hints to the underlying database. For some scenarios you can achieve the same results with transactions, but this is certainly not a general solution.

    Allowing hints is tricky because they are very database specific, so it's hard to come up with a general purpose mechanism to do that without losing the database independence. The topic does come up from time to time, but we don't have specific plans for hints as of right now.

    Pablo Castro
    ADO.NET Technical Lead
    Microsoft Corporation

    Wednesday, September 6, 2006 7:32 PM
  •  

    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

    Friday, August 31, 2007 5:21 AM

All replies

  • 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

    Tuesday, September 5, 2006 7:43 PM
  • Currently, it is not possible to pass hints to the underlying database. For some scenarios you can achieve the same results with transactions, but this is certainly not a general solution.

    Allowing hints is tricky because they are very database specific, so it's hard to come up with a general purpose mechanism to do that without losing the database independence. The topic does come up from time to time, but we don't have specific plans for hints as of right now.

    Pablo Castro
    ADO.NET Technical Lead
    Microsoft Corporation

    Wednesday, September 6, 2006 7:32 PM
  • 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.

    Wednesday, June 13, 2007 9:48 PM
  • If transaction isolation is sufficient for your scenario, then system.transactions integration should do the trick, and that will be available in the next release.  You can set the isolation level on the transaction scope and then it will be honored for the entire transaction (queries, updates, etc.).
    Thursday, June 14, 2007 2:27 AM
  • 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

    Thursday, August 16, 2007 1:11 PM
  •  

    when you said next release, which release are you referring to?  VS2008?  I too agree that being able to specify locking hints is very important for any reasonable sized project.
    Friday, August 31, 2007 5:18 AM
  •  

    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

    Friday, August 31, 2007 5:21 AM
  •  

    Hi Danny,

     

    I wonder if there are any exampls on how TransactionScope should be used with linq.  I have some test code that looks like the following:

     

    Code Snippet

    using(DB db = DB(...))

    {

    ...

    TransactionOptions to = new TransactionOptions() { IsolationLevel = IsolationLevel.ReadUncommitted }

    using(TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew, to))

    {

    var items = (from i in db.Items where i.Property == value select i).ToArray();

    ts.Complete();

    }

    ...

    }

     

     

    But this causes an exception:

    System.Transactions.TransactionPromotionException: Failure while attempting to promote transaction. --->  System.Data.SqlClient.SqlException: There is already an open DataReader associated with this Command which must be closed first..

     

    What's the right way of doing it? Thanks in advance.

     

     

    Thursday, September 6, 2007 4:00 PM
  • 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

    Thursday, September 6, 2007 7:42 PM
  •  

    Hi Kavitha,

     

    Access is done through Linq to SQL, but the DataContext instance does not issue any other query in this particular test case. Connection pooling is enabled by default and there are other instances of DataContext in the test program with no pending result sets. Hmm... what could have caused the exception?

     

    However I am definitely interesting in learning more about the connection management and transaction framework in EF.

    Friday, September 7, 2007 1:51 AM
  • 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

    Tuesday, September 11, 2007 10:23 PM
  • 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:

     

    This shall work for most scenarios.

     

    Thank you

     

    Tim

     

    Dinesh, correct me if wrong.

    Thursday, September 13, 2007 8:53 AM
  • 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.

    Thursday, September 13, 2007 8:53 AM
  • 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

     

    This shall work for most scenarios.

     

    Thank you

     

    Tim

     

    Dinesh, correct me if wrong.

    Thursday, September 13, 2007 8:53 AM
  • 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 also provide

     

    This shall work for most scenarios.

     

    Thank you

     

    Tim

     

    Dinesh, correct me if wrong.

    Thursday, September 13, 2007 8:53 AM
  • 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 also

     

    This shall work for most scenarios.

     

    Thank you

     

    Tim

     

    Dinesh, correct me if wrong.

    Thursday, September 13, 2007 8:53 AM
  • 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 also provide this

     

    This shall work for most scenarios.

     

    Thank you

     

    Tim

     

    Dinesh, correct me if wrong.

    Thursday, September 13, 2007 8:54 AM
  • 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 also provide this functionality

     

    This shall work for most scenarios.

     

    Thank you

     

    Tim

     

    Dinesh, correct me if wrong.

    Thursday, September 13, 2007 8:54 AM
  • 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 also provide this functionality wit

     

    This shall work for most scenarios.

     

    Thank you

     

    Tim

     

    Dinesh, correct me if wrong.

    Thursday, September 13, 2007 8:54 AM
  • 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 also provide this functionality with

     

    This shall work for most scenarios.

     

    Thank you

     

    Tim

     

    Dinesh, correct me if wrong.

    Thursday, September 13, 2007 8:54 AM
  • 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 also provide this functionality with RTM

     

    This shall work for most scenarios.

     

    Thank you

     

    Tim

     

    Dinesh, correct me if wrong.

    Thursday, September 13, 2007 8:54 AM