Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2213
  • Last Modified:

SQL Server 2005 - WHILE LOOP and Transaction

I'm using a Transaction and a WHILE loop in my proc.  Does the Transaction go inside the WHILE loop or does the WHILE loop go inside the transaction?  My thought is the latter but wanted to ask and double-check.
0
Eric3141
Asked:
Eric3141
4 Solutions
 
smu95rpCommented:
It really depends on whether you need the transaction to protect the entire query.

I know that's not much help but there are many factors which affect which you should use. Perhaps you could tell us a little more about what you are trying to do inside the loop and what you are trying to use the transaction to protect?

Rich
0
 
printnix63Commented:
Basically as smu95rp said, the answer depends mainly on what you want to do.

Transactions should enclose operations that should be processed in whole, and if something fails it can be rolled back.

When each operation inside the while loop is a complete process - where it is sufficient to roll back only this particular operation, if something goes wrong, then the transaction can be inside the while loop.
If your Process needs the whole loop to be processed and if something fails, the whole set has to be rolled back, then you must place the transaction outside the loop.

There might be other things like performance that might provide reason to put it outside, but the main reason for using a transaction is the above said, in case of a failure.
0
 
jogosCommented:
Transactions and errorhandling (messages, ..) are a pair.

If you loop customers and do some work for each customer do you want :
- error on customer, all customers are unchanged -> loop in transaction
- error on customer x and y , all customers treated and message customer x and y with error -> transaction in loop
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Alpesh PatelAssistant ConsultantCommented:
If you open transaction belfore while loop then while loop goes in transaction.
0
 
Eric3141Author Commented:
All these answers have been good and certainly error handling is the key factor.  
But while I'm asking questions about this, I thought I'd also ask about performance considerations -- is there a big difference one way or the other?
0
 
smu95rpCommented:
Yes as the transaction will hold locks for the entirety of its duration. If you create a new transaction inside the loop it will only hold locks for each iteration.

Inside the loop is basically the better for concurrency and performance and will better reduce the chance of deadlocks.

That doesn't mean that you shouldn't put the loop inside the transaction if that really is what is required.
0
 
jogosCommented:
A long running process keeping locks on a db that's also in use by other processes is not only measured by the duration of the process itself, also the wait time of the other proceses need to be considered. So there is a lot off things lacking to give an advice on that.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now