Solved

About CFTransaction (and Locking): Time-Consuming operations insdide of CFTransaction?

Posted on 2004-09-21
11
376 Views
Last Modified: 2013-12-24
Hello EE!

When the user place an order, we currently...
   (A) Send the Order Information in a XML Package to the customer (is NOT the user, by the way) using the CFHTTP tag

and only when we receive a confirmation of the order, we....
   (B) Save all the information in the Database

(else we just show an error message)


But sometimes there could be a problem, for example, we already did (A) and got a confirmation but then we get a database error or a coldfusion error or something.
now we have differences between our database and the customer database which is very bad.

We thought about using CFTRANSACTION and doing it this way:

We want to...
   (a) Open a new transaction (cftransaction)
   (b) Insert the order to our database (cfquery or cfstoredproc)
   (c) Send the XML to the customer (cfhttp)

When we get a confirmation, we...
   (d1) Commit (cftransaction action)

else when we get an error from the customer, or we had any kind of internal error, we...
   (d2) Rollback (cftransaction action)


Now the problem is that the CFHTTP Request might take a "long time".
Currently we have a two-minute-timeout set before we cancel the CFHTTP request.

We heard, that you should keep transactions short... Is that too long?


Questions:
What would happen, if we keep the CFTRANSACTION Tag open including
the CFHTTP operations we do?

What happens if user 1 is placing his order (currently waiting for the CFHTTP reply),
while at the same time, user 2 is trying to place an order?

Would the user (or coldfusion-server) need to wait until the first users transaction
has been commited or rolled back?

If yes, is there any chance to allow concurrent orders but keep the possibility
of rolling back the order (to avoid the problem as described above)?


Thanks in advance!
0
Comment
Question by:eclipse2k
  • 5
  • 4
  • 2
11 Comments
 
LVL 17

Expert Comment

by:Tacobell777
ID: 12119912
I can't  give you any specific answers on how long it takes for cf to time out a transaction, I would say at the end of the request (most likely)...

Even if it did not, it would not be a good idea to keep a transaction open for a long period of time.

Who is the customer? The person placing the order?
Who is confirming what?
0
 
LVL 17

Expert Comment

by:Tacobell777
ID: 12119918
PS. I would definately say 2 minutes is to long to keep a transaction open.

Need more info on the above, why etc...
0
 
LVL 4

Author Comment

by:eclipse2k
ID: 12120578
to clarify a few things:

the CUSTOMER is a company, who sell things in a online shop, which is running at our servers. we store orders in our database and send the same order via XML to reach his merchandise management system (or ERP system)

the USER is the customer of that firm who is buying things in that online shop

when the USER is placing his order, we then send the order via HTTP (XML Package) to the CUSTOMERS ERP system. when the package we receive contains the ErrorCode 0 we then also place the order in our database because we know that the order reached the CUSTOMER. but here we might have a problem with keeping the orders synchronous, when we suddenly encounter an error somewhere. at this point, the CUSTOMER already got the order, but we lost it.

one method to avoid this would be, control this by a transaction. only when both transactions (place in our database AND send the xml) run successful we commit the transaction.

unfortunately there is this cfhttp request which is delaying the process.
0
 
LVL 17

Expert Comment

by:Tacobell777
ID: 12120613
so the request to the customer is to see if the customer can actually produce the merchandise?
And this is all automated?
If so, then I would do all the inserting after you receive the response back, if you then still need the cftransaction it will be lots quicker...
Thats if I understand it all.
0
 
LVL 4

Author Comment

by:eclipse2k
ID: 12121143
Hi, "If so, then I would do all the inserting after you receive the response back"

yes, this is how we do it right now, but the request is not only to see if the customer can actually produce the merchandise, its also handled as an official and complete order if we dont receive some error message.

but now imagine the situation:

 A) User clickt at "order now"
 B) We create the XML Package and Send It
 C) We got a confirmation, the order is now valid and complete
 D) We want to put it all to the database, but...
 E) ...we get some unpredictable SQL error! (Could happen)

Now for the customer the order is complete because he received a valid XML order and confirmed it.

But he wont find the order in the shop statistics because of (E).

We were not able to cancel or rollback the process in any way because step (C) is already done.

You know what i mean?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 17

Expert Comment

by:Tacobell777
ID: 12121351
Then put a <cftry> and <cfcatch> around the sql statements, and if something happens you send an email tot he admin with the details of the error and order... You can fix it up then...
0
 
LVL 4

Author Comment

by:eclipse2k
ID: 12123357
Haha well but this is not really a solution, is it? :o)

We already have some good logging and tracking mechanisms, we always know if something happened and why and are able to fix it.
But instead of this, we would like to PREVENT it by encapsulating it with a transaction and ensure, that either both transactions succeeded,
or we canceled both if one of them encountered an error...
0
 
LVL 1

Expert Comment

by:LuckyBlood
ID: 12125506
hi eclipse2k,

put a <cftry> arround the http-request. if an error occurs you shouldn´t write the information in the database.

yeah, i know, it´s not a really good solution. otherwise you should test the reaction of an active transaction. in ms sql you can´t read data until an other transaction has been completed. so if your transaction blocks the application for 2 minutes, maybe no one else can write datas to the database.

the best solution is to simulate the behaviour of <cftransaction> in almost an equal evironment.
0
 
LVL 1

Accepted Solution

by:
LuckyBlood earned 450 total points
ID: 12126263
i tried it on my system. now i can give a concret answer. Each time your user sends an order to the customer you put an <cftransaction action="begin" isolation="read_uncommitted"> around it. when an error occurs you have to catch it with a <cftry>. in this catching operation you have to rollback this transaction with the command <cftransaction action="rollback" isolation="read_uncommitted">.

During this process each other user can read the data out of the database your first transaction is writing to at the moment. The reason is the isolation level you set in your transaction. So don´t use a higher isolation level, because you can´t read the data during such a transaction.

Important:
if you insert data in a database in a transaction and an error occurs, the automatic set identity in the database is intermittent, but the data has not been inserted in the database. so there is a lag in your ID in the chronological order.
0
 
LVL 17

Assisted Solution

by:Tacobell777
Tacobell777 earned 50 total points
ID: 12127058
From what I read, I stilll think

cfhttp

cftransaction

cftry
sql stuff
cfcatch
cftry

cftransaction

if there was a problem
rollback request made with xml package

you are aware that cftransaction does not roll back your cfhttp request, right?
0
 
LVL 4

Author Comment

by:eclipse2k
ID: 12131290
@Taco

Yes of course i am aware of that i cannot rollback a cfhttp request...
finally, this is NOT really a solution to prevent the problem as described above
in my topic post and my comment post " 09/22/2004 11:38AM CEST".
further, we already use try-catch blocks very often, by the way, also in that case.
thanks for the assistance anyway! :)

@LuckyBlood

Ok, thanks for the tests, gonna try it right now, but it sounds good.
I hope that i will never get any locks because of that. Else i will contact you again :)
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now