Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 418
  • Last Modified:

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

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!
  • 5
  • 4
  • 2
2 Solutions
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?
PS. I would definately say 2 minutes is to long to keep a transaction open.

Need more info on the above, why etc...
eclipse2kAuthor Commented:
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.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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.
eclipse2kAuthor Commented:
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?
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...
eclipse2kAuthor Commented:
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...
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.
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.
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?
eclipse2kAuthor Commented:

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 :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now