Stored Procedure Locking
-
I am having some problems related to locking of Stored Procedures.
I am using nested procedures where there are two procedures. SP1 and SP2.
The code flows something like this
------SP1
Create a #temp_table
Exec SP2
Select * from #temp_table
----SP2
This procedure Inserts data into #temp_table created in SP1.
Problem: SP2 gets locked when SP1 starts Executing SP2. The problem is aggravated when multiple users hit SP1.
SP2 does not have any dynamic SQL and based on what I have seen in the Profiler, SP2 does not get recompiled everytime it is run.
Is there any way I can avoid such locks.
Any help would be really appreciated.
Thanks,
Abhinav
-
>>SP2 gets locked when SP1 starts Executing SP2<<
SP2 Must be locked by something else than SP1 otherwise if you are READING #Temp_Table Try:
Select * from #Temp_Table with (noLock)
I have use this before successfully!
>>The problem is aggravated when multiple users hit SP1. <<
This kind of confirms my suspicion because Temp Tables are Session Dependent so each user has its own copy. Therefore no reason for multiple user lock escalation!
* Noel -
That didn't help
The SP2 is being locked when it is being executed from SP1. The Locks are released as soon as it completes executing SP2 and before it goes to the next statement in SP1.
Any more ideas??
-
What exactly is being locked ? Which table ? What does sp_lock and sp_who2 show you in terms of locking & blocking.
Is it a #temp table, or a ##temp table ?
-
Can you post the code on those procs?
It will be a wild guess to figure it out without further information
* Noel -
No table is getting locked. The Stored Procedure is being locked down. Based on a query joining syslocks and sysprocesses I can see that the exclusive locks are placed on the SP2. So if there is more than one concurrent hit, the second request has to wait on the first request to complete.
I am using local temp tables (i.e. #temp_table)
Help plz?
-
Once again:
Can you post the code?
* Noel -
The lock hint against a table won't help against the [COMPILE] lock being generated on the stored procedure. You are right that it is an exclusive lock which serializes access through the stored procedure. One thing you can attempt to use is option (KEEP PLAN). It won't entirely stop stored procedure recompilations, but it may reduce them. More here:
Troubleshooting stored procedure recompilation (243586)
K. Brian Kelley
@kbriankelley -
Based on the Profiler, the SP2 is not being recompiled. So, using the KEEP PLAN option did not help either
Here is the complete code. Please help
ALTER procedure SP1
@hdr_mst_id as int, @date as smalldatetime,@uom_cd as varchar(4) = 'BBL'
as
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
create table #TMP_PARAM(
FLD_NM varchar(50),
FLD_VAL varchar(50)
)
insert into #tmp_param(fld_nm, fld_val) values ('HDR_MST_ID',@hdr_mst_id)
insert into #tmp_param(fld_nm, fld_val) values ('DATE',@date)
insert into #tmp_param(fld_nm, fld_val) values ('QRY_FLG','SUMMARY')
SELECT *
INTO #temp_table
FROM HDRGL_DATA
exec SP2
--SP2 uses #tmp_param to get the parameters.
SELECT
*
FROM #temp_table with (nolock)
DROP TABLE #temp_table
DROP TABLE #TMP_PARAM
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-
Your Locks are caused by SELECT INTO!
Change it to
Create Table #temp_table(Fld1,Fld2) Select Fld1,Fld2...From HDRGL_DATA
* Noel -
That didn't help either
Any other ideas....plzzz
Thanks,
Abhinav
-
Can you post what you have so far?
* Noel
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply