using the same connection

[sawatdee]

For a web application connecting to a database, is there any compelling reason why any of the requests would need a separate database connection to run their queries, or is it better to force them all to use the existing database connection if there is one? Is there any way that using the same database connection could affect scalability? Does anyone know of any good web sites or books that discuss this topic in detail?

[JavaServlet]

Most efficient way to connect to database is to use Connection pooling. After the connection is created it is placed in a pool and used again so a new one doesnt have to be created. A new one is only created if all the connections are used.

[sawatdee]

I guess I don't understand the advantage of having multiple connections. If you only have one database server running, then it can handle one query at a time. So whether they all come from the same connection or from multiple connections, a bottleneck exists in the fact that the same server application has to handle them all. And creating multiple connections takes a small amount of time. So what is the benefit of having more than one?

If you implement a connection pool and create a new connection only when all the others are used, then there must be a maximum number beyond which you would have to start sharing or else refuse connections. This sounds like a semaphore to me.

[JavaServlet]

Web users connect and disconnect frequently with requests for data access that can grow very high with large datasets. Connection pooling increases performance for web apps where they sometimes share connections among hundreds of users within the same transaction. After the request is satisfied and the response is returned to the user, the resource returns the connection to the pool for reuse and eliminates disconnection overhead.