Multithreading: "There is already an open DataReader associated with this Connection"

[Guest]

Hi all

I am running multiple threads retrieving information from a SQL database, using sqlDataAdapter and DataSet. I regularly (but not constantly) get an exception "There is already an open DataReader associated with this Connection" thrown. Now, I know that the sqlDataAdapter uses DataReader under the hood, and that DataReader does not accept more than one connection open at a time. How might I work around this issue? Having the threads wait for eachother by calling Join() is not an option. I need to run a large number of threads calling this function

I'll add some code to (hopefully) clarify my issue. The function PollSubscriptions is called by multiple threads

private void PollSubscriptions(string devicePIN)
ArrayList subscriptions = new ArrayList()
ArrayList events = new ArrayList()
string subscriptionIDs = ""
XmlDocument XmlDoc = new XmlDocument()
try {
sqlListenerThreadDS.Clear()
sqlListenerThreadDA.SelectCommand.Parameters["@PIN"].Value = devicePIN.ToUpper()
sqlListenerThreadDA.Fill(sqlListenerThreadDS)
foreach (sqlListenerThreadDS.subscriptionsRow subscription in sqlListenerThreadDS.subscriptions)
subscriptions.Add(subscription.subscriptionID.ToString())


sqlSubscriptionsDA.Fill(sqlUserInfoDS)
foreach (string id in subscriptions)
sqlUserInfoDS.subscriptionsRow subscription = sqlUserInfoDS.subscriptions.FindBysubscriptionID(Convert.ToInt32(id))
subscription.lastPoll = System.DateTime.Now.ToUniversalTime()

sqlSubscriptionsDA.Update(sqlUserInfoDS)

catch (Exception ex)
logEntry = "Failed to poll subscriptions for device " + devicePIN.ToUpper() + ": " + ex.Message + "\n" + ex.StackTrace
eventLog.WriteEntry(logEntry, EventLogEntryType.Error)
}


Would anyone know a solution? sqlDataAdapter and DataSet are thread-safe, aren't they

much regard
Eri
(e-mail address removed)

 

[Marina]

Don't have all your threads share one connection. Because then, they are all
trying to execute queries on the same one - and you get the error.
You will have the same problem using a dataset - the issue is having the
connection running more then one query at once. It can't.

Hi all,

I am running multiple threads retrieving information from a SQL database,

using sqlDataAdapter and DataSet. I regularly (but not constantly) get an
exception "There is already an open DataReader associated with this
Connection" thrown. Now, I know that the sqlDataAdapter uses DataReader
under the hood, and that DataReader does not accept more than one connection
open at a time. How might I work around this issue? Having the threads wait
for eachother by calling Join() is not an option. I need to run a large
number of threads calling this function.

I'll add some code to (hopefully) clarify my issue. The function

PollSubscriptions is called by multiple threads:

private void PollSubscriptions(string devicePIN) {
ArrayList subscriptions = new ArrayList();
ArrayList events = new ArrayList();
string subscriptionIDs = "";
XmlDocument XmlDoc = new XmlDocument();
try {
sqlListenerThreadDS.Clear();
sqlListenerThreadDA.SelectCommand.Parameters["@PIN"].Value = devicePIN.ToUpper();
sqlListenerThreadDA.Fill(sqlListenerThreadDS);
foreach (sqlListenerThreadDS.subscriptionsRow subscription

in sqlListenerThreadDS.subscriptions) {

subscriptions.Add(subscription.subscriptionID.ToString());
}

sqlSubscriptionsDA.Fill(sqlUserInfoDS);
foreach (string id in subscriptions) {
sqlUserInfoDS.subscriptionsRow subscription = sqlUserInfoDS.subscriptions.FindBysubscriptionID(Convert.ToInt32(id));
subscription.lastPoll = System.DateTime.Now.ToUniversalTime();
}
sqlSubscriptionsDA.Update(sqlUserInfoDS);
}
catch (Exception ex) {
logEntry = "Failed to poll subscriptions for device " +

devicePIN.ToUpper() + ": " + ex.Message + "\n" + ex.StackTrace;

 

[Mike Swaim]

Hi all,

I am running multiple threads retrieving information from a SQL
database, using sqlDataAdapter and DataSet. I regularly (but not
constantly) get an exception "There is already an open DataReader
associated with this Connection" thrown. Now, I know that the
sqlDataAdapter uses DataReader under the hood, and that DataReader
does not accept more than one connection open at a time. How might I
work around this issue? Having the threads wait for each other by
calling Join() is not an option. I need to run a large number of
threads calling this function.

Each thread should have its own connection and datareaders. If you
keep all of your SQL Objects on a component, the easy solution is to
have each thread own its own copy of the component. Looking at your
code, I'm not sure why it needs to be executed by multiple threads.
In most cases where I've done heavily multithreaded DB work, I've
used a single reader that would batch together a block of rows (usually
around 1000-10000), and stick them in a threadsafe queue. The writers
would take a block off of the queue, process it, and then write the new
records to the database. This has worked pretty well with Oracle
databases. (I've had problems with timeout errors on MS Sql Server
where I'd overwhelm the database. It could just be that the server
hardware wasn't up to the task.)
SqlDataAdapter uses DataReaders under the hood, so I doubt that it's
threadsafe. AFAIK, DataSets are not threadsafe, and I don't know of any
way to easily make them threadsafe. (On the other hand, most
collections, such as ArrayLists and Hashtables can be made threadsafe
pretty easily.)

 

[Miha Markic [MVP C#]]

Hi Erik,

Hi all,

Would anyone know a solution? sqlDataAdapter and DataSet are thread-safe,

aren't they?

No, they aren't. However, if you always create a new connection from
connectionstring there should be no penalities nor problems using
dataadapters or readers (pooling).
Just don't forget to close them asap.
You will have to do manually threadsafety for dataset.

 

[Guest]

Thank you very much for your response

----- Miha Markic [MVP C#] wrote: ----

No, they aren't. However, if you always create a new connection fro
connectionstring there should be no penalities nor problems usin
dataadapters or readers (pooling)
Just don't forget to close them asap

How would I go about doing that? Creating a new SqlConnection and assigning that to the DataAdapter.SelectCommand.Connection?

regards
Erik

 

[Guest]

First of all, thanks for your response

----- Mike Swaim wrote: ----

Looking at you
code, I'm not sure why it needs to be executed by multiple threads

I have left out a bit of code to simplify the problem. Each thread is associated with a user device, and a subscription might reveal an event happened which needs to be handled. Polling (and the subsequent handling of events) for a particular user device should not affect the polling for other users. So this seemed like the most logical solution

Alternatively, I think I could have one thread doing the polling for all users and use seperate threads for handling events. IMHO this would only move the problem to a different area, though. Could be wrong. Multithreading is something new for me, and I am learning as I go

regards
Erik

 

[Mike Swaim]

----- Miha Markic [MVP C#] wrote: -----

No, they aren't. However, if you always create a new connection
from connectionstring there should be no penalities nor problems
using dataadapters or readers (pooling).
Just don't forget to close them asap.

How would I go about doing that? Creating a new SqlConnection and
assigning that to the DataAdapter.SelectCommand.Connection??

The thread should own both the connection and DataAdapter object.