Yes source of those quotes would be great please... Still not sure thats my issue, I'm not changing the isolation level WITHIN a transaction, rather consecutive transactions use different levels.
/* Email: [email protected] Twitter: @simonmfox Phone: +64 21 079 2900 */ On Fri, May 4, 2012 at 1:34 AM, TheCPUWizard <[email protected]> wrote: > These factors could be involved:**** > > ** ** > > "Only one of the isolation level options can be set at a time, and it > remains set for that connection until it is explicitly changed."**** > > "With one exception, you can switch from one isolation level to another at > any time during a transaction."**** > > "When you change a transaction from one isolation level to another, > resources that are read after the change are protected according to the > rules of the new level. "**** > > "If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or > trigger, when the object returns control the isolation level is reset to > the level in effect when the object was invoked."**** > > ** ** > > ** ** > > *From:* [email protected] [mailto:[email protected]] *On > Behalf Of *Simon Fox > *Sent:* Wednesday, May 02, 2012 9:13 PM > > *To:* [email protected] > *Subject:* Re: [nhusers] nolock, Read Uncommitted and sp_executesql**** > > ** ** > > The transaction in question is the second I have created from the session, > with the previous transaction using ReadCommitted (not specified, i.e. I > use _session.BeginTransaction() and ReadCommitted is default). I have > assumed this won't have an effect??**** > > > /* > Email: [email protected]**** > > Twitter: @simonmfox > Phone: +64 21 079 2900 > */**** > > > > **** > > On Thu, May 3, 2012 at 4:10 PM, Simon Fox <[email protected]> wrote:** > ** > > 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.**** > > -- > 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.
