In our ASP.NET application we've implemented a data layer built on top of MySQL, and which uses Connector/NET in order to implement the Entity Framework.
This works perfectly well for most of our CRUD actions, but when we run an INSERT statement with several hundred records it throws a "Too many connections" exception.
While I accept that we are making a lot of separate read/write calls (we query for duplicate entries prior to every insert, plus there's an audit log which is also written to using a separate query for every insert) but shouldn't the connection pool take care of this?
Also, in this similar thread the OP was told to use using statements which I fully agree with, and in our case every query is already wrapped in a using statement which I assume is managing the connections in an efficient manner: i.e., not disposing of them if another request has been queued.
And anyway, the connect timeout
is currently 50, which is already pretty small, right? Moving it up to 500 made no difference, while moving it down to 10 gave me about 50% more inserts before the same exception was thrown.
So, given that every instance of the DbContext is wrapped in a using statement, why are we getting a "Too many connections" exception?
UPDATE: We found that the root of our problem was actually caused by one of our repositories opening a DB connection in its constructor, even though this connection was never used. Sorry guys, this turned out to be a red herring. Thanks for your suggestions.
Not exactly the answer you are looking for... but:
Is there a particular reason why you aren't using the same connection for all your actions?
As an example:
If you are performing 50 inserts in a row and by consequence 50 queries (one for each insert) using the same connection would greatly improve your system performance.