locked
catch block not reached in order to rollback transaction RRS feed

  • Question

  • HI,

    I found a situation where a catch block is not reached and my trancount remains higher than 0 which means potential deadlocks (if session isn't closed or cleaned with sp_reset_connection like in connection pooling). questions follow after example:


    --rollback
    create proc my_proc

    as
    begin try
    create table #tmp1 (a int)
    create table #tmp2 (a int)
    begin tran
    select a from #tmp1 cross join #tmp2
    print 'before commit'
    commit
    end try
    begin catch
     print 'before rollback'
     rollback
    end catch

    --output of proc (rollback is not reached)
    /*
    Msg 209, Level 16, State 1, Procedure my_proc, Line 10
    Ambiguous column name 'a'.
    Msg 266, Level 16, State 2, Procedure my_proc, Line 10
    Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 2.
    */

     

    Before I start my questions - I know the difference between a statement terminating error and a batch terminating error. But since both are caught in the catch block - this error is somewhat different.... 

    So my questions are:

    1. Is there a way to avoid the above scenario?  (my stored proc is simple here but if real tables are involved as well as the temp tables this causes deadlocks and blocks).

    2. While I would like to solve this problem in the database, there is another issue to consider here, my stored proc (and the one above isn't the actual stored proc where real tables are involved) is called from a service in a loop (every interval of time). This service is keeping its connection always open (not using connection pooling and closing connection after each call). Is such a thing acceptable? Should the developer do something different and not just me? (btw - an old RPC API is used and I'm not sure if connection polling is enabled there).

    3. I know another solution is not to have bugs:-), but it doesn't look good to me that there are situations where the catch block is not reached (I thought that errors are either batch terminating or statement terminating and both are caught in the catch)...

    Thanks in advance,

    Dror

    Tuesday, January 31, 2012 5:29 PM

Answers

  • This error is not caught in the CATCH block, because it's a compilation error. Yes, it still happens at run-time. Yes, I think Microsoft should fix this.

    Here is a Connect item to vote for, but Microsoft has closed it as Won't Fix:
    http://connect.microsoft.com/SQLServer/feedback/details/496758/try-catch-should-capture-the-parse-errors

    As SergNL, you can catch the error in an outer procedure, but that is not really practical.

    As for you question 2, the normal these days is to open-query-close, but if the service is using the same connection throughout and is always using it, there is on major harm.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by KJian_ Tuesday, February 7, 2012 2:02 AM
    Tuesday, January 31, 2012 11:21 PM

All replies

  • 1.Consider adding one more level of procedures

    drop proc errproc;
    go
    drop table #tmp1 
    go
    drop table #tmp2
    go 
    create proc  errproc 
    as
      begin
        select a from #tmp1 cross join #tmp2
      end
    go
    
    begin try
     create table #tmp1 (a int)
     create table #tmp2 (a int)
     begin tran
    
     exec errproc;
    
     print 'before commit'
     commit
     end try
     begin catch
      print 'before rollback'
      rollback
     end catch
     
    
    

    Some exceptions  (including object resolution error) are not caught on the current level but passed to the upper level where you can catch them.


    Serg
    Tuesday, January 31, 2012 6:07 PM
  • This error is not caught in the CATCH block, because it's a compilation error. Yes, it still happens at run-time. Yes, I think Microsoft should fix this.

    Here is a Connect item to vote for, but Microsoft has closed it as Won't Fix:
    http://connect.microsoft.com/SQLServer/feedback/details/496758/try-catch-should-capture-the-parse-errors

    As SergNL, you can catch the error in an outer procedure, but that is not really practical.

    As for you question 2, the normal these days is to open-query-close, but if the service is using the same connection throughout and is always using it, there is on major harm.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by KJian_ Tuesday, February 7, 2012 2:02 AM
    Tuesday, January 31, 2012 11:21 PM
  • As SergNL, you can catch the error in an outer procedure, but that is not really practical.

    The following article demonstrates it:

    http://www.sqlusa.com/articles2008/trycatch/

    I agree with Erland, it is not practical.

    If you need full error control, SSIS is available.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES
    Sunday, February 5, 2012 9:06 PM