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.
