By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,576 Members | 1,399 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,576 IT Pros & Developers. It's quick & easy.

deadlocks in sub-procedures - does the main procedure continue exiting

P: 1
In SQL Server 2008 R2, I have a stored procedure X, which execs 2 other stored procedures:

create procedure X
begin
exec A
exec B
end

Stored procedure A has a transaction in it

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
begin transaction
insert into .....
set @ReadingID = (select SCOPE_IDENTITY())
commit transaction

If stored procedure A fails with a deadlock, does stored procedure B get executed after the user has acknowledged the deadlock message, or is stored procedure X aborted?

I had expected X to be aborted, but my live data indicates that B may have been run after A has failed - or at least that's one explanation of the data - or at least the users description of what appeared to happen.
Sep 23 '13 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,430
Sounds like you already answered your own question. If you want it to all to fail, then you need to code for it to look at the return value of A before running B.
Sep 23 '13 #2

Post your reply

Sign in to post your reply or Sign up for a free account.