1

We have a ServiceStack service (API) which provides HTTP endpoints hosted using AppSelfHostBase. Those services later query database using ServiceStack.OrmLite.MySql. All methods are implemented using async / await pattern. Database connections are registered manually to Funq with Request reuse scope, and injected to the property of base DAL class.

This all works fine when this service is accessed only by HTTP requests. We have another Windows service which calls this API. Since they could be hosted on the same server, we’ve implemented local IRestClientAsync for wrapping service calls, so the API service methods could be loaded to the Windows service, and accessed more efficiently (eg 1200 req/sec compared to 400 req/sec). This Windows service has a lot of threads running at the same time. By doing this, we broke the Request lifecycle and are getting

“There is already an open DataReader associated with this Connection which must be closed first.”

error. We tried handling this manually using custom connection providers separating connections through threads using ThreadLocal and CallContext. This didn’t work all the time. We tried handling Request lifecycle by calling OnBeginRequest(null); and OnEndRequest(); manually, but the performance was bad (close to HTTP calls) and also, got “open DataReader” errors.

We are using RequestContext.UseThreadStatic option, since the threads are instantiated from Quartz .NET job.

What could be the best solution for managing database connections? Can we make the current solution working reliably?

Марко Пађен
  • 13
  • 3

1 Answers1

0

First thing I would do is not bother using the Async API's with MySql since it's not truly asynchronous as it ends up creating new threads behind the scenes to fake asynchrony which makes it even less efficient then using the Sync API's. You also can't use multiple readers with the same db connection which will end up throwing this exception.

So I'd firstly go back to using sync API's with MySql, if it's still an issue use transient scope (i.e. no re-use) instead of Request scope and let the db connection pooling do its job. Request scope holds on to the connection longer, taking up more resources than necessary.

mythz
  • 141,670
  • 29
  • 246
  • 390
  • Thanks for the answer! Which database would you recommend to use (on Linux) in this case (with async .NET client)? Also, why is performance difference so big? Can we, somehow, skip some HTTP life-cycle steps to gain more performance? – Марко Пађен Dec 08 '15 at 09:54
  • @МаркоПађен AFAIK only SQL Server has a stable true async ADO.NET provider. Looks like Npgsql (PostgreSQL) now shipped async in v3 packages, since its a major upgrade it will take some time for ORM's to upgrade to it. Don't know what you mean by skipping HTTP life-cycle. But for best performance you should cache aggressively where possible. – mythz Dec 08 '15 at 11:47
  • By skipping http life-cycle I meant making request initialization and release part taking less time. We have one service call which can perform at 400 requests per second when accessed through http (self hosted). The same method can get up to 1200 requests per second, if it's called by calling `Post(SomeRequest request)` method of `SomeService : Service` object. I understand there's lot in deserializing request, performing filters and other checks, plugins, funq stuff... but was wondering if somehow we could utilize the proccess? – Марко Пађен Dec 08 '15 at 13:47
  • @МаркоПађен You can avoid deserialization/http by calling `ServiceController.Execute()` on AppHost/HostContext to execute a Request DTO or [use ResolveService](http://stackoverflow.com/a/23809669/85785) to call the service method directly. – mythz Dec 08 '15 at 13:58