nolock, Read Uncommitted and sp_executesql

[Simon Fox]

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: simon...@gmail.com

Twitter: @simonmfox
Phone: +64 21 079 2900
*/

[Rodrigo Silva de Andrade]

i thought read uncommitted and nolock had the same effect

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

[Simon Fox]

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: simon...@gmail.com

Twitter: @simonmfox
Phone: +64 21 079 2900
*/

[Simon Fox]

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: simon...@gmail.com

Twitter: @simonmfox
Phone: +64 21 079 2900
*/

[TheCPUWizard]

Is it possible you have Snapshot isolation enabled?

[Simon Fox]

No, 

SELECT snapshot_isolation_state_desc from sys.databases 

where name='MyDB'

returns OFF

/*
Email: simon...@gmail.com

Twitter: @simonmfox
Phone: +64 21 079 2900
*/

[Simon Fox]

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: simon...@gmail.com

Twitter: @simonmfox
Phone: +64 21 079 2900
*/

[TheCPUWizard]

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."

[Alex Norcliffe]

I'm hitting this issue too - please could you post the source of those quotes for further reading?

Cheers

Alex

Alex Norcliffe

Lead Architect, Umbraco 5
ASPInsider
Microsoft Regional Director

@alex_norcliffe on Twitter, blogging at boxbinary.com

[Simon Fox]

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: simon...@gmail.com

Twitter: @simonmfox
Phone: +64 21 079 2900
*/

[Tyler Burd]

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

[TheCPUWizard]

They are straight from the docs…for example: http://msdn.microsoft.com/en-us/library/ms173763.aspx

 

(Of course anyone can Google the phrases to see the various documents they appear int…..)

[Simon Fox]

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: simon...@gmail.com

Twitter: @simonmfox
Phone: +64 21 079 2900
*/

[Simon Fox]

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: simon...@gmail.com

Twitter: @simonmfox
Phone: +64 21 079 2900
*/