No, SELECT snapshot_isolation_state_desc from sys.databases where name='MyDB'
returns OFF /* Email: [email protected] Twitter: @simonmfox Phone: +64 21 079 2900 */ On Thu, May 3, 2012 at 4:01 PM, TheCPUWizard <[email protected]> wrote: > 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 > */**** > > > > **** > > 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.**** > > -- > 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.
