none
try catch with transaction RRS feed

  • Pergunta

  • I'm trying to learn more about this and came up with the following example. Would you please critique or tell me what the difference between SQL1 and SQL2.

    To follow this example, first please create this simple table. We will be inserting to this table.

    create table A
    (col int)

     

    Next, please try these statements. I'm generating divide by zero error. Probably very basic to you guys, but confused what the difference is and why the second one is complaining.

     

    -- SQL1
    
    begin try
    	begin tran
    		insert into A values (10/0)
    	commit tran
    end try
    begin catch
    	select error_number() as ErrorNumber, error_message() as ErrorMsg
    	print ('transaction rolled back')
    	rollback
    end catch
    
    
    -- SQL2
    
    begin tran
    	begin try
    		insert into A values (10/0)
    	end try
    	begin catch
    		select error_number() as ErrorNumber, error_message() as ErrorMsg
    		print ('transaction rolled back')
    		rollback
    	end catch
    commit tran
    
    sexta-feira, 5 de novembro de 2010 20:45

Respostas

  • > But what about what microsoft says in the link I posted. In there, they give this example.

    I don't agree with that example. The normal procedure, in my opinion is to have it as in your example 1 in your original post, that is BEGIN and COMMIT inside BEGIN TRY and END TRY.

    In fact, I would suggest that your procedures in the very most cases should run like this:

    CREATE PROCEDURE yadayada AS
    -- You may have DECLARE here, but no executable code.
    BEGIN TRY
       -- Code to execute. (Which may include BEGIN/COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
       IF @@trancount > 0 ROLLBACK TRANSACTION
       EXEC error_handler_sp @@procid
    END CATCH

    There are situations when you want to deviate from this pattern. In a procedure which is a strict sub-procedure to another procedure, you  may opt to skip TRY-CATCH. If you run a loop where each iteration is its own transaction, you proably need an inner CATCH block. But these are the exceptions. The above applies to 95% of your code at least.

    Note also that you should always include

       IF @@trancount > 0 ROLLBACK TRANSACTION

    even if your procedure does not start a transaction at all. You may be calling a procedure that does, but which does not roll back for some reason. (And yes, this can happen, even if you use TRY-CATCH.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    sexta-feira, 5 de novembro de 2010 22:11

Todas as Respostas

  • In the second query, you have an error and you are rolling back the transaction inside the catch block, so there is no transaction to commit any more. This should work. You want to commit the transaction once it succeeds.

     

    begin tran
    	begin try
    		insert into A values (10/0)
    		commit tran
    	end try
    	begin catch
    		select error_number() as ErrorNumber, error_message() as ErrorMsg
    		print ('transaction rolled back')
    		rollback
    	end catch
    

     


    Abdallah El-Chal, PMP, ITIL, MCTS
    sexta-feira, 5 de novembro de 2010 20:56
  • Thanks, I think I get it. And according to http://msdn.microsoft.com/en-us/library/ms175976.aspx I can also do this.

    begin tran
    	begin try
    		insert into A values (10/0)
    	end try
    	begin catch
    		select error_number() as ErrorNumber, error_message() as ErrorMsg
    		print ('transaction rolled back')
    		rollback
    	end catch
    if @@trancount > 0 commit tran

     

    I simply added the if condition before the commit. So, does it matter which style you use? Perhaps my example is too simple to make a difference in this case. Can someone think of an example where the style would make a difference?

    sexta-feira, 5 de novembro de 2010 21:05
  • I use the same syntax as Abdullah showed and I think this is one of the recommended practices. 

    Take a look at my blog post 

    How to insert information into multiple related tables and return ID using SQLDataSource

    and it has a link to Kevin Goff article about error handling.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    sexta-feira, 5 de novembro de 2010 21:11
    Moderador
  • But what about what microsoft says in the link I posted. In there, they give this example.

    USE AdventureWorks2008R2;
    GO
    BEGIN TRANSACTION;
    
    BEGIN TRY
     -- Generate a constraint violation error.
     DELETE FROM Production.Product
     WHERE ProductID = 980;
    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;
    
     IF @@TRANCOUNT > 0
      ROLLBACK TRANSACTION;
    END CATCH;
    
    IF @@TRANCOUNT > 0
     COMMIT TRANSACTION;
    GO
    

     

    EDIT: also, what about SQL1 in my original post where begin try comes first, rather than begin transaction.

    sexta-feira, 5 de novembro de 2010 21:20
  • I honestly wouldn't use @@trancount for a simple code like this. @@trancount increases by 1 on BEGIN TRAN, and decreases by 1 ON COMMIT and ROLLBACK. I would only include it for nested transaction so you don't have any open transactions on your server.
    Abdallah El-Chal, PMP, ITIL, MCTS
    sexta-feira, 5 de novembro de 2010 21:26
  • Thanks all for the feedbacks. Any comments on the SQL1 from my original post, where it start with BEGIN TRY, not BEGIN TRANSACTION.

    It also worked, but not sure if this is not preferred for any reason.

    sexta-feira, 5 de novembro de 2010 21:29
  • Don't use the first one. Here is an example of why you shouldn't.

    DECLARE @t TABLE(V INT)
    
    BEGIN TRY
    	BEGIN TRAN
    		INSERT INTO @t
    		VALUES(1)
    		INSERT INTO @t
    		VALUES(10/0)
    	COMMIT TRAN
    END TRY
    BEGIN CATCH
    	SELECT ERROR_NUMBER(),ERROR_MESSAGE()
    	PRINT('Transaction failed')
    	ROLLBACK TRAN
    END CATCH
    
    SELECT *
    FROM @t
    


    Abdallah El-Chal, PMP, ITIL, MCTS
    sexta-feira, 5 de novembro de 2010 21:33
  • > But what about what microsoft says in the link I posted. In there, they give this example.

    I don't agree with that example. The normal procedure, in my opinion is to have it as in your example 1 in your original post, that is BEGIN and COMMIT inside BEGIN TRY and END TRY.

    In fact, I would suggest that your procedures in the very most cases should run like this:

    CREATE PROCEDURE yadayada AS
    -- You may have DECLARE here, but no executable code.
    BEGIN TRY
       -- Code to execute. (Which may include BEGIN/COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
       IF @@trancount > 0 ROLLBACK TRANSACTION
       EXEC error_handler_sp @@procid
    END CATCH

    There are situations when you want to deviate from this pattern. In a procedure which is a strict sub-procedure to another procedure, you  may opt to skip TRY-CATCH. If you run a loop where each iteration is its own transaction, you proably need an inner CATCH block. But these are the exceptions. The above applies to 95% of your code at least.

    Note also that you should always include

       IF @@trancount > 0 ROLLBACK TRANSACTION

    even if your procedure does not start a transaction at all. You may be calling a procedure that does, but which does not roll back for some reason. (And yes, this can happen, even if you use TRY-CATCH.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    sexta-feira, 5 de novembro de 2010 22:11
  • Don't use the first one. Here is an example of why you shouldn't.


    DECLARE @t TABLE(V INT) BEGIN TRY BEGIN TRAN INSERT INTO @t VALUES(1) INSERT INTO @t VALUES(10/0) COMMIT TRAN END TRY BEGIN CATCH SELECT ERROR_NUMBER(),ERROR_MESSAGE() PRINT('Transaction failed') ROLLBACK TRAN END CATCH SELECT * FROM @t


    And what are you trying to say? The first row will be in @t, but this has nothing to do with the question at hand. Table variables are not subject to transactions, so the inserted row survives the ROLLBACK.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    sexta-feira, 5 de novembro de 2010 22:27
  • Erland,

    you are right, and that is a mistake on my end. I was trying to show an example of a single transaction that might succeed, and completely didn't pay attention that the transaction logs are not recorded for table-variables. 

     

     


    Abdallah El-Chal, PMP, ITIL, MCTS
    sexta-feira, 5 de novembro de 2010 23:25
  • Thx all for the perspectives
    segunda-feira, 8 de novembro de 2010 14:41
  • You mean because SELECT * FROM @t returns the "1" you inserted?

    Change the variable to a #TMP table and the transaction works as expected.

    CREATE TABLE #T (tcol int)

    BEGIN TRY
        BEGIN TRAN
            INSERT INTO #T
            VALUES(1)
            INSERT INTO #T
            VALUES(10/0)
        COMMIT TRAN
    END TRY
    BEGIN CATCH
        SELECT ERROR_NUMBER(),ERROR_MESSAGE()
        PRINT('Transaction failed')
        IF @@TRANCOUNT > 0 ROLLBACK TRAN
    END CATCH

    SELECT *
    FROM #t

    DROP TABLE #T


    Elisabeth Rédei | SQL Server Consultant and Architect | MCITP, MCT | http://sqlblog.com/blogs/elisabeth_redei | www.dbdirections.com | http://www.linkedin.com/elisabethredei |

    quarta-feira, 5 de fevereiro de 2020 12:10