4

Can a single SQL Server connection be shared among tasks executing in parallel? Each task would dequeue an item from a ParallelQueue, instantiate a SqlClient command, and insert a row into the table. Or does each command-instantiating-task require its own dedicated connection to the database? Can the connection(s) be instantiated and opened at the top of the method and then closed when the parallel tasks have been completed, or should each task instantiate, open, and close its connection?

2 Answers 2

2

A SQLServer Connection can be shared by multiple tasks executing in parallel, e.g. threads in a C# program or requests in an app server. But most use scenarios would require you to synchronize access to the Connection. A task will have to wait for the connection if another task is using it. By the time you build a shared connection mechanism that does not break or become a performance constraint for your parallel tasks, you have likely built a connection pool.

Another way to look at it is, yes you can share a pool of connections among a set of threads and let the connection pool handle dispensing connections and connection state cleanup. Microsoft's .net data access had very capable connection pooling as of 3 to 4 years ago when I last used it. With connection pooling you don't have to spend a lot of extra time opening and reopening connections. You don't have to hold open more connections than you actually need to use in a large long running application. You reuse connections when they are available, and automatically open a new connection when needed, automatically close them when they are no longer needed, e.g. after an inactivity period.

Connection Pooling may be a good answer. Just holding a connection for each task may be a good answer.

1

Usually an enterprise application server would have a datasource configurable through which an API could make requests. For example, WebSphere servers have a datasource which can be called via a defined JNDI name from a persistence framework (JPA, Hibernate, ...) within EJBs which handle threading on their own.

So yes. However, I would highly recommend utilizing a framework to do this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.