Multiple threads

Is it safe to use one database connection shared across multiple threads. Each thread has its own set of data i.e its own SQL command etc, but sharing a single database connection. Actually the scenario is that in a web site I want to open a single database connection when web application starts up. On each page hit will construct a SQL command and query/update the database.

I just want to know if multiple requests come at the same time will threads be blocked if I share global connection. What will happen if one request is trying to retrieve the data and another request is updating the database using the same connection?

Thanks

Hi!

I don't think it's a good idea to share DB connection across threads. One of them may open it, another may close, another may break... So many things to keep in mind.

DB connections are fast for open/close and if you make many of them, internally there is pool of connections, so framework will optimize access. If you do not have proven performance drop because of frequent connections opening, then don't waste your time with it - create, open and close connections as logic requires.

One more thing - if you have local connections, then your code is less dependent and can be better maintained, supported, evolved.

|||

Let us imagine a situation that thousands of requests come to web site at a particular time then if I open connection for every request then even the connection pool will be exausted. Second thing I want to clarify that thread does not know if the connection is shared or not so thread will never end up closing the connection. Thread will talk to some kind of manager which will keep connection internally and leave it open for every request.

I still don't know how the thread behaviour will be. If all the requests use the same connection then whether each request will be serialized (running one after previous one is finished) or each can run in parallel.

Any idea?

Thanks

|||

I will agree about thousands and speed problems when I will see some real numbers. So, you can try to simulate test load and watch how the system performs.

Also I want to notice, that connection is logical, it have state, which can be not only Open or Close, but also Executing, Fetching, Broken. What if one of your threads do fetching? All others must wait until it finish. And If you have 2 connections, then you can install 2 network adapters and they can split job. So I still think global connection is not a good thing to do because of scalability & reliability reasons.

Don't think of connections as of physical connections, you do not connect to some port in fact, instead you connect to client side layer, which will take care of real connection. You work with logical connections.

|||Thanks for the suggestions. I will see how it goes.