ADO Connection still connecting after Open?

  • Thread starter Thread starter Bryce K. Nielsen
  • Start date Start date
B

Bryce K. Nielsen

Suddenly this week, I've started getting this error message:

System.Data.SqlClient.SqlConnection(GetOpenConnection)ExecuteNonQuery
requires an open and available Connection. The connection's current state is
connecting.

I'm very puzzled since I'm not calling "ExecuteNonQuery" and I am calling
"Open", which I've always assumed was Synchronous, i.e. would not return
until either A) the connection was open or B) there was an error. Here is
effectively what I am doing:

SqlConnection SourceServer = new SqlConnection(SourceConnectionString);
SourceServer.Open();

SqlCommand sourcecmd = new SqlCommand();
sourcecmd.Connection = SourceServer;
sourcecmd.CommandType = CommandType.StoredProcedure;
sourcecmd.CommandText = "sp" + TableName + "Select";
sourcecmd.CommandTimeout = 1800;
sourcecmd.Parameters.Add("myid", SqlDbType.Int).Value = MyId;
SqlDataReader read = sourcecmd.ExecuteReader();

It's on that ExecuteReader that this error is happening. Anyone else
experiencing this? And what have you done to solve this?

-BKN
 
Dear Bryce,

I tried the same code as yours in Windows XP SP2, C# 2.0, SQL Server 2005
and it worked fine. As it has stopped all of a sudden some of the things
that could have caused this issue are (These are just my guesses)
1) Multiple connections/connection pooling issues
2) Any other process/thread doing the same

Also if you check the state of the connection just before the
ExecuteReader(), what do you get?
 
Suddenly this week, I've started getting this error message:

System.Data.SqlClient.SqlConnection(GetOpenConnection)ExecuteNonQ
uery requires an open and available Connection. The connection's
current state is connecting.

I'm very puzzled since I'm not calling "ExecuteNonQuery" and I
am calling "Open", which I've always assumed was Synchronous,
i.e. would not return until either A) the connection was open or
B) there was an error. Here is effectively what I am doing:

SqlConnection SourceServer = new
SqlConnection(SourceConnectionString); SourceServer.Open();

SqlCommand sourcecmd = new SqlCommand();
sourcecmd.Connection = SourceServer;
sourcecmd.CommandType = CommandType.StoredProcedure;
sourcecmd.CommandText = "sp" + TableName + "Select";
sourcecmd.CommandTimeout = 1800;
sourcecmd.Parameters.Add("myid", SqlDbType.Int).Value = MyId;
SqlDataReader read = sourcecmd.ExecuteReader();

It's on that ExecuteReader that this error is happening. Anyone
else experiencing this? And what have you done to solve this?
Click to expand...

Bryce,

This sounds like a race condition.

Does your app have multiple threads calling this code?

Chris.
 
This sounds like a race condition.
Does your app have multiple threads calling this code?
Click to expand...

Yes, each of these threads open a new connection, so you're probably right.
Is there a thread-safe way to create a connection and have it properly pull
from the connection pool?

-BKN
 
Also if you check the state of the connection just before the
ExecuteReader(), what do you get?
Click to expand...

I'm confused on this though, isn't Connection.Open() synchronous? I mean
won't it not come back until the connection is open? Is there a way to make
it synchronous?

-BKN
 
Yes, each of these threads open a new connection, so you're
probably right. Is there a thread-safe way to create a
connection and have it properly pull from the connection pool?
Click to expand...

Bryce,

What might do the trick is a thread-safe factory that ensures only
one thread at a time can construct a connection instance (untested):


public class SqlConnectionFactory
{
private static readonly object _lockObject = new object();

public static SqlConnection GetConnection(string connectionString)
{
lock(_lockObject)
{
return new SqlConnection(connectionString);
}
}
}


Also see Jon Skeet's excellent article on multithreading in .Net for
more info:

http://www.yoda.arachsys.com/csharp/threads/
 
public class SqlConnectionFactory
{
private static readonly object _lockObject = new object();

public static SqlConnection GetConnection(string connectionString)
{
lock(_lockObject)
{
return new SqlConnection(connectionString);
}
}
}
Click to expand...

Help me understand what's going on here. How does this make it thread safe?

I was thinking as a "hack" workaroud to add a Sleep(500) between each Object
Creation call...

-BKN
 
public class SqlConnectionFactory
{
private static readonly object _lockObject = new object();

public static SqlConnection GetConnection(string connectionString)
{
lock(_lockObject)
{
return new SqlConnection(connectionString);
}
}
}
Click to expand...

K, I think I understand this now (previously I was unaware of the lock()
clause). So because this is a static class, I don't have to worry about
multiple instances, correct? Also, correct my understanding of lock(),
ThreadA calls GetConnection and has LOCKed the object, when ThreadB calls
GetConnection, does it error because the object is already locked, or does
it wait until the object is unlocked?

Thanks for the help,

-BKN
 
K, I think I understand this now (previously I was unaware of the lock()
clause). So because this is a static class, I don't have to worry about
multiple instances, correct? Also, correct my understanding of lock(),
ThreadA calls GetConnection and has LOCKed the object, when ThreadB calls
GetConnection, does it error because the object is already locked, or does
it wait until the object is unlocked?
Click to expand...

Well I tried that and was still getting the error. It really feels like
there's a threaded race issue. So, as a workaround, I tried adding this
code:

while (MyConnection.State != ConnectionState.Open)
System.Threading.Thread.Sleep(500);

But it's still getting that error. I'm at a complete loss as to what's wrong
or what to do now...

-BKN
 
Bryce K. Nielsen said:
Help me understand what's going on here. How does this make it thread safe?

I was thinking as a "hack" workaroud to add a Sleep(500) between each Object
Creation call...
Click to expand...

The code above serializes the construction of SqlConnection(). In my
experience, it hasn't been necessary. I've written many servers which
all use SqlConnection and call Open, concurrently (but always with one
SqlConnection object per thread).

-- Barry
 
Bryce K. Nielsen said:
K, I think I understand this now (previously I was unaware of the lock()
clause). So because this is a static class, I don't have to worry about
multiple instances, correct? Also, correct my understanding of lock(),
ThreadA calls GetConnection and has LOCKed the object, when ThreadB calls
GetConnection, does it error because the object is already locked, or does
it wait until the object is unlocked?
Click to expand...

lock() acquires the monitor for the object. Every object logically has a
monitor associated with it (it's lazily allocated by the CLR when you
call Monitor.Enter on an object). A monitor is basically just like a
critical section: if the monitor is already acquired by another thread,
the thread blocks until it is released.

lock(obj) is equivalent to:

Monitor.Enter(obj);
try
{
}
finally
{
Monitor.Exit(obj);
}

(There are some other features, with Monitor.Pulse, Monitor.Wait etc.,
which give more of the Java-style monitor-related semantics if you need
them.)

-- Barry
 
Bryce K. Nielsen said:
Well I tried that and was still getting the error. It really feels like
there's a threaded race issue. So, as a workaround, I tried adding this
code:

while (MyConnection.State != ConnectionState.Open)
System.Threading.Thread.Sleep(500);

But it's still getting that error. I'm at a complete loss as to what's wrong
or what to do now...
Click to expand...

Are you sharing the connection between multiple threads? Does the
problem also occur with only a single thread?

-- Barry
 
The code above serializes the construction of SqlConnection(). In my
experience, it hasn't been necessary. I've written many servers which
all use SqlConnection and call Open, concurrently (but always with one
SqlConnection object per thread).
Click to expand...

Well, that's what I'm trying to do (as you replied on my other thread). I
have these 12 threaded processes all creating a new SqlConnection but appear
to be running into a concurrency issue. I tried the above but still have
problems, even after adding that while loop. I'm at a complete loss...

-BKN
 
Are you sharing the connection between multiple threads? Does the
problem also occur with only a single thread?
Click to expand...

No, I have 12 different threads, spawned from delegates (which I'm in the
process of moving away from delegates and using Thread.Start instead) and
the first thing that happens in my spawned method is to create a new
connection object.

The strange thing about all of this is I only recently started getting these
errors. I can only thing of 2 things that have changed: I moved all the
"process" code from the Form onto it's own thread, and I added a lot of
Connection.Close() calls, to make sure previous accesses to the connection
were released back into the connection pool.

What really puzzles me is why this is happening at all. Isn't
Connection.Open synchronous? I mean, when I call Open(), shouldn't my
process "hang" until it's the connection has actually opened?

-BKN
 
Bryce K. Nielsen said:
No, I have 12 different threads, spawned from delegates (which I'm in the
process of moving away from delegates and using Thread.Start instead) and
the first thing that happens in my spawned method is to create a new
connection object.

The strange thing about all of this is I only recently started getting these
errors. I can only thing of 2 things that have changed: I moved all the
"process" code from the Form onto it's own thread, and I added a lot of
Connection.Close() calls, to make sure previous accesses to the connection
were released back into the connection pool.

What really puzzles me is why this is happening at all. Isn't
Connection.Open synchronous? I mean, when I call Open(), shouldn't my
process "hang" until it's the connection has actually opened?
Click to expand...

Yes, I would expect that, and I've never had any kind of race between
Open() and first use of the connection.

Are you possibly using the connection after you've called Close(),
indirectly through an IDataReader or something? Did you pass
CommandBehavior.CloseConnection to the ExecuteReader method, yet close
the reader after you've closed the connection (possibly causing a
connection that was passed back to the pool, then handed out again, to
be closed early - total guess, I don't know the implementation)?

What's always worked for me is a call stack that ultimately (after
everything has been stripped away) looks somewhat like this:

using (SqlConnection conn = ...)
{
// ...
using (IDataReader reader = cmd.ExecuteReader())
// ...
}

-- Barry
 
What's always worked for me is a call stack that ultimately (after
everything has been stripped away) looks somewhat like this:

using (SqlConnection conn = ...)
{
// ...
using (IDataReader reader = cmd.ExecuteReader())
// ...
}
Click to expand...

That's what my code has morphed into as well, just to make sure everything
is chucked before being used again (I hate GC). I basically have this:

using (SqlConnection conn = new SqlConnection(SourceConnectionString))
{
using (SqlDataReader read = conn.ExecuteReader())
{
using (SqlBulkCopy bc = new SqlBulkCopy(DestinationConnectionString))
{
}
}
}

And it's difficult to determine where exactly the errors are coming. I have
form notification events in between each step, and this error sometimes
happens before the reader, after the reader, before the BulkCopy, etc. And
due to that (in effect I have 24 connections going, 12 on the source server,
12 on the destination server) I'm not sure which server even it's happening
on (but I think it's on the "source" server). Other odd thing is I'm not
calling ExecuteNonQuery *anywhere* in my code. There's a couple
ExecuteScalar and ExecuteReader, but no NonQuery.

And what completely irks me is that when I had the code executing on the
Form, I had non of these problems :(

-BKN
 
Bryce K. Nielsen said:
And it's difficult to determine where exactly the errors are coming. I have
form notification events in between each step, and this error sometimes
happens before the reader, after the reader, before the BulkCopy, etc. And
due to that (in effect I have 24 connections going, 12 on the source server,
12 on the destination server) I'm not sure which server even it's happening
on (but I think it's on the "source" server). Other odd thing is I'm not
calling ExecuteNonQuery *anywhere* in my code. There's a couple
ExecuteScalar and ExecuteReader, but no NonQuery.

And what completely irks me is that when I had the code executing on the
Form, I had non of these problems :(
Click to expand...

What does the full stack trace for the exception look like?

-- Barry
 
Help me understand what's going on here. How does this make it
thread safe?
Click to expand...

Bryce,

The documentation for SqlConnection states that its instance methods
are not thread safe. The lock statement around the constructor
ensures that only one thread at a time can create a new instance
of an SqlConnection. If the lock statement weren't there, then
multiple threads could conceivably try to create several
instances of SqlConnection at the same time, possibly corrupting
the state of the connection pool in the process.

I think you need to take some time and get your hands dirty with
regard to multithreading. The link to Jon Skeet's article I posted
earlier is an excellent introduction to the subject. Here are some
more:

..Net Documentation: Threading
http://msdn.microsoft.com/library/en-us/cpguide/html/cpconthreading.asp

C# Programmer's Reference: Threading Tutorial
http://msdn.microsoft.com/library/en-us/csref/html/vcwlkThreadingTutorial.asp

..Net Documentation: Threading Design Guidelines
http://msdn.microsoft.com/library/en-us/cpgenref/html/cpconThreadingDesignGuidelines.asp

MSDN: Safe Thread Synchronozation
http://msdn.microsoft.com/msdnmag/issues/03/01/NET/TOC.ASP

MSDN: Programming the Thread Pool in the .NET Framework
http://msdn.microsoft.com/library/en-us/dndotnet/html/progthrepool.asp


I see your name over in the Delphi newsgroups, so this link might
be a little more palatable than the .Net/C# ones. It's a
small e-book describing the basics of multithreading in Delphi.
The concepts are exactly the same in both Delphi and C#. If you know
multithreading in one, then you basically know both:

http://codecentral.borland.com/Item.aspx?id=14809
 
Chris R. Timmons said:
The documentation for SqlConnection states that its instance methods
are not thread safe.
Click to expand...

Bryce has already indicated that he's using one SqlConnection object per
thread, so it isn't a sharing issue.
The lock statement around the constructor
ensures that only one thread at a time can create a new instance
of an SqlConnection. If the lock statement weren't there, then
multiple threads could conceivably try to create several
instances of SqlConnection at the same time, possibly corrupting
the state of the connection pool in the process.
Click to expand...

This isn't necessary. Constructors aren't instance methods, and besides,
the whole allocation process is thread-safe.

Constructing the connection object does next to nothing. The connection
gets allocated when you call Open(). If you had to serialize all calls
to all instance methods for all connections anywhere in the application,
well, that would be a serious scalability problem. It isn't the case.

-- Barry
 
Arrgggg, this is killing me. I've tried all sorts of different things and am
STILL getting a problem.

Here's a brief overview of what I'm doing:
- Form creates a MainProcess object that starts a Thread executing a method
on this object.
- This method does a bunch of non database preparation steps
- The "mid" step selects data from 12 tables from a "source" SQLServer and
SqlBulkCopy's them into a "destination" SQLServer
- To speed things up, these 12 transfers are threaded.
- This is how I decided to thread the transfers:
- 12 objects are created, passed in ConnectionStrings and TableNames to
use
- 12 threads are created, executing the object's Process() method
- Inside Process() a new SqlConnection is created using the
SourceConnectionString
- A SqlCommand.ExecuteReader is called to get the data from the table
- A new SqlBulkCopy object is created using the
DestinationConnectionString and executed with the source SqlDataReader.

Here's a listing of the various errors I've been getting:
- ExecuteNonQuery requires an open and available Connection. The
connection's current state is connecting.
- Timeout expired. The timeout period elapsed prior to obtaining a
connection from the pool. This may have occurred because all pooled
connections were in use and max pool size was reached.
- A transport-level error has occurred during connection clean-up.
(provider: TCP Provider, error: 0 - The specified network name is no longer
available.)
- The SqlDbType enumeration value, 0, is invalid. Parameter name:
SqlDbType

Here's a summary of everything I've tried to fix this:
- Create SQL Connections in the proposed thread-safe manner
- Use a different ConnectionString (so not pulling from connection pool of
other SQL statements being executed)
- Added "Pooling='false';" to ConnectionString to make sure new Connection
is used.
- Added all Connection.Close call to AFTER thread.join() (in case for some
reason the SQLConnection was somehow shared between objects and once one had
completed and closed, the was causing the other to fail)
- Tried putting the SqlConnection inside a USING() clause, forcing a new
connection to be created each time.

Nothing seems to be working for me. And what's really getting my goat is
that all of this was working with ZERO problems when I had the main process
executing on the Form's thread. ALL of the errors make NO sense to me at
all. The only SqlDbType I'm using is SqlDbType.Int (I'm not doing anything
dynamic there). Why would that work great 99% of the time and suddenly stop
with one or two transfers. Same with the TCP connection error. The pooling
error I'm completed baffled (but made me try different things, like forcing
no pooling, etc). And the Connection State error really puzzles me since the
Connection.Open was called, shouldn't it be Open?!?

Any other suggestions?

-BKN
 
Back
Top