What is recommended lifetime of Database object in a web application?

Question

I consider using PetaPoco in a conventional web app. Saying conventional I mean

handling requests in separate threads from a pool
requests are quick: no long polling, streaming, etc
but not necessarily ASP.NET, it may be for example Nancy

What should be the lifetime of the Database object, and that of the underlying DbConnection?

globally static (I guess no, due to this answer)
per-thread static, [ThreadStatic]
per-request
unit of work style: create and dispose as soon as the task is finished

I especially appreciate answers from those who run production apps with PetaPoco.

You *definitely* should avoid `ThreadStatic` with ASP.NET because of thread agility. — Lucas Trzesniewski, Jul 09 '14 at 19:10

score 2 · Accepted Answer · answered Jul 10 '14 at 17:36

2

Per request is the way to go

globally static: you guess right. Will bring problems accessing the same recordset and others. Definitely no.
per-thread static. You should avoid [ThreadStatic] in Asp.net
per-request: works great
unit of work style: It works great also (not my preference)

answered Jul 10 '14 at 17:36

Eduardo Molteni

38,786
23
141
206

@Eduardo Does doing on per-request basis limit the effect of connection pooling? – Matthew Oct 15 '16 at 01:00
@Matt In fact it fully uses the connection pooling to give new connections without performance penalties – Eduardo Molteni Oct 19 '16 at 19:42

score 1 · Answer 2 · answered Jul 09 '14 at 19:30

1

You need one object per thread, but you shouldn't associate the object with the thread itself, because handling a request can start in one thread and finish in another thread.

Using one object per request would normally be the best, at least for quick request handling. However that is not always simple. If you risk leaving objects without disposing them (for example if there is an exception), it's better to use one object for a limited task where you can make sure that it is disposed properly.

answered Jul 09 '14 at 19:30

Guffa

687,336
108
737
1,005

+1 for non-disposed objects risk. Can you elaborate more on "handling a request can start in one thread and finish in another thread". When is this possible? – amartynov Jul 11 '14 at 11:07
1

@amartynov: This is called thread switching or thread agility (for further searching). Here is a blog post that I found giving a clear example: http://blog.idm.fr/2010/03/aspnet-thread-agility-or-why-threadstatic-should-not-be-used.html – Guffa Jul 11 '14 at 12:18

score 1 · Answer 3 · edited May 23 '17 at 11:50

1

Database is a cheap object to create, so I just create it as needed. I use PetaPoco (or more specifically AsyncPoco so I'm not tying up threads during I/O bound work) in a busy web app and this works great for me. Others do prefer per-request though, and that should also work well.

edited May 23 '17 at 11:50

Community

1
1

answered Jul 09 '14 at 21:00

Todd Menier

37,557
17
150
173

What is recommended lifetime of Database object in a web application?

3 Answers3