Hi everybody..

I have a doubts,when to use BEGIN TRANSACTION? I red that it's use to nast procedures and when I want to do a rollback, it is useful... it's only for that or has another ventages? So if I use it, how many of this I can have in a SP... it's ok if I have more than 1????

more ▼

asked Mar 18, 2011 at 09:26 AM in Default

avatar image

394 33 33 38

Supose I have more than 10 transactions in a SP, and after all ot the transactions i put Begin transactio1 ... End; Begin transactio2 ... End; ...

While @Number< @nMaxTransac begin COMMIT TRAN; Set @Number= @Number+ 1 End;

It's that ok? I should do this like this, so if I have an Error I can do a Rollback? or its not good then I will get a lot of locks??? I mean should I put the commit tran after each transaction or not?

Mar 18, 2011 at 09:46 AM Maripili
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

There are several reasons to use a Transaction block:

  • to ensure that a set of statements are either all executed, or none of them. eg:


  • to wrap up a query while it's in development to ensure that you don't do any damage, or so that you can see the effects of your command before actually running it, eg:


more ▼

answered Mar 18, 2011 at 09:36 AM

avatar image

ThomasRushton ♦♦
42.4k 20 60 54

I would try to keep the nesting of transactions as simple as possible, in order to aid understanding of what you're doing.

Mar 18, 2011 at 09:53 AM ThomasRushton ♦♦

Don't forget that nested transactions aren't really nested at all. If you rollback the inner transaction it in fact rolls back to the outermost transaction. You've only ever got one transaction in reality.

Mar 18, 2011 at 10:32 AM David Wimbush

@David Wimbush - very good point. When using ROLLBACK for tests it's very important to keep that in mind. If you call multiple procedures that start and commit transactions, then if one rolls back, the next one you call could very well perform some actual work on the database, when you least expect it to.

Mar 18, 2011 at 02:24 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

To expand on ThomasRushton's excellent answer, I generally use transactions to group commands that I want to succeed or fail as a group. For instance, if I need to move records to an archive table, I strongly want the insert into newtable and delete from oldtable to succeed or fail together.

I will also use them in the few cases I absolutely have to make a change through ad hoc query on the production server as it gives me a last chance to validate that things happened the way I wanted them to.

I generally do not use them while developping a query personally, though I know some people do. That is what I have a development server for (not to mention point-in-time restore through log backups if I really mess things up).

Also, remember that using explicit transactions will very often cause locks to be held longer then they would be otherwise, especially if there are queries against multiple tables inside your transaction. For that reason alone, I try to use explicit transactions only when I have a good reason to.

more ▼

answered Mar 18, 2011 at 01:47 PM

avatar image

15.6k 22 57 38

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 18, 2011 at 09:26 AM

Seen: 1051 times

Last Updated: Mar 18, 2011 at 12:26 PM

Copyright 2018 Redgate Software. Privacy Policy