c# - Multithreaded caching in SQL CLR

Question

Are there any multithreaded caching mechanisms that will work in a SQL CLR function without requiring the assembly to be registered as "unsafe"?

As also described in this post, simply using a lock statement will throw an exception on a safe assembly:

System.Security.HostProtectionException: 
Attempted to perform an operation that was forbidden by the CLR host.

The protected resources (only available with full trust) were: All
The demanded resources were: Synchronization, ExternalThreading

I want any calls to my functions to all use the same internal cache, in a thread-safe manner so that many operations can do cache reads and writes simultaneously. Essentially - I need a ConcurrentDictionary that will work in a SQLCLR "safe" assembly. Unfortunately, using ConcurrentDictionary itself gives the same exception as above.

Is there something built-in to SQLCLR or SQL Server to handle this? Or am I misunderstanding the threading model of SQLCLR?

I have read as much as I can find about the security restrictions of SQLCLR. In particular, the following articles may be useful to understand what I am talking about:

• SQL Server CLR Integration Part 1: Security
• Deploy/Use assemblies which require Unsafe/External Access with CLR and T-SQL

This code will ultimately be part of a library that is distributed to others, so I really don't want to be required to run it as "unsafe".

One option that I am considering (brought up in comments below by Spender) is to reach out to tempdb from within the SQLCLR code and use that as a cache instead. But I'm not quite sure exactly how to do that. I'm also not sure if it will be anywhere near as performant as an in-memory cache. See update below.

I am interested in any other alternatives that might be available. Thanks.

Example

The code below uses a static concurrent dictionary as a cache and accesses that cache via SQL CLR user-defined functions. All calls to the functions will work with the same cache. But this will not work unless the assembly is registered as "unsafe".

public class UserDefinedFunctions
{
    private static readonly ConcurrentDictionary<string,string> Cache =
                            new ConcurrentDictionary<string, string>();

    [SqlFunction]
    public static SqlString GetFromCache(string key)
    {
        string value;
        if (Cache.TryGetValue(key, out value))
            return new SqlString(value);
        return SqlString.Null;
    }

    [SqlProcedure]
    public static void AddToCache(string key, string value)
    {
        Cache.TryAdd(key, value);
    }
}

These are in an assembly called SqlClrTest, and and use the following SQL wrappers:

CREATE FUNCTION [dbo].[GetFromCache](@key nvarchar(4000))
RETURNS nvarchar(4000) WITH EXECUTE AS CALLER
AS EXTERNAL NAME [SqlClrTest].[SqlClrTest.UserDefinedFunctions].[GetFromCache]
GO

CREATE PROCEDURE [dbo].[AddToCache](@key nvarchar(4000), @value nvarchar(4000))
WITH EXECUTE AS CALLER
AS EXTERNAL NAME [SqlClrTest].[SqlClrTest.UserDefinedFunctions].[AddToCache]
GO

Then they are used in the database like this:

EXEC dbo.AddToCache 'foo', 'bar'

SELECT dbo.GetFromCache('foo')

UPDATE

I figured out how to access the database from SQLCLR using the Context Connection. The code in this Gist shows both the ConcurrentDictionary approach, and the tempdb approach. I then ran some tests, with the following results measured from client statistics (average of 10 trials):

Concurrent Dictionary Cache
10,000 Writes: 363ms
10,000 Reads :  81ms

TempDB Cache
10,000 Writes: 3546ms
10,000 Reads : 1199ms

So that throws out the idea of using a tempdb table. Is there really nothing else I can try?

Answer 1

I've added a comment that says something similar, but I'm going to put it here as an answer instead, because I think it might need some background.

ConcurrentDictionary, as you've correctly pointed out, requires UNSAFE ultimately because it uses thread synchronisation primitives beyond even lock - this explicitly requires access to lower-level OS resources, and therefore requires the code fishing outside of the SQL hosting environment.

So the only way you can get a solution that doesn't require UNSAFE, is to use one which doesn't use any locks or other thread synchronisation primitives. However, if the underlying structure is a .Net Dictionary then the only truly safe way to share it across multiple threads is to use Lock or an Interlocked.CompareExchange (see here) with a spin wait. I can't seem to find any information on whether the latter is allowed under the SAFE permission set, but my guess is that it's not.

I'd also be questioning the validity of applying a CLR-based solution to this problem inside a database engine, whose indexing-and-lookup capability is likely to be far in excess of any hosted CLR solution.