MSSQL & ADO & threads ???!! 
Author Message
 MSSQL & ADO & threads ???!!

Hi!

I try to use ADO to work with MSSQL Server but ...

Is it possible to use one Connection object declared globaly  in many
threads concurently ?
I'd like to get 1 connection to SQL server instead of ~100 from every
running thread.
So now I declare 1 Connection object per thread & establish separate
connection from within (by method Open).
I've found that such a solution is possible till ~64 threads. Every next
doesn't work though it doesn't return any error ! ??

ps

--
Adam Gros


_________/)____/)_/)__/)____



Sun, 08 Sep 2002 03:00:00 GMT  
 MSSQL & ADO & threads ???!!
Hi!

I've forgot to write that I try to do it with VC++ 6.0 :-)

Quote:

> Hi!

> I try to use ADO to work with MSSQL Server but ...

> Is it possible to use one Connection object declared globaly  in many
> threads concurently ?
> I'd like to get 1 connection to SQL server instead of ~100 from every
> running thread.
> So now I declare 1 Connection object per thread & establish separate
> connection from within (by method Open).
> I've found that such a solution is possible till ~64 threads. Every next

> doesn't work though it doesn't return any error ! ??

> ps

> --
> Adam Gros


> _________/)____/)_/)__/)____

--
Adam Gros


_________/)____/)_/)__/)____



Sun, 08 Sep 2002 03:00:00 GMT  
 MSSQL & ADO & threads ???!!
Adam,
    I've used ADO connections to SQL in several different threads.  However, you
cannot use a connection by more than one thread at a time.  You'll need each thread
to somehow lock the connection (from some kind of pool), use it till the thread is
done, then unlock the connection for another thread to use.

John


Quote:
> Hi!

> I've forgot to write that I try to do it with VC++ 6.0 :-)


> > Hi!

> > I try to use ADO to work with MSSQL Server but ...

> > Is it possible to use one Connection object declared globaly  in many
> > threads concurently ?
> > I'd like to get 1 connection to SQL server instead of ~100 from every
> > running thread.
> > So now I declare 1 Connection object per thread & establish separate
> > connection from within (by method Open).
> > I've found that such a solution is possible till ~64 threads. Every next

> > doesn't work though it doesn't return any error ! ??

> > ps

> > --
> > Adam Gros


> > _________/)____/)_/)__/)____

> --
> Adam Gros


> _________/)____/)_/)__/)____



Sun, 08 Sep 2002 03:00:00 GMT  
 MSSQL & ADO & threads ???!!
ADO is thread safe (at least that's what the doc says).  I used 1 connection
between multiple threads all the time (use a ConnectionPtr).  Just be
careful on how you reopen the connection if it fails (i.e., thread safe
code).  You don't want 50 threads all trying to reopen the same connection
at the same time.


Quote:

> I try to use ADO to work with MSSQL Server but ...

> Is it possible to use one Connection object declared globaly  in many
> threads concurently ?
> I'd like to get 1 connection to SQL server instead of ~100 from every
> running thread.
> So now I declare 1 Connection object per thread & establish separate
> connection from within (by method Open).
> I've found that such a solution is possible till ~64 threads. Every next
> doesn't work though it doesn't return any error ! ??

> ps

> --
> Adam Gros


> _________/)____/)_/)__/)____



Sun, 08 Sep 2002 03:00:00 GMT  
 MSSQL & ADO & threads ???!!
I had read that ADO was completely thread safe.  Is that not true?  I always
assumed I could do 2 Execute statements in 2 different threads at the same
time on the same connection.  I just assumed that ADO would "queue" the SQL
operations if it couldn't process them in parallel.  Hum, if not, then I had
better add some thread safe code in...I better go test this out!


Quote:
> Adam,
>     I've used ADO connections to SQL in several different threads.
However, you
> cannot use a connection by more than one thread at a time.  You'll need
each thread
> to somehow lock the connection (from some kind of pool), use it till the
thread is
> done, then unlock the connection for another thread to use.

> John



> > Hi!

> > I've forgot to write that I try to do it with VC++ 6.0 :-)


> > > Hi!

> > > I try to use ADO to work with MSSQL Server but ...

> > > Is it possible to use one Connection object declared globaly  in many
> > > threads concurently ?
> > > I'd like to get 1 connection to SQL server instead of ~100 from every
> > > running thread.
> > > So now I declare 1 Connection object per thread & establish separate
> > > connection from within (by method Open).
> > > I've found that such a solution is possible till ~64 threads. Every
next

> > > doesn't work though it doesn't return any error ! ??

> > > ps

> > > --
> > > Adam Gros


> > > _________/)____/)_/)__/)____

> > --
> > Adam Gros


> > _________/)____/)_/)__/)____



Sun, 08 Sep 2002 03:00:00 GMT  
 MSSQL & ADO & threads ???!!
After some quick testing, ADO Connections are not thread safe (unless you
call getting an exception thread safe).  However, parsing Recordsets appears
to be safe (at least with Forward Only cursors).  It just seems like you
can't have 2 queries executing queries at the same time.  Nexting through 2
Recordsets over the same Connection didn't cause me any problems.  In any
case, thread safe code for ADO is the right answer.

If I find where I read ADO was thread safe I'll publish...


Quote:
> I had read that ADO was completely thread safe.  Is that not true?  I
always
> assumed I could do 2 Execute statements in 2 different threads at the same
> time on the same connection.  I just assumed that ADO would "queue" the
SQL
> operations if it couldn't process them in parallel.  Hum, if not, then I
had
> better add some thread safe code in...I better go test this out!



> > Adam,
> >     I've used ADO connections to SQL in several different threads.
> However, you
> > cannot use a connection by more than one thread at a time.  You'll need
> each thread
> > to somehow lock the connection (from some kind of pool), use it till the
> thread is
> > done, then unlock the connection for another thread to use.

> > John



> > > Hi!

> > > I've forgot to write that I try to do it with VC++ 6.0 :-)


> > > > Hi!

> > > > I try to use ADO to work with MSSQL Server but ...

> > > > Is it possible to use one Connection object declared globaly  in
many
> > > > threads concurently ?
> > > > I'd like to get 1 connection to SQL server instead of ~100 from
every
> > > > running thread.
> > > > So now I declare 1 Connection object per thread & establish separate
> > > > connection from within (by method Open).
> > > > I've found that such a solution is possible till ~64 threads. Every
> next

> > > > doesn't work though it doesn't return any error ! ??

> > > > ps
> > > > Please, send any answer to my email


- Show quoted text -

Quote:

> > > > --
> > > > Adam Gros


> > > > _________/)____/)_/)__/)____

> > > --
> > > Adam Gros


> > > _________/)____/)_/)__/)____



Sun, 08 Sep 2002 03:00:00 GMT  
 MSSQL & ADO & threads ???!!
It's Ok if you open 1 recordset and browse it from many threads, but what about
openning different recordsets ?
What I wanted to do was every thread uses its own recordset (because they can be
defferent).

Quote:

> ADO is thread safe (at least that's what the doc says).  I used 1 connection
> between multiple threads all the time (use a ConnectionPtr).  Just be
> careful on how you reopen the connection if it fails (i.e., thread safe
> code).  You don't want 50 threads all trying to reopen the same connection
> at the same time.



> > I try to use ADO to work with MSSQL Server but ...

> > Is it possible to use one Connection object declared globaly  in many
> > threads concurently ?
> > I'd like to get 1 connection to SQL server instead of ~100 from every
> > running thread.
> > So now I declare 1 Connection object per thread & establish separate
> > connection from within (by method Open).
> > I've found that such a solution is possible till ~64 threads. Every next
> > doesn't work though it doesn't return any error ! ??

> > ps

> > --
> > Adam Gros


> > _________/)____/)_/)__/)____

--
Adam Gros


_________/)____/)_/)__/)____



Mon, 09 Sep 2002 03:00:00 GMT  
 MSSQL & ADO & threads ???!!
ADODB.Connection is apartment threaded. Therefore, if one wants to use it
from different threads, she has to CoMarshalInterThreadInterfaceInStream it
from the creator apartment, and CoGetInterfaceAndReleaseStream from the
thread that uses it.

You cannot use in-process interface pointer from another thread just by
sharing it in the memory, since the apartment-threaded object doesn't need
to be thread-safe (and usually isn't). Doing so breaks the object.

Goran.



Sun, 22 Sep 2002 03:00:00 GMT  
 MSSQL & ADO & threads ???!!
Hi,
  The need for CoMarshalInterThreadInterfaceInStream() is not determined by the
threading model of the interface host -- it is determined by the client's threading
model.  This means that if you have threads in an MTA, and decide to use an
ADODB.Connection object, every one of the threads in the MTA can use the Connection
interface without using CoMarshalInterThreadInterfaceInStream().
  Sure, the interface/object you're using may not be thread safe.  But that only
means that you need to write your code carefully so that two threads don't use it
simultaneously.  It rarely means that only a certain thread may use it.

John

Quote:

> ADODB.Connection is apartment threaded. Therefore, if one wants to use it
> from different threads, she has to CoMarshalInterThreadInterfaceInStream it
> from the creator apartment, and CoGetInterfaceAndReleaseStream from the
> thread that uses it.

> You cannot use in-process interface pointer from another thread just by
> sharing it in the memory, since the apartment-threaded object doesn't need
> to be thread-safe (and usually isn't). Doing so breaks the object.

> Goran.



Sun, 22 Sep 2002 03:00:00 GMT  
 
 [ 9 post ] 

 Relevant Pages 

1. MSSQL & ADO & threads ???!!

2. ADO & Threads

3. Client Server && Threads

4. Interprocess Communication && Multiple threads of execution

5. ***&&&>>> HELP --- CXL --- HELP <<<&&&****

6. Linker error 2001 @&(#&@#&$@

7. Linker error 2001 @&(#&@#&$@

8. Using ADO.NET & Windows Service

9. ADO.NET & ADSI

10. ADO/OLEDB & VC5

11. ADO & events in ATL

12. automation clients & ado recordsets

 

 
Powered by phpBB® Forum Software