CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedurestransactions
Joshua Frank
Joshua Frank

Reputation: 13848

Are T-SQL rollbacks deterministic or garbage collected?

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?

Upvotes: 0

Views: 85

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46233

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.

Upvotes: 1

hash
hash

Reputation: 131

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

Upvotes: 3

Related Questions