ADO Connection still connecting after Open?

[Barry Kelly]

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

OK. It looks a tough one to crack.

Does it happen on other machines (i.e. it's not some broken install of
the SQL client)?

Have you tried detaching it from your user interface, so it runs only in
the console?

The reasons I'm suggesting this:

1) Remove the UI interaction / cross-thread communication as a possible
source of error.
2) Reduce complexity for the next phase of analysis.

If the problem still occurs on the console, there are some things we can
look into with the SOS debugger extensions (.load sos in Immediate
window or using WinDbg). In particular, I'd be looking for inappropriate
sharing of objects:

!dumpheap -type Sql will find all objects with Sql in the name of the
type
!gcroot <addr> will find what's pointing to the object and keeping it
alive
!dumpobj <addr> will dump the values of dword fields of an object.

Once an object is found, it's easier to work with if you can find it in
the VS debugger, though.

It still sounds like some kind of cross-thread sharing problem; either
that, or some kind of heap corruption that doesn't cause a CLR crash.
I'm wondering: is it that bulk-copy isn't safe for concurrent
operations? That doesn't sound likely to me.

Another idea, to fix your problem without getting too bogged down:
structure the copying operation as a separate application, and run
multiple copies of that application in parallel. You can use
System.Diagnostics.Process for that, along with Process.WaitForExit(),
using a very similar approach to Thread.Join().

-- Barry

 

[Barry Kelly]

Does it happen on other machines (i.e. it's not some broken install of
the SQL client)?

Though on second thoughts, the .NET SQL Server client is wholly managed,
IIRC; still, it can't hurt to try.

-- Barry

 

[Bryce K. Nielsen]

Does it happen on other machines (i.e. it's not some broken install of

the SQL client)?

Good question, let me run it on another server to see. That was actually
part of my question, the only other thing that changed recently was a
Windows Update on my dev machine.

Have you tried detaching it from your user interface, so it runs only in
the console?

I'll try that as well. Classic debugging, remove as many variables as
possible to isolate the problems.

-BKN

 

[Bryce K. Nielsen]

It still sounds like some kind of cross-thread sharing problem; either

that, or some kind of heap corruption that doesn't cause a CLR crash.
I'm wondering: is it that bulk-copy isn't safe for concurrent
operations? That doesn't sound likely to me.

I should add that when I made SqlConnection thread-safe through the
ConnectionFactory, I decided to do the same thing with the SqlBulkCopy as a
"just in case" measure.

-BKN

 

[Barry Kelly]

I should add that when I made SqlConnection thread-safe through the
ConnectionFactory, I decided to do the same thing with the SqlBulkCopy as a
"just in case" measure.

FWIW, I don't think serializing the construction could possibly help.
I've looked in Reflector, and traced through the construction logic; it
looks like the work was done to make it threadsafe.

I can't imagine it being any other way, frankly; constructing an object
is (somewhat) like calling a static method. The possibility of two
threads executing a method on the same object at the same time is the
primary reason that instance methods unsafe for threading in the absence
of thread safety work; that's impossible with constructors.

The general pattern for static methods and properties in .NET is that
they are thread-safe.

-- Barry

 

[Bryce K. Nielsen]

FWIW, I don't think serializing the construction could possibly help.

I've looked in Reflector, and traced through the construction logic; it
looks like the work was done to make it threadsafe.

I ran on a different machine, and got one of the three errors each time. I
started researching one of these errors and found this:

Here's another example. Let's say the application loses network connectivity
while its reading results from a SqlDataReader.
In ADO.NET 1.1 this would have resulted in an exception message - "General
network error. Check your network documentation."
This same scenario in ADO.NET 2.0 gives the error message - "A
transport-level error has occurred when receiving results from the server.
(provider: TCP Provider, error: 0 - The specified network name is no longer
available.)".

I'm using a SqlDataReader, and some of the errors seem to happen at either
ExecuteReader or in the SqlBulkCopy (which is probably pumping through the
reader).

This is making me wonder if this is happening:
- I have the 12 threads creating connections
- These 12 connections are pulled from the connection pool
- Somehow, the connection really is shared (due to being pulled from pool)
- One of the 12 finishes the process and closes the connection
- This causes one of the other connections who happened to be sharing this
connection to have strange errors occur.

I don't know if this theory is at all accurate though, since I added
"Pooling='false';" to the connection string, so it shouldn't be pooling?

-BKN

 

[Barry Kelly]

I'm using a SqlDataReader, and some of the errors seem to happen at either
ExecuteReader or in the SqlBulkCopy (which is probably pumping through the
reader).

This is making me wonder if this is happening:
- I have the 12 threads creating connections
- These 12 connections are pulled from the connection pool
- Somehow, the connection really is shared (due to being pulled from pool)
- One of the 12 finishes the process and closes the connection
- This causes one of the other connections who happened to be sharing this
connection to have strange errors occur.

I don't know if this theory is at all accurate though, since I added
"Pooling='false';" to the connection string, so it shouldn't be pooling?

Have you tried using the tracing tools (SQL Profiler, ADO.NET trace)?

This link looks interesting:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/tracingdataaccess.asp

It's for .NET 2.0 Beta 1, but the code calling Bid is all still there.
There should be more up to date information on Google.

I don't have any more suggestions beyond what I've already suggested.

-- Barry

 

[Bryce K. Nielsen]

I think I finally figured it out. And as is the case with most bugs, it's
happening in a completely different area than I suspected it to happen.
You'd think 10 years of debugging applications would have taught me to look
in the least suspectful areas. Bugs are always in the last place you look...

In my data processing threads, I'm raising events at each step of the way to
alert the GUI of the progress. This does 2 things, it updates a couple
visual controls (a progress bar, label, adds to a memo, etc) AND it does a
simple insert into a log table. Previously, since all the main processing
was done on the Form's thread, I called Form.Invoke(event), so all the
logging was also done on the Form's thread. I have now created a new
"process" thread that does the main processing PLUS all the logging. It
creates the 12 data-processing threads. It has a bunch of events the Form
now subscribes to, and passes-through events from the data-processing
threads. It looks like the errors I'm getting are happening on the Logging
stage.

This answers a lot of questions:
- The simple insert is ExecuteNonQuery (something that was puzzling me for
a long time, since no where else am I executing non query but the error
message says I am).
- Answers why this used to work, since the event to update the GUI and log
the event was the same and was being called on Form.Invoke, and thus always
executing on the same thread. Now on 2 different threads, it's failing.

I am wondering now what would be the best way to fix this. Here's how it's
working:
- Form creates MainProcessing object/thread and subscribes to events
- MainProcessing thread creates 12 sub DataProcessing threads and
subscribes to events
- sub DataProcessing thead raises an event to MainProcessing thread.
- MainProcessing thread logs the event in the database ** this is where
error is occuring ** and then raises same event to Form.
- Form updates GUI controls on it's own thread.

Since the thread is raising event to object, it's executing on that thread
and not on the original object's thread. Is there a way to merge this in the
event? Otherwise what, am I going to need to create/destroy the Connection
every time I log, to guarantee that the connection is opened on the
appropriate thread?

-BKN

 

[Barry Kelly]

I think I finally figured it out. And as is the case with most bugs, it's
happening in a completely different area than I suspected it to happen.
You'd think 10 years of debugging applications would have taught me to look
in the least suspectful areas. Bugs are always in the last place you look... [...]
was done on the Form's thread, I called Form.Invoke(event), so all the
logging was also done on the Form's thread. I have now created a new

I had a deep suspicion that it was your UI interface logic that was
causing the problem I didn't want to say it explicitly...

I tried to hint with:

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

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

* Have you tried detaching it from your user interface, so it runs only
in the console?

.... slowly getting more explicit

This answers a lot of questions:
- The simple insert is ExecuteNonQuery (something that was puzzling me for
a long time, since no where else am I executing non query but the error
message says I am).

The stack trace should have told you this straight away
(Exception.StackTrace).

I am wondering now what would be the best way to fix this. Here's how it's
working:
- Form creates MainProcessing object/thread and subscribes to events
- MainProcessing thread creates 12 sub DataProcessing threads and
subscribes to events
- sub DataProcessing thead raises an event to MainProcessing thread.
- MainProcessing thread logs the event in the database ** this is where
error is occuring ** and then raises same event to Form.
- Form updates GUI controls on it's own thread.

Since the thread is raising event to object, it's executing on that thread
and not on the original object's thread. Is there a way to merge this in the
event?

Can you rephrase these two sentences? Merge what into what, exactly?

Otherwise what, am I going to need to create/destroy the Connection
every time I log, to guarantee that the connection is opened on the
appropriate thread?

ISTM that you need a separate connection if you want to put the logging
in the MainProcessing thread. Wouldn't it be better to do you logging on
the thread that does the work, where it's part of the same transaction
etc.? (I don't know how you're handling transactions etc.)

This blog (see the comments) has good information on the transaction
issues for cross-thread transactions, to make all the connections use
the same transaction:

http://blogs.msdn.com/angelsb/archive/2004/07/07/175586.aspx

-- Barry

 

[Bryce K. Nielsen]

* Have you tried detaching it from your user interface, so it runs only

in the console?

Unfortunately, the problem would have still been there. The problem wasn't
with the GUI, but rather with the DB-logging.

Can you rephrase these two sentences? Merge what into what, exactly?

Here's how the threads are working:

- FormThread launches a MainProcessThread
- MainProcessThread does a bunch of junk (including DB logging)
- MainProcessThread launches 12 DataProcessThreads
- DataProcessThreads SqlBulk copy junk
- DataProcessThreads raise events that are DB-Logged in the
MainProcessThread.

Originally my SqlConnection object for my DBLogging was only created once,
but open/closed on each log. Originally this all happened on the FormThread,
and whenever the DataProcessThreads raised an event, I called
Form.Invoke(event) to log it. So, the connection was created on the form
thread, then open/closed still on the form thread.

Now, separating the main processing from the Form thread, the DBLogging is
in this thread too. Problem is, events raised from the DataProcess thread
are executing on the DataProcess thread, not the MainProcessThread. Since my
object is not a Control, I don't have any way of invoking this event on the
MainProcess thread (where the Connection object was created).

So here's my current delima. I have an object on ThreadA that subscribes to
an event of an object on ThreadB. When ThreadB raises the event, how do I
get the code to execute on ThreadA?

My current workaround is to just create the SqlConnection object each time I
log a message. Seems a little expensive, but at least it's working now.

-BKN