September 23, 2015 at 2:29 am
Hi
I was trying to create stored proc
but the query was continuously blocking the query below
updatetable1
setisarchived = 1,
modtime = getdate()
whereid = @id
andisarchived = 0
I was not sure, why the create procedure statement is blocking the update statement.Please help
September 23, 2015 at 6:50 am
CREATE PROCEDURE, and nothing else, should not be placing locks on tables. Further, CREATE PROCEDURE, and nothing else, should complete on most systems in an extremely fast fashion.
Are you certain that what you're seeing is the act of running CREATE PROCEDURE, or, are you looking at the blocking chain and you're seeing a session that is calling the procedure with the definition that you're providing? I'm betting it's that second thing.
If so, the query looks to be just doing an UPDATE and then an INSERT for one table. Those should, under normal circumstances, complete very quickly. Are they in turn being blocked by some other process? Is it possible that you have the query call wrapped in a procedure without a COMMIT statement so that it's holding locks? Additional information is needed. Look to sys.dm_exec_requests to see what is blocking your procedure call or, to see what it's last wait time is.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply