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

Posted on 2004-09-21
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?

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!
Question by:eclipse2k
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
  • 5
  • 4
  • 2
LVL 17

Expert Comment

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?
LVL 17

Expert Comment

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...

Author Comment

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.
Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

LVL 17

Expert Comment

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.

Author Comment

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?
LVL 17

Expert Comment

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...

Author Comment

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...

Expert Comment

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.

Accepted Solution

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.

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.
LVL 17

Assisted Solution

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



sql stuff


if there was a problem
rollback request made with xml package

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

Author Comment

ID: 12131290

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! :)


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 :)

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

A 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…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

636 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