OK so moral of the story is don't believe what you see in SQL Profiler.
As a test I added the following line to each of my transactions:
var res = _session.CreateSQLQuery("DBCC useroptions").List();
and the isolation level returned was what I expected.
/*
Email: [email protected]
Twitter: @simonmfox
Phone: +64 21 079 2900
*/
On Fri, May 4, 2012 at 7:57 AM, Simon Fox <[email protected]> wrote:
> Thanks Tyler, I now see the actual BEGIN TRANSACTION and COMMIT
> TRANSACTION calls, but I am still not seeing the isolation level being set
> to line up with the type I specify in my NH tran.
>
> I'm a little more worried now, as I do see the isolation level set to READ
> UNCOMMITTED at one point but it is when I start the first transaction using:
>
> using (var transaction = _session.BeginTransaction())
>
> the profiler logs:
>
> -- 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 uncommitted*
> *
> *
> *BEGIN TRANSACTION *
> *
> *
> after that transaction has been committed and disposed, I start the second
> transaction like:
>
> using (var transaction = _session.BeginTransaction(*
> IsolationLevel.ReadUncommitted*))
>
> and the profiler logs
>
> -- 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*
> *
> *
> *BEGIN TRANSACTION*
> *
> *
> Nowhere do I change the NH default of read committed to uncommitted, I am
> using SQL Server 2008 R2 and it has default isolation level of read
> committed.
>
> This makes absolutely no sense to me, what am I missing?
>
> /*
> Email: [email protected]
> Twitter: @simonmfox
> Phone: +64 21 079 2900
> */
>
>
>
> On Fri, May 4, 2012 at 7:06 AM, Tyler Burd <[email protected]> wrote:
>
>> 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]****
>>
>> 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.****
>>
>> --
>> 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.
