Solved

SQL Server 2005 - WHILE LOOP and Transaction

Posted on 2011-09-20
7
1,926 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
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 150 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 150 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 50 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 150 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

679 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