x

"BEGIN TRANSACTION"

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

Maripili gravatar image

Maripili
394 32 33 35

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:

    BEGIN TRANSACTION INSERT INTO foo... UPDATE br... UPDATE baz... COMMIT TRANSACTION

  • 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:

    BEGIN TRANSACTION UPDATE foo ... SELECT * FROM foo ROLLBACK TRANSACTION

more ▼

answered Mar 18, 2011 at 09:36 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.9k 18 20 44

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

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x60

asked: Mar 18, 2011 at 09:26 AM

Seen: 863 times

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