sql server - Are T-SQL rollbacks deterministic or garbage collected? -

in our miscrosoft sql server 2008 database, found stored procedures this:

begin try     begin transaction         query1         query2         query3     commit transaction end try  begin catch     rollback transaction     raiserror end catch 

i said coworker functionally identical this:

begin transaction     query1     query2     query3 commit transaction 

if, say, query2 fails, never hit commit line, of course sqlserver rolls back, , because of error, throws client well. since catch block same thing default, argued don't need try/catch block @ all.

my co-worker agrees rollback happen eventually, time later, , hold resources or lock records non-deterministic amount of time, , cause problems.

so question is: if stored procedure fails in transaction, when transaction rolled back?

sql server happily leave transaction open forever long client connection remains open. explicit rollback practice in doesn't wait client connection physically closed or pooled connection reused. simple throw (sql 2012 , later) in catch block rollback transaction , raise error.

i recommend set xact_abort on option hash suggested mitigate issue. if client timeout occurs during query execution, no further code in batch (including rollback) execute , transaction left open. however, transaction still rolled set xact_abort on.