Solved

SQL Server 2005 - WHILE LOOP and Transaction

Posted on 2011-09-20
7
1,954 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 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr‚Ķ

732 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