3

If I run the code below, a lot of connections to the database remain doing nothing. You can check the number of open connections by running:

SELECT COUNT(dbid) as TotalConnections FROM sys.sysprocesses WHERE dbid > 0

Or, if you want more detail run the following command. You'll see a lot of connections with a 'AWAITING COMMAND' state:

sp_who2

I would have assumed that the Codefluent Persistency context would be gone after the thread exited and closed the connection. How can I force Codefluent to close the connection in the pool?

public void TestThreads()
{
    for (var i = 0; i < 1000; i++)
    {
        var t = new Thread(() => StaticticThreadContainer.Test());
        t.Start();
    }
}

public static int Test()
{
   var p = CwObject.LoadByEntityKey("baf04c09-7415-497d-b3cd-00004266f503");
   return 1;
 }

I found out a bit more. If I call the following code before returning in the thread the connection is closed properly. Is this the way to go?

CodeFluentContext.Get(Compareware.Constants.ApplicationStoreName).Persistence.ResetConnection();
Julian
  • 33,915
  • 22
  • 119
  • 174
Peter de Bruijn
  • 792
  • 6
  • 22
  • Might this be due to some form of connection pooling? – Mark Larter Apr 13 '17 at 18:55
  • I think so, Codefluent uses one connection per thread. But why is the connection still there if the thread is gone? – Peter de Bruijn Apr 13 '17 at 19:00
  • Mind you I am unfamiliar with CodeFluent. But db connection pooling in general attempts to avoid the comparatively expensive (time) process of creating and opening db connection by keeping inactive connections around and reusing them. – Mark Larter Apr 13 '17 at 19:31
  • 1
    This answer seems to explain it exactly. http://stackoverflow.com/a/33984601/510091. – Mark Larter Apr 13 '17 at 19:33
  • The problem is that it does not seem to reuse the connections. If I run the code again another 1000 open connections are added. I know of the existence of the 'closeConnectionOnCompleteCommand' parameter but I don't want to use it. It is an application wide setting that forces Codefluent to close the connection after each command which will drastically slow down my application. Codefluent seems to handle connection pooling perfectly, only when starting a new threat it results in persistent connections... – Peter de Bruijn Apr 13 '17 at 19:41
  • closeConnectionOnCompleteCommand issues a connection.Close(), so the connection just goes back into the ADO.NET connection pool. It shouldn't affect performance. – Simon Mourier Apr 16 '17 at 05:55
  • It does not affect performance but eventually the connection pool is flooded. I get the following error: "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached." Even when max connections is at 5000. – Peter de Bruijn Apr 17 '17 at 06:55

2 Answers2

0

CodeFluent Entities stores the context per thread using the LocalDataStoreSlot. When a thread terminates, the thread local data are not automatically disposed. So, before terminating the thread you must call the Dispose method:

CodeFluent.Runtime.CodeFluentContext.GetExisting(Constants.MyStoreName)?.Persistence.Dispose(true);
meziantou
  • 20,589
  • 7
  • 64
  • 83
  • I found that 'Constants.SoftFluent_RowShareStoreName' in our case was %ApplicationNamespace%.Constants.%ApplicationNamespace%StoreName – Peter de Bruijn Apr 14 '17 at 10:58
  • I am afraid this does not do the trick. If I add the following line in my testcode before returning I still get 1000 extra open connections. CodeFluent.Runtime.CodeFluentContext.GetExisting(Compareware.Constants.ComparewareStoreName)?.Dispose(); – Peter de Bruijn Apr 14 '17 at 11:23
  • The only thing that does the trick is CodeFluent.Runtime.CodeFluentContext.GetExisting(Compareware.Constants.ComparewareStoreName)?.Persistence.ResetConnection(); Maybe the .Dispose method is incomplete? – Peter de Bruijn Apr 14 '17 at 11:35
  • In fact, you must use another overload of the `Dispose` method. I've updated my answer. The `ResetConnection` will do the job. However, you should not use `Get` but `GetExisting` to not create a context if there is no context for the current thread. – meziantou Apr 14 '17 at 11:47
  • There is no Overload of the Dispose with 1 boolean argument... Am I missing something? I am uising build 708. – Peter de Bruijn Apr 14 '17 at 14:28
  • Ok, I'll accept this as the answer. Dispite the fact that the overload does not exist. CodeFluent.Runtime.CodeFluentContext.GetExisting(Constants.MyStoreName)?.Persistence.Dispose(true);CodeFluent.Runtime.CodeFluentContext.GetExisting(Constants.MyStoreName)?.Persistence.Dispose(true); – Peter de Bruijn Apr 24 '17 at 09:17
0

Meziantou answered my question but the mentioned overload does not exist. The following code should do the trick:

CodeFluent.Runtime.CodeFluentContext.GetExisting(Constants.MyStoreName)?.Persis‌​tence.ResetConnectio‌​n();
CodeFluent.Runtime.CodeFluentContext.GetExisting(Constants.MyStoreName)?.Persistence.Dispose();
Peter de Bruijn
  • 792
  • 6
  • 22