Is it possible you have Snapshot isolation enabled?
From: [email protected] [mailto:[email protected]] On Behalf Of Simon Fox Sent: Wednesday, May 02, 2012 8:17 PM To: [email protected] Subject: Re: [nhusers] nolock, Read Uncommitted and sp_executesql Now I'm really confused, can someone please explain why when this line of code executes using (var transaction = _session.Transaction.Begin(IsolationLevel.ReadUncommitted)) SQL profiler logs the following -- network protocol: LPC set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed Thanks Simon /* Email: [email protected] Twitter: @simonmfox Phone: +64 21 079 2900 */ On Thu, May 3, 2012 at 7:38 AM, Simon Fox <[email protected]> wrote: Yes so did, that is what makes me think that sp_executesql is not honouring the isolation level of the transaction I have wrapped my query in. When running the exact sql NH executes from a command line (wrapped in Read Uncommitted transaction) app I see the same timeout issue. However if I put nolock in to the query there is no timeout, and if I pull the query out of sp_executesql and replace the placeholders with their actual values the query runs fine as well... I'm stumped, can't find much info on sp_executesql, but I'm assuming it is the problem. /* Email: [email protected] Twitter: @simonmfox Phone: +64 21 079 2900 <tel:%2B64%2021%20079%202900> */ On Thu, May 3, 2012 at 1:56 AM, Rodrigo Silva de Andrade <[email protected]> wrote: i thought read uncommitted and nolock had the same effect On Wed, May 2, 2012 at 12:32 AM, Simon Fox <[email protected]> wrote: Hi All We have recently found that a combination of where clause parameters (which results in minimal filtering) on one of our queries is resulting in sql timeouts in our application. We are happy to do dirty reads in this situation and so I initially just wrapped the query in a ReadUncommitted transaction...but the timeouts kept happening for this specific clause. Next thing I did was write a simple little console app to execute the same query that NHibernate was generating which confirmed that it wasn't some other issue within our application or the web server. What in did make the query work without timeout was explictly adding nolock hints to the query generated by NHibernate and pumping that through my console app. So I have a couple of queries really. First a more general query about sp_executesql (as I believed and currently still do believe that nolock hints and Read Uncommitted have the same effect), does this SP use the database default transaction isolation level and even though I have wrapped the query in a Read Uncommitted transaction it is falling back to Read Committed thus causing the timeout. The query NHibernate generates is via ICriteria, I can't seem to find a way to get nolock hints added to that query. Is there a way I can get a query which includes nolock with ICriteria (without writing an interceptor)? Is there a way I can get NHibernate to issue the query outside of sp_executesql (because I believe that is ignoring my transaction isolation level)? Any other suggestions are welcomed. Thanks Simon /* Email: [email protected] Twitter: @simonmfox Phone: +64 21 079 2900 <tel:%2B64%2021%20079%202900> */ -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected] <mailto:nhusers%[email protected]> . For more options, visit this group at http://groups.google.com/group/nhusers?hl=en. -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected] <mailto:nhusers%[email protected]> . For more options, visit this group at http://groups.google.com/group/nhusers?hl=en. -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en. -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
