how to handle connections on heavely used app

[Smokey Grindel]

I have a web app which could get 10,000 hits per minute, and relies heavely
on database interaction... we've always been told open and close connections
only when needed... but what happens if two people hit a page at the same
time and one has an open connection and the other is trying to open one,
it'd fail saying the connection is already open... how do you guys handle
connections in a heavy use application?

 

[Marina Levit [MVP]]

No it would not fail. You can have more then one open connection from a
machine to a database server. In fact, you are only really limited by how
many your database server can handle.

So yes, just create connections as you need them. There is no rule that says
that your database server is only allowed one database connection at a time.

 

[Smokey Grindel]

They why does it fail for us all the time when two people try to access the
database through the same website application? The application itself has
the connection open already, when someone else accesses the same page at the
same time it throws an exception "The database connection is already open
(State = Open)" when the Open method is called... and this is on an SQL 2005
server with a lot of CAL's on it...

 

[Marina Levit [MVP]]

It sounds like you have a globally defined connection object?

You never ever ever ever ever, under any circumstances, have a global
connection object to be shared by all users in a web application. Now, you
have to synchronize access to it, which is going to kill performance and
scalability.

You are supposed to *create* the connection when you need it, open it, do
your work, then close it. By "create" I mean actually make a new connection
object - not use one already hanging out there in shared space.

Whoever was the one that always told you to open and close connections only
when needed, missed the critical detail of not sharing one connection object
for an entire web application.

 

[Smokey Grindel]

the connection object isnt shared globally... this is why I am confused
about this... the object is made locally on each page at page load... which
I thought would create a unique object for each instance of a page... but
I'm still running into problems for some reason... wish I could figure this
one out...

 

[Smokey Grindel]

wierd the server admin made some changes on how IIS is setup and load
balanced and now its not doing that anymore... now I'm really confused