Can we maintain multiple transactions inside a procedure?

1.1K    Asked by JulianSpringer in SQL Server , Asked on Jan 7, 2020
Answered by Rachit Gupta

Multiple/ nested transaction can be maintained by the transaction inside a transaction, below is the sample for the statement for the same, below is the diagram which explains, if we have nested transaction how are they executed: i.e. first transaction 3 will be committed, post that transaction 2, at the end transaction 1:


Below is the example for the same

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

           ROLLBACK transaction trans2

           PRINT 'ROLLBACK trans2 done'

           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'

    END CATCH

END

Executing statemnets:

select * from input_values

go

exec usp_transaction_handle_multiple

go

select * from input_values




Your Answer

Interviews

Parent Categories