Managing Entity Framework Connectivity from Streams

Question

In my service with multiple Windows threads, I open connections to db in each thread, after which I delete these connections, although the problem is that some of them remain closed on db when I execute a query in the sys.sysprocesses table.

I conducted two unit tests and saw some strange behavior, first I started a cycle with 100 tasks, and in each of them I opened a new connection. After they are finished (I see through WaitAll ()), I see that some connections are still hanging in db. In the second unit test, when I run several open / dispose without parallel execution, it uses them perfectly and there are no connections in my db.

The problem is that in my Windows service these dangling connections add up and, in the end, there is no more room for new connections, and db becomes unusable for the user.

Here are the codes, at first parallel, and the second not:

[TestMethod] public void TestMultiThreadedAccessToExplicitObjectContext() { int taskSize = 100; List<Task> taskList = new List<Task>(); int goodSources = 0; for (int i = 0; i < taskSize; i++) { Task newTask = Task.Factory.StartNew(() => { System.Data.Objects.ObjectContext objectContext = new PersoniteEntities(); objectContext.Connection.Open(); objectContext.Dispose(); Thread.Sleep(1200); }); taskList.Add(newTask); } Task.WaitAll(taskList.ToArray()); GC.Collect(); total += goodSources; } [TestMethod] public void TestMultiThreadedAccessToExplicitObjectContextInline() { System.Data.Objects.ObjectContext objectContext1 = new PersoniteEntities(); objectContext1.Connection.Open(); objectContext1.Dispose(); System.Data.Objects.ObjectContext objectContext2 = new PersoniteEntities(); objectContext2.Connection.Open(); objectContext2.Dispose(); System.Data.Objects.ObjectContext objectContext3 = new PersoniteEntities(); objectContext3.Connection.Open(); objectContext3.Dispose(); System.Data.Objects.ObjectContext objectContext4 = new PersoniteEntities(); objectContext4.Connection.Open(); objectContext4.Dispose(); System.Data.Objects.ObjectContext objectContext5 = new PersoniteEntities(); objectContext5.Connection.Open(); objectContext5.Dispose(); } 

thanks

Answer 1

When pooling, each workflow (or thread in this case) has its own connection pool. Therefore, if you have Max Pool Size installed to say “3” and 100x streams appear, you can have 300 connections.

Additional information from someone who had a similar problem:

ODP.NET Connection Pool Parameters

And some kind of documentation from MS on how connection pools work, and an explanation of fragmentation of the connection pool:

MSDN - SQL Server Connection Pool (ADO.NET)

Solution 1

Enable pooling in the connection string

MSDN - connection string - association

When the value of this key is set to true, all newly created connections will be added to the pool when the application is closed. In the next attempt to open the same connection, this connection will be from the pool. Connections are considered the same if they have the same connection string. Different connections have different connection strings.

The value of this key can be "true", "false", "yes" or "no".

For instance:

 <add name="AppEntities" connectionString=" metadata=res://*/App_Code.AppModel.csdl|res://*/App_Code.AppModel.ssdl|res://*/App_Code.AppModel.msl; provider=System.Data.SqlClient; provider connection string=&quot; Data Source=.; Initial Catalog=Example; Integrated Security=True; Pooling=False; MultipleActiveResultSets=True &quot;" providerName="System.Data.EntityClient" /> 

Decision 2

It seems like a workaround related to semaphores may be generated, described in detail here:

Combining database pools with a multi-threaded service

Answer 2

Assuming this is all the code you use in the test, you probably aren't looking at hanged connections. This is normal behavior with a connection pool.

When using a single stream .net can reuse the same physical connection that was already closed from the previous command. When using multiple threads, it must open several physical connections to search for parallel queries.

Ps. You should use the using statement instead of the manual. Specify, because you risk linking open longer than expected if the material throws an exception after opening. And so you can contact the connections.