XACT_ABORT ON

Question

XACT_ABORT ON

RJ-476420

SSCarpal Tunnel

Points: 4806
More actions
April 23, 2009 at 12:21 pm

#97455

whether XACT_ABORT ON can be used in a stored procedure where you never use begin tran commit or rollback.
i have 2 insert, 1 updates, 1 delete in a stored procedure where there are no transactions. first are inserts, second updates & third deletes. all three operations use different tables & not on same table. whether using XACT_ABORT ON inside the proc will rollback any changes made in the previous batch i.e.> if there are any issue while performing a delete will rollback any changes made by inserts & updates without using begin tran when XACT_ABORT is set to ON?
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Test it and find out, in a dev database. That's what I'd do. If it actually matters which it will do, is there a reason to not use begin...commit?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

RJ (4/23/2009)
whether XACT_ABORT ON can be used in a stored procedure where you never use begin tran commit or rollback.
i have 2 insert, 1 updates, 1 delete in a stored procedure where there are no transactions. first are inserts, second updates & third deletes. all three operations use different tables & not on same table. whether using XACT_ABORT ON inside the proc will rollback any changes made in the previous batch i.e.> if there are any issue while performing a delete will rollback any changes made by inserts & updates without using begin tran when XACT_ABORT is set to ON?
Thanks

It won't do anything for you without explicit transactions on a system that's setup to do implicit transactions for each Insert/Update/Delete.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RJ-476420 SSCarpal Tunnel Points: 4806 More actions · Answer 3

Test it and find out, in a dev database. That's what I'd do. If it actually matters which it will do, is there a reason to not use begin...commit?
- GSquared

The reasons are i have a delete statement followed up a email sending loop. For whoever records are removed from the system I have to send emails to their manager's. if i put a begin tran to delete a record & send emails in loop to the manager's the whole table would be locked until the emails are all sent to each managers.

so i was wondering if i can make delete records as a batch & send emails in a loop.

after some testing on dev I found without begin tran if i use Xact_abort on the transactions are rolled back for a single transaction like insert, update or delete but not for the whole batches inside a procedure.

Also, i never wanted to send emails after delete by locking table until all emails are send. nevertheless, I think i have a solution.

Thank you guys.

Saurabh Dwivedy SSCommitted Points: 1887 More actions · Answer 4

after some testing on dev I found without begin tran if i use Xact_abort on the transactions are rolled back for a single transaction like insert, update or delete but not for the whole batches inside a procedure.

Then this is almost as good as having xact_abort OFF...Right?

Saurabh Dwivedy
___________________________________________________________

My Blog: http://tinyurl.com/dwivedys

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537

Be Happy!

rburgess Old Hand Points: 327 More actions · Answer 5

We used XACT ABORT ON where I worked. The biggest issue we had was if you were to call another stored proc within the stored proc using XACT ABORT ON. The XACT ABORT ON only applied to the first proc and was not active within the called proc. Might be careful in such a case.

Saurabh Dwivedy SSCommitted Points: 1887 More actions · Answer 6

Thanks that's a good point worth being on the lookout for.

Saurabh Dwivedy
___________________________________________________________

My Blog: http://tinyurl.com/dwivedys

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537

Be Happy!

rburgess Old Hand Points: 327 More actions · Answer 7

That caused a 4 table update to only update some tables and not others and changes did not get rolled back in tables that had already been updated in the called procs. Caused numerous issues for us.

bright_day SSC Rookie Points: 48 More actions · Answer 8

Can you send working (smallest) example that shows problem with calling procedures and xact_abort ? My testing showed that XACT_ABORT is behaving like a property of a session, not a procedure context. So, if you call a procedure within procedure, it does't matter, it behaves like you copied code of subprocedure and placed it in calling procedure. So, I don't see the problem you described. Please, post example that shows problem you experience. And exact version of database.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 9

rburgess (4/29/2009)
That caused a 4 table update to only update some tables and not others and changes did not get rolled back in tables that had already been updated in the called procs. Caused numerous issues for us.

I know it's an old post but since someone else hit on it today, I'll ask the question... were all of those updates contained in a single explicit transaction that starts with BEGIN TRANSACTION or are you just saying that you had a similar problem as the OP because you didn't?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)