Is a ROLLBACK TRANSACTION statement necessary

0
votes

When opening an explicit transaction if a failure occurs will all statements between the BEGIN and COMMIT automatically be rolled back? Or do you have to issue a ROLLBACK statement.

In my previous experience everything between the BEGIN and COMMIT automatically rolled back. Therefore what constitutes when you need to issue a ROLLBACK statement to manually roll it back?

1
sqlsql-servertransactions
c-sharpcorner.com/article/… - Dale K
you should manually do a rollback. otherwise your transaction may keep the tables locked, which can lead to deadlocks. - Yeou
it's a good thing from the perspective of code writing discipline. You don't yield implied control, retaining it in your clawy hands. - access_granted

1 Answers

0
votes

It depends upon your session settings and the type of error. If it is a statement-terminating error, then the transaction just continues with the next statement. If it's a batch-terminating error, then the transaction is aborted.

To avoid issues with statement-terminating errors, make sure you have previously executed:

SET XACT_ABORT ON;

and then all statement-terminating errors will also abort the transaction and roll back.

Some programming client libraries turn that on automatically for you, and that's why you may have previously seen the auto-rollback, but I usually add it as the first line of all my stored procedures, just to be sure.