"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????

asked Mar 18 '11 at 09:26 AM in Default

Maripili
394 ● 16 ● 31 ● 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 '11 at 09:46 AM Maripili

(comments are locked)

2 answers:

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.

answered Mar 18 '11 at 01:47 PM

TimothyAWiseman
14.4k ● 17 ● 21 ● 29

(comments are locked)

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

answered Mar 18 '11 at 09:36 AM

ThomasRushton ♦
30.1k ● 7 ● 10 ● 40

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 '11 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 '11 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 '11 at 02:24 PM Matt Whitfield ♦♦

(comments are locked)