Net core DbContextPool vs AddDbContextPool and more

Question

I am trying to consume a Kafka queue at a high rate of processing and store the result in MySql using EF from .net core 2.1 I tried to use both AddDbContext and AddDbContextPool and I ran in both scenarios into problems.

1) When using AddDbContext the only way I was successfully was to scope it transient so you get a new DataContext instance every time I need to call the data insert. My queue is large and I end up having too many connections to mqSQL server so ultimately I start getting tons of timeout errors.

I know I can add options to retry on transient errors (and timeout is one of them) but I am mostly interested in how to reduce the number of DataContext instances to number that would not hammer the database. This brought me to the next attempt

2) When using AddDbContextPool I wasn't able to set the scope to transient couldn't find the syntax! Is there one? not being able to get a new instance for every call I get another breed of weird errors that usually would solve with a transient lifespan

An attempt was made to use the context while it is being configured. A DbContext instance cannot be used inside OnConfiguring since it is still being configured at this point. This can happen if a second operation is started on this context before a previous operation completed. Any instance members are not guaranteed to be thread safe.

Also to my understanding the poolsize parameter in AddDbContextPool is just setting up the cache size for DbContext objects to be reused and by no means is preventing the number of total connections. I would love when the pool is saturated to be able to block the next "get DBContext" call until one instance becomes available.

So my question to the community is how to solve this problem? I would love to reduce the number of DbContext instances to a fix number e.g. 10 and also have them cached. I would still configure the options to allow for retries but again, those timeouts will occur for external reasons and not because my code is creating hundreds of instances that are all try to save tiny messages to the database.

Answer 1

I wasn't able to set the scope to transient couldn't find the syntax! Is there one?

No, clearly because the lifetime of DbContexts is controlled by cache.

Error message you get is caused by accessing the same instance of your DbContext from multiple threads because DbContext class is not thread safe. In fact it means that your DbContext pooling doesn't work properly, probably because you are not creating a scope when consuming a message from your message queue, hence DbContext instance is shared across multiple threads. This scope is created under the cover in MVC when HTTP request arrives, but in other type of application you have to create it by yourself.

Note there is also a difference between DbContext pooling and connection pooling. If you ever need to control the number of connections from your application, you should use connection pooling.

How to solve this problem?

I wouldn't involve context pooling at all, instead I would rather set up some throttling mechanism to control a number of threads consuming from your queue.