title: Are T-SQL rollbacks deterministic or garbage collected?
body: <p>In our Miscrosoft Sql Server 2008 database, I found some stored procedures that do this:</p>
  
  <pre><code>BEGIN TRY
      BEGIN TRANSACTION
          query1
          query2
          query3
      COMMIT TRANSACTION
  END TRY
  
  BEGIN CATCH
      ROLLBACK TRANSACTION
      RAISERROR
  END CATCH
  </code></pre>
  
  <p>I said to my coworker that this is functionally identical to this:</p>
  
  <pre><code>BEGIN TRANSACTION
      query1
      query2
      query3
  COMMIT TRANSACTION
  </code></pre>
  
  <p>If, say, <code>query2</code> fails, you never hit the <code>COMMIT</code> line, so of course SqlServer rolls back, and because of the error, it throws it back to the client as well. Since the <code>CATCH</code> block does the same thing as the default, I argued that we don't need the <code>TRY/CATCH</code> block at all.</p>
  
  <p>My co-worker agrees that the <code>ROLLBACK</code> 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.</p>
  
  <p>So my question is: if a stored procedure fails in a transaction, when does that transaction get rolled back?</p>