SQL Transaction Isolation Level Scope

I have a stored procedure which builds a report.  It calls several other stored procedures to build the report. These procs have READ UNCOMMITTED in them, however the parent proc does not have any explicit isolation set, so the default is READ COMMITTED.  We are still seeing significant blocking from the report as a whole.  Does the report as a whole operate with isolation level READ COMMITTED since the parent proc has nothing set?
tonypatton21Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
From books online:

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. All read operations performed within the transaction operate under the rules for the specified isolation level unless a table hint in the FROM clause of a statement specifies different locking or versioning behavior for a table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tonypatton21Author Commented:
Interpreting that...setting isolation level in the child procs has no effect if the caller proc is operating under READ COMMITTED by default?
0
chapmandewCommented:
That is correct....if they are in a transaction that spans all of the procs, the locks should be help by the read committed initial proc.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

tonypatton21Author Commented:
Well, there is no explicit transaction handling or isolation level set in the parent proc. Yesterday, some read uncommitted levels were set in the child procs, which seemed to reduce blocking a bit and reduce cpu, but it is still producing some blocking. I'm probably going to put read uncommitted in the parent proc because it's not a big deal to have dirty reads for this report. I'm just trying to understand what is happening here and what I'm hearing today is contrary to what we're seeing.  
0
chapmandewCommented:
Are any of the procs updating/inserting/deleting data?
0
tonypatton21Author Commented:
One of procs does some updates on local work tables used to build the report, but not on the tables that actually have the data being reported on.
0
chapmandewCommented:
try setting the isolation level in the initial proc to see if it helps.  

where are you seeing these "locks"?
0
tonypatton21Author Commented:
The report is holding records and blocking the application.
0
chapmandewCommented:
hmmm....If you're not updating anything, then it can't be locking the app....especially if it has returned already.  

Can you tell what objects are being locked through sp_lock?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.