Lets say this is the situation:
[Stored Proc 1]
BEGIN
BEGIN TRANSACTION
...
exec sp 2
COMMIT
END
Now, if SP 2 - rolls back for whatever reason, does SP 1 - commit or rollback or throw exception?
Thanks.
It is possible for the work done by SP2 to be rolled back and not loose the work done by SP1. But for this to happen, you must write your stored procedures using a very specific pattern, as described in Exception handling and nested transactions:
create procedure [usp_my_procedure_name]
as
begin
set nocount on;
declare @trancount int;
set @trancount = @@trancount;
begin try
if @trancount = 0
begin transaction
else
save transaction usp_my_procedure_name;
-- Do the actual work here
lbexit:
if @trancount = 0
commit;
end try
begin catch
declare @error int, @message varchar(4000), @xstate int;
select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
if @xstate = -1
rollback;
if @xstate = 1 and @trancount = 0
rollback
if @xstate = 1 and @trancount > 0
rollback transaction usp_my_procedure_name;
raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
end catch
end
Not all errors are recoverable, there are a number of error conditions that a transaction cannot recover from, the most obvious example being deadlock (your are notified of the deadlock exception after the transaction has already rolled back). Both SP1 and SP@ have to be written using this pattern. If you have a rogue SP, or you want to simple leverage existing stored procedures that nilly-willy issue ROLLBACK
statements then your cause is lost.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With