Multithreaded caching in SQL CLR

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.

The accepted answer is not correct. Interlocked.CompareExchange is not an option since it requires a shared resource to update, and there is no way to create said static variable, in a SAFE Assembly, that can be updated.

There is (for the most part) no way to cache data across calls in a SAFE Assembly (nor should there be). The reason is that there is a single instance of the class (well, within the App Domain which is per-database per-owner) that is shared across all sessions. That behavior is, more often than not, highly undesirable.

However, I did say "for the most part" it was not possible. There is a way, though I am not sure if it is a bug or intended to be this way. I would err on the side of it being a bug since again, sharing a variable across sessions is a very precarious activity. Nonetheless, you can (do so at your own risk, AND this is not specifically thread safe, but might still work) modify static readonly collections. Yup. As in:

using Microsoft.SqlServer.Server;using System.Data.SqlTypes;using System.Collections;public class CachingStuff{    private static readonly Hashtable _KeyValuePairs = new Hashtable();    [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]    public static SqlString GetKVP(SqlString KeyToGet)    {        if (_KeyValuePairs.ContainsKey(KeyToGet.Value))        {            return _KeyValuePairs[KeyToGet.Value].ToString();        }        return SqlString.Null;    }    [SqlProcedure]    public static void SetKVP(SqlString KeyToSet, SqlString ValueToSet)    {        if (!_KeyValuePairs.ContainsKey(KeyToSet.Value))        {            _KeyValuePairs.Add(KeyToSet.Value, ValueToSet.Value);        }        return;    }    [SqlProcedure]    public static void UnsetKVP(SqlString KeyToUnset)    {        _KeyValuePairs.Remove(KeyToUnset.Value);        return;    }}

And running the above, with the database set as TRUSTWORTHY OFF and the assembly set to SAFE, we get:

EXEC dbo.SetKVP 'f', 'sdfdg';SELECT dbo.GetKVP('f'); -- sdfdgSELECT dbo.GetKVP('g'); -- NULLEXEC dbo.UnsetKVP 'f';SELECT dbo.GetKVP('f'); -- NULL

That all being said, there is probably a better way that is not SAFE but also not UNSAFE. Since the desire is to use memory for caching of repeatedly used values, why not set up a memcached or redis server and create SQLCLR functions to communicate with it? That would only require setting the assembly to EXTERNAL_ACCESS.

This way you don't have to worry about several issues:

• consuming a bunch of memory which could/should be used for queries.

• there is no automatic expiration of the data held in static variables. It exists until you remove it or the App Domain gets unloaded, which might not happen for a long time. But memcached and redis do allow for setting an expiration time.

• this is not explicitly thread safe. But cache servers are.

SQL Server locking functions sp_getapplock and sp_releaseapplock can be used in SAFE context. Employ them to protect an ordinary Dictionary and you have yourself a cache!

The price of locking this way is much worse than ordinary lock, but that may not be an issue if you are accessing your cache in a relatively coarsely-grained way.

--- UPDATE ---

The Interlocked.CompareExchange can be used on a field contained in a static instance. The static reference can be made readonly, but a field in the referenced object can still be mutable, and therefore usable by Interlocked.CompareExchange.

Both Interlocked.CompareExchange and static readonly are allowed in SAFE context. Performance is much better than sp_getapplock.