Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored Procedure Insert Locking Until End of Processing Expand / Collapse
Author
Message
Posted Tuesday, September 25, 2012 9:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 4:34 PM
Points: 46, Visits: 298
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!
Post #1364412
Posted Tuesday, September 25, 2012 10:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:21 AM
Points: 6,256, Visits: 7,438
What exact version and service pack are you using? That shouldn't happen and I want to see if I can recreate your event.


- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1364429
Posted Wednesday, September 26, 2012 12:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 4:34 PM
Points: 46, Visits: 298
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
Post #1364869
Posted Wednesday, September 26, 2012 1:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:55 AM
Points: 7,156, Visits: 15,246
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?
Post #1364899
Posted Thursday, September 27, 2012 12:49 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:21 AM
Points: 6,256, Visits: 7,438
+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?


- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1365455
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse