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