Advice on transactions (not nested)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Advice on transactions (not nested)

Author

Topic

eurob
Posting Yak Master

100 Posts

Posted - 2007-06-07 : 13:05:56

I'm looking for some advice on what is best practice with transactions . I'm not talking about nested transactions. I'm talking about a bunch of updates that are not related to each other. I do,however, want to roll everything back when one of these updates fails.
In ex.1, if the second update fails, the first update is still committed so that would not work for me.
In ex.2 I am using one transaction so everything would be rolled back, however, I am doing work in between the updates that IMO shouldn't be a part of the transaction.
Any suggestions ??

ex.1
2 transactions, 2 updates, rolls back 1 transaction when error
begin transaction
...insert into mytable(a,b)
...values(1,2)

...if @@ERROR <> 0
....begin
.....rollback transaction
.....return -1
....end

commit transaction

/* do some stuff here fill temp tables etc. */
/*.... */

begin transaction
...insert into othertable(a,b)
...values(3,4)

...if @@ERROR <> 0
....begin
.....rollback transaction
.....return -1
....end

commit transaction

-- or --
ex.2
1 transaction, 2 updates, rolls back everything when error

begin transaction
...insert into mytable(a,b)
...values(1,2)

...if @@ERROR <> 0
....begin
.....rollback transaction
.....return -1
....end

/* do some stuff here fill temp tables etc. */
/*.... */

...insert into othertable(a,b)
...values(3,4)

...if @@ERROR <> 0
....begin
.....rollback transaction
.....return -1
....end

commit transaction

robert

robert

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-07 : 13:16:20

put 1 transaction.

if you're just playing with temp tables there then you have nothing to worry about.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

eurob
Posting Yak Master

100 Posts

Posted - 2007-06-07 : 13:23:05

I thought if the work I do with the temp tables it would affect performance, like maybe 15 sec max. ( I also open and close a few cursors). Thanks.

robert

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-07 : 13:33:41

opened a few cursors??? arghh... that's bad

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

eurob
Posting Yak Master

100 Posts

Posted - 2007-06-07 : 13:35:07

quote:
Originally posted by spirit1

opened a few cursors??? arghh... that's bad

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

not in this case, I find it very handy

robert

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-06-08 : 14:44:23

Why use a transaction at all? All Inserts and Updates are already in an implicit transaction. So, unless you need to roll back multiple inserts/updates you do not need one.

I guess you already have your heart set on using cursor(s) and since you are not working on my db you can use them. :) But, I'd advise you to figure out a set-based approach or, if that is not possible, an alternative way or handling iteration.

-Ryan

eurob
Posting Yak Master

100 Posts

Posted - 2007-06-08 : 16:06:26

The example I kept simple, but I do have to updates hundreds of records, that's why I needed the transaction, I needed to have the whole deal in one atomic unit so if one update breaks it doesn't result in a trail of unwanted updates. WGT cursors, I think it is a feature of SQL Server that helps out when you have to store a few sets that you need to use later. I would agree not to use them when it takes forever to run them, but in my case it's only a few seconds.

robert

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-06-09 : 19:27:20

Remember, you asked for advice... so...

"Handy" or not, don't use cursors... so use SET XACT_ABORT ON for complex transactions.

--Jeff Moden

eurob
Posting Yak Master

100 Posts

Posted - 2007-06-11 : 13:17:15

So with this setting I don't have to use rollback statements, handy.
Thanks,

robert

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-11 : 13:20:22

yes you don't have to.
but i don't think this solves your problem.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

eurob
Posting Yak Master

100 Posts

Posted - 2007-06-11 : 13:23:33

It actually does.

robert

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-11 : 13:26:46

cool.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources