Best Answer · By JanBask MS SQL Server Expert
Rachit Gupta JanBask Expert
Answered on Jan 28, 2020
In case there is an exception in the inner transaction then all the transactions which outer to that will get rollback.
i.e. if we have transaction1 inside that we have transaction 2 and after that we have transaction 3, and then transaction 3 gets committed after that some statement in transaction 2 fails then transaction 3 will remains as committed as it is already been committed, and the catch part for transaction 2 and transaction 3 is executed, same is explained via below diagram
create PROC usp_transaction_handle_multiple
AS
BEGIN
BEGIN try
BEGIN transaction trans1
INSERT INTO input_values
SELECT 'One', 'Two'
BEGIN try
BEGIN transaction trans2
IF (( SELECT count(1) FROM input_values where col1='One') < 2>
BEGIN
UPDATE input_values SET col2 ='updated two' WHERE col1='One'
COMMIT transaction trans1
PRINT 'Commit trans2 done'
END
ELSE
BEGIN
RAISERROR('No data to update',16,1)
END
END try
BEGIN CATCH
PRINT 'ROLLBACK trans2 done in inner-block'
ROLLBACK transaction trans2
END CATCH
IF (@@TRANCOUNT <> 0)
BEGIN
COMMIT transaction trans1
PRINT 'Commit trans1 done'
END
END try
BEGIN CATCH
ROLLBACK transaction trans1
PRINT 'ROLLBACK trans1 done in Outer-block'
END CATCH
END
Executing statements:
select * from input_values
go
exec usp_transaction_handle_multiple
go
select * from input_values
create PROC usp_transaction_handle_multiple
AS
BEGIN
BEGIN try
BEGIN transaction trans1
INSERT INTO input_values
SELECT 'One', 'Two'
BEGIN try
BEGIN transaction trans2
IF (( SELECT count(1) FROM input_values where col1='One') < 2>
BEGIN
UPDATE input_values SET col2 ='updated two' WHERE col1='One'
COMMIT transaction trans1
PRINT 'Commit trans2 done'
END
ELSE
BEGIN
RAISERROR('No data to update',16,1)
END
END try
BEGIN CATCH
PRINT 'ROLLBACK trans2 done in inner-block'
ROLLBACK transaction trans2
END CATCH
IF (@@TRANCOUNT <> 0)
BEGIN
COMMIT transaction trans1
PRINT 'Commit trans1 done'
END
END try
BEGIN CATCH
ROLLBACK transaction trans1
PRINT 'ROLLBACK trans1 done in Outer-block'
END CATCH
END
Executing statements:
select * from input_values
go
exec usp_transaction_handle_multiple
go
select * from input_values
create PROC usp_transaction_handle_multiple
AS
BEGIN
BEGIN try
BEGIN transaction trans1
INSERT INTO input_values
SELECT 'One', 'Two'
BEGIN try
BEGIN transaction trans2
IF (( SELECT count(1) FROM input_values where col1='One') < 2>
BEGIN
UPDATE input_values SET col2 ='updated two' WHERE col1='One'
COMMIT transaction trans1
PRINT 'Commit trans2 done'
END
ELSE
BEGIN
RAISERROR('No data to update',16,1)
END
END try
BEGIN CATCH
PRINT 'ROLLBACK trans2 done in inner-block'
ROLLBACK transaction trans2
END CATCH
IF (@@TRANCOUNT <> 0)
BEGIN
COMMIT transaction trans1
PRINT 'Commit trans1 done'
END
END try
BEGIN CATCH
ROLLBACK transaction trans1
PRINT 'ROLLBACK trans1 done in Outer-block'
END CATCH
END
Executing statements:
select * from input_values
go
exec usp_transaction_handle_multiple
go
select * from input_values
