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 */ 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 >> */ >> >> -- >> 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. > -- 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.
