Synchronize access to static variables in sql server SQLCLR

Question

I have written an assembly which is integrated in sql server, providing some stored procedures written in C#. The assembly has a readonly static variable holding some configuration data. This data is manipulated via stored procedures, which are also provided by the assembly. Obviously I have to synchronize access to this static variable. I tried to use

lock(someGuard)
{
    // ... access static configuration
}

inside my configuration class. But then I get a HostProtectionException, telling me, that the assembly has to run with full trust to do that. Is there a better way to do that?

Answer 1

There is actually an undocumented hack: decorate the class with the CompilerGenerated attribute. As with any undocumented workarounds, mileage may vary with future releases.

You shouldn't need this though, if the static is readonly then you can declare it readonly and the assembly will deploy fine, readonly statics are accepted in SAFE assemblies. And is truly readonly, the lock guard is also unnecessary.

If you cannot mark it readonly and remove the lock, it means is not readonly and you will be on moving sands territory. You can block SQL workers and have unpredictable results (hence the UNSAFE requirement). The CompilerGenerated trick should really be used with a lot of care, onyl if you understand perfectly the implications. The fact that you need a lock is a strong indicator your code is actually unsafe wrt to SQL and statics.

Answer 2

The only way around this restriction is to deploy the assembly as UNSAFE. Still, static shared data is against recommendations:

The programming model for managed code in SQL Server requires functions, procedures, and types which do not require the use of state held across multiple invocations or the sharing of state across multiple user sessions. Further, as described earlier, the presence of shared state can cause critical exceptions that impact the scalability and the reliability of the application.

Given these considerations, SQL Server disallows the use of static variables and static data members. For SAFE and EXTERNAL-ACCESS assemblies, SQL Server examines the metadata of the assembly at CREATE ASSEMBLY time, and fails the creation of such assemblies if it finds the use of static data members and variables.