Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Details on Database Connections from ColdFusion

Posted on 2003-11-26
4
Medium Priority
?
266 Views
Last Modified: 2013-12-24
Can someone confirm or deny the following?

---------------------------
In the web environment, there are no permanent connections between client
and server. The browser is not able to open a direct connection to the
database, so the role of the application server such as CFMX is to manage
the DB connections on behalf of clients. Basically, the app server
virtualizes database connections by managing a pool of pre-opened
connections and serving it up to applications each time a query is made.
This is very efficient because opening database connections on demand each
time an application makes a query is very costly in system resources. You
can see the connection pooling parameters in CF Admin > Data sources under
Advanced Settings.

Now, the database no longer has any knowledge or control of how many clients
are connecting to it because a small pool of connections can be time-shared
between a large number of users. That's why you can buy a 5-user licence and
have the database serve thousands of concurrent users. Of course the
database vendors can't allow that happen, so most of them now require that
if you serve internet (or intranet) users from the database, you will need
to purchase an additional "Internet licence" which maybe triples the price.

Preamble over, back to the original question. If you insert a new record and
retrieve its identity using @@IDENTITY within the same CFQUERY block, you
will be guaranteed of using the same database connection because a
connection is allocated per query. Because $$IDENTITY gives you the last
identity within the connection, that's what you want. If you put the insert
and select @@identity in different CFQUERY blocks, it is possible that each
may use a different database connection and you'll get wrong results.
---------------------------

And give me some more info on connections, here is how I always *thought* it worked
CF creates a connection to the DB via ODBC, this is one connection and will be seen as one connection to the db, no matter how many clients connect through it.... I could be wrong as this part has always been a bit unclear to me.

PS. I am aware of "CFMX is to manage the DB connections on behalf of clients"
0
Comment
Question by:Tacobell777
[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
  • 2
4 Comments
 
LVL 19

Accepted Solution

by:
Dexstar earned 500 total points
ID: 9828435
@Tacobell777:

> And give me some more info on connections, here is how I always *thought* it worked
> CF creates a connection to the DB via ODBC, this is one connection and will
> be seen as one connection to the db, no matter how many clients connect through
> it.... I could be wrong as this part has always been a bit unclear to me.

Yes, that passage you posted is 100% true.
Here is how it works.  CF creates not just one connection, but a "pool" of connections.  When a client requests a web page that needs a database connection, it gets one from the pool, makes the request, and then puts it back into the pool.  That avoids constant connection/disconnecting to the database.

Apparently, it gets the connection and puts it back in the pool for each CFQUERY block.  That's why if you get the @@IDENTITY in a different CFQUERY block, then it grab a different connection out of the pool, and get the wrong @@IDENTITY value.

Hope That Helps,
Dex*
0
 
LVL 17

Author Comment

by:Tacobell777
ID: 9828465
Hi Dex,

Your saying "CF creates not just one connection, but a "pool" of connections" how can my passage be true then?
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9836670
That passage states "Basically, the app server virtualizes database connections by managing a pool of pre-opened connections and serving it up to applications each time a query is made."  which is just a more complicated way of saying what I said.

Maybe I don't understand.  What part of the passage conflicts or what part is unclear?

Dex*
0
 
LVL 4

Expert Comment

by:procept
ID: 9841767
Hi,

> If you put the insert
> and select @@identity in different CFQUERY blocks, it is possible that each
> may use a different database connection and you'll get wrong results.

That's not hte reason for an incorrect result... if you use separate queries, you may run into race conditions with 2 or more users:

User1 Query1: insert
User2 Query1: insert
User1: select @@identity ==> wrong result

By putting both in the same query, you avoid the race conditions, because the whole block (insert and identity-select) is executed by the database at one time. The DB will use features like e.g. automatic record locking to make sure the SQL batch retrieves the correct result.


> Apparently, it gets the connection and puts it back in the pool for each CFQUERY block.  

AFAIK CF does that for each request, not for each query within the request.... not sure about htat, though.

HTH,

Chris


0

Featured Post

Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

Question has a verified solution.

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

When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
Introduction In this tutorial, I'll explain how to create an animated progress meter in a wireframe prototype developed using Axure RP 7.0 - a leading prototyping tool for designing web sites and software. (For more information about Axure and gett…
The purpose of this video is to demonstrate how to manually back up a WordPress Database. This will be demonstrated using a Windows 8 PC. The Host used will be IPage.com Log into your Hosting account. IPage will be used for demonstration : Locat…
The purpose of this video is to demonstrate how to set up basic WordPress SEO. This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast. Go to your WordPress login page. This will look like the following: myw…

715 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