CFLOCK and Persistent Components

Posted on 2003-03-06
Medium Priority
Last Modified: 2013-12-24
I am working on a new CFMX coding strategy.  In order to put less strain on SQL Server 2000, i'm writing some components that essentially manage a section of my database, such as a Product component.  These components would be loaded into the Server or Application scope, and would keep queries loaded.  In this way, I won't have to hit the product database table everytime, I just have to call Server.ProductManager.getAllProducts() or whatever.  My question:  what sort of concerns does this situation raise in regards to CFLOCK.  When I'm writing the query, do I have to lock it?  When I'm reading?  What if I do an INSERT or UPDATE and the re-load the query.  Does that need a lock?  If so, what level of locking do I need?  Please note that this is CFMX i'm referring to, which seems to be a bit friendlier w/ locking.
Question by:curmudgeon42
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
LVL 15

Accepted Solution

danrosenthal earned 300 total points
ID: 8084412
You would need to put a CFLOCK around every call to set or read the variable.  I would recommend just having a cached query...

<cfquery name="products" datasource="#dsn#" cachedwithin="#CreateTimeSpan(1,0,0,0)#">
  SELECT * FROM Products

This particular query would only run once per day, unless you cycled the CF service or manually updated it.


Expert Comment

ID: 8085754
I agree with dan, let coldfusion cache it for you if you want to use the same query over and over.  Other ways to speed things up: stored procedures, cf's query of a query (for small result sets), make sure you have indexes built for the right fields, cfstructs can sometimes be used in place queries if you pre-populate it with query results and finally optimization of the sql code (using a query optimizer if it's available).

Expert Comment

ID: 8085793
I assume this is related to your other question concerning application caching of queries. The wording of your question this time sheds a bit more light on the issue, I might not have been understanding what you were doing before...

As you saying that your are executing a query from within your CFC and simply copying the result set into the application scope? And if so, are you then saying that when you want to refresh the data, you are re-running the query and then re-copying it into the application scope?

If so, you are not doing yourself any good at all. There is no benefit whatsoever to doing it this way. Using a natively cached query will not only be easier, but will be faster because it eliminates the locking issues.

I was under the impression that you were going to be using the CFC to update the cached data on a row by row basis. In other words, on an update to the db you would both update the cached data and the database at the same time. Keeping the cache "synchronized" so to speak. In that scenerio, then there is a benefit. But if you are not doing that, and are simply refreshing the entire cache when the data is stale, then there is no point in it. You should just use query caching.


Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

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…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month10 days, 11 hours left to enroll

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