I encountered this not long ago when trying to debug some deadlocks. SQL Profiler ONLY logs the default isolation level of a connection when it is established (the Logon event occurs). It will NOT correctly log isolation levels of a transaction that started after the connection was opened. Most connections default to read committed, so that's why you are seeing that. Setting a new isolation level in a transaction after the Logon event occurs will not be logged correctly.
Here are some resources: http://connect.microsoft.com/SQLServer/feedback/details/373788/trace-profiler-shows-incorrect-isolation-level http://stackoverflow.com/questions/3911285/linq-transactionscope-will-not-change-isolation-level-in-sql-server-profiler From: [email protected] [mailto:[email protected]] On Behalf Of Simon Fox Sent: Thursday, May 03, 2012 12:30 PM To: [email protected] Subject: Re: [nhusers] nolock, Read Uncommitted and sp_executesql 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]<mailto:[email protected]> Twitter: @simonmfox Phone: +64 21 079 2900 */ On Fri, May 4, 2012 at 1:34 AM, TheCPUWizard <[email protected]<mailto:[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]> [mailto:[email protected]<mailto:[email protected]>] On Behalf Of Simon Fox Sent: Wednesday, May 02, 2012 9:13 PM To: [email protected]<mailto:[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]<mailto:[email protected]> Twitter: @simonmfox Phone: +64 21 079 2900<tel:%2B64%2021%20079%202900> */ On Thu, May 3, 2012 at 4:10 PM, Simon Fox <[email protected]<mailto:[email protected]>> wrote: No, SELECT snapshot_isolation_state_desc from sys.databases where name='MyDB' returns OFF /* Email: [email protected]<mailto:[email protected]> Twitter: @simonmfox Phone: +64 21 079 2900<tel:%2B64%2021%20079%202900> */ On Thu, May 3, 2012 at 4:01 PM, TheCPUWizard <[email protected]<mailto:[email protected]>> wrote: Is it possible you have Snapshot isolation enabled? From: [email protected]<mailto:[email protected]> [mailto:[email protected]<mailto:[email protected]>] On Behalf Of Simon Fox Sent: Wednesday, May 02, 2012 8:17 PM To: [email protected]<mailto:[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]<mailto:[email protected]> Twitter: @simonmfox Phone: +64 21 079 2900<tel:%2B64%2021%20079%202900> */ On Thu, May 3, 2012 at 7:38 AM, Simon Fox <[email protected]<mailto:[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]<mailto:[email protected]> Twitter: @simonmfox Phone: +64 21 079 2900<tel:%2B64%2021%20079%202900> */ On Thu, May 3, 2012 at 1:56 AM, Rodrigo Silva de Andrade <[email protected]<mailto:[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]<mailto:[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]<mailto:[email protected]> Twitter: @simonmfox Phone: +64 21 079 2900<tel:%2B64%2021%20079%202900> */ -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]<mailto:[email protected]>. To unsubscribe from this group, send email to [email protected]<mailto:nhusers%[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]<mailto:[email protected]>. To unsubscribe from this group, send email to [email protected]<mailto:nhusers%[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]<mailto:[email protected]>. To unsubscribe from this group, send email to [email protected]<mailto:[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]<mailto:[email protected]>. To unsubscribe from this group, send email to [email protected]<mailto:nhusers%[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]<mailto:[email protected]>. To unsubscribe from this group, send email to [email protected]<mailto:[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]<mailto:[email protected]>. To unsubscribe from this group, send email to [email protected]<mailto:nhusers%[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]<mailto:[email protected]>. To unsubscribe from this group, send email to [email protected]<mailto:[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.
