multi-thread tasks using a single connection object concurrently

is it possible to use a single connection object shared by several
tasks where each task is handled by a thread? these tasks call stored
procedures that return record sets, no editing, update or delete.

my objective is that db connection is expensive and each user can only
have 1 connection object. each user submits a request to the web
server, and the result web page construction consists of a number of
result sets obtained from several stored procedure from the same
database instance.

i could obtained my result sets by making each sp call one at a time
but was thinking whether i could instead break up the tasks, create a
thread for each task that shares the same db connection object
concurrently. the connection object will be alive until all tasks are
completed, that is if the final object ive could be achieved in a
shorter time. No connection pooling here.

i am wondering over questions like:

1. within the same db instance, if 2 stored procedures need to access
the same table, does SQLserver queue-up and service one request at a
time.

2. would there be any problem using the connection object this way,
sharing between multiple theads?

3. is it possible that a 'dead-lock' may occur within the DB?

of cos this whole idea can be absurd and into the trash if the
connection object doesn't support multi-thread and is queue based in
the first place.

pardon me if my SQL server basics is off track. been doing too many
other things.

thanks,
mengmeng (hui_km@.star-quest.com) writes:
> i could obtained my result sets by making each sp call one at a time
> but was thinking whether i could instead break up the tasks, create a
> thread for each task that shares the same db connection object
> concurrently. the connection object will be alive until all tasks are
> completed, that is if the final object ive could be achieved in a
> shorter time. No connection pooling here.

I honestly don't know if you can share connection objects between threads,
but in any case it seems like a pointless thing, because the connection
object would be a common resource. You cannot submit to queries on the
same connection object at the same time. ...ah, wait, actually with ADO
you can, but what happens is that ADO opens a new connection behind your
back for you.

> 1. within the same db instance, if 2 stored procedures need to access
> the same table, does SQLserver queue-up and service one request at a
> time.

Depends on how they procedures access the tables. For read operations,
the procedures may well execute in parallel. But if one process starts
to update, the other process will be blocked. (Unless it uses an
isoalation level of uncommitted.)

> 2. would there be any problem using the connection object this way,
> sharing between multiple theads?

As I said, it would be a pointless thing to do.

> 3. is it possible that a 'dead-lock' may occur within the DB?

Yes. That is not to say that you will experience dead-locks, only that
it could happen to you.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||hi erland,

i was thinking the connection object is like a handle to a database
connection, but if has its own state, and if its not a thread-safe
object, then it would not work at all. even if i can wrap the connection
object in thread-safe codes, if it cannot handle thread context switch,
the idea is useless. the part about ado opening a connection on its own
accord is interesting though. i didn't know that at all.

the piece of info is helping decide how i should implement and write my
app server components. thanks for your response.

regards,
meng

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!