Is it acceptable to keep a db connection open for the life of the page?

Question

Everybody knows that you should close a connection immediately after you finish using it.

Due to a flaw in my domain object model design, I've had to leave the connection open for the full page life cycle. Essentially, I have a Just In Time property which opens a connection on first call, and then on Page.Unload (..) it would check if a db connection was ever open, and then close it if it was. Since it only takes a second, I’ve had the opinion its not too much different than closing it immediately.

Is this ok? Or should it still be closed immediately after every single use?

Thanks in advance.

Hopeh

Do you mean the connection is kept open between post-backs? – Impatiens

No, the db connection is open in the Page.Load(...) or later, and then closed in the Page.Unload(...) event. – Hopeh

Answer 1

It's not ideal but I wouldn't re-write my application over it. Unless your page is doing a large amount of time-consuming work in various methods, the whole page lifecycle should execute quickly. In practice it may just mean that your connection object is open a few milliseconds longer than it would have been otherwise. That might be significant in some scenarios, but it doesn't sound like it would be in your case.

Impatiens

Page.Load(..) open, Page.Unload(..) close. – Hopeh

Purists are a lot like instructors at West Point (the US Army academy) - much of what they say is quickly forgotten when the bullets start flying. – Impatiens

Answer 2

No, it is not OK.

If your application will ever need to grow or scale, you'll want to fix this issue. By holding that connection open you're reducing your ability to scale. Keep in mind that open connections take up memory on the server, memory on the client, hold open locks, etc.

Ossuary

Thanks Scott, this particular app will never scale beyond a 100 uers. – Hopeh

Answer 3

What if you page crashes before reaching the Page.Unload event? You will have a opened connection. For me it is better to always close the connection as soon as possible.

Wylie

Answer 4

Yes, it is ok.

Closing the connection as soon as you can is a best practice for preventing orphan open connections, but if you are sure that the connection is being close, there is nothing wrong with that.

Deviled

Never hold a connection open. The application will never scale because you will be using resources when you don't need them. Connection pooling is something that got figured out a long time ago. – Savoyard

Come on! This is a real app, with a real problem, in a real world, and he says that the app will never grow more than 100 users. – Deviled

Answer 5

Every decent ASP.NET app uses connection pooling nowadays, and a pool is basically a bunch of open connections. In your case that would mean that the connection you're holding on to is "occupied" and can't be used to serve other requests.

As far as I see it would be a scalability issue depending on the amount of time your page needs to do work/render. If you expect only 100 users, like you say, then probably it's not an issue - unless it's 100 req/sec of course.

From the technological perspective it's OK. As far as I remember most client-server applications (web- and non-web), including classic ASP-code used to work like that, e.g you declare one connection for the entire page and work with it.

Personally

Answer 6

page crashes? this is what using and finally are for

that said, for the sake of DB performance (i.e. scaling)* it's best to keep connections open as short a period as possible allowing only that you don't want to open close open close open close for rapidly sequential and predictable work

* I was told this by a mentor early in my career, I must say I've not actually tested this myself but it sounds right theoretically

Greatcoat

Connection pooling will help with the scenario where you open & close many connections. – Ossuary

I believe the problem for John is that his architecture for whatever reason precludes using using/finally because open is in PageLoad and PageUnload. – Laky

But I'd say that's a fundamental design problem - it contradicts keeping connection duration as short as possible – Greatcoat

Answer 7

Of course you can keep them open, but no no. Close it after use in finally blocks. A fair trade off from "after every single use" is to close it after every block of use, if you're apt to run a stored proc, update a column, then delete some other row, you could open/close around those three operations, presuming they're all wrapped in a try/catch/finally.

Lancewood

"presuming they're all wrapped in a try/catch/finally" - or more likely and probably better, a try/finally or using statement. – Susa

Answer 8

You should certainly keep the connection open across the lifetime of the page, if you're doing multiple queries during it. Generally, one re-uses connections across many pages, actually.

Iny

Answer 9

I think a better question with much more informed and productive feedback would be possibly providing some snippets of what you're doing (code) and expanding on the reasons why you've made this choice. There is most likely a better solution that doesn't require keeping the connection open so long, but at least, for pragmatic reasons, you could get some feedback on whether it's worth revamping.

In future, you definitely want to move away from data access in your code-behind.

Laky

Answer 10

I find it convenient to keep the connection open when using ORM (Open Session in View) so that after an initial eager fetch, other data can be lazily loaded as needed. This works well when page response times are reasonable so as not to tie up connections.

Resurrectionism