Tech Off Thread

6 posts

Linq to SQL -- modify SQL?

Back to Forum: Tech Off
  • User profile image
    Dr Herbie

    I've just started experimenting with Linq to SQL and so far I'm very impressed.

    But ...
    (there's always a 'but')
    ... the convention where I work is to use the 'WITH (NOLOCK)' hint on all select statements and the SQL generated by Linq to SQL doesn't do this.

    Is there a way to get the Linq to SQL generation code to use the NOLOCK hint in the SQL statements?  I haven't been able to find any details on whether it is possible to 'interfere' with the SQL generation at all.


    Herbie

  • User profile image
    Dr Herbie

    As always, I find an answer within a few minutes of posting a question here:

    A ReadUncommitted isolation level in a TransactionScope has the same effect as NOLOCK (apparently).

    See this blog entry for snippet of code ...

    Herbie

  • User profile image
    mabster

    Dr Herbie said:

    As always, I find an answer within a few minutes of posting a question here:

    A ReadUncommitted isolation level in a TransactionScope has the same effect as NOLOCK (apparently).

    See this blog entry for snippet of code ...

    Herbie

    Glad I could help, Dr Herbie! Wink

  • User profile image
    Matthew van Eerde

    > ... the convention where I work is to use the 'WITH (NOLOCK)' hint on all select statements

    Why? You can get weird results if you SELECT while there's an INSERT or UPDATE or DELETE in effect.

  • User profile image
    Matthew van Eerde

    Reading up on it further, it looks like you're trying to avoid a situation where your SELECT blocks INSERT/UPDATE/DELETEs from other connections. Is that correct?

    There are a couple of other options (assuming SQL Server 2005) which would prevent the SELECTs from getting data that was never committed while still minimizing contention problems:

    1) You can use the SNAPSHOT isolation level. I don't see that in the IsolationLevel property doc but I do see it in the Table Hint doc, so this may not work yet from LINQ.
    2) Set the database option READ_COMMITTED_SNAPSHOT to ON.  This would allow you to use the default "Read committed" isolation level, but would affect every connection, even those that didn't use LINQ.

  • User profile image
    Dr Herbie

    Matthew van Eerde said:
    Reading up on it further, it looks like you're trying to avoid a situation where your SELECT blocks INSERT/UPDATE/DELETEs from other connections. Is that correct?

    There are a couple of other options (assuming SQL Server 2005) which would prevent the SELECTs from getting data that was never committed while still minimizing contention problems:

    1) You can use the SNAPSHOT isolation level. I don't see that in the IsolationLevel property doc but I do see it in the Table Hint doc, so this may not work yet from LINQ.
    2) Set the database option READ_COMMITTED_SNAPSHOT to ON.  This would allow you to use the default "Read committed" isolation level, but would affect every connection, even those that didn't use LINQ.
    You're right, we want the NOLOCK hint to prevent blocking data writes -- this isn't a problem for our scenarios and helps a lot with performance.

    We're running on SQL2000 for live data, so all options must be backward compatible. The isolation level changes would be the granularity of control that we are looking for.
    We're currently not going with Linq to SQL and sticking to plain old datasets (PODS!) for now.  We'll look at the entity framework stuff when it comes out of beta ...

    Herbie


Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.