TSQL Try / Catch within Transaction or vice versa?

Question

I'm writing a script that will delete records from a number of tables, but before it deletes it must return a count for a user to confirm before committing.

This is a summary of the script.

BEGIN TRANSACTION SCHEDULEDELETE
    BEGIN TRY
        DELETE   -- delete commands full SQL cut out
        DELETE   -- delete commands full SQL cut out
        DELETE   -- delete commands full SQL cut out
        PRINT 'X rows deleted. Please commit or rollback.' --calculation cut out.
    END TRY
    BEGIN CATCH 
        SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage

            ROLLBACK TRANSACTION SCHEDULEDELETE
            PRINT 'Error detected, all changes reversed.'
    END CATCH

--COMMIT TRANSACTION SCHEDULEDELETE --Run this if count correct.

--ROLLBACK TRANSACTION SCHEDULEDELETE --Run this if there is any doubt whatsoever.

This is my first time writing transaction, is it correct/best practice to have the TRY/CATCH block inside the transaction or should the transaction be inside the TRY block?

The important factor in this script is that the user must manually commit the transaction.

Solution 1

Only open a transaction once you are inside the TRY block and just before the actual statement, and commit it straightaway. Do not wait for your control to go to the end of the batch to commit your transactions.

If something goes wrong while you are in the TRY block and you have opened a transaction, the control will jump to the CATCH block. Simply rollback your transaction there and do other error handling as required.

I have added a little check for any open transaction using @@TRANCOUNT function before actually rolling back the transaction. It doesn't really make much sense in this scenario. It is more useful when you are doing some validations checks in your TRY block before you open a transaction like checking param values and other stuff and raising error in the TRY block if any of the validation checks fail. In that case, the control will jump to the CATCH block without even opening a transaction. There you can check for any open transaction and rollback if there are any open ones. In your case, you really don't need to check for any open transaction as you will not enter the CATCH block unless something goes wrong inside your transaction.

Do not ask after you have executed the DELETE operation whether it needs to be committed or rolled back; do all these validation before opening the transaction. Once a transaction is opened, commit it straightaway and in case of any errors, do error handling (you are doing a good job by getting detailed info by using almost all of the error functions).

BEGIN TRY

  BEGIN TRANSACTION SCHEDULEDELETE
    DELETE   -- delete commands full SQL cut out
    DELETE   -- delete commands full SQL cut out
    DELETE   -- delete commands full SQL cut out
 COMMIT TRANSACTION SCHEDULEDELETE
    PRINT 'X rows deleted. Operation Successful Tara.' --calculation cut out.
END TRY

BEGIN CATCH 
  IF (@@TRANCOUNT > 0)
   BEGIN
      ROLLBACK TRANSACTION SCHEDULEDELETE
      PRINT 'Error detected, all changes reversed'
   END 
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage
END CATCH

Solution 2

In addition to the good advice by M.Ali and dean above, a little bit help for those looking to use the new(er) TRY CATCH THROW paradigm in SQL SERVER:

(I couldn't easily find the complete syntax, so adding it here)

GIST : HERE

Sample stored procedure code here (from my gist):

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROC [dbo].[pr_ins_test]
@CompanyID INT
AS
 
SET NOCOUNT ON
 
BEGIN
 
    DECLARE @PreviousConfigID INT
    
    BEGIN TRY
        BEGIN TRANSACTION MYTRAN; -- Give the transaction a name
        SELECT 1/0  -- Generates divide by zero error causing control to jump into catch
 
        PRINT '>> COMMITING'
        COMMIT TRANSACTION MYTRAN;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN 
            PRINT '>> ROLLING BACK'
            ROLLBACK TRANSACTION MYTRAN; -- The semi-colon is required (at least in SQL 2012)
            
            
        END; -- I had to put a semicolon to avoid error near THROW
        THROW
    END CATCH
END

Solution 3

Never wait for an end user to commit the transaction, unless it's a single-user mode database.

In short, it's about blocking. Your transaction will take some exclusive locks on resources being updated, and will hold on to those lock untill the transaction is ended (committed or rolled back). Nobody will be able to touch those rows. There are some different problems if snapshot isolation is used with version store cleanup.

Better to first issue a select query to determine a number of qualifying rows, present this to the end user, and after he confirms do the actual delete.

Comments

[Mark G]

Just an observation - The THROW should be outside of the BEGIN/END so it throws regardless of a transaction being open.

[Sudhanshu Mishra]

@MarkG not sure I understand, if the THROW is outside of BEGIN/END Catch, executing this stored proc would always throw an exception, unless you follow the commit tran with a return statement. As the excerpt stands now, just moving the throw outside would be useless

[BrianB]

I suspect @MarkG is saying that the THROW should be after the END but before the END CATCH. This would ensure that the exception is thrown irrespective of @@TRANCOUNT.

[Rich]

I tried to add the THROW statement within a database project in Visual studio and get an error.

[Dai]

I know transactions have been supported by SQL Server for decades, but TRY/CATCH was only recently added - so how was BEGIN TRANSACTION + COMMIT + ROLLBACK supposed to be implemented before TRY/CATCH was added to SQL Server?

[Anton Shepelev]

There is no reason at all to open a transaction insde the TRY block. In fact, opening it immediately before the TRY is the correct structure.

[Anton Shepelev]

This is the right solution. But make sure to remember the rows eligible for deletion and delete pricesely them, instead of whatever happens to be there when the user confirms the operation.

[M.Ali]

@Dai TRY/CATCH has been in SQL Server since 2005, and before SQL Server 2005 (i.e. SQL Sever 2000 and older versions) there were a looooot of things we couldnt do, or there were very convoluded ways of doing the simplest things. So I hope you are not on SQL Server 2000 :) . Just so you know before 2005 we used @@Error function after each statement to check whether the statement errored out.

[M.Ali]

@Ant_222 would you like to expand on your claim please? I have provided reasons in detail for the claims I have made. You have just made a statement but haven't provided any reason why your suggested method is the right way to do.

[Anton Shepelev]

The reason for keeping BEGIN TRAN outside the TRY blolck is simple: if BEGIN TRAN fails, you do not need to ROLLBACK. Now, I should like to hear your reason for putting BEGIN TRAN inside the TRY block (I do not see one in your answer). What may go wrong with BEGIN TRAN outside?

[M.Ali]

@Ant_222 you are talking abosulote bollocks unfortunately, if you open a transaction outside of try block and do not rollback inside catch block you would leave an open transaction. You will always have to rollback or commit a transaction once you have opened it, your claim that if a transaction fails you would not need to rollback is utter BS sorry to say mate. As far as my reason to open a transaction inside try block, read the 3rd paragraph in my answer carefully. (Additional validation checks before you open a transaction) hint hint

[M.Ali]

@Ant_222 its good to have opinions but opinions based on just opinion are mostly nonsense, I suggest you do some reading on how transactions work in SQL Server and maybe then we can have a bit more intellectual debate have a read of this please [SQL Server Transaction](https://docs.microsoft.com/en-us/sql/t-sql/lang‌​uage-elements/transa‌​ctions-transact-sql?‌​view=sql-server-ver1‌​5)

[Anton Shepelev]

Watch your language, Ali. If BEGIN TRAN fails (I did not write if a transaction fails!) then there is nothing to ROLLBACK and everying is fine. If, on the other hand, BEGIN TRAN succeeds, we enter the TRY block and thus guarrantee either to ROLLBACK or to COMMIT the successfully started transaction. So you are wrong. Your test of @@TRANCOUNT is just a workaround for the wrongly placed BEGIN TRAN. Place it before TRY and you shan't need it. If you insist that my approach is wrong, post a small T-SQL script breaks it. OK?

[M.Ali]

@Ant_222 again you are making outrageous assumptions, how and when a Begin TRAN would fail? It never fails it’s the t-sql statement inside a transaction that can fail. Can you provide an example and make begin TRAN fail?

[Anton Shepelev]

If BEGIN TRAN cannot fail, there is even less reason to put it insde the TRY block. TRY blocks are for stuff that can fail.

[M.Ali]

@Ant_222 so now you know that BEGIN TRAN can never fail, which you put forward as the main reason why you should put outside of try block, now what reason do you have to put it outside of the try block, you know my reason for putting inside the try block but you havent given a single valid reason that why we should put outside of the try block?

[Anton Shepelev]

No, I do not know your justification for putting BEGIN TRAN inside the TRY block, because you have not provided any. You said my approach was wrong and I asked you to demonstrate how it can be broken. Here is what I consider the correct pattern for handling a transaction in T-SQL. Can you break it? If not, then please take back your harsh words. The correctness of my approach is indefferent to whether BEGIN TRAN may fail or not. The documentation does not tell it, by the way.

[M.Ali]

@Ant_222 I asked you to read the 3rd paragraph from answer, the reason is there anway I will repeat myself here again. The reason you want to put begin tran inside a try block is, usually you will have some validation checks like checking values passed in params and other sorts of validations checks, if any of the checks fail, you raise an error and the control jumps to catch block without even openning any trasaction, if all the validation passes only then you open a transaction and commit it straight after the statement execution finishes. Keeping the transaction time as short as possible.

[M.Ali]

@Ant_222 looking at the code you have provided , you have put a rollback in the catch block contrary to your claims only a few comments ago :) everyday is a school day :)

[Anton Shepelev]

I agree that performing non-destructive checks outside a transaction to keep its lifetime shorter is very important, but I did not realise you proposed to singnal failed checks by RAISEing errors and CATCHing them. Now I see your point. But that would be (ab)using the same TRY..CATCH block for two purposes: terminating a transaction and reporting failed preconditions, which I never considered. I prefer each TRY to have its own purpose, to keep the code clearer. And I never proposed to put ROLLBACK outside the CATCH block.

[Morvael]

@M.Ali and @Ant_22, This was a very confrontational discussion and I think both ways of doing it have merit. My gut instinct was to put the TRAN outside of the TRY \ CATCH and after reading your whole discussion I feel that it technically doesn't matter(?) so I'm going to stick with TRAN -> TRY as it makes more sence in my head. It just doesn't smell right to be handling the ROLLBACK outside of the block that created the TRAN.

[dyslexicanaboko]

The way I deal with the semi-colon regarding THROW is I just write ;THROW; - whatever works. I just like doing it this way so I don't forget to add it.

[variable]

docs.microsoft.com/en-us/sql/t-sql/language-elements/… - Microsoft recommends to have TRANSACTION -> TRY ?

[variable]

docs.microsoft.com/en-us/sql/t-sql/language-elements/… - Microsoft recommends to have TRANSACTION -> TRY ?

[sbitaxi]

@variable thank you for that, I was looking for something like this. Microsoft appears to recommend TRANS -> TRY, but under item C, they open they BEGIN TRY -> BEGIN TRANS so it doesn't look like we're any closer. docs.microsoft.com/en-us/sql/t-sql/language-elements/…