The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Worker Threads and Database access

I was happily designing away my worker queue system when I came across an article about sharing connections between threads in my databases documentation.  Apparently I can have multiple connections and concurrent access but not a single connection shared between worker threads.

This kind of forces my nice pristine generic Work Queue model out the window as, from what I understand now, I have to either perform a database connection for each job, which I expect to be expensive, or I have to specialise my threads so that each thread has it's own connection it can reuse between jobs.

Given that you guys have gone before me, what the best way forward for me at this point? Is there any gotcha's that I'm going to hit? Is there a better way in general?
Chris Brodie
Thursday, June 08, 2006
Depends on the technology you're using.

For example if you're using ADO.NET, you would typically use a database connection for each job, but it's not normally expensive because of the connection pooling that goes on behind the scenes.
Thursday, June 08, 2006
"I have to specialise my threads so that each thread has it's own connection it can reuse between jobs."

That's the sensible way to do it. Connections tend to be expensive to create and tear down, having them hanging about in threads isn't such a big deal. Some RAM, but that's a problem that tech inflation takes care of.

If the issue is that not all work units need a connection (and therefore under that scenario many of them will be idle), then you could have a connection pool. Worker threads pull work off the work queue. If they need a connection they take one from the pool to use on that unit and release it when they're done. That way you can have less connections than workers if you're limited by the availability in some way.
Katie Lucas
Thursday, June 08, 2006
Thanks a lot guys. I had not considered connection pooling I was thinking about it the wrong was I was going to implement multiple specilised worker thread queues, which I didn't much like the thoughts of.

Chris Brodie
Thursday, June 08, 2006
If each piece of work is a single transaction then you may be able to get away with one connection per thread which is simpler and more efficient than pooling.
son of parnas
Thursday, June 08, 2006

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz