Sql Server – Update Statement is locking the table when executed immediately after Rollback Transaction

I have used transaction with try-catch block in my procedure. If any exception occurs, I am rolling back the transaction and after that trying to update a Results (Status) table. But, it looks like the Update statement is locking table (The Select query on that table is not working after that).

Could you please let me know what is the reason of this behavior?

Here, is my sample code:

ALTER PROCEDURE Test_Tran
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION
            SAVE TRANSACTION T1;
            SELECT 1 / 0;
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION T1;
        UPDATE [DI].[BOMLC_AMPL_DI_Async_Exec_Results]
            SET
                [End_Time] = GETDATE(),
                [Execution_Status] = 'FAILED',
                [Error_Number] = 16,
                [Error_Message] = 'Exception occurred while processing: '
                                    + ERROR_MESSAGE(),

                [Last_Updated_On] = GETDATE()

            WHERE [Token_ID] = 52;
        PRINT('Something went wrong');
    END CATCH

END
GO

EXEC Test_Tran;

Answer

Add COMMIT or ROLLBACK after UPDATE statement. Or do ROLLBACK TRANSACTION instead of ROLLBACK TRANSACTION T1.

Explanation:

The savepoint causes the problem. When an error occurs, you are rolling back to the savepoint, but the transaction is still open. You also get an error on trancount mismatch. The update is done within a transaction which is never committed hence the locking. When the procedure ends, the transaction is still open.