?
Solved

Best Practices Approach to Rollback Management

Posted on 2010-09-07
8
Medium Priority
?
413 Views
Last Modified: 2012-05-10
Hi Techies--
What is the best way to package  multiple inserts and/or updates from a series of separate service requests? Under this particular application design, several independent web service methods will be sending elements to the database  which all together make up the data which will need to be inserted. If for some reason, the data cannot be saved, I will need to rollback all of it--any suggestions?

0
Comment
Question by:ditallop
[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
  • 4
  • 3
8 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33621359
SQL Server can handle as many inserts at a time without any issues. So no need to worry about failure of Inserts..
Reg the updates, it should not ideally fail unless otherwise the table or the particular records are not locked by any other process.

In the meantime, what is the isolation level configured in your database.

>> I will need to rollback all of it--any suggestions?

Just use the construct like this

BEGIN TRANSACTION

INSERT statements
UPDATE statements

IF @@error <> 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
0
 

Author Comment

by:ditallop
ID: 33629722
Thanks rrjegan--
Under usual circumstances, I am used to receiving incoming parameters to a specific referenced stored proc from the requesting client/web service... then updating and/or inserting using the transaction method as you outlined within that stored proc. In this case, I am receiving multiple requests from different clients/web services with different incoming parameters...which when collected all together -- make up one very large transaction set ... which will need to be rolled back if there's an error.

Here's what is happening:

Web Service A  sends the  manufacturing header and detail data for insert or update

Web Service B  sends the  color palettes used for the manufacturing detail for  insert or update

Web Service C sends the customer's ship to info for the order of the manufacturing item for an insert or update.

What I want to do is to gather the parameters coming in from Web Service A,
Web Service B and Web Service C  .... to a procedure which will manage the actual insert as a transaction.

I am not sure how to do this...or if it is able to be done... with a combo of sprocs/triggers...
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33634157
>> What I want to do is to gather the parameters coming in from Web Service A,
Web Service B and Web Service C  .... to a procedure which will manage the actual insert as a transaction.

May I know why are you trying to gather all parameters coming from several Web Services which would create performance issues too.
Ideally, transactions should be kept small as possible in order to avoid performance issues, blocking and deadlocks.

And merging several services at Application level and managing transactions is not a good idea..
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 11

Expert Comment

by:Ved Prakash Agrawal
ID: 33634969
Always use @@trancount to check if any transaction is open.

because if you do rollback it will rollback all open transaction for session while commit need for each open transaction.

0
 

Author Comment

by:ditallop
ID: 33640601
rrjegan17--
The web services are registered from multiple departments in an SOA environment -- each offering a portion of a whole transaction. I'm not committed to gathering parameters as much as I am assuring that the end transaction (200+ elements spanning 34 tables).

ved17nov--
thanks for the @@transactioncount check info.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33643814
Since it is a SOA, you can commit the services hitting directly into the database right without the use of a transaction itself. Can you give more inputs as I am afraid that I didn't understood your requirements clearly.
0
 

Accepted Solution

by:
ditallop earned 0 total points
ID: 33688236
It looks like we'll have to resolve this via the wcf service. Thanks all!
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33690772
Thanks for posting the solution you have identified.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

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.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

765 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