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.
