September 21, 2016 at 8:14 am
Dear all,
I have this stored procedure that has inside:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
The goal is to decrease the isolation level to the lowest possible. It seems to be working fine. but what about the stored procedures that are called from inside others? i.e
create SP1 Name1 ()
as
begin
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
exec SP_2
end
In the above situation the SP2 does not have a direct mention inside is code to the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED but because it is called from inside a SP that has this SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED will it also run with this isolation level?
thanks
September 21, 2016 at 8:19 am
Yes, it'll also run with that isolation level.
I'll give you the same warning as last time. Be very, very careful about read uncommitted. While it makes the symptoms of poorly written queries & bad indexing go away (in that there's less blocking and deadlocks), the tradeoff you're making is less data accuracy.
Are your users aware and happy that their reports and screens may now show incorrect information? (duplicated rows, missing rows). Are they aware that any total, any count, any list may be wrong?
Data errors can cost your company millions or open them to criminal charges in some situations.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 21, 2016 at 8:20 am
river1 (9/21/2016)
Dear all,I have this stored procedure that has inside:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
The goal is to decrease the isolation level to the lowest possible. It seems to be working fine. but what about the stored procedures that are called from inside others? i.e
create SP1 Name1 ()
as
begin
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
exec SP_2
end
In the above situation the SP2 does not have a direct mention inside is code to the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED but because it is called from inside a SP that has this SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED will it also run with this isolation level?
thanks
Why do you want this level? Are you ok with missing and/or duplicate rows which can cause errors that are impossible to reproduce? When you read uncommitted it is like tossing a NOLOCK hint on every single query. Sometimes (albeit very rarely), this is ok. But most time I see people asking about this they have no idea the real ramifications. There are many article discussing this but this one covers most of the issues pretty well.
http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/%5B/url%5D
As for an actual answer, this is easy to test. Create two nested procedures where the outer one set your isolation level and the inner one selects data from a table. Then in a second window begin a transaction, update the table being select in Proc2. Now go back to the first window and run your outer proc. What is returned from the inner proc? Does it hang and wait or does it return the uncommitted data from your open transaction? I know the answer but want you to do the testing as it is pretty easy to setup.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply