0

In our Miscrosoft Sql Server 2008 database, I found some stored procedures that do this:

BEGIN TRY
    BEGIN TRANSACTION
        query1
        query2
        query3
    COMMIT TRANSACTION
END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION
    RAISERROR
END CATCH

I said to my coworker that this is functionally identical to this:

BEGIN TRANSACTION
    query1
    query2
    query3
COMMIT TRANSACTION

If, say, query2 fails, you never hit the COMMIT line, so of course SqlServer rolls back, and because of the error, it throws it back to the client as well. Since the CATCH block does the same thing as the default, I argued that we don't need the TRY/CATCH block at all.

My co-worker agrees that the ROLLBACK will happen eventually, but it could be some time later, and could hold resources or lock records for some non-deterministic amount of time, and this could cause problems.

So my question is: if a stored procedure fails in a transaction, when does that transaction get rolled back?

Joshua Frank
  • 13,120
  • 11
  • 46
  • 95

2 Answers2

3

The rollback won't be triggered with your solution in the expected way. You have to add 

set xact_abort on

to your query.

For further information see an old answer and the Microsoft documentation

Community
  • 1
  • 1
hash
  • 131
  • 6
1

SQL Server will happily leave the transaction open forever as long as the client connection remains open. An explicit rollback is a good practice in that it doesn't wait for the client connection to be physically closed or the pooled connection reused. A simple THROW (SQL 2012 and later) in the CATCH block will rollback the transaction and raise the error.

I recommend the SET XACT_ABORT ON option hash suggested to mitigate a another issue. If a client timeout occurs during query execution, no further code in the batch (including the ROLLBACK) will execute and the transaction will be left open. However, the transaction will still get rolled back with SET XACT_ABORT ON.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • We don't use persistent connections, so does that change anything? If we open a connection, call the proc, and close the connection, and the proc fails, does SqlServer rollback *when the connection is closed*? – Joshua Frank Jun 29 '14 at 12:37
  • Also, I did some reading on `SET XACT_ABORT ON` and it looks like if this is true, then the transaction rolls back on failure even if you don't `CATCH` and `ROLLBACK` explicitly. So if I turn that on, can I dispense with the `TRY/CATCH/ROLLBACK` syntax? – Joshua Frank Jun 29 '14 at 12:50
  • If an open transaction exists when the connection is closed, the connection will be returned to the connection pool with the open transaction. The transaction will be rolled back when the connection is reused (via sp_reset_connection) or the pooled connection is physically closed due to inactivity. – Dan Guzman Jun 29 '14 at 14:01
  • SET XACT_ABORT will stop proc execution and rollback the transaction automatically under most, but not all, situations. An example of such is a syntax error in a dynamically executed statement. Without error handling, the batch will continue and commit rather than rollback the transaction. – Dan Guzman Jun 29 '14 at 14:32
  • If the connection is closed and the TX is open, but going to be rolled back when the connection is reused, why wouldn't it just roll back right away. What's the sense of leaving the TX open? About the dynamic statement, how can the batch commit if a statement in it fails? Doesn't a failed statement immediately jump out of the proc if there's no `TRY/CATCH` in place? – Joshua Frank Jun 29 '14 at 16:08
  • Returning a connection to the pool with an open tran is the exception rather than the rule and won't happen unless there is an error in your code. Calling sp_reset_connection upon pooled connection reuse avoids an extra call to sp_reset_connection call in the more common case where the pooled connection is not resused and physically closed. No need to reset the connection if it will be physically closed anyway. – Dan Guzman Jun 29 '14 at 20:01