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?
 
chapmandewConnect With a Mentor Commented:
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
 
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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
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
All Courses

From novice to tech pro — start learning today.