Sharing one connection with Lock()

  • Thread starter Thread starter Brandon Potter
  • Start date Start date
B

Brandon Potter

Hi,

I was trying to find the best way to go about managing a SqlConnection
object being used by a web service. In theory my thinking was that I would
create a shared class that would hold 1 SqlConnection, handle the connection
string and opening/closing of the connection, and expose the connection as a
read only property from the class for the callers that need to assign it to
SqlCommands.

I also thought, that in theory, calling
HttpContext.Current.Application.Lock() just before opening the connection
[then the callers create a command, assign the connection, run a
SqlDataReader against the command] and
HttpContext.Current.Application.UnLock() just after the connection is
closed, that it would prevent other users from triggering the web service to
throw a "There is already an open DataReader...." exception when being
accessed at the same time.

However, when the web service receives 10-15 hits/sec, the "There is already
an open DataReader..." exception is occasionally thrown. Can someone
enlighten me as to where my thinking was flawed? The grand plan was to try
and "hold" the other instances of the web service until the 1 connection
becomes available again, and I thought that's what Lock() and UnLock() did;
but now I am confused! :)

Thanks,
Brandon
 
Hi Brandon,

SqlConnection is already pooled by ado.net.
When you need a new connection just create a new one and if there is one
available in the pool it will be used.
Why don't you go with connection pooling instead of locking the application
which is a terrible scaling issue?
Btw, Lock just locks access to the application variables and is not intended
to provide a lock to the application.
 
Miha,

Thanks for the explanation. Now, if the pool exceeds the 100 connections
(just hypothetically), how can I check that and "hold up" the application
until one becomes available?

I would like to be able to eventually take 3 machines and set them in a
continuous While loop to hit the web service as fast as it can and would
like for the web service to be able to deal with it.

Thanks,
Brandon


Miha Markic said:
Hi Brandon,

SqlConnection is already pooled by ado.net.
When you need a new connection just create a new one and if there is one
available in the pool it will be used.
Why don't you go with connection pooling instead of locking the application
which is a terrible scaling issue?
Btw, Lock just locks access to the application variables and is not intended
to provide a lock to the application.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com


Brandon Potter said:
Hi,

I was trying to find the best way to go about managing a SqlConnection
object being used by a web service. In theory my thinking was that I would
create a shared class that would hold 1 SqlConnection, handle the connection
string and opening/closing of the connection, and expose the connection
Click to expand...
as
a
read only property from the class for the callers that need to assign it to
SqlCommands.

I also thought, that in theory, calling
HttpContext.Current.Application.Lock() just before opening the connection
[then the callers create a command, assign the connection, run a
SqlDataReader against the command] and
HttpContext.Current.Application.UnLock() just after the connection is
closed, that it would prevent other users from triggering the web
Click to expand...
service
to
throw a "There is already an open DataReader...." exception when being
accessed at the same time.

However, when the web service receives 10-15 hits/sec, the "There is already
an open DataReader..." exception is occasionally thrown. Can someone
enlighten me as to where my thinking was flawed? The grand plan was to try
and "hold" the other instances of the web service until the 1 connection
becomes available again, and I thought that's what Lock() and UnLock() did;
but now I am confused! :)

Thanks,
Brandon
Click to expand...
Click to expand...
 
HI Brandon,

You'll get an exception that connection pool is full.
You might catch the exception and re-try until one becomes available.
However, you might change the maxpoolsize or change the application a bit
(that you won't have a bottleneck like this).

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com


Brandon Potter said:
Miha,

Thanks for the explanation. Now, if the pool exceeds the 100 connections
(just hypothetically), how can I check that and "hold up" the application
until one becomes available?

I would like to be able to eventually take 3 machines and set them in a
continuous While loop to hit the web service as fast as it can and would
like for the web service to be able to deal with it.

Thanks,
Brandon


Miha Markic said:
Hi Brandon,

SqlConnection is already pooled by ado.net.
When you need a new connection just create a new one and if there is one
available in the pool it will be used.
Why don't you go with connection pooling instead of locking the application
which is a terrible scaling issue?
Btw, Lock just locks access to the application variables and is not intended
to provide a lock to the application.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com


Brandon Potter said:
Hi,

I was trying to find the best way to go about managing a SqlConnection
object being used by a web service. In theory my thinking was that I would
create a shared class that would hold 1 SqlConnection, handle the connection
string and opening/closing of the connection, and expose the
Click to expand...
Click to expand...
connection
as
a
read only property from the class for the callers that need to assign
Click to expand...
it
to
SqlCommands.

I also thought, that in theory, calling
HttpContext.Current.Application.Lock() just before opening the connection
[then the callers create a command, assign the connection, run a
SqlDataReader against the command] and
HttpContext.Current.Application.UnLock() just after the connection is
closed, that it would prevent other users from triggering the web
Click to expand...
service
to
throw a "There is already an open DataReader...." exception when being
accessed at the same time.

However, when the web service receives 10-15 hits/sec, the "There is already
an open DataReader..." exception is occasionally thrown. Can someone
enlighten me as to where my thinking was flawed? The grand plan was to try
and "hold" the other instances of the web service until the 1 connection
becomes available again, and I thought that's what Lock() and UnLock() did;
but now I am confused! :)

Thanks,
Brandon
Click to expand...
Click to expand...
Click to expand...
 
Good luck trying to run out of connections with just three machines :) the
100 connection default is outrageously high for most applications and is
mostly useful to allow you to catch connection leaks before you go into
production. Doing perf work on a Server being hit by 5000virtual clients
showed that changing the Max Pool Size to ten was the sweet spot for that
particular application, we never came close to running out of the ten
connections, of course this is my experience and ymmv.

If you do have an application that exceeds the 100 connection and you try to
open the 101st we will continue to attempt to connect for up to 15seconds,
if a connection does not become available then we will throw the dreaded max
pool size exception. Again 15 seconds waiting for a connection to become
available is an outrageous amount of time, I have never seen this exception
without a connection leak (unless you count the times I have seen them after
opening one hundred and one connections in a row w/out releasing any :)

Pooling is a good thing
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.

Brandon Potter said:
Miha,

Thanks for the explanation. Now, if the pool exceeds the 100 connections
(just hypothetically), how can I check that and "hold up" the application
until one becomes available?

I would like to be able to eventually take 3 machines and set them in a
continuous While loop to hit the web service as fast as it can and would
like for the web service to be able to deal with it.

Thanks,
Brandon


Miha Markic said:
Hi Brandon,

SqlConnection is already pooled by ado.net.
When you need a new connection just create a new one and if there is one
available in the pool it will be used.
Why don't you go with connection pooling instead of locking the application
which is a terrible scaling issue?
Btw, Lock just locks access to the application variables and is not intended
to provide a lock to the application.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com


Brandon Potter said:
Hi,

I was trying to find the best way to go about managing a SqlConnection
object being used by a web service. In theory my thinking was that I would
create a shared class that would hold 1 SqlConnection, handle the connection
string and opening/closing of the connection, and expose the
Click to expand...
Click to expand...
connection
as
a
read only property from the class for the callers that need to assign
Click to expand...
it
to
SqlCommands.

I also thought, that in theory, calling
HttpContext.Current.Application.Lock() just before opening the connection
[then the callers create a command, assign the connection, run a
SqlDataReader against the command] and
HttpContext.Current.Application.UnLock() just after the connection is
closed, that it would prevent other users from triggering the web
Click to expand...
service
to
throw a "There is already an open DataReader...." exception when being
accessed at the same time.

However, when the web service receives 10-15 hits/sec, the "There is already
an open DataReader..." exception is occasionally thrown. Can someone
enlighten me as to where my thinking was flawed? The grand plan was to try
and "hold" the other instances of the web service until the 1 connection
becomes available again, and I thought that's what Lock() and UnLock() did;
but now I am confused! :)

Thanks,
Brandon
Click to expand...
Click to expand...
Click to expand...
 
Back
Top