lnardozi 61862 (10/15/2013)
TRY/CATCH statements are used to rollback a transaction if an error occurs in processing. The COMMIT goes at the end of the TRY and the ROLLBACK goes inside the CATCH. Without them, the transaction is left open if an error occurs during processing. This open transaction will continue to hold all the locks it established until the the connection is disconnected, the transaction is rolled back manually, or the process becomes a deadlock victim. In the case of a long running process that doesn't have a command timeout, that's essentially forever - at least until the 'all knowing' DBA terminates 'mysterious hung processes' as part of his daily routine.
Like I said in one of my previous posts on this thread and like what Gail just confirmed, that would all be true except for when SET XACT_ABORT is ON. Read about what it does and how it works in "Books Online".
--Jeff Moden
Change is inevitable... Change for the better is not.