[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Server 2005 - WHILE LOOP and Transaction

Posted on 2011-09-20
7
Medium Priority
?
2,096 Views
Last Modified: 2012-05-12
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
Comment
Question by:Eric3141
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 2

Expert Comment

by:smu95rp
ID: 36569713
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
 
LVL 7

Accepted Solution

by:
printnix63 earned 600 total points
ID: 36570268
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
 
LVL 25

Assisted Solution

by:jogos
jogos earned 600 total points
ID: 36571758
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 200 total points
ID: 36573170
If you open transaction belfore while loop then while loop goes in transaction.
0
 
LVL 2

Author Comment

by:Eric3141
ID: 36574684
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
 
LVL 2

Assisted Solution

by:smu95rp
smu95rp earned 600 total points
ID: 36574914
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
 
LVL 25

Expert Comment

by:jogos
ID: 36574949
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question