What is the SQL Server CLR Integration Life Cycle?

Question

How are CLR (.NET) objects managed in SQL Server?

The entry point to any CLR code from SQL Server is a static method. Typically you'll only create objects that exist within the scope of that method. However, you could conceivably store references to objects in static members, letting them escape the method call scope. If SQL Server retains these objects in memory across multiple stored procedure/function calls, then they could be useful for caching applications -- although they'd be more dangerous too.

How does SQL Server treat this? Does it even allow (non-method) static members? If so, how long does it retain them in memory? Does it garbage collect everything after every CLR call? How does it handle concurrency?

Answer 1

From the C# Specification 3.0 (5.1.1)

A static variable comes into existence before execution of the static constructor (§10.12) for its containing type, and ceases to exist when the associated application domain ceases to exist.

Surely it's not going to shut down the whole application domain after each call as it would be a bit inefficient. So yes, these static objects remain there as long as the database is not stopped or restarted.

Answer 2

Here's some info that I've found.

Troubles with shared state and anonymous delegates in SQLCLR

Not only is shared state not allowed in a non-UNSAFE assembly, but anonymous delegates (unfortunately) trigger this "shared state" restriction.

Answer 3

SQL Server allows static readonly members if assembly is deployed with Unsafe permission level.

Practically objects are retained in memory until SQL service is stopped/restarted.

Regarding concurrency, your object and methods should be thread-safe as everywhere else.

For example:

public static class MyCLRClass
{
    private static readonly ReaderWriterLock rwlock = new ReaderWriterLock();
    private static readonly ArrayList list = new ArrayList();

    private static void AddToList(object obj)
    {
        rwlock.AcquireWriterLock(1000);
        try
        {
            list.Add(obj);
        }
        finally
        {
            rwlock.ReleaseLock();
        }
    }

    [SqlProcedure(Name="MyCLRProc")]
    public static void MyCLRProc()
    {
        rwlock.AcquireReaderLock(1000);
        try
        {
            SqlContext.Pipe.Send(string.Format("items in list: {0}", list.Count));
        }
        finally
        {
            rwlock.ReleaseLock();
        }
    }
}

I use such things in SQL CLR and it works.

Answer 4

In "Pro SQL Server 2005 Assemblies" by Robin Dewson and Julian Skinner, it says that "Assemblies loaded into a database, like other database objects, are owned by a database user. All assemblies owned by the same user in the same database will run within the same AppDomain. Assemblies owned by a different user will run within a separate AppDomain."

What this tells me is that if you're working with a single database and all the assemblies you load in with the CREATE ASSEMBLY statement have the same owner, then your assemblies will all run in the same app domain. However, being in the same AppDomain does not mean using the same code-base, so even the same dll can be loaded into the same application domains multiple times, and it's types will not match up, even though they have the same name. When same-named types are from different code-bases, their static variables will be different instances as well.

The only way I can see to use static variables safely in SQL Server CLR environment with multiple assemblies is to actually just use a single assembly. You can use the ILMerge utility with the "UnionMerge" option to pack all your assemblies into one and merge classes with the same name. This should guarantee that for a given database, in your sole assembly, your static variables will work just like they would in a stand-alone application. I think it's safe to assume the application domain isn't unloaded and reloaded at every request, but you can't depend on it never being unloaded, since that will happen whenever there is an unhandled error (at least if it's running in unsafe mode).