September 25, 2012 at 9:48 pm
Hi, I have been trying to get the stored proc below to not lock dbo.some_table during the waitfor delay portion. I am delaying below just to test. Usually lots of other code may go there. However, others may need to select * from dbo.some_table concurrently. So I want the stored proc to commit the insert before moving on. I have tried wrapping in a tran and using exec().
If I run the code below, then in another tab, select * from dbo.some_table will lock until waitfor delay is finished.
create procedure p_test
(
@variable int = null
)
as
begin tran
insert into dbo.some_table(col1, col2) values('', '');
commit tran
waitfor delay '02:00:00';
go
Thanks for any help!
September 25, 2012 at 10:33 pm
What exact version and service pack are you using? That shouldn't happen and I want to see if I can recreate your event.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 26, 2012 at 12:06 pm
Hi Craig,
Thank you for your interest --- it turns out replicating actually involves a little bit more work. The insert procedure is called by another and it only blocks when called by the other proc. Here's the code (and version info as well):
create table dbo.test(
id int
);
go
create procedure [p_test]
as
begin tran
insert into dbo.test(id) values (1);
commit tran
waitfor delay '02:00:00';
select 1;
go
create procedure [dbo].[p_call_p_test]
as
create table #temp_table(
blah sysname
);
insert into #temp_table (blah)
exec p_test;
go
Now calling "exec dbo.p_call_p_test" in one tab and in another "select * from dbo.test" will result in blocking until the called proc is canceled or 2 hrs later when the delay is finished. I have noticed that only when the caller procedure is inserting into the temp table the results from p_test does this occur. So that insert into the temp table is perhaps causing all objects in p_test to lock.
@@version:
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)
Jul 9 2008 14:17:44
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)
Thank you
September 26, 2012 at 1:08 pm
It makes sense from an ACID point of view.
Since you're calling the stored proc as part of an INSERT statement, the consequences of the procedure being called should only be committed if the INSERT finishes (which includes the insert statement). if the INSERT were to fail, you'd need the DB to be put back to the state before the EXEC occurred, so the engine has to put a lock on anything being changed as part of the exec call.
the fact that the output from the proc comes some something entirely different is just a red herring.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 27, 2012 at 12:49 pm
+1 to what Matt provided. INSERT INTO opens an implicit transaction, and then anything ran under that ends up in the transaction. What's the default isolation level on your system?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply