Why would it be a bad idea to have database connection open between client requests?

Question

1) Book I’m reading argues that connections shouldn’t be opened between client requests, since they are a finite resource.

I realize that max pool size can quickly be reached and thus any further attempts to open a connection will be queued until connection becomes available and for that reason it would be imperative that we release connection as soon as possible.

But assuming all request will open connection to the same DB, then I’m not sure how having a connection open between two client requests would be any less efficient than having each request first acquiring a connection from connection pool and later returning that object to connection pool?

2) Book also recommends that when database code is encapsulated in a dedicated data access class, then method M opening a database connection should also close that connection.

a) I assume one reason why M should also close it, is because if method M opening the connection doesn’t also close it, but instead this connection object is used inside several methods, then it’s more likely that a programmer will forget to close it.

b) Are there any other reasons why a method opening the connection should also close it?

thanx

EDIT:

If during the processing of a web request you don’t close the connection, then same connection can’t be used “directly” by the next request, but instead it first needs to be returned to connection pool, and only then can it be reused? If that is the case, I can see how we don’t gain anything by leaving the connection open during the requests?!

E.g. Transaction 1 reads a row from a table, then the user is thinking long, before modifying the data. During that time, transaction B reads and then updates the same row: transaction A now has stale data! Now if the user finally modifies the data and tx A commits it, the modifications made by tx B may get lost entirely: this is called lost update.

If my above assumption is correct,then how can user U that initiated transaction 1 ( thus established a database connection 1 ) during a first request, get a reference to same database connection 1 ( and thus a "reference" to transaction 1 ) during the second request(aka postback)? Namely, wasn't connection object returned to the connection pool when server finished processing user U's first request?

Answer 1

Hey,

Yes, you never know how long the connection will be open, as the request is initiated by the user... also, what happens if the request gets lost (user closes browser), too easy to have connections open infinitely... hard to have a cleanup process if you do do that.

HTH.

Answer 2

The points in 2) can be solved by wrapping the opening and closing of the connection in a smart manager object. Methods using the database would call this manager to get a connection and to give it back. The manager would count how many methods are using the connection, how long ago it was, etc. and make/close connnections accordingly.

Answer 3

Yeah, with ASP.NET and SQL connections, there are very few scenarios where it makes sense to not use the connection pool. One of the most common scenarios where the connection pooling causes issues is when you change contexts (from an data access/authorization perspective). Almost think of the connection pool as a connection load balancer for you that is going to be more efficient than anything you're going to code up until you learn a lot and then write a lot of code.

A couple links on the topic that will explain it much better than I could:

first link

second link