Cannot unload a collectible AssemblyLoadContext if System.Data.SqlClient is used

[stasgaonkar]

The Assembly we want to load (and unload) needs the ability to connect to a database for which we use Dapper. Dapper internally makes use of System.Data.SqlClient. We find that when we create such an assembly, then we are unable to Unload the collectible AssemblyLoadContext.

The sample source code is available at: https://github.com/stasgaonkar/DynamicLoading

Having followed the steps mentioned in https://docs.microsoft.com/en-us/dotnet/standard/assembly/unloadability#, the following is the output of gcroot command,

0:000> !dumpheap -type LoaderAllocator
         Address               MT     Size
000002022c46e1f0 00007fff74999410       48     
000002022c46e260 00007fff74999538       24     

Statistics:
              MT    Count    TotalSize Class Name
00007fff74999538        1           24 System.Reflection.LoaderAllocatorScout
00007fff74999410        1           48 System.Reflection.LoaderAllocator
Total 2 objects
0:000> !gcroot -all 0x000002022c46e1f0
HandleTable:
    000002022A9D12A0 (strong handle)
    -> 000002022C4E8C48 System.Data.SqlClient.TdsParserStateObjectNative
    -> 000002022C46E1F0 System.Reflection.LoaderAllocator

    000002022A9D12A8 (strong handle)
    -> 000002022C4E00F0 System.Data.SqlClient.TdsParserStateObjectNative
    -> 000002022C46E1F0 System.Reflection.LoaderAllocator

    000002022A9D12E0 (strong handle)
    -> 000002022C4B38C0 System.Data.SqlClient.TdsParserStateObjectNative
    -> 000002022C46E1F0 System.Reflection.LoaderAllocator

    000002022A9D15F8 (pinned handle)
    -> 000002023C461038 System.Object[]
    -> 000002022C475768 System.Threading.TimerQueue[]
    -> 000002022C475840 System.Threading.TimerQueue
    -> 000002022C4ABE88 System.Threading.TimerQueueTimer
    -> 000002022C475708 System.Threading.TimerQueueTimer
    -> 000002022C475580 System.Threading.TimerCallback
    -> 000002022C474D60 System.Data.SqlClient.SqlConnectionFactory
    -> 000002022C46E1F0 System.Reflection.LoaderAllocator

Found 4 roots.

So it seems that the TdsParserStateObjectNative is the culprit. What we have also noticed is that there are some "unmanaged/native DLL" that are used (sni.dll) internally by System.Data.SqlClient, possibly from within TdsParserStateObjectNative. Not sure if this is causing any issues.

Is something that we are attempting to do, incorrect?

[janvorli]

This is an issue in the System.Data.SqlClient. One thing is the TdsParserStateNativeObject that you've mentioned and the other is the timer callback (the last root in your gcroot output, my guess is that it is the _pruningTimer member of the DbConectionFactory, which is the base class of the SqlConnectionFactory). The System.Data.SqlClient will need to be modified to handle the AssemblyLoadContext.Unload event and perform cleanup to become unloadable.
The System.Data.SqlClient already has some cleanup code registered to AppDomain.Unload, so it seems that it could possibly be reused for AssemblyLoadContext.Unload handling.

[stasgaonkar]

thanks @janvorli - is this something that needs to be handled within library or can we do it outside. If this can be done from outside, could you point me to the "cleanup code" that you are referring?

[janvorli]

@stasgaonkar it is an interesting idea. You might be able to solve it by installing a handler for the AssemblyLoadContext.Unload in your code running inside of your AssemblyLoadContext and invoking the System.Data.SqlClient.SqlDependencyPerAppDomainDispatcher.UnloadEventHandler via reflection from there. I am not sure if that's going to be sufficient, but it seems it is worth trying.
See https://github.com/dotnet/samples/blob/master/core/tutorials/Unloading/Plugin/PluginClass.cs for an example on how to install the AssemblyLoadContext.Unload handler.

[AlbertoCe]

Hi,

I tried to put this code into the unloading method of the assemblycontext but with no luck

private static void OnPluginUnloadingRequested(AssemblyLoadContext obj)
{
            var assembly = obj.Assemblies.FirstOrDefault(x => x.FullName.Contains("Microsoft.Data.SqlClient", StringComparison.InvariantCultureIgnoreCase));
            var dispatcher = assembly.GetType("Microsoft.Data.SqlClient.SqlDependencyPerAppDomainDispatcher");
            var value = dispatcher.GetField("SingletonInstance", BindingFlags.NonPublic | BindingFlags.Static).GetValue(null);
            var method = dispatcher.GetMethod("UnloadEventHandler", BindingFlags.NonPublic | BindingFlags.Instance);
            method.Invoke(value, new object[] { null, null });
        }

@stasgaonkar have you gone any further on this ?

[xgnxs]

@AlbertoCe or @stasgaonkar, did either of you manage to figure out a solution? I'm having the same problem, where once I create a new SqlConnection (using System.Data.SqlClient) the plugin assembly I'm trying to create can no longer be unloaded. If I remove that line, I can unload the assembly just fine, although the plugin is useless without database access. I tried Microsoft.Data.SqlClient as well and had the same issue.

@janvorli do you have any suggestions? I have an AssemblyLoadContext.Unload event handler just like in the PluginClass.cs example, I just need to know what to put in it.

[AlbertoCe]

Hi @xgnxs
Unfortunately I have not made progress with this problem. I had to give up using netcore 3.1 and switch to the 4.8 framework.
I don't think it is easily solved nor when they will be able to solve it.

Let me know if you make any progress.

[grant77]

This worked for me:

var sqlClientAssembly = alc.Assemblies.SingleOrDefault(x => x.GetName().Name == "Microsoft.Data.SqlClient");
var sqlConnectionFactoryType = sqlClientAssembly?.GetType("Microsoft.Data.SqlClient.SqlConnectionFactory");
if (sqlConnectionFactoryType != null)
{
    var singletonInstance = sqlConnectionFactoryType.InvokeMember("SingletonInstance", BindingFlags.Public | BindingFlags.Static | BindingFlags.GetField, null, null, null) !;
    var baseType = sqlConnectionFactoryType.BaseType!;
    baseType.InvokeMember("ClearAllPools", BindingFlags.InvokeMethod, null, singletonInstance, null);
    var pruningTimer = (Timer)baseType.InvokeMember("_pruningTimer", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.GetField, null, singletonInstance, null) !;
    pruningTimer.Dispose();
}

[cheenamalhotra]

@stasgaonkar

Could you try solution posted by @grant77 if you're still facing this issue?

[Benjiinator]

@cheenamalhotra I am running into this same issue in .Net 5.0 using Microsoft.Data.SqlClient v2.1.1, the solution @grant77 provided does not appear to solve the issue for me.

Edit: as soon as i call the following my assembly becomes unloadable.
using (var sqlConn = new SqlConnection(connString))

[cheenamalhotra]

Could you provide us a repro app to investigate?

[Benjiinator]

@cheenamalhotra i have created a repro app here: Assembly Unloading with Microsoft.Data.SqlClient