Welcome to weblogs.com.pk Sign in | Join | Help

XACT_ABORT

Using transactions in SQL Server is sometimes tricky. Here is today’s learned lesson.

If you set xact_abort to on; you don’t need to rollback the transaction; it gets automatically rolled back in case of any error. One thing that can lead you to problems is; that transactions get rolled back when you close the connection. If you want to perform some other operations with in same connection; and your next operation uses resources that were involved in the previous transaction you will find those resources locked and thus you will not be able to perform any further operations involving such resources.

So; always roll back the transaction in your TSQL code. The easiest method is to use @@trancount global variable.

if @@trancount >= 1
  commit transaction;
else
  rollback transaction;

Published Monday, January 23, 2006 12:46 PM by khurram
Filed under: ,

Comments

No Comments

New Comments to this post are disabled