Do i need Begin / Commit transaction when using XACT_ABORT ON?

Question

Hi!

In every sample i can find on the internet where the XACT_ABORT is used the code is enclodes by a begin tran and commit tran.

Sample

SET XACT_ABORT ON;

BEGIN TRAN
  do stuff
COMMIT

Is it realy nessecary to use the tran statement? Doesn't XACT_ABORT handle the rollback etc automatically?

Regards

Martin

Answer 1

the entire transaction is terminated and rolled back when you use with option ON. so it will do the rollback itself when error encountered.

Answer 2

it rollback automatically but u must COMMIT TRAN

XACT_ABORT roll back to initial state if runtime error occur automatically.

See :

http://msdn.microsoft.com/en-us/library/ms188792.aspx

Answer 3

You do not need to explicitly begin the transaction and explicitly commit or rollback.  If the transaction fails then using set xact abort to on will insure that all affected transactions are rolled back.  Using begin tran woule require you to explicitly commit or rollback the transaction, but you are correct in that if you do not explicitly use begin tran SQL will commit or rollback transaction.

---

David Dye My Blog

Answer 4

benefit is After setting XACT_ABORT ON, next update did not run after the previous error encountered.

why to take explicit step. bt in end depends on ur req.

Answer 5

Hi Martin,

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. When OFF, only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.

Try this link - http://msdn.microsoft.com/en-us/library/aa259192(v=sql.80).aspx

--Example -Error handling using XACT_ABORT
CREATE PROCEDURE XactAbort
AS
BEGIN
SET XACT_ABORT ON
BEGIN TRY
  BEGIN TRANSACTION
  INSERT..
  UPDATE ..
  DELETE ..
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF XACT_STATE<>0
BEGIN
ROLLBACK TRANSACTION
END
END CATCH
END

---

Thanks & Regards, sathya

Answer 6

Yes, you need BEGIN and COMMIT, if you want the operation to be atomic. If you do:

SET XACT_ABORT ON

INSERT tbl1 (...)
INSERT tbl2 (...)
INSERT tbl3 (...)

And the insert to tbl2 fails, the data in tbl1 will remain the database. (Unless there is an enclosing transaction.) The INSERT in tbl3 will not be executed.

If you instead have

SET XACT_ABORT ON

BEGIN TRANSACTION

INSERT tbl1 (...)
INSERT tbl2 (...)
INSERT tbl3 (...)

COMMIT TRANSACTION

And the INSERT to tbl2 fails, no data will remain in the tables.

Using SET XACT_ABORT ON is very much best practice, but beware that there are a couple of errors is unaffected by XACT_ABORT ON:

* RAISERROR.
* Error 266, trancount mismatch when you exit a procedure.
* Errors in some DROP statements.
* Syntax errors. (May occur with dynamic SQL).

---

Erland Sommarskog, SQL Server MVP, [e-mail]