Solved

SQL Server 2005 - WHILE LOOP and Transaction

Posted on 2011-09-20
7
1,899 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 2 63
format nvarchar field as mm/dd/yyyy 4 78
MS SQL page split per second is high 19 103
ms sql + top 1 for each customer 3 56
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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

809 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